This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Programming Python client applications

To use Python with Vertica, you must either install the Vertica Open Source Python Client or install the pyodbc module and a Vertica ODBC driver on the machine where Python is installed.

To use Python with Vertica, you must either install the Vertica Open Source Python Client or install the pyodbc module and a Vertica ODBC driver on the machine where Python is installed. See Python prerequisites.

Python on Linux

Most Linux distributions come with Python preinstalled. If you want a more recent version, you can download and build it from the source code, though sometimes RPMs are also available. See the Python Web site and click an individual release for details. See also Python documentation.

To determine the Python version on your Linux operating systems, type the following at a command prompt:

# python -V

The system returns the version; for example:

Python 3.3.4

Python on Windows

Windows operating systems do not include Python by default. There are several different distributions of Python for windows:

  • The ActiveState Web site distributes a free Windows installer for Python called ActivePython.

  • The official Python.org site has installer packages for several versions of Python.

If you need installation instructions for Windows, see Using Python on Windows at python.org.

Python and unicode

When you are using Python, be sure that all of your components are using the same unicode text encoding. By default, the DSN Parameter ColumnsAsChar causes the ODBC driver to report CHAR and VARCHAR values as SQL_WCHAR. The driver returns these values to the driver manager in the encoding expected by the driver manager, as controlled by the DriverManagerEncoding parameter in vertica.ini. Similarly, your Python application must use the encoding expected by the driver manager. If any of these components use different encodings, your output can become garbled.

The Vertica Python client

The Vertica Python client is open source and available at https://github.com/vertica/vertica-python.

Using pyodbc and Vertica

Before you can connect to Vertica using pyodbc, you need to download the pyodbc module, which communicates with iODBC/unixODBC driver on UNIX operating systems and the ODBC Driver Manager for Windows operating systems.

The pyodbc module is an open source , MIT-licensed Python module, letting you use ODBC to connect to almost any database from Windows, Linux, Mac OS/X, and other operating systems.

Vertica supports multiple versions of pyodbc. See Python prerequisites for additional details.

Download the source distribution from the pyodbc Web site, unpack it and build it. Note that you need the unixODBC development package (in addition to the regular build tools) to build pyodbc. For example, on RedHat/CentOS run: yum install unixODBC-devel, and on Ubuntu run: sudo apt-get install unixodbc-dev. See the pyodbc wiki for detailed instructions.

Python clients and the UUID data type

Both the Vertica Python client and Vertica ODBC driver (that pyodbc interacts with) do not support Vertica's native UUID data type. Values retrieved using these drivers from a UUID column are converted to strings. When your client queries the metadata for a UUID column, the drivers report its data type as a string. Convert any UUID values that you want to insert into a UUID column to strings. Vertica automatically converts these values into the native UUID data type before inserting them into a table.

External resources

1 - Open source Python client

The Vertica Python client is now open source.

The Vertica Python client is now open source. You can download it at https://github.com/vertica/vertica-python.

2 - Configuring the ODBC run-time environment on Linux

To configure the ODBC run-time environment on Linux:.

To configure the ODBC run-time environment on Linux:

  1. Create the odbc.ini file if it does not already exist.

  2. Add the ODBC driver directory to the LD_LIBRARY_PATH system environment variable:

    export LD_LIBRARY_PATH=/path-to-vertica-odbc-driver:$LD_LIBRARY_PATH

These steps are relevant only for unixODBC and iODBC. See their respective documentation for details on odbc.ini.

See also

3 - Querying the database using Python and pyodbc

The example session below uses pyodbc with the Vertica ODBC driver to connect Python to the Vertica database.

The example session below uses pyodbc with the Vertica ODBC driver to connect Python to the Vertica database.

Example script

The following example script shows how to query Vertica using Python 3, pyodbc, and an ODBC DSN.


import pyodbc
cnxn = pyodbc.connect("DSN=VerticaDSN", ansi=True)
cursor = cnxn.cursor()
# create table
cursor.execute("CREATE TABLE TEST("
    "C_ID  INT,"
    "C_FP  FLOAT,"
    "C_VARCHAR VARCHAR(100),"
    "C_DATE DATE, C_TIME TIME,"
    "C_TS TIMESTAMP,"
    "C_BOOL BOOL)")
cursor.execute("INSERT INTO test VALUES(1,1.1,'abcdefg1234567890','1901-01-01','23:12:34','1901-01-01 09:00:09','t')")
cursor.execute("INSERT INTO test VALUES(2,3.4,'zxcasdqwe09876543','1991-11-11','00:00:01','1981-12-31 19:19:19','f')")
cursor.execute("SELECT * FROM TEST")
rows = cursor.fetchall()
for row in rows:
    print(row, end='\n')
cursor.execute("DROP TABLE TEST CASCADE")
cursor.close()
cnxn.close()

The resulting output displays:

(2, 3.4, 'zxcasdqwe09876543', datetime.date(1991, 11, 11), datetime.time(0, 0, 1), datetime.datetime(1981, 12, 31, 19, 19, 19), False)
(1, 1.1, 'abcdefg1234567890', datetime.date(1901, 1, 1), datetime.time(23, 12, 34), datetime.datetime(1901, 1, 1, 9, 0, 9), True)

Notes

SQLPrimaryKeys returns the table name in the primary (pk_name) column for unnamed primary constraints. For example:

  • Unnamed primary key:

    CREATE TABLE schema.test(c INT PRIMARY KEY);

SQLPrimaryKeys
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "KEY_SEQ", "PK_NAME" <Null>, "SCHEMA", "TEST", "C", 1, "TEST"

  • Named primary key:

    CREATE TABLE schema.test(c INT CONSTRAINT pk_1 PRIMARY KEY);

SQLPrimaryKeys
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "KEY_SEQ", "PK_NAME" <Null>, "SCHEMA", "TEST", "C", 1, "PK_1"

OpenText recommends that you name your constraints.

See also