The Ocient® pyocient Python® module supports direct connections to the Ocient System through a command line interface (CLI) as well as Python programs that follow the Python Database API Specification 2.0.To download, install, and use the pyocient CLI, see Connect Using pyocient.

The pyocient package has these supported features and functionality.

Prerequisites

To use the pyocient client, you should have this software with the corresponding version installed.

Module Features

The pyocient connector supports these features as of the current version.

pyocient CLI

You can make a connection by invoking the pyocient module in a command-line interface with a connection string using this syntax.

Syntax

$ pyocient [named_arguments] dsn [sql]

For details on the parts of a connection string, see these sections.

• Connecting Via a DSN
• Using SQL Queries in the CLI
• Using Named Arguments
• Additional pyocient CLI Commands

Connect Using a DSN

Required. A Data Source Name (DSN) supplies the user credentials, host, port, and database to establish a connection. This string can also include various optional parameters.

Syntax

ocient://<user>:<password>@<host>:[<port>][/<database>][?<parameters>[&...]]

A DSN consists of these arguments.

DSN Example

When you successfully connect with a DSN, your command line switches to interactive mode.

$ pyocient ocient://user:password@myhost:4050/mydb?tls=on&force=true
Ocient Database™
System Version: 22.0, Client Version 1.0.13
>

In interactive mode, you can enter SQL commands and queries directly at the command line. This example shows a simple SELECT query that you enter at the command line.

> SELECT * FROM my_table LIMIT 1;
[
    {
        "time_x": "2022-06-15T13:31:42.824000",
        "col_001": 1.0,
        "col_002": 2.0,
        "col_003": 3,
        "col_004": 4,
        "keyid": 5
    }
]

SQL Queries in the Connection String

Instead of executing queries and commands in the CLI, you can also include SQL scripts directly in your connection string.

A connection string can contain multiple queries as long as each is delineated by semicolons.

You must enclose any SQL statements in a connection string in quotes and specify the statements after the DSN portion.

Example

This example shows a SQL query embedded in a pyocient connection string. The query processes and returns its values without launching the interactive mode.

$ pyocient ocient://user:password@myhost:4050/mydb?tls=on&force=true "SELECT * FROM my_table LIMIT 1"
[
    {
        "time_x": "2022-06-15T13:31:42.824000",
        "col_001": 1.0,
        "col_002": 2.0,
        "col_003": 3,
        "col_004": 4,
        "keyid": 5
    }
]

Use Optional Named Arguments

You can add these optional arguments to a connection string.

[-h] [-o OUTFILE | -n] [-c CONFIGFILE | --noconfig]
[-i INFILE] [-l {critical,error,warning,info,debug}]
[--logfile LOGFILE] [-t] [--format {json | table | csv}]
[--nocolor]

Named Argument Definitions

Additional pyocient CLI Commands

When running in a CLI, pyocient recognizes these commands in addition to the SQL statements and queries.

pyocient API

This Python database API conforms to the Python Database API Specification 2.0, and you can use it to access the Ocient System.

You can also execute this module as a main function, in which case it acts as a primitive CLI for the database.

When you execute this module as a main function, the API takes a connection string in the DSN format. The connection string can also include one or more query strings that execute. For information on using a DSN to connect, see Connect Using a DSN.

The API returns output in JSON format by default.

The database returns any warnings and sends them to the Python warnings module. By default, that module sends warnings to standard output, however, you can change the behavior by using that module.

pyocient.Connection

Constructor

class pyocient.Connection(
    dsn=None, user=None, password=None,
    host=None, database=None, tls=None,
    handshake=None, identityprovider=None,
    force=None, configfile=None, session=None)

A connection to Ocient. This class is normally constructed by executing the module connect(), but you can also construct it directly.

You can specify connection parameters using the dsn argument, other keyword arguments, or a mix of both. If the system receives multiple arguments for the same parameters, the keyword arguments take precedence and override the dsn value.

Definitions for the connection parameters are listed in Connect Using a DSN section. Other parameters are:

