Validate SQL Query Syntax with Python and SQLite
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()