Reputation: 359
I'm attempting to create a multiscript tool, that will take an argument of a .sql file and execute it.
I've set up a simple test, just executing on one database, however the syntax is giving me issues every time.
DELIMITER $$
CREATE DEFINER=`a_user`@`%` PROCEDURE `a_procedure`(
IN DirectEmployeeID TEXT,
IN StartRange DATE,
IN EndRange DATE
)
BEGIN
SELECT aColumn
WHERE thisThing = 1;
END$$
DELIMITER ;
To be clear, this script has been tested, and works when passed like :
mysql -uuser -p -hhost -Pport databaseName < file.sql
and also works through mysql workbench.
I saw this type of solution on another site:
with conn.cursor() as cursor:
f = sys.argv[1]
file = open(f, 'r')
sql = " ".join(file.readlines())
cursor.execute(sql)
which gives me a MySQL syntax error:
pymysql.err.ProgrammingError: (1064, u"You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near 'DELIMITER $$\n CREATE DEFINER=`a_user`@`%` PROCEDURE
`MyCommissionsDirect`(\n \tIN ' at line 1")
as you can see, there are newline characters within the script that mysql isn't liking.
I then tried this:
with conn.cursor() as cursor:
f = sys.argv[1]
file = open(f, 'r')
sql = ''
line = file.readline()
while line:
sql += ' ' + line.strip('\n').strip('\t')
line = file.readline()
print sql
cursor.execute(sql)
and get another syntax issue, the print shows that this is all one line, which is not working in mysqlworkbench. doesn't even try to execute it, which is strange.
When I put the DELIMETER $$ on a separate line first, it executes in mysqlworkbench.
This is one of those situations where I feel like I may be making this more and more complicated. I'm very surprised pymysql doesn't have a way of simply executing a sql file directly. I'm weary of trying to do string manipulation and get this working for this particular file, because then the dream of making this tool ambiguous and reusable kind of goes out the door.
Am I going about this in the complete incorrect way?
Thanks!
Upvotes: 5
Views: 11552
Reputation: 21
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()
Upvotes: 2
Reputation: 31
It's simple code
import pymysql
class ScriptRunner:
def __init__(self, connection, delimiter=";", autocommit=True):
self.connection = connection
self.delimiter = delimiter
self.autocommit = autocommit
def run_script(self, sql):
try:
script = ""
for line in sql.splitlines():
strip_line = line.strip()
if "DELIMITER $$" in strip_line:
self.delimiter = "$$"
continue
if "DELIMITER ;" in strip_line:
self.delimiter = ";"
continue
if strip_line and not strip_line.startswith("//") and not strip_line.startswith("--"):
script += line + "\n"
if strip_line.endswith(self.delimiter):
if self.delimiter == "$$":
script = script[:-1].rstrip("$") + ";"
cursor = self.connection.cursor()
print(script)
cursor.execute(script)
script = ""
if script.strip():
raise Exception("Line missing end-of-line terminator (" + self.delimiter + ") => " + script)
if not self.connection.get_autocommit():
self.connection.commit()
except Exception:
if not self.connection.get_autocommit():
self.connection.rollback()
raise
if __name__ == '__main__':
connection = pymysql.connect(host="127.0.0.1", user="root", password="root", db="test", autocommit=True)
sql = ""
ScriptRunner(connection).run_script(sql)
Upvotes: 0
Reputation: 1339
Here is my solution for using an SQL file with PyMySQL
. The files contain many requests ended by ;
which is used to split requests in a list. So beware of the missing ;
in the list.
I decided to add the missing ;
not in the function to spar a for loop. Maybe there is a better way.
create-db-loff.sql
:
DROP DATABASE IF EXISTS loff;
CREATE DATABASE loff CHARACTER SET 'utf8';
USE loff;
CREATE TABLE product(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`code` BIGINT UNSIGNED NOT NULL UNIQUE,
`name` VARCHAR(200),
`nutrition_grades` VARCHAR(1)
);
CREATE TABLE category(
`id`INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(200)
);
CREATE TABLE asso_prod_cat(
`category_id` INT UNSIGNED NOT NULL,
`product_id` INT UNSIGNED NOT NULL,
CONSTRAINT `fk_asso_prod_cat_category`
FOREIGN KEY(category_id)
REFERENCES category(id)
ON DELETE CASCADE,
CONSTRAINT `fk_asso_prod_cat_product`
FOREIGN KEY(product_id)
REFERENCES product(id)
ON DELETE CASCADE
);
db.py
:
DB_CONFIG = {
'host': 'localhost',
'user': 'loff',
'pass': 'loff',
'db': 'loff',
'char': 'utf8',
'file': 'create-db-loff.sql'
}
def get_sql_from_file(filename=DB_CONFIG['file']):
"""
Get the SQL instruction from a file
:return: a list of each SQL query whithout the trailing ";"
"""
from os import path
# File did not exists
if path.isfile(filename) is False:
print("File load error : {}".format(filename))
return False
else:
with open(filename, "r") as sql_file:
# Split file in list
ret = sql_file.read().split(';')
# drop last empty entry
ret.pop()
return ret
request_list = self.get_sql_from_file()
if request_list is not False:
for idx, sql_request in enumerate(request_list):
self.message = self.MSG['request'].format(idx, sql_request)
cursor.execute(sql_request + ';')
Upvotes: 3
Reputation: 130
DELIMITER
is command used by a MySQL interpreter, such as the command line or Workbench, and not an actual MySQL command.
I ended up working in some logic in my Python application to disable execution of MySQL queries when DELIMITER
has been defined, then to execute when DELIMITER
has been defined again:
import MySQLdb
import re
file = 'somesql.sql'
conn = MySQLdb.Connection(mysqlserver, mysqluser, mysqlpass, mysqldb)
curs = conn.cursor()
ignorestatement = False # by default each time we get a ';' that's our cue to execute.
statement = ""
for line in open(file):
if line.startswith('DELIMITER'):
if not ignorestatement:
ignorestatement = True # disable executing when we get a ';'
continue
else:
ignorestatement = False # re-enable execution of sql queries on ';'
line = " ;" # Rewrite the DELIMITER command to allow the block of sql to execute
if re.match(r'--', line): # ignore sql comment lines
continue
if not re.search(r'[^-;]+;', line) or ignorestatement: # keep appending lines that don't end in ';' or DELIMITER has been called
statement = statement + line
else: # when you get a line ending in ';' then exec statement and reset for next statement providing the DELIMITER hasn't been set
statement = statement + line
# print "\n\n[DEBUG] Executing SQL statement:\n%s" % (statement)
try:
curs.execute(statement)
conn.commit()
statement = ""
except curs.Error, e:
print(file + " - Error applying (" + str(e) + ")\nTerminating.")
sys.exit(1)
It's a bit hacky, but seems to work well enough.
Upvotes: 2