Posted on Mon, 05 Feb 2018 by adamlamers
Most SQL files contain interpreter commands such as DELIMITER that make passing the commands through to pymysql somewhat difficult, this code snippet allows you to separate out the statements in the sql file into a list for sequential execution.
def parse_sql(filename):
data = open(filename, 'r').readlines()
stmts = []
DELIMITER = ';'
stmt = ''
for lineno, line in enumerate(data):
if not line.strip():
continue
if line.startswith('--'):
continue
if 'DELIMITER' in line:
DELIMITER = line.split()[1]
continue
if (DELIMITER not in line):
stmt += line.replace(DELIMITER, ';')
continue
if stmt:
stmt += line
stmts.append(stmt.strip())
stmt = ''
else:
stmts.append(line.strip())
return stmts
Usage example:
conn = pymysql.connect('test')
stmts = parse_sql('my_sql_file.sql')
with conn.cursor() as cursor:
for stmt in stmts:
cursor.execute(stmt)
conn.commit()