pytsql 1.0.0

`Pytsql` allows to run mssql sripts, typically run via GUIs, via CLI.

pytsql

Description: pytsql allows to run mssql scripts, which are typically run via GUIs, to be executed via CLI instead. This, in turn, eases concurrent execution, scheduled execution as well as chained execution of scripts in a workflow. Put differently, it enables automated execution of sql scripts.

This project is in an early stage. Hence suggestions as well as contributions are very welcome.

Installation

To install, execute:

pip install pytsql

or in a conda environment

mamba install pytsql

Be aware that depending on your platform, you'will need specific drivers (Windows, Linux, macOS).

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

my_sql_script = "my_sql_script.sql"

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

pytsql.execute(my_sql_script, engine)

Development

We use conda to manage the development environment. We strongly suggest to use mamba as a replacement for conda.

# Clone the repository
git clone [e-mail]:Quantco/pytsql.git
cd pytsql

# Set up a conda environment with name "pytsql" and activate it.
mamba env create
conda activate pytsql

# Set up our pre-commit hooks for black, mypy, isort and flake8.
pre-commit install

# Install this package in editable mode.
pip install --no-build-isolation -e .

Unit tests

conda activate pytsql
cd pytsql
pytest tests/unit/

Integration tests

Integration tests are run against a database. You can start a local docker-based mssql database configured for the integration tests as follows:

docker run \
    -e "ACCEPT_EULA=Y" \
    -e "SA_PASSWORD=QuantCo@MSSQL" \
    -p 1433:1433 \
    --name=mssql \
    --rm -it mcr.microsoft.com/mssql/server:2019-latest

or in case of running on a ARM based device

docker run \
    -e "ACCEPT_EULA=Y" \
    -e "MSSQL_SA_PASSWORD=QuantCo@MSSQL" \
    -e "MSSQL_USER=sa" \
    -p 1433:1433 \
    --name=mssql \
    --rm -it mcr.microsoft.com/azure-sql-edge

Alternatively, the script at the root level called start_mssql.sh can be used in Unix based environments (Linux, OSX, WSL).

Once the docker container is running, run the tests:

conda activate pytsql
cd pytsql
pytest tests/integration

Add the option --backend=mssql-freetds to the test command to run the tests using the freetds driver.

How to create a new grammar

The only non auto-generated file in pytsql/src/pytsql/grammar is tsql.g4. Once you adapted it to your needs, you can generate all other files by running,

java -jar /usr/local/lib/antlr-4.9.2-complete.jar -Dlanguage=Python3 tsql.g4

with your a path to your respective antlr jar file.

Getting help

Please make sure to use github issues in case you run into trouble.

---

Credits

Inspiration drawn from codebuff.

Original authors: Alex Gonopolskiy Kevin Klein Encho Mishinev