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

Smack Alpha
  • 1,828
  • 1
  • 17
  • 37
Saif S
  • 301
  • 2
  • 8

2 Answers2

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)
Saif S
  • 301
  • 2
  • 8
  • 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 '21 at 08:28
  • I can't seem to find much documentation on this. Anyone know what the consequences of using client_flag=CLIENT.MULTI_STATEMENTS are? – Philip Plachta Sep 01 '21 at 19:33
  • @PhilipPlachta can you clarify what type of consequences are you talking about ? – Ram Shankar Kumar Sep 03 '21 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. – Philip Plachta Sep 04 '21 at 21:26
3

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

deadshot
  • 8,881
  • 4
  • 20
  • 39
Zhou
  • 69
  • 2