Execute MySQL query that access separate databases in Python

Question

I have a MySQL query that accesses 2 databases. I am using Python to execute the query. The problem with Python MySQL connection is that it connects to a single database at any one time like below;

DbConnection = MySQLdb.connect(host='localhost', user='root', passwd='', db='MySQLDatabase')

How can I get Python to execute a query that access 2 databases?

Answer 1

You can qualify table names with the names of databases. For example, if you specify a database db1 when you establish the connection to the server, but you want to execute a query involving a table in another database db2, simply do something like:

conn = MySQLdb.connect(host='localhost', user='root', db='db1')
c = conn.cursor() 
c.execute("SELECT * FROM foo JOIN db2.bar")

Note also that it's not the case that you need to specify a database when setting up a connection. If you do omit the database, you need to qualify every table name with the name of a database:

conn = MySQLdb.connect(host='localhost', user='root')
c = conn.cursor()
c.execute("SELECT * FROM db1.foo JOIN db2.bar")