sqlpt 0.2.2

SQL Probing Tool

sqlpt - SQL Probing Tool

sqlpt is a probing tool for Python that helps to inspect and modify sql statements. Its goal is to allow sql statements to be more visible in the application development process and to allow capturing, reusing, and modifying sql-statement components.

Example

create table person (
    id int primary key not null,
    id_number varchar not null,
    name varchar not null,
    birth_date date not null,
    favorite_food varchar,
    shoe_size number
);

insert into person values (1, '123456', 'Bob Bobson', '2001-01-01', 'lasagna', '11');
insert into person values (2, '123457', 'Jane Janeson', '2002-02-02', 'pad thai', '9');

>>> from sqlpt.sql import Query
>>> sql_str = '''
        select name,
               favorite_food
          from person
         where shoe_size = 9;
    '''

>>> query = Query(sql_str)
>>> query
Query(select_clause=SelectClause(fields=[Field(expression='name', alias=''), Field(expression='favorite_food', alias='')]), from_clause=FromClause(from_dataset=Table(name='person'), joins=[]), where_clause=WhereClause(expression=Expression(comparisons=[Comparison(left_term='shoe_size', operator='=', right_term='9')])))

>>> query.count()
1

Nothing fancy there, but now let's inspect the from clause for further insight:

>>> query.from_clause
FromClause(from_dataset=Table(name='person'), joins=[])

>>> query.from_clause.from_dataset.count()
2

Another quick example before a more comprehensive description--let's probe a scalar subquery in the select clause:

>>> sql_str = '''
        select name,
               favorite_food,
               (select name from country where person.birth_country_id = country.id) country_name
          from person
'''

>>> query = Query(sql_str)

>>> query.select_clause.fields[2].query.crop().count()
195  # ~Number of countries in the world

Several more features exist for inspecting and modifying sql queries. Enjoy probing!

Reasoning

Accurate and well-performing sql queries take careful construction. Having a good understanding of the tables, joins, and filters is essential to forming such queries. sqlpt provides tools to inspect areas of sql queries to make more informed design decisions.

sqlpt utilizes the very useful Python package python-sqlparse and builds upon the idea of parsing sql by converting sql queries and their components (i.e., select, from, where clauses) into objects to help manage, modify, and probe sql queries themselves.

The goal of sqlpt is not to be another ORM. Several Python ORMs and other sql-related packages (i.e., SQLAlchemy) already do a masterful job of interfacing with databases and representing database objects as Python objects. Complementarily, sqlpt places more of the focus on the sql itself, making it a first-class citizen, in an effort to make it more transparent to the developer. It gives tools to both interact with the actual sql as well as run the sql against a database. The goal is to help developers not by ambiguating sql but by bringing sql to the forefront in the development process and simplifying using it in applications. The hope is that there are no surprises or black boxes when using sql.

Installation

pip install sqlpt

Documentation

https://sqlpt.readthedocs.io

Features

Probing

• Count rows in a query
• Count rows in underlying datasets
• Count expected rows in an update statement
• Count expected rows in a delete statement
• Identify filters in join and where clauses
• Check table granularity
• Check if query is leaf query
• Ignore dangling parameters
• Locate columns in expressions
• Generate a diff between sql queries

Modifying

• Add select-clause field
• Remove select-clause field
• Add from-clause join
• Remove from-clause join
• Add where-clause filter
• Remove where-clause filter
• Crop where-clause filter
• Convert left join without where-clause filter to scalar subquery in select clause
• Parameterize query with dangling comparison terms
• Convert select statement to insert statement
• Convert select statement to update statement

Future areas of improvement

Refactoring

• Underscore methods and where to locate methods (move some to service)
• Distinguish between s_str and sql_str everywhere (s_str being a snippet and sql_str a full query sql)
• Address FUTURE notes in code

Documentation

• Document all different ways to construct each clause

Functionality

• Support insert statements
• Support order-by clauses