SQL Utilities.md

SQL Utility Functions

✨ code snippets for handling SQL or NoSQL in python ✨

The gist repository holds some key utility functions for manipulation of SQL or NoSQL objects (database, model definations, etc.) to be used as a sub-module in a python project. The repository follows a monorepo architecture, thus advised to follow the documentation for easier navigation.

Getting Started

The code is publicly available at sql-utils by ZenithClown. For external library requirement, please check individual modules section.

# clone the code like:
git clone https://gist.github.com/ZenithClown/3fc21f94cf9567003b153bcfca738f6d.git sql-utils
export PYTHONPATH="${PYTHONPATH}:sql-utils"

If you find the code useful, please do put reference/stars. For contributing, please follow CONTRIBUTING.md guidelines, and raise a Issue/PR.

sqlparser.py

# -*- encoding: utf-8 -*-

"""
A Set of Utility Commands for Parsing SQL Files

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.

@author:  Debmalya Pramanik
@version: v0.0.1
"""

import sqlparse

def readStatement(filename : str, **kwargs) -> tuple:
    """
    Read a SQL File (`*.sql`) to Parse Statement(s)

    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:

    ```python
    import pandas as pd
    import sqlalchemy as sa

    statements = readStatement(filename = "/path/to/file.sql")
    dataframe = pd.read_sql(statements[0], engine) # ? using pandas
    records = engine.execute(statements[0]).fetchall() # ? sqlalchemy
    ...
    ```

    The module uses the external library `sqlparse` that provides
    useful utility to clear all types of comments from the statement.
    
    TODO Document Known Issue `;` not accepted in Comments.

    :type  filename: str
    :param filename: Full path to the file with extension. Though the
        file extention is not a dependency, however it is recommended
        to pass a `*.sql` file.

    Keyword Arguments
    -----------------
        * **encoding** (`str`): Encoding of the SQL statement,
            defaults to None as in `sqlparse.format()`.
        * **strip_comments** (`bool`): Set this argument to `True` to
            remove all types of comments from the SQL statement.

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

    statements = open(filename, "r").read().split(";")

    # ? keyword arguments, defined for `sqlparse.format()`,
    # TODO: define/set a wrapper with all the available options
    encoding = kwargs.get("encoding", None)
    strip_comments = kwargs.get("strip_comments", True)

    statements = [
        sqlparse.format(
            statement,
            encoding = encoding,
            strip_comments = strip_comments
        ).strip()
        for statement in statements
    ]

    return tuple([statement for statement in statements if statement])