How to connect to Vertica using pyodbc

Question

I read the iODBC documentation regarding odbc.ini , and the Vertica Documentation . I also saw a question with the same error , but I can not get the connection string to work, as suggested in the previous answer to the question.

What I have:

/etc/odbcinst.ini

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

/etc/odbc.ini

  [ODBC Data Sources]
 Vertica DB1 = db1 database on HP Vertica

 [VerticaDB1]
 Description = db1 database on HP Vertica
 Driver = HPVertica
 Database = db1
 Servername = 10.0.0.67
 UID = dbadmin
 PWD = 
 Port = 5433
 Locale = en_GB

 [ODBC]
 Threading = 1 

~/.odbc.ini

  [DEFAULT]

 Driver = VerticaDB1 

Testing with 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 1
 1 rows fetched 

odbcinst -j outputs:

  unixODBC 2.2.14
 DRIVERS ............: /etc/odbcinst.ini
 SYSTEM DATA SOURCES: /etc/odbc.ini
 FILE DATA SOURCES ..: / etc / ODBCDataSources
 USER DATA SOURCES ..: /root/.odbc.ini
 SQLULEN Size .......: 8
 SQLLEN Size ........: 8
 SQLSETPOSIROW Size .: 8 

In Python using "VerticaDB1":

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

In Python using "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

Try connecting using DSN :

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

Alternatively, you can connect using DRIVER , but you need to provide additional information such as database, host and port:

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