CODEWITHSUNDEEP
pythonsql-serversqlalchemy
bboumend
bboumend

Reputation: 510

join on two different databases with sqlalchemy

Im trying to make a join on 2 databases in MSSQL.

here is the SQL query:

SELECT od.Indice, cs.Argued
FROM HN_Ondata.dbo.ODCalls as od
JOIN HN_ADMIN.dbo.CallStatus as cs ON od.CallStatusGroup = cs.StatusGroup

I have tried:

always end up with this error:

pymssql.ProgrammingError: (208, b"Invalid object name 'CallStatus'.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")

The current solution i got is using session.execute and write down raw sql, i could stick with it but im currious is there any way doing that with sqlalchemy ORM ?

EDIT 1:

Here is my code :

 db1 = DatabaseManager(settings.DATABASE['hrm'], database='HN_Ondata')
 db2 = DatabaseManager(settings.DATABASE['hrm'], database='HN_ADMIN')

 metadata1 = MetaData(bind=db1.database)
 metadata2 = MetaData(bind=db2.database)

 table1 = Table('ODCalls', metadata1, autoload=True)
 table2 = Table('CallStatus', metadata2, autoload=True)


 with db1.session(raise_err=True) as session:
     result = (
         session
         .query(table1.c.Indice, table2.c.Argued)
         .join(table2, table1.c.CallStatusGroup == table2.c.StatusGroup)
         .all()
     )

who produce the following query:

SELECT [ODCalls].[Indice] AS [ODCalls_Indice], [CallStatus].[Argued] AS [CallStatus_Argued] 
FROM [ODCalls]
JOIN [CallStatus] ON [ODCalls].[CallStatusGroup] = [CallStatus].[StatusGroup]

Upvotes: 2

Views: 3308

Answers (1)

bboumend
bboumend

Reputation: 510

Found the solution thank's to Ryan Gadsdon and Ilja Everilä pointing me the way.

You need to precise database.schema in Table schema parameters like this:

table1 = Table('ODCalls', metadata1, autoload=True, schema='HN_Odcalls.dbo')

Specify Database in schema is needed only if the table refer to a database who your engine is not connected, if you precise database.schema in schema parameters you can then use the table with any engine connected to any database on the same server.

http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#multipart-schema-names

Upvotes: 3

Related Questions