How to run complex sql script in python program?

Question

I have a large SQL script that creates my database (multiple tables, triggers, etc. using MySQL), and I need to execute this script from a python program. My old code did this:

sql_file = open(os.path.join(self.path_to_sql, filename), 'r') sql_text = sql_file.read() sql_stmts = sql_text.split(';') for s in sql_stmts: cursor.execute(s) 

This worked fine until I included triggers in my sql script. Since I now need to change the delimiter; for something else, to support triggers with multiple SQL statements in each, my code to split each statement into its own line no longer works, because the “separator” line in my script cannot be broken correctly, and I get syntax error from cursor.execute (s).

So, I need to somehow tell mysqldb to execute the whole sql script at once, and not the individual sql statements. I tried this:

 sql_file = open(os.path.join(self.path_to_sql, filename), 'r') sql_text = sql_file.read() cursor.execute(sql_text) 

However, when I try to run this code, I get the following error: ProgrammingError: (2014, "Commands are not synchronized, you cannot run this command now") My Google-fu tells me that this is due to the Python package mysqldb does not support complex SQL statements sent to cursor.execute ().

So how can I do this? I would really like to find a way to do this completely in Python, so that the code will remain fully portable. We have several programmers who work on this project in Eclipse, some on Windows, and some on Mac, and the code should also work on a Linux production server.

If I cannot use Python code to actually run SQL, how can I tell Python to run a separate program to execute it?

Answer 1

(not a python solution), you can use

  os.system('mysql < etc') 

ooh, edit (python solution):

if you have a query broken down by line, you can close and re-open the cursor and execute by line.

redit: sorry, just removed the first paragraph. it looks like you did such things first.

Answer 2

This is not a good way to structure a multilingual program.

Brandon's answer is really the right way, if all you do is just execute a large piece of sql.

On the other hand, if you are doing material with query results throughout the process, you should not try to parse a large, well-formed sql script. Instead, you should mix sql statements in your python code.