Connect Python script to PostgreSQL database

Question

Hey I'm not sure what is wrong with this connection string, i'm getting the error - pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

here is my script:

import pyodbc
conn_str = (
    "DRIVER={PostgreSQL ANSI};"
    "DATABASE=postgres;"
    "UID=postgres;"
    "PWD=password;"
    "SERVER=localhost;"
    "PORT=5432;"
    )
conn = pyodbc.connect(conn_str)
crsr = conn.execute("SELECT * FROM initialdata limit 50")
row = crsr.fetchone()
print(row)
crsr.close()
conn.close()

and here is my ODBCINST.INI file contents:

[PostgreSQL ANSI(x64) (32 bit)]
Driver=C:\Program Files\psqlODBC\0905\bin\psqlodbc30a.dll
Setup=C:\Program Files\psqlODBC\0905\bin\psqlodbc30a.dll
32Bit=1

[ODBC 32 bit Drivers]
PostgreSQL ANSI(x64) (32 bit)=Installed
PostgreSQL Unicode(x64) (32 bit)=Installed

[PostgreSQL Unicode(x64) (32 bit)]
Driver=C:\Program Files\psqlODBC\0905\bin\psqlodbc35w.dll
Setup=C:\Program Files\psqlODBC\0905\bin\psqlodbc35w.dll
32Bit=1

Any help would be appreciated!

Based on the odbcinst.ini file it looks like your driver name should be: PostgreSQL ANSI(x64) (32 bit) ... not just PostgreSQL ANSI — clockwatcher
What does the name look like in your ODBC Data Source Administrator (odbcad32.exe), Drivers tab. Also... it looks like you're running 32bit drivers on a 64bit machine. Is your python 32 or 64 bit? It should match the drivers that you installed. — clockwatcher

Noor Noor · Accepted Answer · 2019-03-29T18:41:37

check list of drivers by using pyodbc.drivers() and use driver name from it

the driver name should be like PostgreSQL ANSI(x64) instead of PostgreSQL ANSI.

Connect Python script to PostgreSQL database

1 Answers