Is there a way to execute more than one query per string in MySQL-Python?

Question

I have some input which is a string containing more than one MySQL queries, such as USE some_db; SELECT * FROM some_table;. When I store this string as s and run cursor.execute(s), it prints out 0L and when I do cursor.fetchall() it returns an empty tuple. It does not raise any exception, but it does not work either. When I split the string into two queries and open a cursor for each and execute, it works, but correctly splitting a string into queries is not easy, especially when there are comments. Is there a way to avoid splitting and execute the whole string of multiple queries? Or this there a good library to split a string of multiple queries into strings with one query each?

Thanks!

Parfait · Accepted Answer · 2015-06-29 03:23:11Z

1

Certainly, a Python script can run multiple SQL statements from a string or list, external .sql or .txt file that can be sourced to MySQL.

However, the cur.execute command runs one SQL line one at a time. Hence, you will need to loop through each SQL line iteratively. So, consider splitting the multiple SQL commands by semicolon.

s = "USE some_db; SELECT * FROM some_table;"

# filter() removes trailing empty list item
s = filter(None, s.split(';'))

for i in s:
    # strip() removes leading and trailing white spaces  
    # semicolon is re-added per line for query run
    cur.execute(i.strip() + ';')

But be sure to remove any semicolons found in comments.

# PROCESSING STEP 1;
# PROCESSING STEP 1

answered Jun 29, 2015 at 3:23

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Watte Over a year ago

That code is dangerous for constant strings that include semicolons, and doesn't work that well for parameterized queries (which should be almost all your queries.)

Jon Watte · Accepted Answer · 2015-06-29 03:36:48Z

-1

Look at the documentation for MySQLCursor.execute().

It claims that you can pass in a multi parameter that allows you to run multiple queries in one string.

If multi is set to True, execute() is able to execute multiple statements specified in the operation string.

multi is an optional second parameter to the execute() call:

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
for result in cursor.execute(operation, multi=True):

answered Jun 29, 2015 at 3:36

Jon Watte

7,4054 gold badges58 silver badges72 bronze badges

2 Comments

user274602 Over a year ago

I think this is mysql.connector, not mysql-python.

Jon Watte Over a year ago

That would then change the advise to "switch to mysql.connector instead of mysql-python" :-)

Collectives™ on Stack Overflow

Is there a way to execute more than one query per string in MySQL-Python?

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related