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.

Answer 1

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()

Answer 2

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