How can I run multiple SQL statements in a query in python?

Question

I'm implementing migrations for my program, and I need some way to run multiple statements in a query.

I have a dict of migrations:

MIGRATIONS = {
        "test": ("-- apply", "-- revert", "does nothing")
        }

(first tuple element is the "apply" query, second is the "revert" query, third is human-readable text for help pages)

I am currently using SQLite, but would like the option to switch to another DB in the future. As such, I can't use the Connection.executescript method, as that's "a nonstandard shortcut" for "a nonstandard convenience method".

Databases generally discourage/prevent this because it often leads to ambiguities as to what to return as a result. What if you send two select queries? What would you expect to get back? MySQL (and maybe others) has a cursor.execute() function that takes multiple queries, but I think there is a very restricted set of SQL statements that can be passed to it. - I'm not a DBA. This is just my limited experience as a long time back-end programmer who's used lots of MySQL databases. - if you don't care about ever working wiith MySql, maybe this info is useless to you. — CryptoFool, May 07 '19 at 00:28
It should return absolutely nothing, ofc. I don't see what else would/should be reasonable. — SoniEx2, May 07 '19 at 00:33
Everyone would have a different opinion. That's probably why they stay away from it. I would think you'd want to return the result of the last statement. That seems to make perfect sense. The prior statements could set up the query, like maybe setting timezone or string encoding settings, and then the query would be performed as the last step. — CryptoFool, May 07 '19 at 00:48
I understand. I was just telling you why I thought databases don't allow multiple statements in single request. I wrote a db migration tool in python for my company. I send over a script and then run the script vi the mysql command line tool, just like if I was doing it locally. This allows for migration files with any amount of stuff in them. — CryptoFool, May 07 '19 at 13:53

score 0 · Accepted Answer · answered May 09 '19 at 23:08

Since you're implementing the migration, why don't you define a function that does it in terms of well-defined functions:

def executemany(conn, statements: List[str]):
    with conn.cursor() as c:
        for statement in statements:
            c.execute(statement)
        conn.commit()

Another option is to use a dedicated migration tool. I'm partial to goose.

How can I run multiple SQL statements in a query in python?

1 Answers1