CODEWITHSUNDEEP
pythonsqlsql-serverdatabasepyodbc
Marquis Blount
Marquis Blount

Reputation: 8105

Pyodbc Accessing Multiple Databases on same server

I'm tasked with obtaining data from two MS SQL databases on the same server so i can run a single query that uses info from both databases simultaneously. I am trying to achieve this in python 2.7 with pyodbc 3.0.7. My query would look like this:

     Select forcast.WindGust_Forecast, forcast.Forecast_Date, anoSection.SectionName, refTable.WindGust
  FROM [EO1D].[dbo].[Dashboard_Forecast] forcast
  JOIN [EO1D].[dbo].[Dashboard_AnoSections] anoSection
  ON forcast.Section_ID = anoSection.Record_ID
  JOIN [EO1D].[dbo].[Dashboard_AnoCircuits] anoCircuits
  ON anoSection.Circuit_Number = anoCircuits.Circuit_Number
  JOIN [FTSAutoCaller].[dbo].[ReferenceTable] refTable
  ON anoCircuits.StationCode = refTable.StationCode
  Where refTable.Circuit IS NOT NULL and refTable.StationCode = 'sil'

the typical connection for pyodbc looks like:

cnxn = pyodbc.connect('DRIVER{SQLServer};SERVER=SQLSRV01;DATABASE=DATABASE;UID=USER;PWD=PASSWORD')

Which would only allow access to the database name provided.

how would I go about setting up a connection that allows me access to both databases so this query can be ran. The two database names in my case are EO1D and FTSAutoCaller.

Upvotes: 5

Views: 10434

Answers (2)

Marquis Blount
Marquis Blount

Reputation: 8105

The query takes care of its self although I only referenced one of the tables in the connection the query didnt have an issue connecting to both of the database. Not 100% sure but im assuming it worked because of the prefixed in "[ ]"

Upvotes: 0

Brad
Brad

Reputation: 1367

you're overthinking it. If you setup the connection as you did above, and then simply pass the sql along to a cursor it should work.

import pyodbc
conn_string = '<removed>'
conn = pyodbc.connect(conn_string)
cur = conn.cursor()
query = 'select top 10 * from table1 t1 inner join database2..table2 t2 on t1.id = t2.id'
cur.execute(query)

and you are done (tested in my own environment, clearly the connection string and query were different, but it did work.)

Upvotes: 7

Related Questions