Ocient Python Module: pyocient
The pyocient module supports direct connections to Ocient databases through a command line interface (CLI) as well as Python programs that follow the Python Database API Specification 2.0.
This page lists supported features and functionality for the pyocient package. For a walkthrough on installing and using pyocient, see Connecting via pyocient.
To use the pyocient client, you should have the following software packages with the listed versions installed.
Software | Version |
Ocient | All Ocient versions are supported. |
OS | , , or . Use the latest version of each OS system. |
Python | Version 3.5 or later. |
The pyocient connector supports the following features as of the current version.
Unicode Support | UTF-8 |
Isolation Levels | Ocient does not support transactions at this time. |
Data Types | Supports all Ocient Data Types. |
Security / Encryption | GCM, CBC and SSO protocols supported. TLS protocol is optional as a setting. |
You can make a connection by invoking the pyocient module in a command line interface with a connection string using this syntax.
Syntax
For more information on the parts of a connection string, see these sections.
Required. A Data Source Name (DSN) supplies the user credentials as well as the host, port, and database needed to establish a connection. This string can also include various optional parameters.
Syntax
A DSN consists of the following arguments.
Argument | Definition |
<user> | Required. An Ocient username. |
<password> | Required. The password associated with your username to connect to Ocient. |
<host> | Required. The Ocient host address. You can specify multiple hosts if they are separated by commas. The system attempts to connect to multiple hosts in order, starting with the leftmost. For example, host1 would be the first for the connection attempt in this case: ocient://someone:somepassword@host1,host2:4051/mydb |
<port> | The port number used by your host. By default, this is set to 4050. |
<database> | The name of the database for connection. If unspecified, defaults to system. |
<parameters> | One or more optional parameters to use for the connection in the format parameter=value. Supported parameters include: tls: Sets TLS protocol security. Can be set to off, unverified, or on. If unspecified, defaults to off. force: Can be set to either true or false . If true, this parameter forces the connection to stay on this server. Defaults to false. handshake: The security handshake protocol. Supported values include cbc. By default, the system does not use cbc. |
DSN Example
When you successfully connect with a DSN, your command line switches to interactive mode.
When in interactive mode, you can enter SQL commands and queries directly at the command line. This example shows a simple SELECT query entered in the command line while it is in interactive mode:
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.
Optional. You can add the following optional arguments to a connection string.
Named Argument Definitions
Argument | Definition |
-o | --outfile OUTFILE | If included, pyocient records all SQL output to a specified output file. You must also include the file path to the output file, enclosed in quotation marks. The Ocient system saves output values to the file after query completion. |
-n | --noout | If included, this option prevents all output results. |
-c | --configfile CONFIGFILE | The option directs pyocient to use a configuration file. You must include the path location and name of the configuration file, enclosed in quotation marks. |
--noconfig | Directs pyocient not to use a configuration file. |
-i | --infile INFILE | Directs pyocient to use an input file containing SQL statements. |
-l | --loglevel LOGLEVEL | Sets the logging level. Logging level options include: CRITICAL | ERROR | WARNING | INFO | DEBUG Defaults to CRITICAL. For more information, see Error Monitoring. |
--logfile LOGFILE | Directs pyocient to record log events in a specified log file. You must include the path location and name of the log file, enclosed in quotation marks. |
-t, --time | If included, pyocient outputs the execution time for all queries and commands. |
-f, --format | Specifies the output format for all SQL queries. Format options include: JSON | TABLE | CSV JSON - Object Notation. TABLE - A table rendered in ASCII characters. CSV - Comma separated values format. Defaults to JSON. |
--nocolor | When using pyocient in interactive mode, the interface does not color SQL keywords. |
When running in a command line interface, pyocient recognizes the following commands in addition to SQL statements and queries.
Command | Description |
CONNECT TO <dsn> USER <username> USING <password>; | Allows switching to a different DSN or user during an interactive session. The DSN must be enclosed in quotation marks. Otherwise, it follows the normal format explained in Connect Using a DSN. You can specify the username and password either in the DSN string or with the USER and USING keywords. |
SOURCE '<file>'; | Executes the input statements from the specified file. The file name must be in single quotes. |
SET FORMAT TABLE; | Sets the output format. Format options include JSON, TABLE and CSV. Defaults to JSON. JSON - Javascript Object Notation. TABLE - A table rendered in ASCII characters. CSV - Comma-separated values format. |
QUIT; | Quits the CLI. Alternatively, you can exit the interface by using Ctrl + D. |
This Python database API conforms to the Python Database API Specification 2.0, and you can use it to access the Ocient database.
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 will execute. For information on using a DSN to connect, see the Connect Using a DSN section.
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.
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 by using the dsn argument, other keyword arguments, or a mix of the two. 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.
The currently supported parameter is
tls: Can have the values on, off, or unverified in the DSN. You can also set TLS by calling Connection.TLS_NONE, Connection.TLS_UNVERIFIED, or Connection.TLS_ON as a keyword parameter.
Close the connection. Any subsequent queries on the closed connection will fail.
Commit any pending transaction to the database.
Return a new cursor object for this connection.
A database cursor able to manage a query and its returned results.
Cursors are normally created by executing cursor() on a connection, but they can also be created directly by providing a connection.
Close this cursor. The current result set is closed, but the cursor can be reused for subsequent execute() calls.
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)
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)
Fetch the next row of a query result set. The function returns a single row, or None when no more data rows are available.
Fetch the next set of rows of a query result, returning a sequence of sequences as a list of tuples. An empty sequence is returned when no more rows are available.
The number of rows to fetch per call is specified by the size parameter. If it is not given, 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.
Fetch all (remaining) rows of a query result, returning 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.
This read and write attribute specifies the number of rows to fetch at a time with fetchmany.. Defaults to 1, meaning the attribute fetches a single row at a time.
Retrieve the database tables. By default, all tables are returned.
Retrieve the database system tables.
Retrieve the database views.
Retrieve the database columns.
Retrieve the database indexes.
Retrieve the database type information.
The fetchval() convenience method returns the first column of the first row if there are results, otherwise it returns None.
Handles mapping to a secondary interface based on the secondary interface mapping saved on this connection.
Arguments:
Returns:
Redirects to the proper secondary interface given a new endpoint.
Arguments:
Returns:
Refresh the session associated with this connection. The server returns a new server session identifier and security token.
Used before an execute call to set memory sizes for the specified operation.
For more information on usage, see the Python API documentation.
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 more information on usage, see the Python API documentation.
The following 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 the input parameter and binds it accordingly.
For more information, see the Python documentation on Type Objects and Constructors.
This function constructs an object holding a date value.
This function constructs an object holding a time value.
This function constructs an object holding a time stamp value.
This function constructs an object holding a date value from the given ticks value (number of seconds after the epoch; see the documentation of the standard Python time module for details).
This function constructs an object holding a time value from the given ticks value (number of seconds after the epoch; see the documentation of the standard Python time module for details).
This function constructs an object holding a time stamp value from the given ticks value (number of seconds after the epoch; see the documentation of the standard Python time module for details).
This function constructs an object capable of holding a binary (long) string value.
This type object is used to describe columns in a database that are string-based (e.g., CHAR).
This type object is used to describe (long) binary columns in a database (e.g., LONG, RAW, BLOB).
This type object is used to describe numeric columns in a database.
This type object is used to describe date and time columns in a database.
This type object is used to describe the “Row ID” column in a database.
Error
Exception that is the base class of all other error exceptions.
Warning
Exception that is the base class of all other warning exceptions.
InterfaceError
Exception raised for errors that are related to the database interface rather than the database itself.
DatabaseError
Exception raised for errors that are related to the database.
InternalError
Exception raised when the database encounters an internal error, e.g., the cursor is not valid anymore.
OperationalError
Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g., an unexpected disconnect occurs, the data source name is not found.
ProgrammingError
Exception raised for programming errors, e.g., table not found, syntax error in the SQL statement, wrong number of parameters specified, etc.
IntegrityError
Exception raised when the relational integrity of the database is affected, e.g., a foreign key check fails.
DataError
Exception raised for errors that are due to problems with the processed data like division by zero, numeric value out of range, etc.
NotSupportedError
Exception raised in case a method or database API was used which is not supported by the database,
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.
The index can be seen as index of the cursor in a sequence (the result set). The next fetch operation will fetch the row indexed by .rownumber in that sequence.
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 return 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 to the Python iteration protocol.