0

I have a SQL script that joins two CTE's together, one CTE is from database1 and another CTE is from database2. It can be run successfully in SQL Server.

However, I'd like to establish a connection between the SQL Server to Python using pyodbc package (like below) so that I can read-in the results directly. Since we can only specify one database in the following code, how do I establish the connection if my SQL script contains two different databases?

conn = pyodbc.connect('Driver= {SQL Server Native Client 11.0};'
                      'Server= server;'
                      'Database = database1;'
                      'InitialCatalog=dbo;'
                      'Trusted_Connection=yes;')

query = open(file_path, 'r') 
df = pd.read_sql_query(query.read(), conn)
query.close()
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jiamei
  • 405
  • 3
  • 14
  • 1
    What happens when you try running the query via pandas `.read_sql_query()`? If it contains three-part table names like `database2.dbo.tablename` then it may work as-is. That's how we refer to databases – a.k.a. "catalogs" – other than the default (which is the one specified by `DATABASE=whatever` in the connection string). – Gord Thompson Aug 21 '21 at 13:40
  • I did put three-part table names for each CTE. Python still gives me the error message. – Jiamei Aug 25 '21 at 22:29
  • … and what error message might that be? – Gord Thompson Aug 25 '21 at 23:50

0 Answers0