SQL Utility Codes

✨ code snippets for handling SQL or NoSQL in python ✨

Attention

Source Code: GH/sqlutils


The code GitHub Gist is focused towards providing utility functions related to SQL statements. The recommended cloning syntax for the gist is as below:

$ git clone https://gist.github.com/ZenithClown/3fc21f94cf9567003b153bcfca738f6d.git sqlutils

SQL Query File Parser

To interact with the database, one can use (I) ‘Raw SQL Queries’, or (II) ‘SQL Query Builder’, or (III) an ‘ORM’. The parser is focused to provide functionalities for raw sql queries via python code.

sqlparser.readStatement(filename: str, encoding: str | None = None, **kwargs) → tuple

Read a SQL File (*.sql, *.txt, etc.) to Parse Statement(s)

The following codes are written for endusers who like to group of statements into a same file based on usage or convention. However, this poses a challenge that the code cannot be called directly in python to read file as often an parsing engine supports execution of a single statement - thus the function.

Read a file which has one or multiple sql statements seperated by semicolon (;) and return a clean string that can be executed using any python methods like:

import pandas as pd
import sqlalchemy as sa

statements = readStatement(filename = "/path/to/file.sql")

# ? read sql as dataframe using pandas:
dataframe = pd.read_sql(statements[0], engine)

# ? or, directly execute using engine driver:
records = engine.execute(statements[0]).fetchall()

The module acts as a simple wrapper to the external library sqlparse that provides useful utility to clear all types of comments, make clear formatting, etc. The function just creates a simple looping in case there are multiple statements in a file.

Parameters:

  • filename (str) – Full path to the file with extension. Though the file extention is not a dependency, however it is recommended to pass a *.sql file.

  • encoding (str, optional) – Encoding of the SQL statement, defaults to None. The value is directly send to undelying function.

Keyword Arguments

The function internally uses the sqlparse.format() to render codes from the file, and it accepts all the arguments as defined in documentation.

All the formatting arguments uses the default as in the parent module except the following:

strip_comments (bool, optional, default True):

Overrides the default argument to strip comments from the underlying code. Set the value to False to preserve.

Known Issue(s)

  • The code fails if semicolon (;) is used in comment section. (See GH#9)

Example Usage(s)

  1. Typically when a SQL is used directly for database management then one typically writes similar SELECT statements in a single file. The function makes it easier to execute all the statement using .execute() connection object.

Return type:

tuple

Returns:

An iterable tuple of (cleaned) sql statement(s) read from the provided file.