sqlcompose 0.0.2

Composition of linked SQL files

sqlcompose: Composition of linked SQL files

sqlcompose allows you to compose sql files from multiple files by introducing INCLUDE keywords. The SQL output is composed as CTE's or Common Table Expressions.

Examples

Execute the script directly:

sqlcompose query.sql

sqlcompose 'select * from $INCLUDE(included-query1.sql)' # on linux
sqlcompose "select * from $INCLUDE(included-query1.sql)" # on windows

Import it in another script:

from sqlcompose import load, loads
# method 1 : loading from a file
sql1 = load("query.sql")

# method 2 : loading from an SQL string
sql2 = loads("""
    select *
      from dataset.table main
inner join $INCLUDE(other.sql) other
        on other.field = main.field
  """)

Preparing SQL scripts

Insert a $INCLUDE(filename) where the reference to the file should be in the resulting SQL, keeping in mind that references are loaded relative to the file loaded or the current working dir in case of an SQL string.

--main-query.sql
select * from $INCLUDE(includes\included-query2.sql)

--included-query1.sql
select 1 as test

--included-query2.sql
select * from $INCLUDE(included-query1.sql)
union all
select * from $INCLUDE(nested\included-query3.sql)

--nested\included-query3.sql
select 1 as test

Which outputs:

WITH Q_1_1 AS (
  WITH Q_2_1 AS (
    --includes\included-query1.sql
    select 1 as test
  ), Q_2_2 AS (
    --includes\nested\included-query3.sql
    select 1 as test
  ), Q_2 AS (
    --includes\included-query2.sql
    select * from Q_2_1
    union all
    select * from Q_2_2
  )
  SELECT * FROM Q_2
), Q_1 AS (
  --test\main-query.sql
  select * from Q_1_1
)
SELECT * FROM Q_1