DOCSEARCHbyAlgolia
Submit Docs

vertica-python

repository·master·Indexed 18 days ago

https://github.com/vertica/vertica-python

A native Python client for the Vertica database and the official open-source replacement for the deprecated vertica_db_client. It provides functionality for managing database sessions via the Connection class and executing queries through the Cursor class, supporting both server-side and client-side binding, Kerberos and OAuth authentication, TLS/SSL configuration, and efficient data loading using COPY FROM STDIN.

Tokens
13K
Snippets
33
Records
35
Agent score
14%

What's inside vertica-python

  1. Understand rowcount behavior for SELECT and DML statements

    master

    The rowcount attribute in vertica_python behaves differently than standard DBAPI implementations:

    SELECT statements

    Immediately after a SELECT execution, cur.rowcount will be -1, indicating the row count is unknown. The value is updated incrementally as data is streamed from the server.

    DML statements (INSERT, UPDATE, DELETE)

    After executing a DML statement, cur.rowcount will initially be -1. To retrieve the actual number of affected rows, you must fetch the result as a single-element row.

    # SELECT behavior
cur.execute('SELECT 10 things')
print(cur.rowcount)  # -1
cur.fetchone()
print(cur.rowcount)  # 1

# DML behavior
cur.execute("DELETE 3 things")
print(cur.rowcount)  # -1
print(cur.fetchone()[0])  # 3

  2. Use Binary Data Transfer

    master

    By default, connections use text format for data transfer. Setting binary_transfer to True uses binary format, which is generally more efficient and requires less bandwidth.

    Caveats:

    • FLOAT: Binary may offer slightly higher precision.
    • TIMESTAMPTZ: Text uses the session timezone; binary might use the local timezone if it fails to retrieve the session timezone.
    • NUMERIC: In newer server versions, binary transfer is forcibly disabled for NUMERIC data by the server regardless of client settings.
    import vertica_python

conn_info = {
    'host': '127.0.0.1',
    'port': 5433,
    'user': 'some_user',
    'password': 'some_password',
    'database': 'vdb',
    'binary_transfer': True  # False by default
    }

# Server enables binary transfer
with vertica_python.connect(**conn_info) as conn:
    cur = conn.cursor()
    ...

  3. Configure TLS/SSL for Vertica connections

    master

    TLS/SSL is controlled by tlsmode and ssl. If both are provided, tlsmode takes precedence.

    TLS Modes

    • 'disable': Only try a non-TLS connection.
    • 'prefer' (Default): Try TLS first; fallback to non-TLS if the server doesn't support it. If TLS is enabled on the server but the attempt fails, the client rejects the connection.
    • 'require': Connect using TLS without verifying certificates.
    • 'verify-ca': Connect using TLS and confirm the server certificate is signed by a trusted CA (requires tls_cafile).
    • 'verify-full': Connect using TLS, confirm CA signature, and verify the hostname matches the certificate (requires tls_cafile).

    Using ssl.SSLContext

    For advanced customization, pass an ssl.SSLContext object to the ssl parameter. This allows for manual control over certificate verification and mutual TLS (mTLS) by loading client certificates and keys into the context.

    import vertica_python
import ssl

# Example: TLSMode: verify-full with Mutual Mode (mTLS)
ssl_context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
ssl_context.verify_mode = ssl.CERT_REQUIRED
ssl_context.check_hostname = True
ssl_context.load_verify_locations(cafile='/path/to/ca_file.pem')
# Load client certificate and key for mutual authentication
ssl_context.load_cert_chain(certfile='/path/to/client.pem', keyfile='/path/to/client.key')

conn_info = {
    'host': '127.0.0.1',
    'user': 'some_user',
    'database': 'a_database',
    'tlsmode': 'verify-full',
    'ssl': ssl_context
}

