MySQL connector doesn't take "USE db" in account

Question

This works and returns the correct rows :

cursor.execute('SELECT * FROM my_db.my_table')

This doesn't, no rows are returned :

cursor.execute('USE my_db; SELECT * FROM my_table;', multi=True)

Running the query in GUI client works. What do I need to do for the mysqlconnector to behave in the same way as other tools ?

Thanks.

There's no multi in Connector/Python 9.2. Check Executing Multiple Statements. According to the docs cursor.execute(sql_script) should work. To get results from all statements you should use map_results=True. What version are you using? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 7 at 12:58
8.2.0. Pinned. But map_results is the issue, you can publish an answer if you wish :) — Arcus
– Arcus, Commented Apr 14 at 7:48

Pedro Rodriguez Larez · Accepted Answer · 2025-04-14 23:45:41Z

3

You can keep things simple by splitting the queries:

cursor.execute('USE my_db')
cursor.execute('SELECT * FROM my_table')

GUI tools usually manage the context (i.e., the selected database) behind the scenes. Python connector is more strict—you need to explicitly fetch from each result in a multi=True call.

Edited to add context:

In the MySQL CLI or GUI tools (like DBeaver or MySQL Workbench), semicolon-separated statements are parsed and executed together.

But in mysql-connector-python, the behavior is different.

When you use:

cursor.execute('USE my_db; SELECT * FROM my_table;', multi=True)

It returns a generator of result objects, one per statement.

If you don’t iterate over that generator, none of the statements beyond the first are executed.

The USE my_db statement is executed. You never advance to the SELECT statement, so it’s not actually run. This explains why no rows are returned — the SELECT never ran.

You must iterate over the results:

rresults = cursor.execute('USE my_db; SELECT * FROM my_table;', multi=True)

for result in results:
    if result.with_rows:
        rows = result.fetchall()
        print(rows)  # or handle rows

edited Apr 14 at 23:45

answered Apr 7 at 15:28

Pedro Rodriguez Larez

5453 silver badges14 bronze badges

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

3 Comments

Arcus Apr 14 at 7:46

The sql statement comes from a migration script, I cannot split it up. Plus I'd rather actually understand why things are going wrong.

Pedro Rodriguez Larez Apr 14 at 23:47

I edited the post to add context. TLDR; You're running multiple SQL statements in one go, but only the first one (USE db) is executed unless you explicitly step through each result. Unlike GUI tools, mysql-connector-python doesn't automatically run all semicolon-separated queries — you have to manually handle each result when using multi=True.

Arcus Apr 16 at 7:17

That sounds insane. The whole purpose of putting statements in a single string is so I'm not going back and forth with the DB all the time. How do I achieve this ? This is typical pythonic bullshittery.

Collectives™ on Stack Overflow

MySQL connector doesn't take "USE db" in account

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related