CODEWITHSUNDEEP
sqlsql-serverpython-3.xpyodbcopenquery
SheerKahn
SheerKahn

Reputation: 321

pyodbc - OPENQUERY support?

Python Version: 3.7.4
PyODBC Version: 4.0.26-cp37

I am attempting to connect to a Microsoft SQL server. I am using the following code and am able to query the database using a standard query:

Example 1:

cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=SERVER-A;"
                      "Database=DATABASE-A;"
                      "uid=xxx,pwd=yyy")

df = pd.read_sql_query('SELECT * FROM DATABASE-A.dbo.XXX')

However the query that I need to execute is:

SELECT * FROM OPENQUERY(SERVER-B, SELECT DateTime = convert(nvarchar, DateTime, 21), item1,item2,item3 FROM TableY')

I receive the "Execution failed on sql 42000" error code & "Could not find stored procedure 'SQL' 2812".

The above open query works in SQL Server Management Studio.

I am unsure if the error is due to string syntax or if open queries are not supported by the library.

Code Source

Upvotes: 0

Views: 1870

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

Yes, pyodbc does indeed support OPENQUERY. The following works fine for me:

print('Info: Python version ' + sys.version)
# Info: Python version 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 21:26:53) [MSC v.1916 32 bit (Intel)]
print('Info: pyodbc version ' + pyodbc.version)
# Info: pyodbc version 4.0.26

connection_string = 'DRIVER=ODBC Driver 17 for SQL Server;SERVER=(local)\SQLEXPRESS;DATABASE=myDb;Trusted_Connection=yes;UseFMTONLY=Yes;'
cnxn = pyodbc.connect(connection_string, autocommit=True)
crsr = cnxn.cursor()

sql = "SELECT * FROM OPENQUERY([SERVER-B], 'SELECT item1,item2,item3 FROM TableY')"
print(crsr.execute(sql).fetchall())
# [(1, 'HoHoHo', datetime.datetime(2019, 12, 25, 0, 0))]

Upvotes: 2

Related Questions