Usage

pytsql is tailored to run with MSSQL. Other sql dialects are not supported.

The main function of interest is execute. It requires a sqlalchemy Engine object as well as the path to a sql script. A typical use case might look as follows:

import pytsql
from sqlalchemy import sa

db_connection_string = "connection_to_my_database"
engine = sa.get_engine(db_connection_string)

pytsql.execute("my_sql_script.sql", engine)

Parametrization

execute has a parameters parameter. With the help of parameters, values and expressions in the underlying sql script can be dynamically set.

The way this is done is similar to templating. Yet, most traditional templating approaches don’t allow for the execution of the template source code. We chose an approach which guarantees executability of a sql script with default values while also allowing for the replacement of values during execution.

Concretely, a sql script might look as such:

USE master;
/* <replace> */
select * from [dbo].[table];
/* </replace> select * from {table_qualifier}; */

If this script is run ‘as such’, the default table, master.dbo.table will be selected from. At the same time, we can parametrize the table which is selected from:

for table_name in ["table_name1", "table_name2"]:
    parameters = {"table_qualifier": f"different_schema.{table_name}"}
    pytsql.execute(my_sql_script, engine, parameters)

Logging the prints

When working in sql IDEs or GUIs, some developers seem to use the sql PRINT function as a logging or debugging mechanism. We have observed use cases ranging from PRINT 'section 4 completed' over PRINT(CAST(GETDATE() AS VARCHAR) + ': start section 5') to printing intermediate execution information such as how many rows had been affected by a certain operation or what the mean of a newly created column turned out to be.

If such mechanisms are already in place, pytsql is able to reuse them. Given that a python logger has been set up in the script calling pytsql.execute, pytsql will log the results of any PRINT statements with level INFO.