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 https://www.connectionstrings.com/postgresql/ — ewokx, Jul 15 '20 at 07: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, Jul 15 '20 at 08:06
Data Source entry is needed. In your connection string, you're missing the ```Data Source``` entry. — ewokx, Jul 15 '20 at 08:13
Okay but actually I am using psqlodbc whose connection string is given as below as in [https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/] `Driver={PostgreSQL};Server=IP address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword;` So there is no Data Source parameter needed. Correct me If this is wrong — kdkarthik, Jul 15 '20 at 08:28
Please post the relevant code and the full traceback. That error is starting to not make any sense — ewokx, Jul 15 '20 at 08:30
might this help?https://stackoverflow.com/questions/16280304/pyodbc-data-source-name-not-found-and-no-default-driver-specified — ewokx, Jul 15 '20 at 09:19
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/217886/discussion-between-ewong-and-kdkarthik). — ewokx, Jul 15 '20 at 09:21
@kdkarthik - Check the list returned by `pyodbc.drivers()` to see what drivers are available to your Python app. — Gord Thompson, Jul 15 '20 at 11:15
@GordThompson, I checked and PostgreDriver is not showing up `['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', 'SQL Server']`, what might be the issue then ? — kdkarthik, Jul 16 '20 at 07:00

kdkarthik · Answer 1 · 2020-07-16T08:09:15.973

@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

@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 ? — kdkarthik, Jul 16 '20 at 07:48
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. — Gord Thompson, Jul 16 '20 at 10:49

Unable to connect to PostgreSQL using pyodbc

1 Answers1