The Vertica Database Forums

Posted: **Wed Mar 14, 2012 1:17 pm**

I would like to connect to Vertica using Python 2.7.2. Could someone point me in the right direction?

Thanks,
Patrick

Posted: **Wed Mar 14, 2012 4:00 pm**

Hi Patrick,

In order to connect to Vertica using Python, you have to have the pyodbc module and a Vertica ODBC driver installed on the machine where Python itself is installed. Most Linux distributions have Python installed by default. Although, there is typically a newer version which you can download from the Python Web site http://www.python.org/download.

You can verify the version of Python installed with the python -V command:

Code: Select all

bash-3.2$ python -V
Python 2.4.3

According to Vertica's documentation, their ODBC driver is tested with Python version 2.4 and they say it should also work with versions 2.4 through 2.7. They go so far as to say it should also work with 3.0 but that it hasn't been tested.

Check out this forum link to find out how to install the Vertica ODBC driver on Linux: viewtopic.php?f=21&t=62.

The native python driver is not supported by Vertica. You'll need the pyodbc module which communicates with iODBC/unixODBC driver on UNIX and the ODBC Driver Manager for Windows. The pyodbc module implements the Python Database API Specification v2.0, letting you use ODBC to connect to almost any database from Windows, Linux, Mac OS/X, and other operating systems.

Vertica supports pyodbc version 2.1.6 which you can download from the pyodbc Web site pyodbc Web site http://code.google.com/p/pyodbc/. Follow the set up instructions here http://code.google.com/p/pyodbc/w/list.

Once you have odbc and pyodbc set up you should be able to connect from Python.

Example:

Code: Select all

[root]# python
Python 2.4.3 (#1, Sep  3 2009, 15:37:12)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-46)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> cnxn = pyodbc.connect(vertica_prod01, ansi=True) cursor = cnxn.cursor() # create table cursor.execute("CREATE TABLE python_test(" "ID INT," "PET_NAME VARCHAR(100)")
>>> cursor.execute("SELECT IMPLEMENT_TEMP_DESIGN('PYTHON_TEST')")

Now I can insert some records and select from the table:

Code: Select all

>>> cursor.execute("INSERT INTO python_test VALUES(1,'FUDGE')")
>>> cursor.execute("INSERT INTO python_test VALUES(2,'FIDO')")
>>> cursor.execute("SELECT * FROM python_test")
>>> rows = cursor.fetchall()
>>> for row in rows:
>>> print row

The following is the example output:

Code: Select all

(1L, 'FUDGE') (2L, 'FIDO')

Now do some clean up work:

Code: Select all

cursor.execute("DROP TABLE python_test CASCADE")
cursor.close()
cnxn.close()

Vertica's Programmer's Guide document has an entire section on connecting to Vertica using Python. Check it out if you can!

Posted: **Thu Jul 05, 2012 4:23 am**

Jim, what are you using for your connection string? (See my related question at viewtopic.php?f=21&t=287)

The Vertica Database Forums

Connecting to Vertica

Connecting to Vertica

Re: Connecting to Vertica

Re: Connecting to Vertica