querpy - to make building SQL strings in Python as painless as possible

The Query class is intended to provide a high level interface for building/editing SQL query strings.

This library should now support python 3.

Example usage:

>>>fromquerpyimportQuery>>>new_query=Query()
>>>new_query.f+='ex_db.dbo.ex_table tbl'>>>new_query.s+= ['col1', 'col2', 'col3'] # can take lists>>>new_query.s+='col4'# can take single strings>>>new_query.w+='col1 = 1'# can also take a list (separated by AND)>>>new_query.w|='col2 IS NULL'# handles &= and |= operators>>>printnew_querySELECTcol1,
col2,
col3,
col4FROMex_db.dbo.ex_tabletblWHEREcol1=1ORcol2ISNULL>>>new_query# also prints full querySELECTcol1,
col2,
col3,
col4FROMex_db.dbo.ex_tabletblWHEREcol1=1ORcol2ISNULL

The Query class avoids redundancy for similar queries by allowing you to modify a single component at a time:

>>>new_query.s.clear() # clear SELECT component>>>new_query.s+='col1'>>>new_querySELECTcol1FROMex_db.dbo.ex_tabletblWHEREcol1=1ORcol2ISNULL

Another way to edit the SELECT clause is to use indexing:

>>>new_query.s[0] ='col2'>>>printnew_query.s# printing the component shows indicesindex: item0: 'col2'

Suppose you want to extend your query by joining to another table and adding columns from this table:

>>>new_query.j+='ex_db.dbo.new_tbl nt ON tbl.id = nt.id'>>>new_query.s+='nt.id'>>>new_querySELECTcol1,
nt.idFROMex_db.dbo.ex_tabletblJOINex_db.dbo.new_tblntONtbl.id=nt.idWHEREcol1=1ORcol2ISNULL

While this works, we are returning to the land of long strings. We can do the same thing (n.b. we'll LEFT JOIN this time) using the Query.build_join helper function to make the join step more readable and modular:

>>>new_query.j.clear()
>>>new_query.join_type='LEFT'>>>new_query.j+=Query.build_join('ex_db.dbo.new_tbl nt', 'tbl.id', 'nt.id', 'tbl.city', 'nt.city')
>>>new_query.join_type=''# set back to regular join>>>new_querySELECTcol1,
nt.idFROMex_db.dbo.ex_tabletblLEFTJOINex_db.dbo.new_tblntONtbl.id=nt.idANDtbl.city=nt.cityWHEREcol1=1ORcol2ISNULL

When your query string is ready to be passed to the function that will execute the query, simply pass it using statement (without the pretty print fluff):

>>>new_query.statementSELECTcol2, nt.idFROMex_db.dbo.ex_tabletblLEFTJOINex_db.dbo.new_tblntONtbl.id=nt.idANDtbl.city=nt.cityWHEREcol1=1ORcol2ISNULL

You can also prepend a CREATE TABLE <> AS to the SQL

>>>new_query.j+='ex_db.dbo.new_tbl nt ON tbl.id = nt.id'>>>new_query.s+='nt.id'>>>new_query.g+='col1'>>>new_query.l+=' 10, 100'>>>new_query.ci+=' db_name.tbl_name '>>>new_queryCREATETABLEdb_name.tbl_nameASSELECTcol1,
nt.idFROMex_db.dbo.ex_tabletblJOINex_db.dbo.new_tblntONtbl.id=nt.idWHEREcol1=1ORcol2ISNULLGROUPBYcol1LIMIT10, 100

Suppose you want to extend your query by joining to another table and adding columns from this table:

NOTE: the SQL constructed is not validated.