Query Execution

class Cursor:
    """
    Database cursor for executing queries and fetching results.
    """
    
    def execute(self, operation: str, parameters=None, use_prepared_statements=None, 
                copy_stdin=None, buffer_size=131072) -> None:
        """
        Execute a database operation (query or command).
        
        Parameters:
        - operation (str): SQL statement to execute
        - parameters (dict or sequence, optional): Parameters for SQL statement
        - use_prepared_statements (bool, optional): Use prepared statements for performance
        - copy_stdin (file-like, optional): Input stream for COPY FROM STDIN operations
        - buffer_size (int): Buffer size for COPY operations (default: 131072)
        
        Raises:
        ProgrammingError: If SQL syntax is invalid
        DatabaseError: If execution fails
        """
    
    def executemany(self, operation: str, seq_of_parameters, use_prepared_statements=None) -> None:
        """
        Execute a database operation against multiple parameter sets.
        
        Parameters:
        - operation (str): SQL statement to execute
        - seq_of_parameters (sequence): Sequence of parameter dictionaries/sequences
        - use_prepared_statements (bool, optional): Use prepared statements for performance
        
        Raises:
        ProgrammingError: If SQL syntax is invalid
        DatabaseError: If execution fails
        """
    
    def fetchone(self) -> tuple:
        """
        Fetch the next row from the result set.
        
        Returns:
        tuple: Next row as a tuple, or None if no more rows
        
        Raises:
        ProgrammingError: If no query has been executed
        """
    
    def fetchmany(self, size=None) -> list:
        """
        Fetch multiple rows from the result set.
        
        Parameters:
        - size (int, optional): Number of rows to fetch (default: arraysize)
        
        Returns:
        list: List of tuples representing rows
        
        Raises:
        ProgrammingError: If no query has been executed
        """
    
    def fetchall(self) -> list:
        """
        Fetch all remaining rows from the result set.
        
        Returns:
        list: List of tuples representing all remaining rows
        
        Raises:
        ProgrammingError: If no query has been executed
        """
    
    def nextset(self) -> bool:
        """
        Move to the next result set (for multi-statement queries).
        
        Returns:
        bool: True if another result set is available, False otherwise
        """
    
    def close(self) -> None:
        """
        Close the cursor and free associated resources.
        Cursor becomes unusable after this call.
        """
    
    def copy(self, sql: str, data, buffer_size=131072, **kwargs) -> None:
        """
        Execute COPY FROM STDIN operation for bulk data loading.
        
        Parameters:
        - sql (str): COPY FROM STDIN SQL statement
        - data (file-like or iterable): Data source (file object or iterable of rows)
        - buffer_size (int): Buffer size for copy operation (default: 131072)
        
        Raises:
        ProgrammingError: If COPY statement is invalid
        CopyRejected: If data is rejected during copy
        """
    
    def iterate(self) -> Generator:
        """
        Return a generator for memory-efficient iteration through results.
        
        Yields:
        tuple: Each row as a tuple
        
        Raises:
        ProgrammingError: If no query has been executed
        """
    
    def cancel(self) -> None:
        """
        Cancel the current operation (deprecated, use Connection.cancel()).
        
        Raises:
        OperationalError: If cancellation fails
        """
    
    def closed(self) -> bool:
        """
        Check if cursor is closed.
        
        Returns:
        bool: True if cursor is closed, False otherwise
        """
    
    @property
    def description(self) -> tuple:
        """
        Get column metadata for the last query.
        
        Returns:
        tuple: Sequence of 7-item tuples describing each column:
               (name, type_code, display_size, internal_size, precision, scale, null_ok)
        """
    
    @property
    def rowcount(self) -> int:
        """
        Get number of rows affected by the last execute() call.
        
        Returns:
        int: Number of affected rows, or -1 if not available
        """
    
    @property
    def arraysize(self) -> int:
        """
        Get or set default fetch size for fetchmany().
        
        Returns:
        int: Current arraysize setting
        """
    
    @arraysize.setter
    def arraysize(self, size: int) -> None:
        """
        Set default fetch size for fetchmany().
        
        Parameters:
        - size (int): New arraysize value
        """
    
    @property
    def cursor_type(self) -> str:
        """
        Get or set cursor result format type.
        
        Returns:
        str: Current cursor type ('list', 'dict', etc.)
        """
    
    @cursor_type.setter
    def cursor_type(self, cursor_type: str) -> None:
        """
        Set cursor result format type.
        
        Parameters:
        - cursor_type (str): New cursor type
        """
    
    def __enter__(self) -> 'Cursor':
        """
        Enter context manager.
        
        Returns:
        Cursor: Self for context manager protocol
        """
    
    def __exit__(self, exc_type, exc_val, exc_tb) -> None:
        """
        Exit context manager and close cursor.
        
        Parameters:
        - exc_type: Exception type (if any)
        - exc_val: Exception value (if any)
        - exc_tb: Exception traceback (if any)
        """

def object_to_sql_literal(self, py_obj) -> str:
    """
    Convert Python object to SQL literal string.
    
    Parameters:
    - py_obj: Python object to convert
    
    Returns:
    str: SQL literal representation
    """

def register_sql_literal_adapter(self, obj_type: type, adapter_func) -> None:
    """
    Register custom SQL literal adapter for a Python type.
    
    Parameters:
    - obj_type (type): Python type to adapt
    - adapter_func (callable): Function that converts instances to SQL literals
    """

def register_sqldata_converter(self, oid: int, converter_func) -> None:
    """
    Register custom data type converter for a Vertica type OID.
    
    Parameters:
    - oid (int): Vertica type OID
    - converter_func (callable): Function that converts raw data to Python objects
    """