with vertica_python.connect(**conn_info) as connection:
    # do things

  4. Use server-side binding with prepared statements

    master

    Server-side binding sends the query and parameters to the Vertica server separately. This is useful for preventing SQL injection.

    Key requirements:

    • Enable via use_prepared_statements: True in the connection info or as an argument in execute*().
    • Use question marks (?) as placeholders.
    • Limitation: Does not support queries containing multiple statements (compound statements).
    conn_info = {
    'host': '127.0.0.1',
    'user': 'some_user',
    'password': 'some_password',
    'database': 'a_database',
    'use_prepared_statements': True,
}

with vertica_python.connect(**conn_info) as connection:
    cur = connection.cursor()
    cur.execute("INSERT INTO tbl VALUES (?, ?)", [1, 'aa'])
    cur.execute("SELECT * FROM tbl WHERE a>=? AND a<=?", (2, 4))
    import vertica_python

conn_info = {
    'host': '127.0.0.1',
    'user': 'some_user',
    'password': 'some_password',
    'database': 'a_database',
    'use_prepared_statements': True,
}

with vertica_python.connect(**conn_info) as connection:
    cur = connection.cursor()
    cur.execute("CREATE TABLE tbl (a INT, b VARCHAR)")
    cur.execute("INSERT INTO tbl VALUES (?, ?)", [1, 'aa'])
    cur.execute("INSERT INTO tbl VALUES (?, ?)", [2, 'bb'])
    cur.executemany("INSERT INTO tbl VALUES (?, ?)", [(3, 'foo'), (4, 'xx'), (5, 'bar')])
    cur.execute("COMMIT")

    cur.execute("SELECT * FROM tbl WHERE a>=? AND a<=? ORDER BY a", (2,4))
    print(cur.fetchall())

  5. Use client-side binding with named or format parameters

    master

    Client-side binding merges the query and parameters on the client side before sending the final SQL string to the server. This is more robust for complex types and allows for more efficient batch inserts.

    Key requirements:

    • Set use_prepared_statements=False (either at connection level or in execute*()).
    • Named parameters: Use :name placeholders and pass a dictionary.
    • Positional parameters: Use %s placeholders and pass a tuple/list. Note: Always use %s, never %d or %f.
    • Do not quote placeholders: VALUES (%s) is correct; VALUES ('%s') is incorrect.

    For complex types (like arrays), you may need explicit typecasting in the SQL string (e.g., %s::ARRAY[DATE]).

    # Named parameters
cur.execute("SELECT * FROM a_table WHERE a = :propA AND b = :propB", {'propA': 1, 'propB': 'val'}, use_prepared_statements=False)

# Positional parameters
cur.execute("SELECT * FROM a_table WHERE a = %s AND b = %s", (1, 'val'), use_prepared_statements=False)

# Complex types with casting
cur.execute("INSERT INTO table VALUES (%s, %s::ARRAY[DATE])", [100, [date(2021, 6, 10)]], use_prepared_statements=False)

  7. Install vertica-python

    master

    You can install vertica-python using pip for the latest release or directly from the master branch on GitHub. You can also install from source using setup.py.

    # Latest release version
pip install vertica-python

# Latest commit on master branch
pip install git+https://github.com/vertica/vertica-python.git@master

  8. Enable Kerberos authentication

    master

    For Unix-like systems, vertica-python supports optional Kerberos authentication. This requires the kerberos Python package. Because kerberos is a Python extension module, you must also have python-dev installed on your system.

    1. Install python-dev using your system package manager (e.g., sudo apt-get install python-dev or sudo yum install python-dev).
    2. Install the kerberos package via pip.
    pip install kerberos

  9. Authenticate using Kerberos

    master

    To use Kerberos authentication, ensure a valid Ticket-Granting Ticket (TGT) is available (verify with klist, or obtain via kinit). You can customize the authentication by providing kerberos_service_name (defaults to "vertica") and kerberos_host_name (defaults to the value of host).

    import vertica_python

