Connect pyodbc to Postgres
22,215
Since you already have a working DSN defined in odbc.ini you can just use that:
con = pyodbc.connect("DSN=my-connector")
Also, for the record, that extra whitespace in your connection string may have been confusing the issue because this worked fine for me, under 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()
Author by
Christopher Carlson
Updated on December 24, 2020Comments
-
Christopher Carlson over 3 years
Trying to connect to Postgres using pyodbc.
I can connect to the DB with isql:
echo "select 1" | isql -v my-connector
Returns:
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select 1 +------------+ | ?column? | +------------+ | 1 | +------------+ SQLRowCount returns 1 1 rows fetched
But when I try to connect with 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)')
obdc.ini file looks like this:
[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 =
odbcinst.ini file looks like this:
[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
I have used psycopg2 in the past 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 the different databases. Any help would be greatly appreciated.
-- Thanks
-
Pedro Moisés Camacho Ureña about 4 yearsthis approach also solved the following error that I was having:stackoverflow.com/questions/61278211/…