import vertica_python

conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             # autogenerated session label by default,
             'session_label': 'some_label',
             # default throw error on invalid UTF-8 results
             'unicode_error': 'strict',
             # SSL is disabled by default
             'ssl': False,
             # autocommit is off by default
             'autocommit': True,
             # using server-side prepared statements is disabled by default
             'use_prepared_statements': False,
             # connection timeout is not enabled by default
             # 5 seconds timeout for a socket operation (Establishing a TCP connection or read/write operation)
             'connection_timeout': 5}

# simple connection, with manual close
try:
    connection = vertica_python.connect(**conn_info)
    # do things
finally:
    connection.close()

# using `with` for auto connection closing after usage
with vertica_python.connect(**conn_info) as connection:
    # do things

import vertica_python

connection_str = ('vertica://admin@localhost:5433/db1?connection_load_balance=True&connection_timeout=1.5&'
                  'session_label=vpclient+123%7E456')
print(vertica_python.parse_dsn(connection_str))
# {'user': 'admin', 'host': 'localhost', 'port': 5433, 'database': 'db1',
#  'connection_load_balance': True, 'connection_timeout': 1.5, 'session_label': 'vpclient 123~456'}

additional_info = {
    'password': 'some_password', 
    'backup_server_node': ['10.6.7.123', ('10.20.82.77', 6000)]  # invalid value to be set in a connection string
    }

with vertica_python.connect(dsn=connection_str, **additional_info) as conn:
   # do things

import vertica_python

# [TLSMode: require]
conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             'ssl': True}
connection = vertica_python.connect(**conn_info)

import vertica_python
import ssl

# [TLSMode: require]
# Ensure connection is encrypted.
ssl_context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
ssl_context.check_hostname = False
ssl_context.verify_mode = ssl.CERT_NONE

conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             'ssl': ssl_context}
connection = vertica_python.connect(**conn_info)


# [TLSMode: verify-ca]
# Ensure connection is encrypted, and client trusts server certificate.
ssl_context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
ssl_context.verify_mode = ssl.CERT_REQUIRED
ssl_context.check_hostname = False
ssl_context.load_verify_locations(cafile='/path/to/ca_file.pem') # CA certificate used to verify server certificate

conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             'ssl': ssl_context}
connection = vertica_python.connect(**conn_info)


# [TLSMode: verify-full]
# Ensure connection is encrypted, client trusts server certificate,
# and server hostname matches the one listed in the server certificate.
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') # CA certificate used to verify server certificate

conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             'ssl': ssl_context}
connection = vertica_python.connect(**conn_info)

import vertica_python
import ssl

# [TLSMode: verify-full]
# Ensure connection is encrypted, client trusts server certificate,
# and server hostname matches the one listed in the server certificate.
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') # CA certificate used to verify server certificate

# For Mutual mode, provide client certificate and client private key to ssl_context.
# CA certificate used to verify client certificate should be set at the server side.
ssl_context.load_cert_chain(certfile='/path/to/client.pem', keyfile='/path/to/client.key')

conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             'ssl': ssl_context}
connection = vertica_python.connect(**conn_info)

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

import vertica_python
import logging

## Example 1: write DEBUG level logs to './vertica_python.log'
conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             'log_level': logging.DEBUG}
with vertica_python.connect(**conn_info) as connection:
    # do things

## Example 2: write WARNING level logs to './path/to/logs/client.log'
conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             'log_path': 'path/to/logs/client.log'}
with vertica_python.connect(**conn_info) as connection:
   # do things

## Example 3: write INFO level logs to '/home/admin/logs/vClient.log'
conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             'log_level': logging.INFO,
             'log_path': '/home/admin/logs/vClient.log'}
with vertica_python.connect(**conn_info) as connection:
   # do things

## Example 4: use root handlers to process logs by setting 'log_path' to '' (empty string) 
conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             'log_level': logging.DEBUG,
             'log_path': ''}
with vertica_python.connect(**conn_info) as connection:
    # do things

import vertica_python

conn_info = {'host': 'unreachable.server.com',
             'port': 888,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             'backup_server_node': ['123.456.789.123', 'invalid.com', ('10.20.82.77', 6000)]}
connection = vertica_python.connect(**conn_info)

import vertica_python

conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             '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])

## Output
#  Client connects to primary node: v_vdb_node0003
#  Client redirects to node: v_vdb_node0005

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()
    ...

import vertica_python

conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'user': 'some_user',
             'password': 'some_password',
             'database': 'vdb'}

