将 pyodbc 连接到 Postgres [英] Connect pyodbc to Postgres

问题描述

尝试使用 pyodbc 连接到 Postgres.

Trying to connect to Postgres using pyodbc.

我可以使用 isql 连接到数据库:

I can connect to the DB with isql:

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

返回:

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

但是当我尝试与 pyodbc 连接时:

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;")

我收到以下错误:

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

obdc.ini 文件如下所示:

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 文件如下所示:

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

注意事项:

• Ubuntu 14.04
• Python 3
• PostgreSQL 9.3

我过去使用 psycopg2 连接到 Postgres，但是我现在的公司使用 Netezza、Postgres 和 MySQL.我想编写1个连接模块，并使用不同的驱动程序连接到不同的数据库.任何帮助将不胜感激.

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.

--谢谢

推荐答案

由于您已经在 odbc.ini 中定义了一个可用的 DSN，您可以直接使用它:

Since you already have a working DSN defined in odbc.ini you can just use that:

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

另外，作为记录，连接字符串中的额外空格可能会混淆问题，因为这对我来说很好，至少在 Python 2.7 下

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()

这篇关于将 pyodbc 连接到 Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！