[CONPY-151] Multiple statements in a single execute call Created: 2021-04-12  Updated: 2021-04-12  Resolved: 2021-04-12

Status: Closed
Project: MariaDB Connector/Python
Component/s: DBAPI 2.0
Affects Version/s: 1.0.6
Fix Version/s: N/A

Type: New Feature Priority: Minor
Reporter: Luciano Barcaro Assignee: Georg Richter
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

How can I execute a sql script with mariadb connector? (create a trigger for example , with delimiter and ; inside trigger body)

https://mariadb-corporation.github.io/mariadb-connector-python/connection.html
does not mention anything about multiple statements.

https://mariadb-corporation.github.io/mariadb-connector-python/cursor.html describes nextset() method (So, I assume multiple statements is possible).

A simple
crsr.execute('select 1; select 2') returns a ProgrammingError.

crsr.execute('delimiter $$') also returns error.

 Comments   
Comment by Georg Richter [ 2021-04-12 ]
  • Binary protocol in both MariaDB and MySQL doesn't support multiple statements:

MariaDB [test]> prepare my from "select 1; select 2";
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select 2' at line 1

therefore it can't be supported in MariaDB Connector/Python

  • DELIMITER is not a valid SQL keyword, it is a macro for the MySQL command line client.
  • Stored procedures and triggers are not multi statements and can be easily defined with MariaDB Connector/Python:

>>> import mariadb
 
>>> c= mariadb.connect(db="test");
 
>>> cursor=c.cursor()
 
>>> cursor.execute("create procedure p1()\nBEGIN\nSELECT 1;\nSELECT 2;END");
 
>>> cursor.callproc("p1")
 
>>> cursor.fetchall()
 
[(1,)]
 
>>> cursor.nextset()
 
True
 
>>> cursor.fetchall()
 
[(2,)]

Generated at Thu Feb 08 03:30:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.