# Connect to a vertica database
with vertica_python.connect(**conn_info) as conn:
    # Open a cursor to perform database operations
    # vertica-python only support one cursor per connection
    cur = conn.cursor()
    
    # Execute a command: create a table
    cur.execute("CREATE TABLE tbl (a INT, b VARCHAR)")
    
    # Insert a row
    cur.execute("INSERT INTO tbl VALUES (1, 'aa')")
    inserted = cur.fetchall()  # [[1]]
    
    # Bulk Insert with executemany()
    # Pass data to fill a query placeholders and let vertica-python perform the correct conversion
    cur.executemany("INSERT INTO tbl(a, b) VALUES (?, ?)", [(2, 'bb'), (3, 'foo'), (4, 'xx'), (5, 'bar')], use_prepared_statements=True)
    # OR
    # cur.executemany("INSERT INTO tbl(a, b) VALUES (%s, %s)", [(6, 'bb'), (7, 'foo'), (8, 'xx'), (9, 'bar')], use_prepared_statements=False)
    
    # Query the database and obtain data as Python objects.
    cur.execute("SELECT * FROM tbl")
    datarow = cur.fetchone()         # [1, 'aa']
    remaining_rows = cur.fetchall()  # [[2, 'bb'], [3, 'foo'], [4, 'xx'], [5, 'bar']]

    # Make the changes to the database persistent
    conn.commit()
    
    # Execute a query with MULTIPLE statements
    cur.execute("SELECT 1; SELECT 2; ...; SELECT N")
    while True:  # Fetch the result set for each statement
        rows = cur.fetchall()
        print(rows)
        if not cur.nextset():
            break
    # Output:        
    # [[1]]
    # [[2]]
    # ...
    # [[N]]

cur = connection.cursor()
cur.execute("SELECT * FROM a_table LIMIT 2")

for row in cur.iterate():
    print(row)
# [ 1, 'some text', datetime.datetime(2014, 5, 18, 6, 47, 1, 928014) ]
# [ 2, 'something else', None ]

cur = connection.cursor()
cur.execute("SELECT * FROM a_table LIMIT 2")
cur.fetchall()
# [ [1, 'something'], [2, 'something_else'] ]

cur = connection.cursor('dict')
cur.execute("SELECT * FROM a_table LIMIT 2")
cur.fetchall()
# [ {'id': 1, 'value': 'something'}, {'id': 2, 'value': 'something_else'} ]
connection.close()

cur.execute('SELECT 1; SELECT 2;')

cur.fetchone()
# [1]
cur.fetchone()
# None

cur.nextset()
# True

cur.fetchone()
# [2]
cur.fetchone()
# None

cur.nextset()
# False

import vertica_python

# Enable using server-side prepared statements at connection level
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))
    cur.fetchall()
    # [[2, 'bb'], [3, 'foo'], [4, 'xx']]

import vertica_python

# Enable using server-side prepared statements at connection level
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)")

    # Executing compound statements
    cur.execute("INSERT INTO tbl VALUES (?, ?); COMMIT", [1, 'aa'])
    # Error message: Cannot insert multiple commands into a prepared statement

    # Disable prepared statements but forget to change placeholders (?)
    cur.execute("INSERT INTO tbl VALUES (?, ?); COMMIT;", [1, 'aa'], use_prepared_statements=False)
    # TypeError: not all arguments converted during string formatting

    cur.execute("INSERT INTO tbl VALUES (%s, %s); COMMIT;", [1, 'aa'], use_prepared_statements=False)
    cur.execute("INSERT INTO tbl VALUES (:a, :b); COMMIT;", {'a': 2, 'b': 'bb'}, use_prepared_statements=False)

# Disable using server-side prepared statements at connection level
conn_info['use_prepared_statements'] = False
with vertica_python.connect(**conn_info) as connection:
    cur = connection.cursor()

    # Try using prepared statements
    cur.execute("INSERT INTO tbl VALUES (?, ?)", [3, 'foo'])
    # TypeError: not all arguments converted during string formatting

    cur.execute("INSERT INTO tbl VALUES (?, ?)", [3, 'foo'], use_prepared_statements=True)

    # Query using named parameters
    cur.execute("SELECT * FROM tbl WHERE a>=:n1 AND a<=:n2 ORDER BY a", {'n1': 2, 'n2': 4})
    cur.fetchall()
    # [[2, 'bb'], [3, 'foo']]

cur = connection.cursor()
data = {'propA': 1, 'propB': 'stringValue'}
cur.execute("SELECT * FROM a_table WHERE a = :propA AND b = :propB", data, use_prepared_statements=False)
# converted into a SQL command similar to: SELECT * FROM a_table WHERE a = 1 AND b = 'stringValue'

