> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Ocient Python Module: pyocient
export const Windows = "Windows®";
export const Spark = "Apache® Spark™";
export const Python = "Python®";
export const Ocient = "Ocient®";
export const macos = "macOS®";
export const Linux = "Linux®";
export const JavaScript = "JavaScript®";
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. ](https://peps.python.org/pep-0249/)To download, install, and use the pyocient CLI, see [Connect Using pyocient](/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.
| **Software** | **Version** |
| ------------ | ------------------------------------------------------------------------- |
| Ocient | All Ocient versions are supported. |
| OS | {Windows}, {Linux}, or {macos}. Use the latest version of each OS system. |
| Python | Version 3.8 or later. |
### Module Features
The pyocient connector supports these features as of the current version.
| Unicode Support | UTF-8 |
| --------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Isolation Levels | Read committed. The pyocient module supports multi-statement transactions using the connection [autocommit](#autocommit) attribute and the [commit()](#commit) and [rollback()](#rollback) methods. |
| Data Types | Supports all Ocient [Data Types](/data-types). |
| Security / Encryption | GCM, CBC, and SSO protocols are supported.
TLS protocol is optional as a setting. |
The pyocient module supports multi-statement transactions. By default, a connection uses autocommit mode, in which the database commits each statement immediately. To group several statements into a single transaction, set the connection [autocommit](#autocommit) attribute to `False`, and then complete the transaction with [commit()](#commit) or [rollback()](#rollback).
The HTTP Query API does not support transactions. To use transactions, connect to the Ocient System with the pyocient module or the Ocient JDBC driver.
The {Spark} Connector supports transactions and performs the commit and rollback actions automatically. When you use the Spark Connector, you do not need to call `commit()` or `rollback()` methods.
## 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**
```shell Shell theme={null}
$ pyocient [named_arguments] dsn [sql]
```
For details on the parts of a connection string, see these sections.
* [Connecting Using a DSN](#connect-using-a-dsn)
* [Using SQL Queries in the CLI](#sql-queries-in-the-connection-string)
* [Using Named Arguments](#use-optional-named-arguments)
* [Additional pyocient CLI Commands](#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**
```shell Shell theme={null}
ocient://:@:[][/][?[&...]]
```
A DSN consists of these arguments.
| **Argument** | **Definition** |
| -------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `` | Required. An Ocient username. |
| `` | Required. The password associated with your username for connection to Ocient. |
| `` | 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 host.
For example, `host1` is the first for the connection attempt in this case: `ocient://someone:somepassword@host1,host2:4051/mydb` |
| `` | The port number used by your host.
The default is `4050`. |
| `` | The name of the database for the connection.
The default is `system`. |
| `` | One or more optional parameters for the connection in the format `parameter=value`.
Supported parameters include:
`tls`: TLS protocol security. You can set the value to `off`, `unverified`, or `on`. The default is `off`.
`force`: Values are `true` or `false`. If you set this value to `true`, this argument forces the connection to stay on this server. The default is `false`.
`handshake`: Security handshake protocol. Supported values include `cbc`. By default, the system does not use `cbc`.
`identityprovider`: An SSO integration established in the database. For details, see [CREATE SSO INTEGRATION](/cluster-and-node-management#create-sso-integration).
`ssoOAuthFlow`: This parameter is only applicable if SSO authorization is enabled. Forces the driver to use either the `authorizationCode` or `deviceGrant` flow to establish a Single Sign-On session. If you do not specify this parameter, the system uses the `authorizationCode` flow when a web browser is available to the client and the `deviceGrant` flow when a web browser is not available to the client. |
**DSN Example**
When you successfully connect with a DSN, your command line switches to interactive mode.
```shell Shell theme={null}
$ 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.
```shell Shell theme={null}
> 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.
```shell Shell theme={null}
$ 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.
```shell Shell theme={null}
[-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**
| **Named Argument** | **Definition** |
| ------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `-o \| --outfile OUTFILE` | 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` | Prevent all output results. |
| `-c \| --configfile CONFIGFILE` | Direct pyocient to use a configuration file.
You must include the path location and name of the configuration file, enclosed in quotation marks. |
| `--noconfig` | Direct pyocient not to use a configuration file. |
| `-i \| --infile INFILE` | Direct pyocient to use an input file that contains SQL statements. |
| `-l \| --loglevel LOGLEVEL` | Sets the logging level.
Logging level options include: `CRITICAL \| ERROR \| WARNING \| INFO \| DEBUG`
Defaults to `CRITICAL`.
For details, see [Log Monitoring](/log-monitoring). |
| `--logfile LOGFILE` | Direct 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` | 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` — {JavaScript} Object Notation.
`TABLE` — A table rendered in ASCII characters.
`CSV` — Comma-separated values format.
The default value is `JSON`. |
| `--nocolor` | When you use pyocient in interactive mode, the interface does not color SQL keywords. |
#### Additional pyocient CLI Commands
When running in a CLI, pyocient recognizes these commands in addition to the SQL statements and queries.
| **Command** | **Description** |
| ---------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `CONNECT TO USER USING ;` | Allows switching to a different DSN or user during an interactive session.
The DSN must be enclosed in quotation marks. Otherwise, the command follows the normal format explained in [Connect Using a DSN](#connect-using-a-dsn).
You can specify the username and password either in the DSN string or with the `USER` and `USING` keywords. |
| `SOURCE '';` | Executes the input statements from the specified file. The filename 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`. |
#### Run a Transaction in the pyocient CLI
The pyocient CLI runs two scripts on a single connection with the `-i` argument. To run multiple statements in one transaction, use the `SET AUTOCOMMIT OFF` statement, and then end the transaction with the `COMMIT` or `ROLLBACK` statement. Because each invocation uses one connection, include all statements of the transaction in a single input file.
The `txn_commit.sql` example script inserts multiple rows into the `public.txn_demo` table, confirms the row count with a query that reads its own uncommitted writes, and then commits the transaction.
```sql SQL theme={null}
SET AUTOCOMMIT OFF;
INSERT INTO public.txn_demo VALUES (1, 'alpha');
INSERT INTO public.txn_demo VALUES (2, 'beta');
SELECT count(*) AS n_in_txn FROM public.txn_demo;
COMMIT;
```
The `txn_rollback.sql` example script inserts multiple rows into the `public.txn_demo` table, confirms the row count, and then rolls back the transaction, discarding the inserted rows. The example confirms the discard using another row-count query.
```sql SQL theme={null}
SET AUTOCOMMIT OFF;
INSERT INTO public.txn_demo VALUES (3, 'gamma');
INSERT INTO public.txn_demo VALUES (4, 'delta');
SELECT count(*) AS n_in_txn FROM public.txn_demo;
ROLLBACK;
SELECT count(*) AS n_after_rollback FROM public.txn_demo;
```
Run each script with the `-i` argument.
```shell Shell theme={null}
pyocient "ocient://user:password@myhost:4050/mydb?tls=unverified" -i txn_commit.sql
pyocient "ocient://user:password@myhost:4050/mydb?tls=unverified" -i txn_rollback.sql
```
## pyocient API
This Python database API conforms to the [Python Database API Specification 2.0](https://peps.python.org/pep-0249/), 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](#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
```python Python theme={null}
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](#connect-using-a-dsn). 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.
```ini INI theme={null}
[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**
#### autocommit
Controls the autocommit mode for the connection. When you set this method to `True`, the database commits each statement immediately. This mode is the default value. When you set this attribute to `False`, the database groups statements into a transaction that you complete by calling [commit()](#commit) or [rollback()](#rollback).
When you change the value from `False` to `True`, the database commits any pending transaction.
This example disables autocommit mode for a connection.
```python Python theme={null}
connection.autocommit = False
```
#### close()
Close the connection. Any subsequent queries on the closed connection fail.
#### commit()
Commit the pending transaction and permanently save all changes made after the transaction started. This method has an effect only when the `autocommit` method value is `False`.
#### cursor()
Return a new cursor object for this connection.
#### rollback()
Roll back the pending transaction and discard all changes made after the transaction started. This method has an effect only when the `autocommit` method value is `False`.
### pyocient.Cursor
#### Constructor
```python Python theme={null}
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) \[#execute]
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) \[#fetchmany]
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](#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:**
```text Text theme={null}
new_host[string]: the new host to be remapped
new_port[int]: the new port to be remapped
```
**Returns:**
```text Text theme={null}
[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:**
```text Text theme={null}
new_host[string]: the new host to be redirected
new_port[int]: the new port to be redirected
```
**Returns:**
```text Text theme={null}
[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](#execute) call to set memory sizes for the specified operation.
For details on usage, see the [Python API documentation](https://peps.python.org/pep-0249/#setinputsizes).
#### setoutputsize(size \[, column])
Used before an [execute](#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](https://peps.python.org/pep-0249/#setinputsizes).
### 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](https://peps.python.org/pep-0249/#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](http://docs.python.org/library/time.html) 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.
```text Text theme={null}
exception Error(reason, sql_state='58005', vendor_code=-100)
```
**Warning**
Exception that is the base class of all other warning exceptions.
```text Text theme={null}
exception Warning(reason='', sql_state='00000', vendor_code=0)
```
**InterfaceError**
Exception raised for errors related to the database interface rather than the database itself.
```text Text theme={null}
exception InterfaceError(reason, sql_state='58005', vendor_code=-100)
```
**DatabaseError**
Exception raised for errors that are related to the database.
```text Text theme={null}
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.
```text Text theme={null}
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.
```text Text theme={null}
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.
```text Text theme={null}
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.
```text Text theme={null}
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.
```text Text theme={null}
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.
```text Text theme={null}
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](https://peps.python.org/pep-0249/#rownumber) in that sequence.
#### Connection.Error, Connection.ProgrammingError, etc.
All exception classes defined by the DB API standard should be exposed on the [Connection](https://peps.python.org/pep-0249/#connection) objects as attributes (in addition to being available at module scope).
These attributes simplify error handling in multi-connection environments.
#### Cursor[.connection](https://peps.python.org/pep-0249/#id34)
This read-only attribute returns a reference to the [Connection](https://peps.python.org/pep-0249/#connection) object on which the cursor was created.
The attribute simplifies writing polymorph code in multi-connection environments.
#### Cursor[.\_\_iter\_\_](https://peps.python.org/pep-0249/#iter)()
Return self to make cursors compatible with the Python iteration protocol.
### Transaction pyocient API Example
To run multiple statements in a single transaction with the pyocient API, set the connection [autocommit](#autocommit) attribute to `False`, and then call the [commit()](#commit) or [rollback()](#rollback) methods. For transactions, autocommit mode is on by default. Ensure that you execute SQL statements that are supported by transactions. Otherwise, the database throws an error. For a list of supported statements, see [Transactions](/transactions).
The example code establishes a connection and creates the `txn_demo` table. Then, the example inserts two rows, commits the transaction, and verifies the row count. This example continues to insert two more rows and verify the row count. The code then calls the rollback method and verifies the row count to ensure that the rollback action is successful. The example resets the autocommit mode for other transactions.
```python Python theme={null}
import pyocient
conn = pyocient.connect(
host="myhost:4050",
user="user", password="password",
database="mydb", tls="unverified",
)
def count(label):
cur = conn.cursor()
cur.execute("SELECT count(*) AS n FROM public.txn_demo")
print(f"{label} -> {cur.fetchone()[0]}")
cur.close()
# DDL must run outside a transaction (autocommit is on by default)
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS public.txn_demo")
cur.execute("CREATE TABLE public.txn_demo (id INT, v VARCHAR(64))")
cur.close()
# Transaction 1: execute multiple inserts, then commit
conn.autocommit = False
cur = conn.cursor()
cur.execute("INSERT INTO public.txn_demo VALUES (1, 'alpha')")
cur.execute("INSERT INTO public.txn_demo VALUES (2, 'beta')")
count("T1 in-txn (read-your-writes, expect 2)")
conn.commit()
count("T1 after commit() (expect 2)")
# Transaction 2: execute multiple inserts, then rollback
cur.execute("INSERT INTO public.txn_demo VALUES (3, 'gamma')")
cur.execute("INSERT INTO public.txn_demo VALUES (4, 'delta')")
count("T2 in-txn (expect 4)")
conn.rollback()
count("T2 after rollback() (expect 2)")
conn.autocommit = True # commits any open work
conn.close()
```
## Related Links
[pyocient PyPI](https://pypi.org/project/pyocient/)
***
*Linux® is the registered trademark of Linus Torvalds in the U.S. and other countries.*