SimQLe

Hi all,

I found when creating various Python projects at work that require connections to SQL Databases, it wasn't easy to tick all the following boxes:

• Easy to read and write SQL integration in any project
• Manage multiple database connections simultaneously (including multiple types of SQL databases)
• Get robust SQL connections in quick scripts up and running ASAP
• Store connection strings, drivers etc nicely and consistently in secret .env variables

But most importantly:

• Make it dead easy to write integration tests with test database instances where the code of the project doesn't need to change! Plus doing so with best practice and where you can be 100% sure that across the board your project isn't touching production databases during tests.

SimQLe was created to fix all these issues. It is based on the amazing SQLAlchemy library. SimQLe intends to be perfect when:

• You don't need an ORM, just SQL queries,
• You want a dead simple way to manage databases, and
• You want super easy, robust testing.

The Basics

Install it: $ pip install simqle

Create a .connections.yaml file anywhere that looks something like this:

connections:
  - name: my-sql-server-database
    driver: mssql+pyodbc:///?odbc_connect=
    connection: DRIVER={SQL Server};UID=<username>;PWD=<password>;SERVER=<my-server>
    url_escape: True


test-connections:
    # the names of the test-connections should mirror the connections above.
  - name: my-sql-server-database
    driver: mssql+pyodbc:///?odbc_connect=
    connection: DRIVER={SQL Server};UID=<username>;PWD=<password>;SERVER=<my-test-server>
    url_escape: True

Then write some python like this:

from simqle import recordset, load_connections

load_connections("./.connections.yaml")

sql = "SELECT name, age FROM people WHERE category = :category"
params = {"category": 5}
result = recordset(con_name="my-database", sql=sql, params=params)

Then, when you're writing the tests, simply flip the SIMQLE_TEST environment variable to True before running the tests and the whole project will now use the connections defined in test-connections instead. Note how the server in the test connection is different. Voila! Stress free tests, and the actual project is just as clean as ever.

Make sure .connections.yaml is in your .gitignore for secure connections when collaborating too.

There's more it can do - but the above demonstrates how simple it can be. It has named parameters, engine exposure if you want to use them for pandas' fantastic read_sql, 100% coverage and is fully tested with every major open source SQL Database. Check out the Repo for more.

Let me know if you have any ideas or questions! There's a few upgrades on the roadmap :)

Github Repo:

from simqle import ConnectionManager
# Intialise your connections
cm = ConnectionManager(".connections.yaml")
# Write some simple SQL
sql = "SELECT name, age FROM people WHERE category = :category"
params = {"category": 5}
result = cm.recordset(con_name="my-database", sql=sql, params=