Features

---

Here are some modules one most likely will use in their program.

sql_executor

Main class, SqlExecutor, inherits all functionalities from SqlHistoryManager, SqlQueryPreparator and SqlTransactionManager:

• SqlHistoryManager

  • stores information about query executions and their results in local SQLite database

  • provides easy access to saved data via UUID

  • performs database cleaning to keep its size limited

• SqlQueryPreparator

  • validates that there is exactly 1 statement in a query which is being executed

  • determines query type

  • formats query

  • automatically adds LIMIT clause to query

• SqlTransactionManager

  • provides context manager for performing transactions

Moreover, SqlExecutor keeps configuration (sqlalchemy engine parameters, default LIMIT clause value, file name for history database) and provides single method for executing SQL queries.

sql_asyncio

Note

The following tools are available only with sqlalchemy version >= 1.4 installed, since the support for asynchronous engines was added in that release.

SqlAsyncExecutor is a simplified version of SqlExecutor, which provides a single method to execute queries asynchronously. It may be useful for the case when one needs to execute queries in parallel or to schedule an execution without blocking the main program.

SqlQueryPreparator is a wrapper around SqlAsyncExecutor with builtin tasks queue, which is used to store and obtain results of asynchronous executions. All queries are immediately scheduled for execution once they are added to the queue.

db_inspector

Note

This module is under development, and currently provides minimal functionality.

Provides a wrapper around sqlalchemy inspect function.

Apart from standard sqlalchemy.engine.reflection.Inspector methods, a DBInspector instance has the following functionalities:

• creates text representation of table columns

• provides get_views method to get consistent result throughout different sqlalchemy versions

dialects.postgresql

SqlViewFactory collects all available data about a regular or materialized view and all its dependencies into a Python View object. SqlViewMaterializer applies changes made to a View instance to a corresponding database object and all its dependencies. Together, SqlViewFactory and SqlViewMaterializer provide a tool which helps redefine a view in a database without the need of manually dropping it and its dependencies and then recreating them all. It also takes care of all the permissions that recreated objects had, that is the permissions will be automatically restored along with the view and its dependencies. Note that all the necessary steps will be executed in a separate transaction, which ensures that the whole operation either will be completed fully or will not be done at all.

Warning

‘INSTEAD OF’ view triggers are not supported yet and will not be automatically restored during view recreation.

utils

Here are some helpful tools to:

• display pandas DataFrame in a full size (all rows and columns) in Jupyter Notebook environment

  import pandas as pd
  from sqldbclient import set_full_display
  
  set_full_display(max_rows=200, display_whole_colwidth_by_default=True)
  
  pd.DataFrame({'sample_column': range(150)}).full_display()
  

  Note

  By default, only DataFrame with the rows and columns numbers are less than 1000 can be displayed in full size. Otherwise, a corresponding exception is raised.

• grant access to a database object in a PostgreSQL database

  from sqldbclient.dialects.postgresql import grant_access
  
  pg_executor = SqlExecutor.builder.config(
     SqlExecutorConf().set('engine_options',
        'postgresql+psycopg2://postgres:mysecretpassword@localhost:5555')
  ).get_or_create()
  
  grant_access(
      object_name='sales_statistics',
      object_schema='public',
      user_name='postgres',
      sql_executor=pg_executor,
      privilege='SELECT',
  )
  

• create sqlalchemy engines and avoid resource leakage by keeping only one engine per a unique set of parameters

  from sqldbclient import sql_engine_factory
  
  # pass arguments and keyword arguments as to sqlalchemy create_engine function
  
  sqlite_engine = sql_engine_factory.get_or_create('sqlite:///my_sqlite.db')