Connect pyodbc to Postgres

Question

Trying to connect to Postgres using pyodbc.

I can connect to the database using isql:

echo "select 1" | isql -v my-connector 

Return:

 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select 1 +------------+ | ?column? | +------------+ | 1 | +------------+ SQLRowCount returns 1 1 rows fetched 

But when I try to connect to pyodbc:

 import pyodbc con = pyodbc.connect("DRIVER={PostgreSQL Unicode}; DATABASE=<dbname>; UID=<username>; PWD=<password>; SERVER=localhost; PORT=5432;") 

I get the following error:

 pyodbc.Error: ('08001', '[08001] [unixODBC]connction string lacks some options (202) (SQLDriverConnect)') 

The obdc.ini file is as follows:

 [my-connector] Description = PostgreSQL connection to '<dbname>' database Driver = PostgreSQL Unicode Database = <dbname> Servername = localhost UserName = <username> Password = <password> Port = 5432 Protocol = 9.3 ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings = 

The odbcinst.ini file is as follows:

 [PostgreSQL ANSI] Description = PostgreSQL ODBC driver (ANSI version) Driver = psqlodbca.so Setup = libodbcpsqlS.so Debug = 0 CommLog = 1 UsageCount = 1 [PostgreSQL Unicode] Description = PostgreSQL ODBC driver (Unicode version) Driver = psqlodbcw.so Setup = libodbcpsqlS.so Debug = 0 CommLog = 1 UsageCount = 1 

Notes:

• Ubuntu 14.04
• Python 3
• Postgresql 9.3

In the past, I used psycopg2 to connect to Postgres, however my current company uses Netezza, Postgres, and MySQL. I want to write 1 connection module and use different drivers to connect to different databases. Any help would be greatly appreciated.

- Thanks

Answer 1

Since you already have a working DSN defined in odbc.ini, you can simply use this:

 con = pyodbc.connect("DSN=my-connector") 

Also, to record that extra spaces in your connection string may be confusing the problem, because it worked for me, in Python 2.7 at least

 import pyodbc conn_str = ( "DRIVER={PostgreSQL Unicode};" "DATABASE=postgres;" "UID=postgres;" "PWD=whatever;" "SERVER=localhost;" "PORT=5432;" ) conn = pyodbc.connect(conn_str) crsr = conn.execute("SELECT 123 AS n") row = crsr.fetchone() print(row) crsr.close() conn.close()