How to connect python with vertica using ODBC?

Question

import sqlalchemy as sa
conn = "vertica+pyodbc://dbadmin:password@VMart"
sa.create_engine(conn, pool_size=10, max_overflow=20)
%load_ext sql
%sql vertica+pyodbc://VMart

error

(pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

(Background on this error at: http://sqlalche.me/e/rvf5)
Connection info needed in SQLAlchemy format, example:

 postgresql://username:password@hostname/dbname<br>
           or an existing connection: dict_keys([])

First

import sqlalchemy as sa
import urllib
sa.create_engine('vertica+vertica_python://dbadmin:[email protected]:5433/VMart')
%load_ext sql
%sql vertica+pyodbc://VMart

Second

import sqlalchemy as sa
sa.create_engine('vertica+vertica_python://dbadmin:password@VMart')

import pyodbc

Third

conn = pyodbc.connect("DRIVER=Vertica;SERVER=192.168.11.132;DATABASE=VMart;PORT=5433;UID=dbadmin;PWD=password")

same error

error but I already test vertica odbc in windows10. It connect successfuled

(pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/rvf5)
Connection info needed in SQLAlchemy format, example:

postgresql://username:password@hostname/dbname
or an existing connection: dict_keys([])

calestini calestini · Accepted Answer · 2019-08-07T19:00:19

Might not get to the root of your problem, but below are a few steps that could help in making a connection:

Have you tried installing sqlalchemy-vertica driver?. Specifically I would recommend installing with vertica-python. This is one driver for the connection with vertica in python and I found it works pretty well (caveats later)

pip install sqlalchemy-vertica[vertica-python]

Have you imported vertica-python? Not sure this will impact the code, but it definitely helps to remove as an issue. In case you have not installed it:

pip install vertica-python

Edit: As a matter of fact if all you need is to read data, this package worked well for me on itself. Below is a sample code that is currently working for me:

import vertica_python
conn_info = {
    'host': 'host-ip',
    'port': port,
    'user': 'usr',
    'password':password,
    'database': 'db',
    # autogenerated session label by default,
    'session_label': 'current_session',
    # default throw error on invalid UTF-8 results
    'unicode_error': 'strict',
    # SSL is disabled by default
    'ssl': False,
    # using server-side prepared statements is disabled by default
    'use_prepared_statements': False,
    # connection timeout is not enabled by default
    # 'connection_timeout': 1
}

vertica_python.connect(**conn_info)

Making the connection. I would recommend your first line to connect:

sa.create_engine('vertica+vertica_python://dbadmin:[email protected]:5433/VMart')

Caveats using Pandas:

I had issues with sending str variables to the db using pandas.to_sql. If that is your case, just make sure to pass the types explicitly for those columns as VARCHAR (it will force to TEXT for some reason). This function from this question might help you:

def updateType(df_para):
    dtypedict = {}  # create and empty dictionary
    for i,j in zip(df_para.columns, df_para.dtypes):
        if "object" in str(j):
            dtypedict.update({i: sa.types.VARCHAR})

    return dtypedict

updatedict = updateType(df)

I would receive a weird error when using to_sql:

NotImplementedError: executemany is implemented for simple INSERT statements only.

Did not find a proper solution but tried to remediate it using the chunksize param, and it seems to have worked.

How to connect python with vertica using ODBC?

1 Answers