CODEWITHSUNDEEP
sql-serversqlalchemy
user41140
user41140

Reputation: 133

SQLAlchemy: Multiple databases (on the same server) in a single session?

I'm running MS SQL Server and am trying to perform a JOIN between two tables located in different databases (on the same server). If I connect to the server using pyodbc (without specifying a database), then the following raw SQL works fine.

SELECT * FROM DatabaseA.dbo.tableA tblA 
         INNER JOIN DatabaseB.dbo.tableB tblB 
         ON tblA.id = tblB.id

Unfortunately, I just can't seem to get the analog to work using SQLAlchemy. I've seen this topic touched on in a few places:

Most recommend to use different engines / sessions, but I crucially need to perform joins between the databases, so I don't think this approach will be helpful. Another typical suggestion is to use the schema parameter, but this does not seem to work for me. For example the following does not work.

engine = create_engine('mssql+pyodbc://...')  #Does not specify database

metadataA = MetaData(bind=engine, schema='DatabaseA.dbo', reflect=True)
tableA = Table('tableA', metadataA, autoload=True)

metadataB = MetaData(bind=engine, schema='DatabaseB.dbo', reflect=True)
tableB = Table('tableB', metadataB, autoload=True)

I've also tried varients where schema='DatabaseA' and schema='dbo'. In all cases SQLAlchemy throws a NoSuchTableError for both tables A and B. Any ideas?

Upvotes: 10

Views: 2235

Answers (2)

Wes H
Wes H

Reputation: 4439

If you can create a synonym in one of the databases, you can keep your query local to that single database.

USE DatabaseB;
GO
CREATE SYNONYM dbo.DbA_TblA FOR DatabaseA.dbo.tableA;
GO

Your query then becomes:

SELECT * FROM dbo.DbA_TblA tblA 
     INNER JOIN dbo.tableB tblB 
     ON tblA.id = tblB.id

Upvotes: 2

zzzeek
zzzeek

Reputation: 75207

I'm able to run a test just like this here, reflecting from two remote databases, and it works fine.

  1. Using a recent SQLAlchemy (0.8.3 recommended at least)?

  2. turn on "echo='debug'" - what tables is it finding?

  3. after the reflect all, what's present in metadataA.tables metadataB.tables?

  4. is the casing here exactly what's on SQL server ? (e.g. tableA). Using a case sensitive name like that will cause it to be quoted.

Upvotes: 0

Related Questions