Connecting Python to an Oracle Database via pyodbc-CodePudding

I am trying to use pyodbc to connect to an oracle database. Below the Python code I have developed:

import pyodbc
conn=pyodbc.connect('DRIVER={Oracle in OraClient19Home1};Host=xxxxxx.yyy.com;Port=1521;Service Name=myservicename;User ID=myuserid;Password=mypassword')

Unfortunately, the above code does not work as it returns the following error:

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

Try the new cross-platform PowerShell https://aka.ms/pscore6

PS C:\Users\balestram\OneDrive - World Health Organization\Documents\Data Analytics\Python Training> & C:/Users/balestram/AppData/Local/Programs/Python/Python311/python.exe "c:/Users/balestram/OneDrive - World Health Organization/Documents/Data Analytics/Python Training/python"
Traceback (most recent call last):
  File "c:\Users\xxxxxxxxx\OneDrive - xxxxxxxxxxxxxxxxxxxxx\Documents\Data Analytics\Python Training\python", line 2, in <module>
    conn=pyodbc.connect('DRIVER={Oracle in OraClient19Home1};Host=xxxxxx.yyy.com;Port=1521;Service Name=myservicename;User ID=myuserid;Password=mypassword')
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pyodbc.Error: ('HY000', '[HY000] [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error\n (12560) (SQLDriverConnect); [HY000] [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error\n (12560)')
PS C:\Users\xxxxxxxxxx\OneDrive - xxxxxxxxxxxxxxxxxxxxx\Documents\Data Analytics\Python Training>

I must add that the above connection parameters work well if I connect to the Oracle database with Ms Access and/or Ms Power BI.

Grateful to anyone who could put me on the right track.

CodePudding user response：

Try the below and see if its works.Taken from the pyodbc documentation

 pyodbc.connect('DRIVER={Oracle in OraClient19Home1};DBQ=xxxxxx.yyy.com:1521/myservicename;
 UID=myuserid;PWD=mypassword'))"