python - 如何使用pyodbc连接到vertica

Question

我读过 iODBC documentation关于 odbc.ini 和 Vertica documentation .我还看到一个问题with the same error ，但无法使连接字符串按照上一个问题的答案中的建议工作。

我目前拥有的:

/etc/odbcinst.ini

[HPVertica]Description = HP Vertica ODBC DriverDriver = /opt/vertica/lib64/libverticaodbc.so

/etc/odbc.ini

[ODBC Data Sources]VerticaDB1 = db1 database on HP Vertica[VerticaDB1]Description = db1 database on HP VerticaDriver = HPVerticaDatabase = db1Servername = 10.0.0.67UID = dbadminPWD = Port = 5433Locale = en_GB[ODBC]Threading = 1

~/.odbc.ini

[DEFAULT]Driver = VerticaDB1

使用 isql 进行测试

[root@ip-10-0-0-67 /]# echo "select 1;" | isql -v VerticaDB1+---------------------------------------+| Connected!                            ||                                       || sql-statement                         || help [tablename]                      || quit                                  ||                                       |+---------------------------------------+SQL> select 1;+---------------------+| ?column?            |+---------------------+| 1                   |+---------------------+SQLRowCount returns 11 rows fetched

odbcinst -j 输出:

unixODBC 2.2.14DRIVERS............: /etc/odbcinst.iniSYSTEM DATA SOURCES: /etc/odbc.iniFILE DATA SOURCES..: /etc/ODBCDataSourcesUSER DATA SOURCES..: /root/.odbc.iniSQLULEN Size.......: 8SQLLEN Size........: 8SQLSETPOSIROW Size.: 8

在使用“VerticaDB1”的 Python 中:

>>> import pyodbc>>> conn = pyodbc.connect("DRIVER={VerticaDB1};UID={dbadmin};PWD={...}")Traceback (most recent call last):  File "", line 1, in pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

在 Python 中使用“HPVertica”:

>>> import pyodbc>>> conn = pyodbc.connect("DRIVER={HPVertica};UID={dbadmin};PWD={...}")Traceback (most recent call last):  File "", line 1, in pyodbc.Error: ('HY000', '[HY000] [unixODBC][Vertica][ODBC] (10430) Not enough information provided to establish a connection to the data source and specified to not prompt for more information. (10430) (SQLDriverConnect)')

Answer 1

尝试使用 DSN 连接:

conn = pyodbc.connect("DSN=VerticaDB1;UID=dbadmin;PWD=mypassword")

或者，您可以使用DRIVER 进行连接，但您需要提供更多信息，例如哪个数据库、主机和端口:

conn = pyodbc.connect("DRIVER=HPVertica;SERVER=10.0.0.67;DATABASE=db1;PORT=5433;UID=dbadmin;PWD=mypassword")