DBAPI Module

Pure Python DB API wrapper around Kinetica Python API.

gpudb.dbapi.connect(connection_string: str = 'kinetica://', **kwargs: Dict[str, Any]) → KineticaConnection

The global method to return a Kinetica connection

Example

#  Basic authentication
con = gpudb.connect("kinetica://",
                url=URL,
                username=USER,
                password=PASS,
                bypass_ssl_cert_check=BYPASS_SSL_CERT_CHECK,
                )

#  oauth2 authentication
con = gpudb.connect("kinetica://",
                url=URL,
                oauth_token="token_Value",
                bypass_ssl_cert_check=BYPASS_SSL_CERT_CHECK,
                )

Parameters

connection_string (str, optional) –

the connection string which must be ‘kinetica://’

**kwargs (Dict[str, Any]) –

the arguments passed to the overloaded method connect()

Raises

ProgrammingError –

Raised in case wrong connection parameters are detected or a connection fails for some

other reason

Returns

KineticaConnection –

a KineticaConnection instance

gpudb.dbapi.aconnect(connection_string: str = 'kinetica://', **kwargs: Dict[str, Any]) → AsyncKineticaConnection

The global method to return an async Kinetica connection

Example

#  Basic authentication
con = gpudb.connect("kinetica://",
             connect_args={
                 'url': 'http://localhost:9191',
                 'username': 'user',
                 'password': 'password',
                 'options': {'bypass_ssl_cert_check': True},
             })

#  oauth2 authentication
con = gpudb.connect("kinetica://",
             connect_args={
                 'url': 'http://localhost:9191',
                 'oauth_token': 'token_value',
                 'options': {'bypass_ssl_cert_check': True},
             })

Parameters

connection_string (str, optional) –

the connection string which must be ‘kinetica://’

**kwargs (Dict[str, Any]) –

the arguments passed to the overloaded method connect()

Raises

ProgrammingError –

Raised in case wrong connection parameters are

detected or a connection fails for some other reason

Returns

KineticaConnection –

a KineticaConnection instance

class gpudb.dbapi.KineticaConnection(param_style: str | None = 'numeric_dollar', *, url: str | None = None, username: str | None = None, password: str | None = None, oauth_token: str | None = None, connection_options: dict | None = None)

A DB API 2.0 compliant connection for Kinetica, as outlined in PEP 249.

Constructor Called by connect() in gpudb.dbapi

Parameters

param_style (Optional[str], optional) –

String constant stating the type of parameter marker formatting expected by the interface. Defaults to ParamStyle.NUMERIC_DOLLAR.

url (Optional[str], optional) –

the Kinetica URL; has to be keyword only. Defaults to None.

username (Optional[str], optional) –

the Kinetica username; has to be keyword only. Defaults to None.

password (Optional[str], optional) –

the Kinetica password; has to be keyword only. Defaults to None.

oauth_token (Optional[str], optional) –

the oauth2 token; has to be keyword only. Defaults to None.

connection_options (Optional[dict], optional) –

Defaults to None.

Raises

ProgrammingError –

Raised in case of incorrect parameters passed in

callproc(procname: str, parameters: Sequence[Any] | None = None) → Sequence[Any] | None

This method is used to call a Kinetica procedure.

The sequence of parameters must contain one entry for each argument that the procedure expects. The result of the call is returned as modified copy of the input sequence.

The procedure may also provide a result set as output.

Parameters

procname (ProcName) –

the name of the procedure

parameters (Optional[ProcArgs], optional) –

the parameters to the procedure, which Kinetica doesn’t support

as of now. Defaults to None. This is ignored for now

Returns

Optional[ProcArgs] –

None

close() → None

Close the database connection.

commit() → None

Commit changes made since the start of the pending transaction.

cursor() → Cursor

Return a database cursor.

execute(sql_statement: str, parameters: Sequence[Any] | Dict[str | int, Any] | None = None) → Cursor

The method to execute a single SQL statement (query or command) and return a cursor.

Parameters may be provided as sequence or mapping and will be bound to variables in the operation. Variables are specified in a database-specific notation (see the module’s paramstyle attribute for details).