cur.fetchall()
# [ [1, 'stringValue'] ]

cur = connection.cursor()
data = (1, "O'Reilly")
cur.execute("SELECT * FROM a_table WHERE a = %s AND b = %s" % data) # WRONG: % operator
cur.execute("SELECT * FROM a_table WHERE a = %d AND b = %s", data)  # WRONG: %d placeholder
cur.execute("SELECT * FROM a_table WHERE a = %s AND b = %s", data)  # correct
# converted into a SQL command: SELECT * FROM a_table WHERE a = 1 AND b = 'O''Reilly'

cur.fetchall()
# [ [1, "O'Reilly"] ]

>>> cur.execute("INSERT INTO table VALUES (':propA')", {'propA': "someString"}) # WRONG
>>> cur.execute("INSERT INTO table VALUES (:propA)", {'propA': "someString"})   # correct
>>> cur.execute("INSERT INTO table VALUES ('%s')", ("someString",)) # WRONG
>>> cur.execute("INSERT INTO table VALUES (%s)", ("someString",))   # correct

from datetime import date
cur.execute("CREATE TABLE table (a INT, b ARRAY[DATE])")
value = [date(2021, 6, 10), date(2021, 6, 12), date(2021, 6, 30)]

cur.execute("INSERT INTO table VALUES (%s, %s)", [100, value], use_prepared_statements=False)  # WRONG
# Error Message: Column "b" is of type array[date] but expression is of type array[varchar], Sqlstate: 42804, 
# Hint: You will need to rewrite or cast the expression

cur.execute("INSERT INTO table VALUES (%s, %s::ARRAY[DATE])", [100, value], use_prepared_statements=False)  # correct
# converted into a SQL command: INSERT INTO vptest VALUES (100, ARRAY['2021-06-10','2021-06-12','2021-06-30']::ARRAY[DATE])

# Client-side binding of cursor.executemany is different from cursor.execute internally
# But it also supports some of complex types mapping
cur.executemany("INSERT INTO table (a, b) VALUES (%s, %s)", [[100, value]], use_prepared_statements=False)

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y

# Adapter should return a string value
def adapt_point(point):
    return "STV_GeometryPoint({},{})".format(point.x, point.y)

cur = conn.cursor()
cur.register_sql_literal_adapter(Point, adapt_point)

cur.execute("INSERT INTO geom_data (geom) VALUES (%s)", [Point(1.23, 4.56)])
cur.execute("select ST_asText(geom) from geom_data")
cur.fetchall()
# [['POINT (1.23 4.56)']]

cur = conn.cursor
cur.object_to_sql_literal("O'Reilly")  # "'O''Reilly'"
cur.object_to_sql_literal(None)  # "NULL"
cur.object_to_sql_literal(True)  # "True"
cur.object_to_sql_literal(Decimal("10.00000"))  # "10.00000"
cur.object_to_sql_literal(datetime.date(2018, 9, 7))  # "'2018-09-07'"
cur.object_to_sql_literal(Point(-71.13, 42.36))  # "STV_GeometryPoint(-71.13,42.36)" if you registered in previous step

# Note the query parameter placeholders difference!
cur.executemany("INSERT INTO tbl(a, b) VALUES (?, ?)", [(2, 'bb'), (3, 'foo'), (4, 'xx'), (5, 'bar')], use_prepared_statements=True)

cur.executemany("INSERT INTO tbl(a, b) VALUES (%s, %s)", [(6, 'bb'), (7, 'foo'), (8, 'xx'), (9, 'bar')], use_prepared_statements=False)
cur.executemany("INSERT INTO tbl(a, b) VALUES (:a, :b)", [{'a': 2, 'b': 'bb'}, {'a': 3, 'b': 'foo'}], use_prepared_statements=False)

cur = connection.cursor()

# inline commit (when 'use_prepared_statements' is False)
cur.execute("INSERT INTO a_table (a, b) VALUES (1, 'aa'); commit;")

# commit in execution
cur.execute("INSERT INTO a_table (a, b) VALUES (1, 'aa')")
cur.execute("INSERT INTO a_table (a, b) VALUES (2, 'bb')")
cur.execute("commit;")

# connection.commit()
cur.execute("INSERT INTO a_table (a, b) VALUES (1, 'aa')")
connection.commit()

# connection.rollback()
cur.execute("INSERT INTO a_table (a, b) VALUES (0, 'bad')")
connection.rollback()

