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

Return to the regular view of this page.

Python

The Vertica Python drivers provide an interface for Python client applications to interact with the database.

The Vertica Python drivers provide an interface for Python client applications to interact with the database.

Prerequisites

You must configure a Python development environment before creating Python client applications.

1 - 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

2 - Querying the database with 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