This package helps automatically parse and execute sql queries
There will be many times when we need to run SQL queries via python code. Often this is hadeled by copying our sql into our python code as a string and executed it using jaydebeapi. This works well enough but makes version control a pain if we are editing sql files and then copying them into our python, especially if we are outsourcing the actual development of the queries to another team.
SqlParser is a Python package that wraps around jaydebeapi that seeks to address this issue by automatically parsing our SQL queries based on a few simple annotations that get added when a query is being developed.
This is currently only tested for teradata but it should work with any database supported by jaydebeapi.
There are three types of sql "Blocks" that can be created using the following annotations: --:--SETUP These are queries that get run first to maybe create some intermediate table. No data is returned from these --:--DATA: output_filename These are queries that extract data, and get saved under theme given inlace of "output_filename" --:--CLEANUP Finally these are queries that get run last two do any cleanup that needs to be done, maybe to remove and intermediate table.
You can have more than one of each block and within each Block, you can have multiple commands and each should be terminated by a semicolon (;). Note: Data blocks should only contain one command. Here is a simple example using all three types of block but note you will often only need a "DATA" block:
Example Annotated SQL
--:--SETUP CREATE table temporary_table as Select * from table_a a Join table_b b on a.key = b.key; CREATE table temporary_table2 as Select * from new_table_a a Join table_b b on a.key = b.key; --:--DATA: tracked Select * from temporaty_table Where package_type = “tracked”; --:--DATA: sd_agg Select route_id, count(barcode) From temporary_table Where package_type = “special_delivery” groupby 1; --:--CLEANUP DROP table temporary_table; DROP table temporary_table2;
Using the Parser
Once you have annotated your sql you can easily use the SqlParser to parse and execute your commands. Here is a simple example: SqlParser Example
import glob import yaml from sqlparser import SqlParser #load credentials with open('credentials.yaml', 'r') as file: creds = yaml.load(file) #create a parser and point it to where you want the sql extracts to be stored parser = SqlParser(output_basepath="/path/to/store/files/", sep="|") #set the address and credentials parser.set_address("//00.000.00.000/DBS_PORT=0000") #replace address with ip and port of your database parser.set_credentials(creds['td_username'], creds['td_password']) parser.open_connection() #run all the queries you want executed queries = glob.glob("path/to/annotated/files/*.sql") for query in queries: parser.parse_sql(path=query) parser.run_all()
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Hashes for sqlparser-0.0.9-py3-none-any.whl