> -----Original Message----- > From: sqlalchemy@googlegroups.com > [mailto:sqlalch...@googlegroups.com] On Behalf Of kkapron > Sent: 01 October 2009 21:27 > To: sqlalchemy > Subject: [sqlalchemy] Select from multiple databases > > > Hello, I'm a beginner in sqlalchemy and I've got a problem with select > statement. > > example: > > tabela T1: id, username, date (in database B1) > tabela T2: id, user_id, ip (in database B2) > > I've got two engines > ... > self.db['B1']['engine'] = create_engine('mysql://B1') > self.db['B1']['conn'] = self.db['B1']['engine'].connect() > self.db['B1']['metadata'] = MetaData() > self.db['B1']['metadata'].bind = self.db['B1']['engine'] > self.db['B1']['metadata'].create_all() > > self.db['B2']['engine'] = create_engine('mysql://B2') > self.db['B2']['conn'] = self.db['B1']['engine'].connect() > self.db['B2']['metadata'] = MetaData() > self.db['B2']['metadata'].bind = self.db['B2']['engine'] > self.db['B2']['metadata'].create_all() > ... > > and tables > ... > self.tables['T1'] = Table('T1', self.db['B1']['metadata'], > autoload=True) > self.tables['T2'] = Table('T2', self.db['B2']['metadata'], > autoload=True) > ... > > and a test query: > ... > T1 = self.tables['T1'] > T2 = self.tables['T2'] > s = select( [T1.c.username, T2.c.ip], (T2.c.user_id == T1.c.id) ) > s.execute().fetchall() > > and error: > > (ProgrammingError) (1146, "Table 'B2.T1' doesn't exist")... > > it's true that T1 doesn't exists in B2, because it exist in T2. > > Does anybody know how to help me? :) >
I don't think you can do this - the 'select' function represents a single SQL SELECT statement, which can't be sent to 2 different database servers. You'd need to run two separate queries and join the results in Python. If your tables are actually in different schemas but the same MySQL instance (ie. If you can connect to the MySQL server and write 'SELECT * FROM B1.T1' and 'SELECT * FROM B2.T2'), then you can use a single engine and metadata to access them both by specifying the schema in your Table definitions. See: http://www.sqlalchemy.org/docs/05/metadata.html#specifying-the-schema-na me for an example. Hope that helps, Simon --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
