postgresql 将 pyodbc 连接到 Postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34407531/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connect pyodbc to Postgres
提问by Christopher Carlson
Trying to connect to Postgres using pyodbc.
尝试使用 pyodbc 连接到 Postgres。
I can connect to the DB with isql:
我可以使用 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:
但是当我尝试与 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:
obdc.ini 文件如下所示：
[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:
odbcinst.ini 文件如下所示：
[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
- Ubuntu 14.04
- 蟒蛇 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.
我过去曾使用 psycopg2 连接到 Postgres，但是我现在的公司使用 Netezza、Postgres 和 MySQL。我想编写1个连接模块，并使用不同的驱动程序连接到不同的数据库。任何帮助将不胜感激。
-- Thanks
- 谢谢
采纳答案by Gord Thompson
Since you already have a working DSN defined in odbc.ini you can just use that:
由于您已经在 odbc.ini 中定义了一个工作 DSN，您可以使用它：
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
另外，作为记录，您的连接字符串中的额外空格可能会混淆问题，因为这对我来说很好用，至少在 Python 2.7 下
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()