• configfile — The name of a configuration file in .ini format, where each section either uses default connection settings or a pattern that matches the host or database. Sections are matched in order, so highly detailed sections should precede less detailed sections.
• session — A string of an SSO security token.

This code provides an example of an .ini configuration file.

[DEFAULT]
tls = unverified
; This matches the specific host and database.
[foo.ocient.com/somedb]
user = panther
password = pink
; This matches the specific host.
[foo.ocient.com]
user = panther
password = pink
; This matches any host in the ocient.com domain.
[*.ocient.com]
user = tom
password = jerry
database = mice
; This matches any host in the ocient.com domain using the default database.
[*.ocient.com]
user = tom
password = jerry

The currently supported parameter is:

tls — The values are on, off, or unverified in the DSN. You can also set TLS by using Connection.TLS_NONE, Connection.TLS_UNVERIFIED, or Connection.TLS_ON as a keyword parameter.

Connection Object Methods

close()

Close the connection. Any subsequent queries on the closed connection fail.

commit()

Commit any pending transactions to the database.

cursor()

Return a new cursor object for this connection.

pyocient.Cursor

Constructor

class pyocient.Cursor(conn)

A database cursor that can manage a query and its returned results.

Cursors are normally created by executing cursor() on a connection, but you can also create them directly by providing a connection.

Cursor Object Methods

close()

Close this cursor. The current result set is closed, but you can reuse the cursor for subsequent execute() calls.

execute(operation, parameters=None)

Prepare and execute a database operation (query or command).

Parameters might be provided as a mapping and are bound to variables in the operation. Variables are specified in Python extended format codes, for example: WHERE name=%(name)

executemany(operation, parameterlist)

Prepare a database operation (query or command) and then execute it against all parameter sequences or mappings found in the sequence parameterlist.

Parameters might be provided as a mapping and are bound to variables in the operation. Variables are specified in Python extended format codes, for example: WHERE name=%(name)

fetchone()

Fetch the next row of a query result set. The function returns a single row or None when no more data rows are available.

fetchmany(size=None)

Fetch the next set of rows of a query result and return a sequence of sequences as a list of tuples. The database returns an empty sequence when no more rows are available.

The number of rows to fetch per call is specified by the size parameter. If this parameter is not specified, the array size of the cursor determines the number of rows to fetch. The method tries to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, the function returns fewer rows.

fetchall()

Fetch all (remaining) rows of a query result and return them as a sequence of sequences (e.g., a list of tuples). Note that the array size attribute of the cursor can affect the performance of this operation.

arraysize

This read-and-write attribute specifies the number of rows to fetch at a time with fetchmany. The default value is 1, meaning the attribute fetches a single row at a time.

tables(schema=’%’, table=’%’)

Retrieve the database tables. By default, the database returns all tables.

system_tables(table=’%’)

Retrieve the database system tables.

views(view=’%’)

Retrieve the database views.

columns(schema=’%’, table=’%’, column=’%’)

Retrieve the database columns.

indexes(schema=’%’, table=’%’)

Retrieve the database indexes.

getTypeInfo()

Retrieve the database type information.

fetchval()

The fetchval() convenience method returns the first column of the first row if there are results, otherwise it returns None.

resolve_new_endpoint(new_host, new_port)

Handles mapping to a secondary interface based on the secondary interface mapping saved on this connection.

Arguments:

  new_host[string]: the new host to be remapped
  new_port[int]: the new port to be remapped

Returns:

  [tuple(string, int)]: The actual endpoint to connect to in the format: (host, port).

redirect(new_host, new_port)

Redirects to the proper secondary interface with a new endpoint.

Arguments:

  new_host[string]: the new host to be redirected
  new_port[int]: the new port to be redirected

Returns:

  [Connection]: A new connection.

refresh()

Refresh the session associated with this connection. The server returns a new server session identifier and security token.

setinputsizes(sizes)

Used before an execute call to set memory sizes for the specified operation.

For details on usage, see the Python API documentation.

setoutputsize(size [, column])