See also

ParamStyle

Parameters

sql_statement (SQLQuery) –

the SQL statement (query or command) to execute

parameters (Optional[QueryParameters], optional) –

the parameters to the query; typically a heterogeneous list. Defaults to None.

Raises

ProgrammingError –

Raised in case the SQL statement passed in is invalid

Returns

Cursor –

a Cursor containing the results of the query

executemany(operation: str, seq_of_parameters: Sequence[Sequence[Any] | Dict[str | int, Any]]) → Cursor

Method used to execute the same statement with a sequence of parameter values.

The cursor is only returned from the last execution

See also

execute()

Example - inserting multiple records

con1 = gpudb.connect("kinetica://", connect_args={
    'url': 'http://localhost:9191',
    'username': '',
    'password': '',
    'bypass_ssl_cert_check': True})

create_query = ("create table ki_home.test_table (i integer not null, bi bigint not null) using table "
                "properties (no_error_if_exists=TRUE)")
con1.execute(create_query)

i = 1
bi = 1000
num_pairs = 50000

# Generate a list of pairs with the same values and monotonically increasing first value
pairs = [[i + x, bi + x] for x in range(num_pairs)]
insert_many_query = "insert into ki_home.test_table (i, bi) values ($1, $2)"
con1.executemany(insert_many_query, pairs)
con1.close()

Parameters

operation (SQLQuery) –

the SQL query

seq_of_parameters (Sequence[QueryParameters]) –

a list of parameters (tuples)

Returns

Cursor –

a Cursor instance to iterate over the results

executescript(script: str) → Cursor

This method executes an SQL script which is a ‘;’ separated list of SQL statements.

See also

execute()

Parameters

script (SQLQuery) –

an SQL script

Returns

Cursor –

the Cursor returned as a result of execution of the last statement in the script

rollback() → None

Roll back to the start of the pending transaction, discarding changes.

class gpudb.dbapi.Cursor(connection: KineticaConnection, query: str = None, query_params: Sequence[Any] | Dict[str | int, Any] = None)

A DB API 2.0 compliant cursor for Kinetica, as outlined in PEP 249.

callproc(procname: str, parameters: Sequence[Any] | None = None) → Sequence[Any] | None

Execute an SQL stored procedure, passing the sequence of parameters. The parameters should contain one entry for each procedure argument.

The result of the call is returned as a modified copy of the input parameters. The procedure may also provide a result set, which can be made available through the standard fetch methods.

close() → None

Close the cursor.

commit() → None

Commit changes made since the start of the pending transaction.

property connection: KineticaConnection

The parent Connection of the implementing cursor.

property description: Sequence[Tuple[str, type, int | None, int | None, int | None, int | None, bool | None]] | None

This read-only attribute is a sequence of 7-item sequences.

Each of these sequences contains information describing one result column:

name type_code display_size internal_size precision scale null_ok

The first two items (name and type_code) are mandatory, the other five are optional and are set to None if no meaningful values can be provided.

This attribute will be None for operations that do not return rows or if the cursor has not had an operation invoked via the .execute*() method yet.

Returns

Optional[Sequence[ColumnDescription]] –

a sequence of immutable tuples like:

[ (‘field_1’, <class ‘int’>, None, None, None, None, None) –

(‘field_2’, <class ‘int’>, None, None, None, None, None), –

(‘field_3’, <class ‘str’>, None, None, None, None, None), –

(‘field_4’, <class ‘float’>, None, None, None, None, None)]

execute(operation: str, parameters: Sequence[Any] | Dict[str | int, Any] | None = None) → Cursor

Executes an SQL statement and returns a Cursor instance which can

used to iterate over the results of the query

Parameters

operation (SQLQuery) –

an SQL statement

parameters (Optional[QueryParameters], optional) –

the parameters to the SQL statement; typically a heterogeneous list. Defaults to None.

Returns

Cursor –

Returns an instance of self which is used by KineticaConnection

executemany(operation: str, seq_of_parameters: Sequence[Sequence[Any] | Dict[str | int, Any]]) → Cursor

