Ocient Python Module: pyocient

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 docid 7fvlfho nm62iwjqs9bw8 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 ocient does not support transactions at this time data types supports all ocient data types docid\ g9 6amoszrxd9exdwggcz security / encryption gcm, cbc, and sso protocols are supported tls protocol is optional as a setting 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 ocient python module pyocient docid\ bi n4be0tvikotwhafxxr ocient python module pyocient docid\ bi n4be0tvikotwhafxxr ocient python module pyocient docid\ bi n4be0tvikotwhafxxr ocient python module pyocient docid\ bi n4be0tvikotwhafxxr 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 argument definition \<user> required an ocient username \<password> required the password associated with your username for connection 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 host for example, host1 is the first for the connection attempt in this case ocient //someone\ somepassword\@host1,host2 4051/mydb \<port> the port number used by your host the default is 4050 \<database> the name of the database for the connection the default is system \<parameters> 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 cluster and node management docid\ bnlgs0qq1wre7ndja8q0x 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 m ultiple 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 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 docid\ goypkivlud77sz0sx ekq 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 \<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, the command follows the normal format explained in ocient python module pyocient docid\ bi n4be0tvikotwhafxxr 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 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 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 ocient python module pyocient docid\ bi n4be0tvikotwhafxxr the api returns o utput 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 ocient python module pyocient docid\ bi n4be0tvikotwhafxxr 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 transaction 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 ocient python module pyocient docid\ bi n4be0tvikotwhafxxr 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 given 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 ocient python module pyocient docid\ bi n4be0tvikotwhafxxr call to set memory sizes for the specified operation for details on usage, see the python api documentation setoutputsize(size \[, column]) used before an ocient python module pyocient docid\ bi n4be0tvikotwhafxxr 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 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 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 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 related links pyocient pypi https //pypi org/project/pyocient/ {{linux}} is the registered trademark of linus torvalds in the u s and other countries