Used before an execute call to set a column buffer size for fetches of columns with large data types, such as LONG, BLOB, etc.

For details on usage, see the Python API documentation.

Type Objects and Constructors

These constructors create objects to hold special values to comply with defined types in a database schema. When you pass these objects to the cursor methods, the module detects the proper type of input parameter and binds it accordingly.

For details, see the Python documentation on Type Objects and Constructors.

Date(year, month, day)

This function constructs an object that holds a date value.

Time(hour, minute, second)

This function constructs an object that holds a time value.

Timestamp(year, month, day, hour, minute, second)

This function constructs an object that holds a timestamp value.

DateFromTicks(ticks)

This function constructs an object that holds a date value from the specified ticks value (number of seconds after the epoch; see the documentation of the standard Python time module for details).

TimeFromTicks(ticks)

This function constructs an object that holds a time value from the specified ticks value (number of seconds after the epoch; see the documentation of the standard Python time module for details).

TimestampFromTicks(ticks)

This function constructs an object that holds a timestamp value from the specified ticks value (number of seconds after the epoch; see the documentation of the standard Python time module for details).

Binary(string)

This function constructs an object capable of holding a binary (long) string value.

STRING type

This type object describes columns in a database that are string-based (e.g., CHAR).

BINARY type

This type object describes (long) binary columns in a database (e.g., LONG, RAW, BLOB).

NUMBER type

This type object describes numeric columns in a database.

DATETIME type

This type object describes date and time columns in a database.

ROWID type

This type object describes the “Row ID” column in a database.

Exceptions

Error

Exception that is the base class of all other error exceptions.

  exception Error(reason, sql_state='58005', vendor_code=-100)

Warning

Exception that is the base class of all other warning exceptions.

  exception Warning(reason='', sql_state='00000', vendor_code=0)

InterfaceError

Exception raised for errors related to the database interface rather than the database itself.

  exception InterfaceError(reason, sql_state='58005', vendor_code=-100)

DatabaseError

Exception raised for errors that are related to the database.

  exception DatabaseError(reason, sql_state='58005', vendor_code=-100)

InternalError

Exception raised when the database encounters an internal error, e.g., the cursor is not valid anymore.

  exception InternalError(reason, sql_state='58005', vendor_code=-100)

OperationalError

Exception raised for errors related to the operation of the database and not necessarily under the control of the programmer, e.g., an unexpected disconnect occurs, the data source name is not found, etc.

  exception OperationalError(reason, sql_state='58005', vendor_code=-100)

ProgrammingError

Exception raised for programming errors, e.g., table not found, syntax error in the SQL statement, wrong number of parameters specified, etc.

  exception ProgrammingError(reason, sql_state='58005', vendor_code=-100)

IntegrityError

Exception raised when the relational integrity of the database is affected, e.g., a foreign key check fails.

  exception IntegrityError(reason, sql_state='58005', vendor_code=-100)

DataError

Exception raised for errors due to problems with the processed data like division by zero, numeric value out of range, etc.

  exception DataError(reason, sql_state='58005', vendor_code=-100)

NotSupportedError

Exception raised when a method or database API that is not supported by the database is used.

  exception NotSupportedError(reason, sql_state='58005', vendor_code=-100)

Optional DB API Extensions

Cursor.rownumber

This read-only attribute should provide the current 0-based index of the cursor in the result set or None if the index cannot be determined.

You can see the index as the index of the cursor in a sequence (the result set). The next fetch operation fetches the row indexed by .rownumber in that sequence.

Connection.Error, Connection.ProgrammingError, etc.

All exception classes defined by the DB API standard should be exposed on the Connection objects as attributes (in addition to being available at module scope).

These attributes simplify error handling in multi-connection environments.

Cursor.connection

This read-only attribute returns a reference to the Connection object on which the cursor was created.

The attribute simplifies writing polymorph code in multi-connection environments.

Cursor.__iter__()

Return self to make cursors compatible with the Python iteration protocol.

Related Links

pyocient PyPI

**Linux®is the registered trademark of Linus Torvalds in the U.S. and other countries.