| Bug #78308 | Executing several statements is "allowed" by default | ||
|---|---|---|---|
| Submitted: | 2 Sep 2015 18:09 | Modified: | 11 Jan 2023 20:21 |
| Reporter: | Sebastien Celles | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / Python | Severity: | S3 (Non-critical) |
| Version: | 2.0.4 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[3 Sep 2015 8:09]
MySQL Verification Team
Hello Sebastien Celles, Thank you for the report. Thanks, Umesh
[11 Jan 2023 20:21]
Oscar Pacheco
Posted by developer: Hello Sebastien, After analyzing the report carefully, I found there isn't a bug from connector-python. SQLAlchemy does not support multi-query/command statements based on my research, and even if it does, the problem does not come from connector-python (see script below). Regarding connector-python, I created an example very similar to the one you provided, I see no issues. --------- bug78308.py (python script) --------- """ $ pip list Package Version ---------------------- ------- mysql-connector-python 8.0.31 numpy 1.24.1 pandas 1.5.2 SQLAlchemy 1.4.46 """ import os import sqlalchemy import pandas as pd import numpy as np import mysql.connector password = os.environ.get("mypass") db_uri = f"mysql+mysqlconnector://root:{password}@localhost:3306/test" engine = sqlalchemy.create_engine(db_uri, connect_args={"use_pure": True}) with engine.connect() as cnx: df = pd.DataFrame(np.random.randn(4, 3), columns=["a", "b", "c"]) df.to_sql("df", con=cnx, if_exists="replace") df.to_sql("test_table", con=cnx, if_exists="replace") """ This code block works fine """ # res = pd.read_sql("SELECT * from df where a>0", con=cnx) # print(res) """ This code block fails with error: sqlalchemy.exc.InterfaceError: (mysql.connector.errors.InterfaceError) Use multi=True when executing multiple statements [SQL: SELECT * from df where a>0; SELECT @@version] because SQLAlchemy does not support multi-query/command statements. """ # res = pd.read_sql("SELECT * from df where a>0; SELECT @@version", con=cnx) # print(res) config = { "username": "root", "password": password, "use_pure": True, "port": 3306, "database": "test", } with mysql.connector.connect(**config) as cnx: with cnx.cursor() as cur: cur.execute("SELECT * from df where a>0") single_query_res_1 = cur.fetchall() cur.execute("SELECT @@version") single_query_res_2 = cur.fetchall() """ for recent versions of connector python, multi=False by default """ try: multi_query_res = [ res_cursor.fetchall() for res_cursor in cur.execute( "SELECT * from df where a>0; SELECT @@version; DROP TABLE test_table; DROP TABLE df", multi=True, ) ] except mysql.connector.errors.InterfaceError: print("Bug report confirmed!") else: if multi_query_res[:2] == [single_query_res_1, single_query_res_2]: print( "Execute with multi=True is working!" ) --------- Terminal --------- $ python bug78308.py Execute with multi=True is working! Finally, after taking a look at the MySQL console I found both tables `df` and `test_table` were dropped successfully. --------- MySQL Console --------- mysql> USE test; mysql> SHOW TABLES; Empty set (0.00 sec)
[11 Jan 2023 20:23]
Oscar Pacheco
Posted by developer: Hello Sebastien, After analyzing the report carefully, I found there isn't a bug from connector-python. SQLAlchemy does not support multi-query/command statements based on my research, and even if it does, the problem does not come from connector-python (see script below). Regarding connector-python, I created an example very similar to the one you provided, I see no issues. --------- bug78308.py (python script) --------- """ $ pip list Package Version ---------------------- ------- mysql-connector-python 8.0.31 numpy 1.24.1 pandas 1.5.2 SQLAlchemy 1.4.46 """ import os import sqlalchemy import pandas as pd import numpy as np import mysql.connector password = os.environ.get("mypass") db_uri = f"mysql+mysqlconnector://root:{password}@localhost:3306/test" engine = sqlalchemy.create_engine(db_uri, connect_args={"use_pure": True}) with engine.connect() as cnx: df = pd.DataFrame(np.random.randn(4, 3), columns=["a", "b", "c"]) df.to_sql("df", con=cnx, if_exists="replace") df.to_sql("test_table", con=cnx, if_exists="replace") """ This code block works fine """ # res = pd.read_sql("SELECT * from df where a>0", con=cnx) # print(res) """ This code block fails with error: sqlalchemy.exc.InterfaceError: (mysql.connector.errors.InterfaceError) Use multi=True when executing multiple statements [SQL: SELECT * from df where a>0; SELECT @@version] because SQLAlchemy does not support multi-query/command statements. """ # res = pd.read_sql("SELECT * from df where a>0; SELECT @@version", con=cnx) # print(res) config = { "username": "root", "password": password, "use_pure": True, "port": 3306, "database": "test", } with mysql.connector.connect(**config) as cnx: with cnx.cursor() as cur: cur.execute("SELECT * from df where a>0") single_query_res_1 = cur.fetchall() cur.execute("SELECT @@version") single_query_res_2 = cur.fetchall() """ for recent versions of connector python, multi=False by default """ try: multi_query_res = [ res_cursor.fetchall() for res_cursor in cur.execute( "SELECT * from df where a>0; SELECT @@version; DROP TABLE test_table; DROP TABLE df", multi=True, ) ] except mysql.connector.errors.InterfaceError: print("Bug report confirmed!") else: if multi_query_res[:2] == [single_query_res_1, single_query_res_2]: print( "Execute with multi=True is working!" ) --------- Terminal --------- $ python bug78308.py Execute with multi=True is working! Finally, after taking a look at the MySQL console I found both tables `df` and `test_table` were dropped successfully. --------- MySQL Console --------- mysql> USE test; mysql> SHOW TABLES; Empty set (0.00 sec)
[11 Jan 2023 20:23]
Oscar Pacheco
Posted by developer: Hello Sebastien, After analyzing the report carefully, I found there isn't a bug from connector-python. SQLAlchemy does not support multi-query/command statements based on my research, and even if it does, the problem does not come from connector-python (see script below). Regarding connector-python, I created an example very similar to the one you provided, I see no issues. --------- bug78308.py (python script) --------- """ $ pip list Package Version ---------------------- ------- mysql-connector-python 8.0.31 numpy 1.24.1 pandas 1.5.2 SQLAlchemy 1.4.46 """ import os import sqlalchemy import pandas as pd import numpy as np import mysql.connector password = os.environ.get("mypass") db_uri = f"mysql+mysqlconnector://root:{password}@localhost:3306/test" engine = sqlalchemy.create_engine(db_uri, connect_args={"use_pure": True}) with engine.connect() as cnx: df = pd.DataFrame(np.random.randn(4, 3), columns=["a", "b", "c"]) df.to_sql("df", con=cnx, if_exists="replace") df.to_sql("test_table", con=cnx, if_exists="replace") """ This code block works fine """ # res = pd.read_sql("SELECT * from df where a>0", con=cnx) # print(res) """ This code block fails with error: sqlalchemy.exc.InterfaceError: (mysql.connector.errors.InterfaceError) Use multi=True when executing multiple statements [SQL: SELECT * from df where a>0; SELECT @@version] because SQLAlchemy does not support multi-query/command statements. """ # res = pd.read_sql("SELECT * from df where a>0; SELECT @@version", con=cnx) # print(res) config = { "username": "root", "password": password, "use_pure": True, "port": 3306, "database": "test", } with mysql.connector.connect(**config) as cnx: with cnx.cursor() as cur: cur.execute("SELECT * from df where a>0") single_query_res_1 = cur.fetchall() cur.execute("SELECT @@version") single_query_res_2 = cur.fetchall() """ for recent versions of connector python, multi=False by default """ try: multi_query_res = [ res_cursor.fetchall() for res_cursor in cur.execute( "SELECT * from df where a>0; SELECT @@version; DROP TABLE test_table; DROP TABLE df", multi=True, ) ] except mysql.connector.errors.InterfaceError: print("Bug report confirmed!") else: if multi_query_res[:2] == [single_query_res_1, single_query_res_2]: print( "Execute with multi=True is working!" ) --------- Terminal --------- $ python bug78308.py Execute with multi=True is working! Finally, after taking a look at the MySQL console I found both tables `df` and `test_table` were dropped successfully. --------- MySQL Console --------- mysql> USE test; mysql> SHOW TABLES; Empty set (0.00 sec)
Description: mysql-connector-python execute several statements but raises InterfaceError How to repeat: Hello, I noticed a strange behavior with mysql-connector-python I think it's more on mysql-connector-python side than on Pandas side but I think you should have a look. import sqlalchemy import pandas as pd import numpy as np db_uri = 'mysql+mysqlconnector://root:root@localhost:3306/test' engine = sqlalchemy.create_engine(db_uri) df = pd.DataFrame(np.random.randn(4,3), columns=['a','b','c']) df.to_sql("df", engine) df.to_sql("test_table", engine) #pd.read_sql("SELECT * from df where a>0", engine) pd.read_sql("SELECT * from df where a>0; DROP TABLE test_table", engine) raises InterfaceError: (mysql.connector.errors.InterfaceError) Use multi=True when executing multiple statements [SQL: 'SELECT * from df where a>0; DROP TABLE test_table'] but test_table is dropped same with SQLite db_uri = 'sqlite:///test.db' engine = sqlalchemy.create_engine(db_uri) df = pd.DataFrame(np.random.randn(4,3), columns=['a','b','c']) df.to_sql("df", engine) df.to_sql("test_table", engine) raises Warning: You can only execute one statement at a time. and test_table is (fortunately) not dropped with SQLite. I haven't test with MySQL and other driver such as MySQLdb driver. Kind regards