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
.