Connecting to Vertica

Moderator: NorbertKrupa

Post Reply
User avatar
pkelly
Newbie
Newbie
Posts: 2
Joined: Tue Feb 07, 2012 2:40 am
Contact:

Connecting to Vertica

Post by pkelly » 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
Patrick Kelly
http://www.proplabs.org/

"Continuous effort - not strength or intelligence - is the key to unlocking our potential."
- Winston Churchill

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Connecting to Vertica

Post by JimKnicely » 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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

yang
Newbie
Newbie
Posts: 11
Joined: Wed Jul 04, 2012 11:27 am

Re: Connecting to Vertica

Post by yang » 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)

Post Reply

Return to “Python”