def unregister_sqldata_converter(self, oid: int) -> None:
    """
    Remove custom data type converter for a Vertica type OID.
    
    Parameters:
    - oid (int): Vertica type OID to remove converter for
    """

@property
def disable_sqldata_converter(self) -> bool:
    """
    Get or set whether to bypass all data type conversions.
    
    Returns:
    bool: True if conversions are disabled, False otherwise
    """

@disable_sqldata_converter.setter
def disable_sqldata_converter(self, value: bool) -> None:
    """
    Enable or disable all data type conversions.
    
    Parameters:
    - value (bool): True to disable conversions, False to enable
    """

import vertica_python

with vertica_python.connect(host='localhost', user='dbadmin', database='mydb') as conn:
    with conn.cursor() as cursor:
        # Simple SELECT query
        cursor.execute("SELECT name, age FROM users WHERE age > 25")
        
        # Fetch results
        rows = cursor.fetchall()
        for row in rows:
            name, age = row
            print(f"Name: {name}, Age: {age}")

with conn.cursor() as cursor:
    # Named parameters (recommended)
    cursor.execute(
        "SELECT * FROM users WHERE age > :min_age AND city = :city",
        {'min_age': 25, 'city': 'New York'}
    )
    
    # Positional parameters also supported
    cursor.execute(
        "SELECT * FROM users WHERE age > %s AND city = %s",
        (25, 'New York')
    )
    
    results = cursor.fetchall()

with conn.cursor() as cursor:
    # Insert multiple rows efficiently
    users_data = [
        {'name': 'Alice', 'age': 30, 'city': 'Boston'},
        {'name': 'Bob', 'age': 25, 'city': 'Chicago'},
        {'name': 'Carol', 'age': 35, 'city': 'Denver'}
    ]
    
    cursor.executemany(
        "INSERT INTO users (name, age, city) VALUES (:name, :age, :city)",
        users_data
    )

with conn.cursor() as cursor:
    # Enable prepared statements for better performance with repeated queries
    cursor.execute(
        "SELECT * FROM large_table WHERE id = :user_id",
        {'user_id': 12345},
        use_prepared_statements=True
    )
    
    result = cursor.fetchone()

with conn.cursor() as cursor:
    cursor.execute("SELECT * FROM very_large_table")
    
    # Use iterate() for memory-efficient processing
    for row in cursor.iterate():
        process_row(row)  # Process one row at a time
        
    # Alternative: Control batch size with fetchmany
    cursor.arraysize = 1000
    while True:
        rows = cursor.fetchmany()
        if not rows:
            break
        for row in rows:
            process_row(row)

import io

with conn.cursor() as cursor:
    # COPY from string data
    csv_data = "1,Alice,30\n2,Bob,25\n3,Carol,35\n"
    data_stream = io.StringIO(csv_data)
    
    cursor.copy(
        "COPY users (id, name, age) FROM STDIN DELIMITER ','",
        data_stream
    )
    
    # COPY from file
    with open('users.csv', 'r') as f:
        cursor.copy(
            "COPY users FROM STDIN DELIMITER ',' ENCLOSED BY '\"'",
            f
        )
    
    # COPY from list of rows
    rows = [
        [1, 'Alice', 30],
        [2, 'Bob', 25],
        [3, 'Carol', 35]
    ]
    
    cursor.copy(
        "COPY users (id, name, age) FROM STDIN DELIMITER ','",
        rows
    )

with conn.cursor() as cursor:
    cursor.execute("SELECT name, age, salary FROM employees LIMIT 1")
    
    # Access column metadata
    for col_desc in cursor.description:
        name, type_code, display_size, internal_size, precision, scale, null_ok = col_desc
        print(f"Column: {name}, Type: {type_code}, Nullable: {null_ok}")
    
    # Check affected row count
    cursor.execute("UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering'")
    print(f"Updated {cursor.rowcount} rows")

import decimal
from datetime import datetime

with conn.cursor() as cursor:
    # Register custom converter for high-precision decimals
    def decimal_converter(value):
        return decimal.Decimal(value)
    
    cursor.register_sqldata_converter(vertica_python.datatypes.VerticaType.NUMERIC, decimal_converter)
    
    # Register custom SQL literal adapter
    def decimal_adapter(obj):
        return str(obj)
    
    cursor.register_sql_literal_adapter(decimal.Decimal, decimal_adapter)
    
    # Use custom conversion
    cursor.execute("SELECT price FROM products WHERE id = :id", {'id': 123})
    price = cursor.fetchone()[0]  # Returns decimal.Decimal instead of float

# Default cursor returns tuples
with conn.cursor() as cursor:
    cursor.execute("SELECT name, age FROM users LIMIT 1")
    row = cursor.fetchone()  # ('Alice', 30)

# Dictionary cursor returns dict objects
with conn.cursor(cursor_type='dict') as cursor:
    cursor.execute("SELECT name, age FROM users LIMIT 1")
    row = cursor.fetchone()  # {'name': 'Alice', 'age': 30}

try:
    with conn.cursor() as cursor:
        # Begin transaction (implicit)
        cursor.execute("INSERT INTO accounts (name, balance) VALUES ('Alice', 1000)")
        cursor.execute("INSERT INTO accounts (name, balance) VALUES ('Bob', 500)")
        
        # Verify data
        cursor.execute("SELECT COUNT(*) FROM accounts")
        count = cursor.fetchone()[0]
        
        if count >= 2:
            conn.commit()  # Commit transaction
        else:
            conn.rollback()  # Rollback on error
            
except Exception as e:
    conn.rollback()  # Rollback on exception
    raise