The main connection

The class Jdbc creates a connection to a database, which remains open until the object isdestroyed.

class Jdbc(login, auto_commit=False, upper_case=True)

Creates a connection. Raises an exception if the connection fails, see the example below.

Parameters:
  • login (str) –

    login alias defined in config.yml, or authentication credentials like:

    username/password@service

    The parser assumes that the name of the service does not contain the ‘@’ character. The password should not contain a ‘/’.

  • auto_commit bool (bool) – specifies the auto-commit mode of the connection at startup. Defaults to False (auto-commit disabled).
  • upper_case (bool) – specifies if the column names of SQL queries are converted into upper-case. Convenient if the result of queries is converted into dictionaries.

Example:

from lwetl import Jdbc, ServiceNotFoundException, DriverNotFoundException

# create a connection to 'scott' with password 'tiger' to oracle server 'osrv1'
# (as defined in tnsnames.ora)

try:
    jdbc = Jdbc('scott/tiger@osrv01')
except (ServiceNotFoundException, DriverNotFoundException) as nfe:
    print('ERROR - could initialize: ' + str(nfe))
except ConnectionError as ce:
    print('ERROR - failed to connect: ' + str(ce))
connection

The connection to the database as returned by jaydebeapi.connect. See PEP249 for further details.

execute(sql: str, parameters: (list, tuple) = None, cursor: Union[Cursor, None] = None, use_current_cursor: bool = True, keep_cursor: bool = False) → Cursor:

Execute a query, optionally with list of parameters, or a list of a list of parameters. Raises an SQLExecutionException if the query fails, see the example below.

Parameters:
  • sql (str) – query to execute
  • parameters (tuple,list,None) – parameters specified in the sql query. May also be None (no parameters), or a list of lists
  • cursor (Cursor,None) – the cursor to use for execution. Auto-generate, if not specified. For details see the next function arguments.
  • use_current_cursor (bool) – if the cursor is not specified, it will try to use the last-used cursor of the same life-cycle type. See the next argument for details. Defaults to True.
  • keep_cursor (bool) – life-span of the cursor: keep it on a commit or rollback. Defaults to False. It is intended to permit long-lived cursors, when a large number of rows are read from the database, which triggers write- operations on a different cursor, possibly with multiple commits.
Returns:

a jaydebeapi.connect.Cursor for further processing.

Example:

from lwetl import Jdbc, SqlExecutionException

jdbc = Jdbc('scott/tiger@osrv01')

try:
    cur = jdbc.execte("INSERT INTO TST_NAMES (ID, USERNAME) VALUES (17,'scott')")
except SQLExectionException as sqle:
    print('ERROR - could not execute: ' + str(sqle))
close(cursor=None):

Closes the specified cursor. Use the current if not specified. Cursors which are aleady closed, or are not associated to the jdbc conection are silently ignored.

get_columns(cursor=None) → OrderedDict:
Parameters:cursor (Cursor) – the cursor to query. Uses the last used (current) cursor, if not specified.
Returns:the column associated to the cursor as an OrderedDict, or an empty dictionary if no columns were found.
commit():

Commits pending modifications of the specified cursor to the database. Commits and invalidates all cursors with pending commits.

Use appropriate arguments in the execute() command, if you intend to generate read-only queries, which must extend over multiple commits to the database. It is recommended to use the query() command for this purpose.

rollback():

Rolls back pending modifications to the database. Cancels and invalidates all cursors with pending commits.

get_data(cursor: Cursor = None, return_type=tuple, include_none=False, max_rows: int = 0, array_size: int = 1000) → iterator:

Get the data retrieved from a execute() command.

Parameters:
  • cursor (Cursor) – cursor to query, use current if not specified
  • return_type (Any) – the return type of the method. Defaults to tuple. Other valid options are list, dict, OrderedDict, or a (tuple of) stings. In case of the latter, the output is casted to the specified types. Supported types are Any (no casting), str, int, bool, float, date, or a format string compatible with :class:’datetime.strptime()’. The format string for ‘date’ is ‘%Y-%m-%d [%H:%M:%S]’. If a single string is specified, the returned row will only be the first value of each row. Otherwise the output is a tuple of values with a maximum length of the specified input tuple. This option is particularly useful for connections to a sqlite, where the auto-casting casting of the types in the jdbc driver may fail.
  • include_none (bool) – if set to True, also returns None values in dictionaries. Defaults to False. For tuple, or list, all elements are always returned.
  • max_rows (int) – maximum number of rows to return before closing the cursor. Negative or zero implies all rows
  • array_size (int) – the buffer size to retrieve batches of data.
Returns:

an iterator with rows of data obtained from an SQL with the data-type specified with the return_type parameter.
query(sql: str, parameters=None, return_type=tuple, max_rows=0, array_size=1000) → iterator:

Combines the execute() and get_data() into a single statement.

query_single(sql: str, parameters=None, return_type=tuple) → (tuple, list, dict, OrderedDict):
Returns:only the first row from query()
query_single_value(sql: str, parameters=None):
Returns:the first column from query_single()
get_int(sql: str, parameters=None):

A short-cut for:

int(query_single_value(sql, parameters))

Exceptions

class SQLExcecuteException

Raised when an execute() command cannot be parsed.

class ServiceNotFoundException

Raised when a database connection cannot be reach the database server.

class DriverNotFoundException

Raised when the jdbc driver, associated to the database connection, cannot be retrieved.

class CommitException

Raised when a commit() command fails.

Utility functions and classes

class JdbcInfo(login: str)

Displays parameter information of the jdbc driver.

Parameters:login (str) – login alias defined in config.yml, or authentication credentials.

Example:

from lwetl JdbcInfo

jdbc_info = JdbcInfo('scott')
jdbc_info()
get_execution_statistics() → str

Retrieves some timing statistics on the established connections.

Return type:multi-line string
tag_connection(tag:str, jdbc:Jdbc)

Marks specific connections, such that the function get_execution_statistics() provides more detail.

Parameters:
  • tag (str) – a tag for a connection
  • jdbc (Jdbc) – an established database connection

Example:

from import Jdbc, get_execution_statistics, tag_connection

jdbc = {
    'SRC': Jdbc('scott_source'),
    'TRG': Jdbc('scott_target')
}
for tag, con in jdbc.items():
    tag_connection(tag,con)

# do lots of work

print(get_execution_statistics())