import vertica_python

# Enable autocommit at startup
conn_info = {'host': '127.0.0.1',
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             # autocommit is off by default
             'autocommit': True,
             }
             
with vertica_python.connect(**conn_info) as connection:
    # Check current session autocommit setting
    print(connection.autocommit)    # should be True
    # If autocommit is True, statements automatically commit their transactions when they complete.
    
    # Set autocommit setting with attribute
    connection.autocommit = False
    print(connection.autocommit)    # should be False
    # If autocommit is False, the methods commit() or rollback() must be manually invoked to terminate the transaction.

cur = connection.cursor()
cur.copy("COPY test_copy (id, name) FROM stdin DELIMITER ',' ",  csv)

with open("/tmp/binary_file.csv", "rb") as fs:
    cursor.copy("COPY table(field1, field2) FROM STDIN DELIMITER ',' ENCLOSED BY '\"'",
                fs, buffer_size=65536)

import sys
import vertica_python

conn_info = {'host': '127.0.0.1',
             'user': 'some_user',
             'password': 'some_password',
             'database': 'a_database',
             # False by default
             #'disable_copy_local': True,
             # Don't support executing COPY LOCAL operations with prepared statements
             'use_prepared_statements': False
             }

with vertica_python.connect(**conn_info) as connection:
    cur = connection.cursor()
    
    # Copy from local file
    cur.execute("COPY table(field1, field2) FROM LOCAL"
                " 'data_Jan_*.csv','data_Feb_01.csv' DELIMITER ','"
                " REJECTED DATA 'path/to/write/rejects.txt'"
                " EXCEPTIONS 'path/to/write/exceptions.txt'",
                buffer_size=65536
    )
    print("Rows loaded:", cur.fetchall())
    
    # Copy from local stdin
    cur.execute("COPY table(field1, field2) FROM LOCAL STDIN DELIMITER ','", copy_stdin=sys.stdin)
    print("Rows loaded:", cur.fetchall())

    # Copy from local stdin (compound statements)
    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())
    
    # Copy from local stdin (StringIO)
    from io import StringIO
    data = "Anna|123-456-789\nBrown|555-444-3333\nCindy|555-867-53093453453\nDodd|123-456-789\nEd|123-456-789"
    cur.execute("COPY customers (firstNames, phoneNumbers) FROM LOCAL STDIN ENFORCELENGTH RETURNREJECTED AUTO",
                copy_stdin=StringIO(data))

from multiprocessing import Process
import time
import vertica_python

def cancel_query(connection, timeout=5):
    time.sleep(timeout)
    connection.cancel()

# Example 1: Cancel the query before Cursor.execute() return.
#            The query stops executing in a shorter time after the cancel message is sent.
with vertica_python.connect(**conn_info) as conn:
    cur = conn.cursor()

    # Call cancel() from a different process
    p1 = Process(target=cancel_query, args=(conn,))
    p1.start()

    try:
        cur.execute("<Long running query>")
    except vertica_python.errors.QueryCanceled as e:
        pass
    p1.join()
    
# Example 2: Cancel the query after Cursor.execute() return.
#            Less number of rows read after the cancel message is sent.
with vertica_python.connect(**conn_info) as conn:
    cur = conn.cursor()
    cur.execute("SELECT id, time FROM large_table")
    nCount = 0
    try:
        while cur.fetchone():
            nCount += 1
            if nCount == 100:
                conn.cancel()
    except vertica_python.errors.QueryCanceled as e:
        pass
        # nCount is less than the number of rows in large_table

with vertica_python.connect(**conn_info) as conn:
    cur = conn.cursor()
    sql = "select 'foo'::VARCHAR, 100::INT, '2001-12-01 02:50:00'::TIMESTAMP"
    
    #### Convert SQL types to Python objects ####
    print(cur.disable_sqldata_converter)   # Default is False
    # False
    cur.execute(sql)
    print(cur.fetchall())
    # [['foo', 100, datetime.datetime(2001, 12, 1, 2, 50)]]
    
    #### No Conversion: return raw bytes data ####
    cur.disable_sqldata_converter = True   # Set attribute to True
    cur.execute(sql)
    print(cur.fetchall())
    # [[b'foo', b'100', b'2001-12-01 02:50:00']]

import vertica_python
from vertica_python.datatypes import VerticaType

conn_info = {'host': '127.0.0.1',
             'port': 5433,
             ...
            }

conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
cur.execute("SELECT 123.45::NUMERIC")
print(cur.fetchone()[0])
# Decimal('123.45')

