A small Python package to quickly run database object scripts against a database without dependency issues.
MIT License
*Written by Kyle Catterall of Baltic Apprenticeships L4.
This message will remain here as evidence of identity until appr. end.
dbscripts is a small Python package for handling what my collegues and I like to refer to as "database scripts" - SQL scripts, often generated by a DBMS, that allow for the quick creation and altering of database objects.
Minor Analysis - this package can be used to analyse database scripts produced by MS-SSMS. For any given database script, you can get the name of the database object, the type of the database object, and the schema of the database object.
Dependency Management - this package can be used to reorder a collection of database scripts into an order safe to execute without dependency issues. It does this through a combination of regular-expressions and Khan's topological sorting algorithm.
Script Execution - of course, this package can be used to execute database scripts against a database.
To connect to a SQL server database, create a pyodbc.Connection
using your connection string as usual.
For those who are unfamiliar with SQL Server connection strings, I have created a connection string builder that will hopefully simplify its creation.
import pyodbc
from dbscripts.dbwriter import ConnectionStringBuilderFactory, DBTypes
builder = ConnectionStringBuilderFactory.get_builder(DBTypes.MSSQL)
connection_string = (
builder.set_driver("{DRIVER}")
.set_server("SERVER")
.set_database("DATABASE_NAME")
.set_windows_authentication(True) # Include this line if using Windows Auth.
.set_options({"Encrypt": "yes", "TrustServerCertificate": "yes"}) # Adjust as needed.
.build()
)
conn = pyodbc.connect(connection_string)
DBScript
instances.A DBScript
instance can be created with a path to a database script and the flavor of your database. Given that, as mentioned, only MSSQL is currently supported, this process is very simple.
from dbscripts.dbscripts import DBScript, DBFlavor_MSSQL
script = DBScript('./your_database_script.sql', DBFlavor_MSSQL())
# DBScript attributes. . .
print(script.contents)
print(script.metadata.obj_name, script.metadata.obj_type, script.metadata.obj_schema, sep=" - ")
DBScripts
.The DBScripts
class allows you to have a collection of DBScript
instances, and perform operations that require or affect multiple scripts at once. The most noteable use-case is handling dependencies.
from dbscripts.dbscripts import DBScripts, DBScript, DBFlavor_MSSQL, DBScriptsAppendRegular
script_a = DBScript('./dependent_on_b.sql', DBFlavor_MSSQL())
script_b = DBScript('./b.sql', DBFlavor_MSSQL())
scripts = DBScripts(DBFlavor_MSSQL(), DBScriptsAppendRegular())
scripts.append(script_a)
scripts.append(script_b)
# Example: handling dependencies. . .
for script in scripts.scripts:
print(script.metadata.obj_name)
for script in scripts.safe_execution_order():
print(script.metadata.obj_name)
As you can see, as well as taking a flavor, the DBScripts
class also takes one of several IDBScriptsAppender
implementations, which determine how the append
method will behave. The following implementations exist:
DBScriptsAppendRegular
- does nothing fancy; it just appends the scripts.DBScriptsAppendIgnoreDuplicates
- if you append a DBScript
instance already present, it will be ignored.DBScriptsAppendErrorOnDuplicates
- if you append a DBScript
instance already present, a DBScriptAlreadyPresentError
exception is raised.These implementations are a lot more handy when using populating methods, such as populate_from_dir
, which just attempts to append instances of DBScript
for every SQL file in a given directory.
DBWriter
.A DBWriter
class can be given a pyodbc.Connection
instance and used to execute either a DBScript
instance or a list of DBScript
instances. Two common use cases are shown below as examples.
from dbscripts.dbscripts import DBScripts, DBFlavor_MSSQL, DBScriptsAppendRegular
from dbscripts.dbwriter import DBWriter
conn = pyodbc.connect('your_connection_string')
writer = DBWriter(conn)
scripts = DBScripts(DBFlavor_MSSQL(), DBScriptsAppendRegular())
scripts.populate_from_dir('./your_database_scripts_directory')
writer.execute_scripts(scripts.scripts, raise_exceptions=True)
from dbscripts.dbscripts import DBScripts, DBFlavor_MSSQL, DBScriptsAppendRegular
from dbscripts.dbwriter import DBWriter
conn = pyodbc.connect('your_connection_string')
writer = DBWriter(conn)
scripts = DBScripts(DBFlavor_MSSQL(), DBScriptsAppendRegular())
scripts.populate_from_dir('./your_database_scripts_directory')
writer.execute_scripts(scripts.safe_execution_order(), raise_exceptions=True)