10

Using the latest version of PyMySQL(0.9.3) I am unable to execute a query string which has multiple SQL statements in it separated by a semicolon(;)

My python version is: Python 3.6.5

import pymysql # version 0.9.3
conn = {
    "host": "mysql_server_hostname",
    "password": "my_password",
    "port": <<port_no>>,
    "user": "my_username"
}

my_query_str = 'show databases;use random_existing_db;'
with pymysql.connect(**conn):
    cur.execute(query)

Error returned: (1064, 'Multiple statements detected in a single query)

Tried running the same query with PyMySQL-0.7.11 and it works

2 Answers 2

19

In pymysql version 0.8 onwards client flag is no longer set by default. For executing multiple statements one needs to pass client_flag=CLIENT.MULTI_STATEMENTS.

reference: https://github.com/PyMySQL/PyMySQL/blob/v0.9.3/CHANGELOG#L66

Example of the fix below.

import pymysql
from pymysql.constants import CLIENT

conn = {
    "host": "mysql_server_hostname",
    "password": "my_password",
    "port": <<port_no>>,
    "user": "my_username",
    "client_flag": CLIENT.MULTI_STATEMENTS
}

my_query_str = 'show databases;use random_existing_db;'
with pymysql.connect(**conn) as cur:
    cur.execute(my_query_str)
4
  • 3
    This is a great answer but the next logical question is how to I get the data returned for each of my queries? The answer is. To get the results from the first query in the compound query, do results_of_statement_1 = cur.fetchall(), for subsequent queries, first check whether they are pending results by cur.nextset() == True. Once this is True, you can get the results set by results_of_statement_n = cur.fetchall(). cur.nextset() will be None when they are no more results to fetch.
    – Samuel Nde
    May 30, 2021 at 8:28
  • I can't seem to find much documentation on this. Anyone know what the consequences of using client_flag=CLIENT.MULTI_STATEMENTS are? Sep 1, 2021 at 19:33
  • @PhilipPlachta can you clarify what type of consequences are you talking about ? Sep 3, 2021 at 11:30
  • @RamShankarKumar Maybe it's a stupid question but I was wondering if there were any downsides to using that option. I ended up only passing that option in when I needed it b/c I couldn't find much about it. Sep 4, 2021 at 21:26
3

That's right, I used the client_flag = CLIENT.MULTI_STATEMENTS parameter to take effect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.