Bo Qiang
Bo Qiang

Reputation: 789

Connect to Oracle database using pyodbc

I would like to connect to an Oracle database with python through pyodbc. I have installed oracle driver and I tried the following script:

import pyodbc
connectString = """
                DRIVER={Oracle in OraClient12Home1};
                SERVER=some_oracle_db.com:1521;
                SID=oracle_test;
                UID=user_name;
                PWD=user_pass
                """
cnxn = pyodbc.connect(connectString)

I got the following error message:

cnxn = pyodbc.connect(connectString)

Error: ('HY000', '[HY000] [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error\n (12560) (SQLDriverConnect)')

What's wrong here?

Upvotes: 4

Views: 8890

Answers (4)

Angad
Angad

Reputation: 21

Why keyword DBQ works and SID/Service Name does not, see the section 21.4.1 Format of the Connection String in Oracle 12c documentation. https://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#ADFNS1183/

or google keywords for odbc oracle 12c

Upvotes: 1

kung-fu-kenny
kung-fu-kenny

Reputation: 101

So close...

connectString = """
        DRIVER={Oracle in OraClient12Home1};
        SERVER=some_oracle_db.com:1521;
        DBQ=oracle_test;
        Uid=user_name;
        Pwd=user_name
        """

I replaced SID with DBQ

Upvotes: 0

Hinit Kumar
Hinit Kumar

Reputation: 1

You have to specify server or hostname (or IP address in connection string for your database server is running.

Upvotes: 0

Ryan Dooley
Ryan Dooley

Reputation: 254

Looks Like your missing a PORT

Try this way

NOTE: Depending on your Server the syntax can be different this will work for Windows without DSN using an SQL Server Driver.

connectString = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;PORT=1433;DATABASE=testdb;UID=me;PWD=pass')

This is the connection, you still need a cursor and to use execute along with an SQL Statement..

Upvotes: 0

Related Questions