def convert_numeric(val, ctx):
    # val: bytes - this is a text representation of NUMERIC value
    # ctx: dict - some info that may be useful to the converter
    return float(val)

cur.register_sqldata_converter(VerticaType.NUMERIC, convert_numeric)
cur.execute("SELECT 123.45::NUMERIC")
print(cur.fetchone()[0])
# 123.45

cur.unregister_sqldata_converter(VerticaType.NUMERIC)  # reset
cur.execute("SELECT 123.45::NUMERIC")
print(cur.fetchone()[0])
# Decimal('123.45')

import json
import numpy as np
import vertica_python
from vertica_python.datatypes import VerticaType

conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'binary_transfer': False/True,
             ...
            }

conn = vertica_python.connect(**conn_info)
cur = conn.cursor()

#==================================================
cur.execute("SELECT ARRAY[-1.234, 0, 1.66, null, 50]::ARRAY[FLOAT]")
data = cur.fetchone()[0]
print(type(data))  # <class 'list'>
print(data)  # [-1.234, 0.0, 1.66, None, 50.0]
numpy_data = np.array(data) # This is equal to the query value below
#==================================================
def convert_array(val, ctx):
    # val: b'[-1.234,0.0,1.66,null,50.0]'
    json_data = json.loads(val)
    return np.array(json_data)
# VerticaType.ARRAY1D_FLOAT8 represents one-dimensional array of FLOAT type
cur.register_sqldata_converter(VerticaType.ARRAY1D_FLOAT8, convert_array)
cur.execute("SELECT ARRAY[-1.234, 0, 1.66, null, 50]::ARRAY[FLOAT]")
data = cur.fetchone()[0]
print(type(data))  # <class 'numpy.ndarray'>
print(data)  # [-1.234 0.0 1.66 None 50.0]

#==================================================
# VerticaType.ARRAY represents multidimensional array or contain ROWs
cur.register_sqldata_converter(VerticaType.ARRAY, convert_array)
cur.execute("SELECT ARRAY[ARRAY[-1, 234, 5],ARRAY[88, 0, 19]]::ARRAY[ARRAY[INT]]")
data = cur.fetchone()[0]
print(type(data))  # <class 'numpy.ndarray'>
print(data)
#[[ -1 234   5]
# [ 88   0  19]]

import pandas
import vertica_python
from io import BytesIO
from vertica_python.datatypes import VerticaType

conn_info = {'host': '127.0.0.1',
             'port': 5433,
             'binary_transfer': False/True,
             ...
            }

conn = vertica_python.connect(**conn_info)
cur = conn.cursor()

cur.execute("SELECT ROW(ROW('a','b') as row1, ROW('c','d') as row2)")
data = cur.fetchone()[0]
print(type(data)) # <class 'dict'>
print(data)  # {'row1': {'f0': 'a', 'f1': 'b'}, 'row2': {'f0': 'c', 'f1': 'd'}}

def convert_row(val, ctx):
    # val: b'{"row1":{"f0":"a","f1":"b"},"row2":{"f0":"c","f1":"d"}}'
    return pandas.read_json(BytesIO(val), orient='index')

cur.register_sqldata_converter(VerticaType.ROW, convert_row)
cur.execute("SELECT ROW(ROW('a','b') as row1, ROW('c','d') as row2)")
data = cur.fetchone()[0]
print(type(data)) # <class 'pandas.core.frame.DataFrame'>
print(data)
#      f0 f1
# row1  a  b
# row2  c  d

row = cursor.execute(...).fetchone()

for row in cur.execute(...).fetchall():
    ...

cur.execute('SELECT 10 things')

cur.rowcount == -1  # indicates unknown rowcount

cur.fetchone()
cur.rowcount == 1
cur.fetchone()
cur.rowcount == 2
cur.fetchall()
cur.rowcount == 10

cur.execute("DELETE 3 things")

cur.rowcount == -1  # indicates unknown rowcount
cur.fetchone()[0] == 3

cur = vertica_python.Connection({..., 'unicode_error': 'strict'}).cursor()
cur.execute(r"SELECT E'\xC2'")
cur.fetchone()
# caught 'utf8' codec can't decode byte 0xc2 in position 0: unexpected end of data

cur = vertica_python.Connection({..., 'unicode_error': 'replace'}).cursor()
cur.execute(r"SELECT E'\xC2'")
cur.fetchone()
# �

cur = vertica_python.Connection({..., 'unicode_error': 'ignore'}).cursor()
cur.execute(r"SELECT E'\xC2'")
cur.fetchone()
#