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

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.

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