2

I know it is possible to specify the schema in the URL used at engine creation. In this case every connection generated by the engine will be already using the specified database:

engine = create_engine('mysql://%s:%s@%s:%d/%s' % (user, pwd, host, port, schema), echo=True)

a = engine.connect()
b = engine.connect()
c = engine.connect()
print([t for t in a.execute("SHOW TABLES;")])
print([t for t in b.execute("SHOW TABLES;")])
print([t for t in c.execute("SHOW TABLES;")])

Note that the command SHOW TABLES successfully finds the tables for every connection:

2019-03-22 11:24:10,599 INFO sqlalchemy.engine.base.Engine SHOW TABLES;
2019-03-22 11:24:10,599 INFO sqlalchemy.engine.base.Engine ()
[('Table1',), ('Table2',), ('Table3',)]
2019-03-22 11:24:10,599 INFO sqlalchemy.engine.base.Engine SHOW TABLES;
2019-03-22 11:24:10,599 INFO sqlalchemy.engine.base.Engine ()
[('Table1',), ('Table2',), ('Table3',)]
2019-03-22 11:24:10,600 INFO sqlalchemy.engine.base.Engine SHOW TABLES;
2019-03-22 11:24:10,600 INFO sqlalchemy.engine.base.Engine ()
[('Table1',), ('Table2',), ('Table3',)]

My question is: is it possible to change (or specify at a later moment) the database/schema used by an engine ?

For example, consider the following attempt:

engine = create_engine('mysql://%s:%s@%s:%d' % (user, pwd, host, port), echo=True)

engine.execute('USE MySchema')
a = engine.connect()
b = engine.connect()
c = engine.connect()
print([t for t in a.execute("SHOW TABLES;")])
print([t for t in b.execute("SHOW TABLES;")])
print([t for t in c.execute("SHOW TABLES;")])

Here we create an engine which has not been connected to any particular schema. We the issue a USE <schema> command. However this doesn't seem to work properly:

2019-03-22 11:24:56,175 INFO sqlalchemy.engine.base.Engine USE MySchema
2019-03-22 11:24:56,175 INFO sqlalchemy.engine.base.Engine ()
2019-03-22 11:24:56,176 INFO sqlalchemy.engine.base.Engine SHOW TABLES;
2019-03-22 11:24:56,176 INFO sqlalchemy.engine.base.Engine ()

[('Table1',), ('Table2',), ('Table3',)]
2019-03-22 11:24:56,177 INFO sqlalchemy.engine.base.Engine SHOW TABLES;
2019-03-22 11:24:56,177 INFO sqlalchemy.engine.base.Engine ()
2019-03-22 11:24:56,177 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "test_sqlalchemy.py", line 20, in <module>
    print([t for t in b.execute("SHOW TABLES;")])
  ...
  File "/home/user/.py2env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1046, 'No database selected')
[SQL: SHOW TABLES;]
(Background on this error at: http://sqlalche.me/e/e3q8)

Only the first connection has been actually attached to the database, all others will fail with a No database selected exception.

Is there any way to achieve that or I will have to create a new engine each time ?

ttk203
  • 347
  • 5
  • 10
  • 2
    When you do `engine.execute('USE MySchema')` a connection is made to the database, the statement executed and the connection returned to the connection pool. Then `a = engine.connect()` gets that same connection from the pool but the next two calls to connect are new connections that know nothing about the USE statement executed on the first connection so they would also have to have the USE statement called on them. I think the usual way to do this would be to have two different engines for connecting to each schema. – SuperShoot Mar 22 '19 at 12:21

0 Answers0