conn_info = {
    'host': '127.0.0.1',
    'port': 5433,
    'user': 'some_user',
    'password': 'some_password',
    'database': 'a_database',
    # The service name portion of the Vertica Kerberos principal
    'kerberos_service_name': 'vertica_krb',
    # The instance or host name portion of the Vertica Kerberos principal
    'kerberos_host_name': 'vcluster.example.com'
}

with vertica_python.connect(**conn_info) as conn:
    # do things

  10. Authenticate using OAuth

    master

    To authenticate via OAuth, provide a valid oauth_access_token in your connection information.

    import vertica_python

conn_info = {
    'host': '127.0.0.1',
    'port': 5433,
    'database': 'a_database',
    # valid OAuth access token
    'oauth_access_token': 'xxxxxx'
}

with vertica_python.connect(**conn_info) as conn:
    # do things

  11. Load data using COPY FROM LOCAL with Cursor.execute()

    master

    The COPY FROM LOCAL method allows loading data from the client system.

    Prerequisites: Only files on the client system should be loaded via these methods. For files already on the server system, use Cursor.execute("COPY target-table FROM 'path-to-data'").

    Key Features:

    • Local Files: Specify file paths directly in the SQL string.
    • Local STDIN: Use the copy_stdin parameter in Cursor.execute() to pass a file-like object or a list of file-like objects.
    • Compound Statements: You can execute multiple COPY statements in one call by providing a list of file-like objects to copy_stdin and using cur.nextset() to iterate through results.
    • Configuration: Setting the connection option disable_copy_local: True prevents COPY LOCAL operations (including writing rejects/exceptions to local files).
    import sys
import vertica_python
from io import StringIO

conn_info = {
    'host': '127.0.0.1',
    'user': 'some_user',
    'password': 'some_password',
    'database': 'a_database',
    'use_prepared_statements': False
}

with vertica_python.connect(**conn_info) as connection:
    cur = connection.cursor()

    # 1. Copy from local files
    cur.execute("COPY table(field1, field2) FROM LOCAL 'data_Jan_*.csv' DELIMITER ','"
                " REJECTED DATA 'path/to/write/rejects.txt'",
                buffer_size=65536)

    # 2. Copy from local stdin (single file-like object)
    cur.execute("COPY table(field1, field2) FROM LOCAL STDIN DELIMITER ','", copy_stdin=sys.stdin)

    # 3. Copy from local stdin (compound statements with list of files)
    with open('f1.csv', 'r') as fs1, open('f2.csv', 'r') as fs2:
        cur.execute("COPY tlb1(field1, field2) FROM LOCAL STDIN DELIMITER ',';"
                    "COPY tlb2(field1, field2) FROM LOCAL STDIN DELIMITER ',';",
                    copy_stdin=[fs1, fs2], buffer_size=65536)
        print("Rows loaded 1:", cur.fetchall())
        cur.nextset()
        print("Rows loaded 2:", cur.fetchall())

    # 4. Copy from local stdin (StringIO)
    data = "Anna|123-456-789\nBrown|555-444-3333"
    cur.execute("COPY customers (firstNames, phoneNumbers) FROM LOCAL STDIN DELIMITER '|'",
                copy_stdin=StringIO(data))

  12. Configure Connection Load Balancing

    master

    To spread connection overhead across a cluster, set connection_load_balance to True. Both the client and the server must have load balancing enabled for this to function. If the server has it disabled, the client's request will be ignored.

    import vertica_python

conn_info = {
    'host': '127.0.0.1',
    'port': 5433,
    'database': 'vdb',
    'connection_load_balance': True
}

# Server enables load balancing
with vertica_python.connect(**conn_info) as conn:
    cur = conn.cursor()
    cur.execute("SELECT NODE_NAME FROM V_MONITOR.CURRENT_SESSION")
    print("Client connects to primary node:", cur.fetchone()[0])
    cur.execute("SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN')")

with vertica_python.connect(**conn_info) as conn:
    cur = conn.cursor()
    cur.execute("SELECT NODE_NAME FROM V_MONITOR.CURRENT_SESSION")
    print("Client redirects to node:", cur.fetchone()[0])