Execute an SQL query, parameterising the query with sequences or mappings passed as parameters.

executescript(script: str) → Cursor

Not supported as of now.

fetchall() → Sequence[Sequence[Any] | Dict[str, Any]]

Fetch the remaining rows from the query result set as a list of sequences of Python types.

If rows in the result set have been exhausted, an empty list will be returned. If the previous call to execute did not produce a result set, an error can be raised.

fetchmany(size: int | None = None) → Sequence[Sequence[Any] | Dict[str, Any]]

Fetch the next size rows from the query result set as a list of sequences of Python types.

If the size parameter is not supplied, the arraysize property will be used instead.

If rows in the result set have been exhausted, an empty list will be returned. If the previous call to execute did not produce a result set, an error can be raised.

fetchone() → Sequence[Any] | Dict[str, Any] | None

Fetch the next row from the query result set as a sequence of Python types (or return None when no more rows are available).

If the previous call to execute did not produce a result set, an error can be raised.

nextset() → bool | None

Skip the cursor to the next available result set, discarding rows from the current set. If there are no more sets, return None, otherwise return True.

This method is optional, as not all databases implement multiple result sets.

rollback() → None

Roll back to the start of the pending transaction, discarding changes.

property rowcount: int

A read-only attribute returning the number of rows that the last execute call returned (for e.g. SELECT calls) or affected (for e.g. UPDATE/INSERT calls).

If no execute has been performed or the rowcount cannot be determined, this should return -1.

setinputsizes(sizes: Sequence[int | Type | None]) → None

Can be used before a call to execute to predefine memory areas for the operation’s parameters.

sizes is a sequence containing an item - a type, or an integer specifying the maximum length for a string - for each input parameter. If the item is None, no memory will be reserved for that column.

Implementations are free to have this method do nothing.

setoutputsize(size: int, column: int | None) → None

Can be used before a call to execute to predefine buffer sizes for fetches of ‘large’ columns (e.g. LONG, BLOB, etc.).

size is an int, referring to the size of the column.

column is an optional int, referring to the index in the result sequence. If this is not provided, this will set the default size for all ‘large’ columns in the cursor.

Implementations are free to have this method do nothing.

gpudb.dbapi.TimestampFromTicks()

timestamp[, tz] -> tz’s local time from POSIX timestamp.

gpudb.dbapi.TimeFromTicks(timestamp: float)

Return the time, given a Unix timestamp.

gpudb.dbapi.DateFromTicks(timestamp, /)

Create a date from a POSIX timestamp.

The timestamp is a number, e.g. created via time.time(), that is interpreted as local time.

gpudb.dbapi.Date

alias of date

gpudb.dbapi.Time

alias of time

gpudb.dbapi.Timestamp

alias of datetime

gpudb.dbapi.ROWID

alias of str

gpudb.dbapi.DATETIME

alias of datetime

gpudb.dbapi.NUMBER

alias of float

gpudb.dbapi.BINARY

alias of bytes

gpudb.dbapi.STRING

alias of str

gpudb.dbapi.Binary

alias of bytes

exception gpudb.dbapi.Error

Base error outlined in PEP 249.

exception gpudb.dbapi.InterfaceError

Interface error outlined in PEP 249.

Raised for errors with the database interface.

exception gpudb.dbapi.DatabaseError

Database error outlined in PEP 249.

Raised for errors with the database.

exception gpudb.dbapi.DataError

Data error outlined in PEP 249.

Raised for errors that are due to problems with processed data.

exception gpudb.dbapi.IntegrityError

Integrity error outlined in PEP 249.

Raised when errors occur which affect the relational integrity of the database (e.g. constraint violations).

exception gpudb.dbapi.InternalError

Integrity error outlined in PEP 249.

Raised when the database encounters an internal error.

exception gpudb.dbapi.NotSupportedError

Not supported error outlined in PEP 249.

Raised when an unsupported operation is attempted.

exception gpudb.dbapi.OperationalError

Operational error outlined in PEP 249.

Raised for errors in the database’s operation.

exception gpudb.dbapi.ProgrammingError

Programming error outlined in PEP 249.

Raised for SQL programming errors.