Enable executing multiple statements while execution via sqlalchemy

Question

I have a DDL object (create_function_foo) that contains a create function statement. In first line of it I put DROP FUNCTION IF EXISTS foo; but engine.execute(create_function_foo) returns:

sqlalchemy.exc.InterfaceError: (InterfaceError) Use multi=True when executing multiple statements

I put multi=True as parameter for create_engine, engine.execute_options and engine.execute but it doesn't work.

NOTE: engine if my instance of create_engine

NOTE: I'm using python 3.2 + mysql.connector 1.0.12 + sqlalchemy 0.8.2

create_function_foo = DDL("""\
DROP FUNCTION IF EXISTS foo;
CREATE FUNCTION `foo`(
    SID INT
) RETURNS double
READS SQL DATA
BEGIN
  ...
END
""")

Where I should put it?

warvariuc · Accepted Answer · 2013-11-05 07:50:22Z

36

multi=True is a requirement for MySql connector. You can not set this flag passing it to SQLAlchemy methods. Do this:

conn  = session.connection().connection
cursor = conn.cursor()  # get mysql db-api cursor
cursor.execute(sql, multi=True)

More info here: http://www.mail-archive.com/[email protected]/msg30129.html

edited Nov 5, 2013 at 7:50

answered Nov 5, 2013 at 7:08

warvariuc

60.1k45 gold badges183 silver badges234 bronze badges

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

1 Comment

Esben Eickhardt Over a year ago

TypeError: execute() takes no keyword arguments

DonkeyKong · Accepted Answer · 2019-08-18 03:03:24Z

6

Yeah... This seems like a bummer to me. I don't want to use the ORM so the accepted answer didn't work for me.

I did this instead:

with open('sql_statements_file.sql') as sql_file:
    for statement in sql_file.read().split(';'):
        if len(statement.strip()) > 0:
             connection.execute(statement + ';')

And then this failed for a CREATE function.... YMMV.

edited Aug 18, 2019 at 3:03

answered Aug 18, 2019 at 2:57

DonkeyKong

1,10914 silver badges18 bronze badges

3 Comments

Dzamo Norton Over a year ago

A more reliable way of splitting the script into statements is to use a SQL parsing library e.g. sqlparse.split(sqlparse.format(self.sql, strip_comments=True))

Jakob Lovern Over a year ago

Although this is a good idea for simple scripts that you can guarantee don't have oddly placed semicolons, there are some common edge cases that make this a bad idea in practice. Note that semicolons in comments or strings (IE --; or ';') will cause confusion to this setup. I would also recommend using a sql parsing lib.

DonkeyKong Over a year ago

@JakobLovern for sure. The idea here was never meant to be more than a solution to a problem. I was using it for DDL; a clean script; if any comments, none with semi-colons. I just needed each query to run, individually. That is all.

Antony Fuentes · Accepted Answer · 2018-08-22 00:13:56Z

4

There are some cases where SQLAlchemy does not provide a generic way at accessing some DBAPI functions, such as as dealing with multiple result sets. In these cases, you should deal with the raw DBAPI connection directly.

From SQLAlchemy documentation:

connection = engine.raw_connection()
try:
    cursor = connection.cursor()
    cursor.execute("select * from table1; select * from table2")
    results_one = cursor.fetchall()
    cursor.nextset()
    results_two = cursor.fetchall()
    cursor.close()
finally:
    connection.close()

You can also do the same using mysql connector as seen here:

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
for result in cursor.execute(operation, multi=True):
  if result.with_rows:
    print("Rows produced by statement '{}':".format(
      result.statement))
    print(result.fetchall())
  else:
    print("Number of rows affected by statement '{}': {}".format(
      result.statement, result.rowcount))

answered Aug 22, 2018 at 0:13

Antony Fuentes

1,0539 silver badges15 bronze badges

1 Comment

Hamza Zubair Over a year ago

sqlalchemy solution doesn't seem to work. One query works, two queries throw ProgrammingError. Has there been a change in sqlalchemy API?

Collectives™ on Stack Overflow

Enable executing multiple statements while execution via sqlalchemy

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related