There are many ways to connect to Vertica from Python. Most of the modern-day applications uses either odbc or jdbc drivers to connect to relational databases. In my other post, we have discussed how to connect to Vertica database using JDBC and Python modules. Other methods include, using vertica_python, vertica_db_client python modules. The vertica_db_client is a Vertica provided Python connector. In this article, we will check method on connecting Vertica using Python pyodbc driver with a working example.
Vertica ODBC Driver
Before trying to connect Vertica from either windows or Linux system, you must install Vertica ODBC driver. Without ODBC driver python will not connect to Vertica.
Configure Vertica ODBC on Linux
Just like many other modern-day analytics databases, Vertica also provides support to ODBC drivers. You must download it from Vertica official website and configure it.
Follow below link to download Vertica ODBC drivers for Linux and windows:
Follow below link to configure ODBC driver:
Install Vertica ODBC on Windows
You can download the Vertica odbc drivers for Vertica from official website and install it required system or server.
Follow below link for more information:
Configure Vertica ODBC on Windows
Vertica ODBC driver may not automatically configured. You can always use windows ODBC data source to setup Vertica ODBC connection.
Follow Below link for more information:
You will not be able to use pyodbc driver without installing and configuring OBDC drivers on required system. This step is one of the pre-requisites to use pyodbc module.
Connecting Vertica using Python pyodbc
Once you have installed required drivers, you are now ready to use pyodbc to connect Vertica analytics database.
Vertica ODBC Connection String
Below is the sample Vertica ODBC connection string:
Driver={Vertica}; Database=databaseName; Servername=ServerName; UID=dbUsername; PWD=dbPassword; PreferredAddressFamily=none; Connecting Vertica using Python pyodbc Example
If you have configured Vertica ODBC drivers properly and you have VSQL data source in place.
Next step would be to write small Python script that uses pyodbc module to connect to Vertica databases.
import pyodbc
# pyodbc connection string
conn = pyodbc.connect("DRIVER={Vertica};SERVER=192.168.239.132;PORT=5433;DATABASE=vmart;UID=dbadmin;PWD=password;")
# Define Cursor
cus=conn.cursor()
# Execute SQL statement to get current datetime and store result in cursor
cus.execute("select now();")
# Display the content of cursor
row = cus.fetchone()
print(row)Output:
(datetime.datetime(2019, 5, 1, 3, 50, 57, 502465), )
You can also use JDBC connector to connect Vertica and export using any programming language. You can read more about this on my other post:
Hope this helps 🙂