vertica-sqlalchemy-dialect 0.0.8.2

Vertica-Python

vertica-python is needed to use the Vertica-SQLAlchemy-Dialect. The connector does not need to be installed as the dialect installation takes care of it.

Note: We recommend using the vertica-python connector. However, the dialect also allows connecting using pyodbc. More instructions are at the end of this README.

Connection Parameters

Vertica SQLAlchemy Dialect uses the following syntax for the connection string used to connect to Vertica and initiate a session:

'vertica+vertica_python://<user>:<password>@<host_name>/<database_name>'

Where:

• <user> is the login name for your Vertica user.

• <password> is the password for your Vertica user.

• <host_name> is the IP/FQDN of your Vertica Host.

• <database_name> is the name of your Vertica Database.

You can optionally specify the initial database and schema for the Vertica session by including them at the end of the connection string, separated by /. You can also specify other supported parameters by vertica-python at the end of the connection string:

'vertica+vertica_python://<user>:<password>@<host_name>/<database_name>?session_label=sqlalchemy&connection_load_balance=1'

For more information, check out the connection options of vertica-python.

Opening and Closing Connection

Open a connection by executing engine.connect(); avoid using engine.execute(). Make certain to close the connection by executing connection.close() before engine.dispose(); otherwise, the Python Garbage collector removes the resources required to communicate with Vertica, preventing the Python connector from closing the session properly.

engine = create_engine(...)
connection = engine.connect()
try:
    connection.execute(<SQL>)
finally:
    connection.close()
    engine.dispose()

Using pyodbc instead of vertica-python

You may use pyodbc instead of vertica-python for the connection.

Create a Vertica DSN

You will need to have a Vertica ODBC driver installed from Vertica-Client-Drivers. For steps to install ODBC for Vertica, follow official Vertica Docs.

For example, you will need to configure these files with your credentials:

/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 =

Then use the Vertica DSN in a file like so:

from sqlalchemy import create_engine

engine = sa.create_engine('vertica+pyodbc://@verticadsn')
try:
    res = engine.connect().scalar('select version();')
    print(res)
finally:
    connection.close()
    engine.dispose()

This should display the Vertica version info: “Vertica Analytic Database v12.0.0-0”.