Validate SQL Query Syntax with Python and SQLite

Question

I am using Python to execute a series of SQLite queries from a .sql file. It seems like there should be a good way to check the syntax of the queries to verify that they are all written correctly and will execute. This is especially important because these queries will for both MySQL and SQLite, so any MySQL-specific syntax should be caught and flagged.

Of course, I could just execute the queries and look for an exception. But it seems like there should be a better way than that.

If you can split up each statement, you can create prepared queries from them, but not actually execute them. Splitting them up might be tricky if you have semicolons in strings/comments, though there might be a SQLite function to do it (I can't remember). — Colonel Thirty Two
– Colonel Thirty Two, Commented Feb 26, 2015 at 15:11
I've used regexes to split the statements up into a list of strings. I'm having trouble finding how to keep the statements from executing. I could use sqlite3.Connection.execute() and then sqlite3.Connection.rollback(), but that seems inefficient. — Bryant
– Bryant, Commented Feb 26, 2015 at 16:02
Looks like Python's built-in bindings don't have the ability to create prepared statements. You might have to use a different binding or language. — Colonel Thirty Two
– Colonel Thirty Two, Commented Feb 27, 2015 at 13:56
Hi @Bryant, with Python am trying to read a .sql file and check for some rules and notify if not followed. Could you please let me know how you split up each sql statement from file into a list? — RVandakar
– RVandakar, Commented Feb 23, 2021 at 9:51
Oh man, it's been so long I don't recall what the regex was. A starting point might be to 1. iterate across lines, 2. remove --.* comments from each line, 3. rejoin list, 4. split across semi-colons, and 5. remove line endings. Good luck! — Bryant
– Bryant, Commented Feb 24, 2021 at 11:34

Bryant · Accepted Answer · 2015-03-04 14:19:33Z

I've settled with creating an in-memory database and executing the queries in which I am interested. However, the following code example is very slow and I will continue looking for a better solution. Also, I am aware of the vulnerability to SQL injection attacks in the following code, but that is not something with which I am concerned at the moment.

import sqlite3

# open the SQL file and read the contents
f_contents = open("example.sql").read()

# Use regexes to split the contents into individual SQL statements.
# This is unrelated to the issues I'm experiencing, show I opted not
# to show the details. The function below simply returns a list of
# SQL statements
stmnt_list = split_statements(f_contents)

temp_db = sqlite3.connect(":memory:")

good_stmnts = []    # a list for storing all statements that executed correctly
for stmnt in stmnt_list:
    # try executing the statement
    try:
        temp_db.execute(stmnt)
    except Exception as e:
        print("Bad statement. Ignoring.\n'%s'" % stmnt)
        continue
    good_stmnts.append(stmnt)

temp_db.close()

7yl4r · Accepted Answer · 2019-09-09 15:34:51Z

0

Using a sql parsing module you should be able to attempt a parse of each statement without having to execute it and rollback. In theory this will be much more efficient.

edit: Initially I attempted this using the sqlparse module without realizing it is intentionally non-validating. This does not work.

import sqlparse

# open the SQL file and read the contents
f_contents = open("example.sql").read()

# Use regexes to split the contents into individual SQL statements.
# This is unrelated to the issue
stmnt_list = split_statements(f_contents)

good_stmnts = []    # statements that executed correctly
for stmnt in stmnt_list:
    try:
        sqlparse.parse(stmnt)
        good_stmnts.append(stmnt)
    except sqlparse.exceptions.SQLParseError:
        print("Bad statement. Ignoring.\n'%s'" % stmnt)

I made a few unrelated (and optional) changes too:

good_stmnts append inside the try block & rm continue
caught only SQLParseError

edited Sep 9, 2019 at 15:34

answered May 16, 2018 at 15:38

7yl4r

5,4164 gold badges37 silver badges47 bronze badges

3 Comments

Phani Rithvij Over a year ago

sqlparse is parsing whatever I send. I'm getting no SQLParseErrors can you give me some examples?

7yl4r Over a year ago

@PhaniRithvij : You are absolutely right. The sqlparse module's docs even say "intentionally non-validating". The best way to make this work is to use a different module. I edited my answer but will need to return to fix it fully after finding the right module.

Neil McGuigan Over a year ago

Sqlparse can split statement for ya

Collectives™ on Stack Overflow

Validate SQL Query Syntax with Python and SQLite

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related