Connecting to Vertica database using SQLAlchemy

Question

I'm trying to connect to a Vertica database using SQLAlchemy.

I came across and installed a Vertica dialect at https://github.com/jamescasbon/vertica-sqlalchemy. I've also installed pyodbc.

Using a basic tutorial at http://www.pythoncentral.io/sqlalchemy-orm-examples/, I have the following code snippet :-

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)

engine = create_engine(sa.engine.url.URL(drivername='vertica+pyodbc',
       username='<username>',password='<password>',
       host='<host>',database='<db name>',))


session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

When this runs I get the traceback :-

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/schema.py", line 3291, in    create_all
tables=tables)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1546, in _run_visitor
with self._optional_conn_ctx_manager(connection) as conn:
File "/usr/lib/python2.7/contextlib.py", line 17, in __enter__
return self.gen.next()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1539, in    _optional_conn_ctx_manager
with self.contextual_connect() as conn:
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1729, in contextual_connect
self.pool.connect(),
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 332, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 626, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 433, in checkout
rec = pool._do_get()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 945, in _do_get
return self._create_connection()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 278, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 404, in __init__
self.connection = self.__connect()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 527, in __connect
connection = self.__pool._creator()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 95, in connect
connection_invalidated=invalidated
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 89, in connect
return dialect.connect(*cargs, **cparams)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 376, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)') None None

The last line I guess is the one to look at :--

sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)') None None

Connections can be made to the database from other clients so the credentials etc are fine.

I'm not sure what is causing this - can anyone help?

Thanks in advance!

Answer 1

This is setup for Ubuntu 14.04 assuming you have a driver installed in /opt/vertica/ and using HP Vertica from this Dockerfile https://hub.docker.com/r/sumitchawla/vertica/ and have https://github.com/jamescasbon/vertica-sqlalchemy.

/etc/vertica.ini

[Driver]
ErrorMessagesPath = /opt/vertica/lib64/
ODBCInstLib = /usr/lib/x86_64-linux-gnu/libodbcinst.so
DriverManagerEncoding=UTF-16

~/.odbc.ini

[ODBC Data Sources]
vertica = "My Database"

[verticadsn]
Description = My Database
Driver = /opt/vertica/lib64/libverticaodbc.so
Database = docker
Servername = 127.0.0.1
UID = dbadmin
PWD =

If you did everything right, this command should return your version of Vertica

engine = create_engine('vertica+pyodbc://dbadmin:@verticadsn')
engine.connect().scalar('select version()')

Answer 2

SQLAlchemy is using unixODBC to connect to Vertica. You need to install the drivers and set up a DSN

You should be able to connect with these parameters. This is what worked for me in my previous SQLAlchemy / Vertica project. Also, if this doesn't work, I would make sure it is configured properly and that you can connect using isql (comes with unixODBC).

drivername='vertica+pyodbc',
username='myuser',
password='mypassword',
host='hostname',
database='DBNAME',

You can also do this for a DSN connection:

engine = create_engine('vertica+pyodbc://username:password@mydsn')