Unable to connect to PostgreSQL using pyodbc

Question

I am new to pyodbc.

I am unable to connect to PostgreSQL with the below code -

cnxn = pyodbc.connect('Driver={PostgreSQL35W};Server=dummy.cag.com;Port=5432;Database=postgres;Uid=postgres;Pwd=postgres;')

It throws "pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')" error.

I have already configured the DataSource as in

Even the test connection is successful.

Looking for help in this.

FULL CODE - It is just a simple Program Unit -

import pyodbc as pyodbc

def perform_db_operation():
  
    cnxn = pyodbc.connect('Driver={PostgreSQL35W};Server=dummy.cag.com;Port=5432;Database=postgres;Uid=postgres;Pwd=postgres;')
    select_cursor = cnxn.cursor()
    sql_select_stmt = "SELECT * from public.gluetable212a"
    select_cursor.execute(sql_select_stmt)
    result_set = select_cursor.fetchall()

    for current_record in result_set:
        passenger_id = current_record[0]
        age = current_record[1]
        fare = current_record[2]
        ticket = current_record[3]
        # insert_cursor.execute(sql_insert_stmt, emp_name, emp_id)
    print(passenger_id, age, fare, ticket)
    select_cursor.close()
    cnxn.commit()
    cnxn.close()


perform_db_operation()

RUN CONSOLE LOG -

C:\Users\KarthikDeepan.Gujulu\PycharmProjects\MyProj\venv\Scripts\python.exe C:/Users/KarthikDeepan.Gujulu/PycharmProjects/MyProj/ICOMP/PyODBCTrail1.py
Traceback (most recent call last):
  File "C:/Users/KarthikDeepan.Gujulu/PycharmProjects/MyProj/ICOMP/PyODBCTrail1.py", line 38, in <module>
    perform_db_operation()
  File "C:/Users/KarthikDeepan.Gujulu/PycharmProjects/MyProj/ICOMP/PyODBCTrail1.py", line 16, in perform_db_operation
    cnxn = pyodbc.connect('Driver={PostgreSQL ANSI};Data Source=decheqaperf01v.asg.com;Port=5432;Database=postgres;Uid=postgres;Pwd=postgres;')
pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Process finished with exit code 1

as the error states, you're missing the Data Source entry. Have a look at connectionstrings.com/postgresql — ewokx
– ewokx, Commented Jul 15, 2020 at 7:54
Thanks, but I have already checked this. Is my connection string correct ? Should I give Driver=PostgreSQL35W in here? Or am I missing something ? — kdkarthik
– kdkarthik, Commented Jul 15, 2020 at 8:06
Data Source entry is needed. In your connection string, you're missing the Data Source entry. — ewokx
– ewokx, Commented Jul 15, 2020 at 8:13
Please post the relevant code and the full traceback. That error is starting to not make any sense — ewokx
– ewokx, Commented Jul 15, 2020 at 8:30
@kdkarthik - Check the list returned by pyodbc.drivers() to see what drivers are available to your Python app. — Gord Thompson
– Gord Thompson, Commented Jul 15, 2020 at 11:15

kdkarthik · Accepted Answer · 2020-07-16 08:09:15Z

1

@GordThompson, Your comment helped me to find what is the issue. I had the downloaded and installed the driver x64 version, that time pyodbc.drivers() is not showing up PostgresDriver as one of the drivers. Then I downloaded x86 version, it now started showing the drivers list ('ODBC Data Sources 32-bit' application and pyodbc.drivers() output) and the Program Unit works as Expected.

CONSOLE OUTPUT:

['Driver da Microsoft para arquivos texto (*.txt; *.csv)', 'Driver do Microsoft Access (*.mdb)', 'Driver do Microsoft dBase (*.dbf)', 'Driver do Microsoft Excel(*.xls)', 'Driver do Microsoft Paradox (*.db )', 'Microsoft Access Driver (*.mdb)', 'Microsoft Access-Treiber (*.mdb)', 'Microsoft dBase Driver (*.dbf)', 'Microsoft dBase-Treiber (*.dbf)', 'Microsoft Excel Driver (*.xls)', 'Microsoft Excel-Treiber (*.xls)', 'Microsoft ODBC for Oracle', 'Microsoft Paradox Driver (*.db )', 'Microsoft Paradox-Treiber (*.db )', 'Microsoft Text Driver (*.txt; *.csv)', 'Microsoft Text-Treiber (*.txt; *.csv)', 'SQL Server', 'PostgreSQL ANSI', 'PostgreSQL Unicode']
10 employee1 10 address1
20 employee2 20 address2
30 employee3 30 address3
40 employee4 40 address4
50 employee5 50 address5

edited Jul 16, 2020 at 8:09

answered Jul 16, 2020 at 7:41

kdkarthik

1131 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kdkarthik Over a year ago

@GordThompson, I still have a question, my PC is 64 bit operating system, x64 based processor, how can we make sure that which version (x64, x86) based driver to be installed for this ?

Gord Thompson Over a year ago

The "bitness" of the driver must match the "bitness" of the version of Python you are running. You are running 32-bit Python so you need to install the 32-bit version of the ODBC driver.

Collectives™ on Stack Overflow

Unable to connect to PostgreSQL using pyodbc

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related