Vertica Python Connection

Question

I'm trying to connect to a Vertica database via Python. Here is what I have so far.

Using vertica_python:

! pip install vertica_python
from vertica_python import connect
conn_info = {'host': '192.168...',
             'port': my_port_number,
             'user': 'my_uid',
             'password': 'my_pwd',
             'database': 'my_dbname',
             # 10 minutes timeout on queries
             'read_timeout': 600,
             # default throw error on invalid UTF-8 results
             'unicode_error': 'strict',
             # SSL is disabled by default
             'ssl': False}
connection = vertica_python.connect(**conn_info)

Gives the following error:

---------------------------------------------------------------------------
ConnectionError                           Traceback (most recent call last)
<ipython-input-3-629986180e1e> in <module>()
----> 1 connection = connect(host = "192.168...", port = my_port_number, user = 'my_uid', password ='my_pwd', database = 'my_dbname')

/Users/MyUserName/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/vertica_python/__init__.pyc in connect(**kwargs)
     28 def connect(**kwargs):
     29     """Opens a new connection to a Vertica database."""
---> 30     return Connection(kwargs)

/Users/MyUserName/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/vertica_python/vertica/connection.pyc in __init__(self, options)
     36         self.options.setdefault('port', 5433)
     37         self.options.setdefault('read_timeout', 600)
---> 38         self.startup_connection()
     39 
     40     def __enter__(self):

/Users/MyUserName/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/vertica_python/vertica/connection.pyc in startup_connection(self)
    255 
    256         while True:
--> 257             message = self.read_message()
    258 
    259             if isinstance(message, messages.Authentication):

/Users/MyUserName/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/vertica_python/vertica/connection.pyc in read_message(self)
    190                     "Bad message size: {0}".format(size)
    191                 )
--> 192             message = BackendMessage.factory(type_, self.read_bytes(size - 4))
    193             logger.debug('<= %s', message)
    194             return message

/Users/MyUserName/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/vertica_python/vertica/connection.pyc in read_bytes(self, n)
    242             bytes_ = self._socket().recv(n - len(results))
    243             if not bytes_:
--> 244                 raise errors.ConnectionError("Connection closed by Vertica")
    245             results = results + bytes_
    246         return results

ConnectionError: Connection closed by Vertica

Using jaydebeapi gives a different error:

! pip install jaydebeapi
import jaydebeapi
connection = jaydebeapi.connect('com.vertica.Driver', ['jdbc:vertica://...','my_uid','my_pwd'], '/Users/MyUserName/Documents/JAR Files/vertica-jdbc-4.1.14.jar')
RuntimeError: Unable to load DLL [/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Libraries/libjvm.dylib], error = dlopen(/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Libraries/libjvm.dylib, 9): no suitable image found.  Did find:
/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Libraries/libjvm.dylib: mach-o, but wrong architecture at native/common/include/jp_platform_linux.h:45 

Lastly, I tried pyodbc but I'm not able to even import it:

! pip install pyodbc
import pyodbc
ImportError: dlopen(/Users/MyUserName/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pyodbc.so, 2): Library not loaded: /usr/local/lib/libodbc.2.dylib
  Referenced from: /Users/MyUserName/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pyodbc.so
  Reason: image not found 

I'm new to Python so any help is much appreciated

Answer 1

You can pass key/value pair like that in Python.

Use as follow:

from vertica_python import connect
connection = connect(
    host='jdbc:vertica://...',
    port=my_port_number,
    user='my_uid',
    password='my_pwd'
    )

Answer 2

This works for me (change your ip&port, database, user&password),

import vertica_python
conn_info = {'host': '192.168.1.42', 'port': 5543, 'database': 'mydb',
    'user': 'me', 'password': '123',
    'read_timeout': 600, 'unicode_error': 'strict', 'ssl': False}
print conn_info #check that your information is correct
connection = vertica_python.connect(**conn_info)

Answer 3

If you want to connect with pyodbc , your DRIVER is visible in the cat /etc/odbc.ini section:

import pyodbc

cnxn = pyodbc.connect ("DRIVER = {HPVertica}; SERVER = 192.168.20.10; PORT = 5433; DATABASE = mydb; UID = myuser; PWD = mypassword")

Or if you want to use vertica_python , you can connect as follows:

import vertica_python

conn_info = {'host': '192.168.20.10',
              'port': 5433,
              'user': 'myuser',
              'password': 'mypassword',
              'database': 'mydb',
              # default throw error on invalid UTF-8 results
              'unicode_error': 'strict',
              'connection_load_balance': True
              }