Connecting to Vertica via Python (Simple Example)

Moderator: NorbertKrupa

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

Connecting to Vertica via Python (Simple Example)

Post by JimKnicely » Tue Oct 02, 2012 4:25 pm

Hi,

Here is a quick example to show how easy it is to connect to Vertica via Python on Linux! For this test I used the 6.0 version of the Vertica ODBC driver.

Once you've installed the Vertica ODBC drivers you'll need to create a DSN in the /etc/odbc.ini file.

Here is the contents of my odbc.ini file for this test:

Code: Select all

[root@HADOOPTST01 jknicely]# cat /etc/odbc.ini
[vertica]
Driver = /opt/vertica/lib64/libverticaodbc.so
Servername = 10.255.XX.XXXX
Database = verticadb
Port = 5433
UserName = myuser
Password = mypassword
You'll also need a vertica.ini file to define some Vertica-specific settings required by the ODBC client driver. Here's mine:

Code: Select all

[root@HADOOPTST01 etc]# cat /etc/vertica.ini
[Driver]
DriverManagerEncoding=UTF-16
ODBCInstLib=/usr/lib64/libodbcinst.so
ErrorMessagesPath=/opt/vertica/lib64
LogLevel=4
LogPath=/tmp
Next, I'll connect to Vertica in Python and select data from a table named test:

Code: Select all

[root@HADOOPTST01 jknicely]# python
Python 2.6.6 (r266:84292, Sep 11 2012, 08:34:23)
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> cnxn = pyodbc.connect("DSN=vertica")
>>> cursor = cnxn.cursor()
>>> cursor.execute("SELECT col1 FROM test")
<pyodbc.Cursor object at 0xff2390>
>>> rows = cursor.fetchall()
>>> for row in rows:
...  print row
...
('Hi There!', )
>>> cursor.close()
>>> cnxn.close()
>>>
[1]+  Stopped                 python
Really simple! Have fun!
Jim Knicely

Image

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

Post Reply

Return to “Python”