sql_client#




Classes#

SqlClient

This class is a convenience wrapper around SQLAlchemy that makes it easy to get an engine object to run queries against any SQL backend.

class SqlClient#

class rootski.services.database.non_orm.sql_client.SqlClient(sql_dialect=None, driver_lib=None, username=None, password=None, host=None, port=None, database=None, engine_kwargs={})[source]#

Bases: object

This class is a convenience wrapper around SQLAlchemy that makes it easy to get an engine object to run queries against any SQL backend.

The official SQLAlchemy documentation page on engines, connections, transactions, sessions, etc. is very good: https://docs.sqlalchemy.org/en/13/core/connections.html

Using this class, you have 2 options for running queries:

  1. You can use the run_query method to execute queries like a typical client

  2. You can get an engine or session and do ORM style queries

Usage:

SQLAlchemy requires underlying database drivers and python libraries to be installed. For example, to connect to MySQL, you would need to install the PyMySQL package and mysql driver binaries in the project that uses this class.

# python dependencies
postgres  : pip install psycopg2-binary
mysql     : pip install pymysql
snowflake : pip install snowflake-connector-python snowflake-sqlalchemy

One of the big advantages of SQLAlchemy is that it allows you to use SQLite for writing unit tests. Sadly, not all queries are testable in this way because of SQLite’s limitations. The following are some of the bigger limitations:

SQLite does not support

  1. RETURNING clauses on INSERT statements

  2. UPDATE statements with multiple-table criteria

run_query(query, *query_args, commit=True, fetch_all=False, return_raw_result=False)[source]#

Execute a SQL query.

Parameters

  • query (str | sqlalchemy expression) – a SQL expression expressed as a string or SQLAlchemy string object.

  • query_args (object) – any values that are to be inserted in some way into the query

  • commit (bool) – whether or not to commit changes to the database

  • fetch_all (bool) – returns a ResultProxy (array of ResultRowProxy) objects if True which can be treated as an array of Row results. Otherwise returns a single ResultRowProxy or None if no results are found.

  • return_raw_results (bool) – Set to True to skip fetching the query results. This is useful for non-select statements such as INSERT, UPDATE, CREATE, etc. which do not typically return anything.

Returns

This is a very useful object, with attributes like

Return type

sqlalchemy.engine.result.ResultProxy