API Reference¶

The central object in Sibilla is the sibilla.Database class. Any application that wants to use Sibilla to extract and manipulate the data stored in an Oracle database has to create an instance of this class, either directly or via a subclass that implements the customisation described in this reference.

sibilla module¶

exception sibilla.ConnectionError¶: Database connection error.

class sibilla.CursorRow(cursor, row, columns=None)¶

Turn a row of data into a dictionary/list-like object.

This default implementation of the RowWrapper abstract base class allows accessing row values both as attributes as well as tuple entries.

For example, if a query returns the tuple (10, None, "Hello") and the corresponding columns are named A, B and C, by wrapping it with CursorRow you get an object row that gives you the attributes a/A, b/B and c/C for which row.a = row[0], row.B = row[1] and row.c = row[2].

static from_cursor(cursor)¶

Convert the rows of a cursor into richer Python objects.

Subclasses must implement this method in order to instruct the Database.fetch_all() method on how to wrap the result from the cursor.

Parameters:	cursor (`cx_Oracle.Cursor`) – The cursor with the results ready to be fetched.
Returns:	the wrapped rows.
Return type:	generator

static from_list(cursor, data)¶

Convert the elements in the data list into richer Python objects.

Subclasses must implement htis method in order to instruct the fetch_many method on how to wrap the results in the data list.

Parameters:	cursor (`cx_Oracle.Cursor`) – The cursor used to obtain the data. data (`list`) – The list of rows obtained from the given cursor.
Returns:	the wrapped rows.
Return type:	`list`

exception sibilla.CursorRowError¶: Cursor row wrapper error.

class sibilla.Database(*args, **kwargs)¶

The Database class.

A subclass of the DB API specification-compliant cx_Oracle.Connection object, extended with additional features of Oracle Databases.

Objects of this kind identify the database connection with the database itself. This is because the Database class offers the capability of accessing Oracle stored object in a Pythonic way. Typical examples are data access from tables and views, which would normally require the user to embed “alien” SQL queries in Python code. The same would be true if the user wishes to call a stored function or procedure. Traditionally, this would be done by writing a block of PL/SQL code or via the cx_Oracle.Cursor.callfunc() and cx_Oracle.Cursor.callproc() methods. Sibilla allows you to call them as if they were plain Python methods of any Database instance.

Example

To create an Database instance, import Database from sibilla and pass username, password and dsn to it (plus any other optional argument accepted by the constructor of cx_Oracle.Connection):

>>> from sibilla import Database
>>> db = Database(username, password, dsn=TNS)

The Database class offers a built-in look-up for Oracle objects, like Tables, Views, Functions, Procedures and Packages.

Example

To create a reference to a table called COUNTRY in the Oracle database, access the attribute country from the Database instance:

>>> db.country
<table 'COUNTRY'>

A table is a special instance of a sibilla.dataset.DataSet object, which allows you to perform basic SQL queries in a Pythonic way, without writing a single line of SQL code. See the sections on siblla.dataset.DataSet and sibilla.table.Table for more details.

In a similar way one can access stored functions, procedures and packages. For functions and procedures stored in a package, the code will try to figure out whether the sought object is a function or a procedure. When there are overloaded objects that cannot be resolved, one can explicitly call a procedure or a function by accessing the proc and func attributes of a sibilla.package.Package instance.

Example

Assume that the database has a stored package called foo containing (overloaded) procedures and functions named bar. To get foo.bar as a procedure one has to use:

>>> db.foo.proc.bar
<procedure 'BAR' from <package 'FOO'>>

Similarly, to get bar as a function,

>>> db.foo.func.bar
<function 'BAR' from <package 'FOO'>>

When there are no ambiguities like in the previous case (e.g. there are only functions or only procedures named bar), one can simply use

>>> db.foo.bar

One can then call stored functions and procedures as normal Python functions/methods, using both positional and keyword arguments.

>>> db.foo.bar(42)
'The answer is 42. So long and thanks for all the fish!'

class Scope¶

Oracle Data Dictionary scopes.

This type is used to specify which scope the database look-up must use when looking up stored objects. Choose between ALL, DBA and USER. For more details on the meaning of these scopes, please refer to the Oracle documentation.

The ALL scope is set by default. However, if you do not make use of many objects from different schema other than the current one, consider using the USER scope instead. This will make object look-ups faster as the search is restricted to a smaller view.

fetch_all(stmt: str, *args, **kwargs) → Generator[[Any, NoneType], NoneType]¶

Fetch all rows from the execution of the provided statement.

Bind variables can be provided both as positional and as keyword arguments to this method.

Parameters:

stmt (str) – The statement to execute.
*args – Variable length argument list for positional bind variables.
**kwargs – Arbitrary keyword arguments for named bind variables.

Returns:

A collection of all the records returned by the: provided statement, either wrapped in __row_wrapper__ if not None or as tuple s otherwise.

Return type:

generator

fetch_many(stmt: str, n: int, *args, **kwargs) → list¶

Fetch (at most) n rows from the given query.

The number of raws returned as a list by this function is at most n for each call made.

Parameters:	stmt (str) – The statement to execute. n (int) – The number of rows to fetch. args – Variable length argument list for positional bind variables. *kwargs – Arbitrary keyword arguments for named bind variables.
Returns:	A list of at most n records returned by the provided statement, either wrapped in `__row_wrapper__` if not `None` or as `tuple` s otherwise.
Return type:	list

fetch_one(stmt: str, *args, **kwargs) → Any¶

Fetch a single row from the execution of the provided statement.

Bind variables can be provided both as positional and as keyword arguments to this method.

Parameters:

stmt (str) – The statement to execute.
*args – Variable length argument list for positional bind variables.
**kwargs – Arbitrary keyword arguments for named bind variables.

Returns:

An instance of the __row_rapper__ class if not: None or tuple otherwise.

Return type:

object

get_errors(name: str = None, type: str = None) → list¶

Get Oracle errors.

Retrieves all the current errors for the logged user. Internally, this method performs a query on the inner join between USER_ERRORS and USER_SOURCES. The result is a collection of records with the fields name, type, line, position, error and text.

Parameters:

name (str) – Filter errors by object name. A pattern can be used (case sensitive)
type (str) – Filter errors by object type. A pattern can be used (case insensitive)

Returns:

a list of CursorRow objects describing all the requested: error messages.

Return type:

list

get_output() → str¶: Get the output from the standard output stream buffer.

plsql(stmt: str, *args, batch: list = None, **kwargs)¶

Execute (PL/)SQL code.

Bind variables can be provided both as positional and as keyword arguments to this method. Returns a cursor in case data needs to be fetched out of it.

If the provided statement is to be executed multiple times but with different values, the batch argument should be used instead of implementing a loop in Python in order to improve performance.

Parameters:

stmt (str) – The (PL/)SQL statement to execute.
*args – Variable length argument list for positional bind variables.
batch (list) – A list of bind variables in the form of tuples to use iteratively with the given (PL/)SQL statement.
**kwargs – Arbitrary keyword arguments for named bind variables.

Returns:

the cursor associated with the code: execution.

Return type:

cx_Oracle.Cursor

session_user¶: Returns the session user for the connection.

set_row_wrapper(wrapper)¶

Set the row wrapper class.

This class is used to wrap the raw rows returned by a SQL query around richer Python objects. See RowWrapper for more details.

set_scope(scope)¶

Set the Oracle Data Dictionary scope.

Use one of the Database.Scope attributes. By default, a Database instance is created with the Database.Scope.ALL scope. For applications that rely mostly or exclusively on the logged user schema, it is recommended that the scope be set to Database.Scope.USER for better performance.

var(var_type)¶

Create a PL/SQL variable reference.

One can either pass the name of the variable type as defined in the cx_Oracle package (e.g. STRING), or pass the type itself (e.g. cx_Oracle.STRING).

Parameters:	var_type – The type of the variable to create.
Returns:	The requested variable reference.
Return type:	object

exception sibilla.DatabaseError¶: Generic database error.

exception sibilla.IdentifierError¶: SQL identifier error.

exception sibilla.LoginError¶: Database login error.

class sibilla.RowWrapper¶

Abstract wrapper class for cursor rows.

The results returned from a query are plain tuples and therefore lack some information like, e.g., column names. A row wrapper allows to wrap the results from a cursor or individual rows around a Python object that carries extra information as required.

static from_cursor(cursor) → Generator[[Any, NoneType], NoneType]¶

Convert the rows of a cursor into richer Python objects.

Subclasses must implement this method in order to instruct the Database.fetch_all() method on how to wrap the result from the cursor.

Parameters:	cursor (`cx_Oracle.Cursor`) – The cursor with the results ready to be fetched.
Returns:	the wrapped rows.
Return type:	generator

static from_list(cursor, data: list) → list¶

Convert the elements in the data list into richer Python objects.

Subclasses must implement htis method in order to instruct the fetch_many method on how to wrap the results in the data list.

Parameters:	cursor (`cx_Oracle.Cursor`) – The cursor used to obtain the data. data (`list`) – The list of rows obtained from the given cursor.
Returns:	the wrapped rows.
Return type:	`list`

exception sibilla.SibillaError¶: Base Sibilla exception.

sibilla.sql_identifier(name: str) → str¶

Treat string as SQL identifier.

SQL identifier are case insensitive, unless they are double-quoted. This helper function ensures that any identifier is treat consistently by returning an upper-case version of the given string if it is not double-quoted, otherwise it return the string itself.

Example

>>> sql_identifier('foo')
'FOO'
>>> sql_identifier('"foo"')
'"foo"'

Submodules¶

sibilla.caching module¶

class sibilla.caching.Cached(cache=None)¶: Cache mixin for adding synchronised TTL caching support to objects.

class sibilla.caching.SynchronizedTTLCache¶

Implement a synchronised TTL cache.

flush()¶: Flush the cache.

sibilla.caching.cachedmethod(f)¶: Caching decorator for class and instance methods.

sibilla.caching.set_maxsize(size)¶: Set the maximum cache size when creating Cached objects.

sibilla.caching.set_ttl(ttl)¶: Set the TTL value to use when creating Cached objects.

sibilla.callable module¶

class sibilla.callable.Callable(db, name, type, schema, package=None)¶: Base class for Procedures and Functions.

exception sibilla.callable.CallableError¶: Database stored callable error.

class sibilla.callable.CallableFactory(callable_class, schema, package=None)¶

Create a Python callable for a database stored callable.

This class should be considered as private as it is used internally to allow calling an object as a function or a procedure explicitly.

sibilla.dataset module¶

class sibilla.dataset.DataSet¶

describe()¶

Describe the table columns.

The return value is the same as the description attribute of a Cursor object from the cx_Oracle module.

fetch_all(select='*', where=None, order_by=None, **kwargs)¶

fetch_many(n, select='*', where=None, order_by=None, **kwargs)¶

fetch_one(select='*', where=None, order_by=None, **kwargs)¶

classmethod set_row_class(row_class)¶

exception sibilla.dataset.MultipleRowsError¶

Multiple rows returned when only one expected.

Usually a consequence of a primary key defined on the table class that is not part of the actual table schema and that is violated by the retrieved data.

exception sibilla.dataset.NoSuchRowError¶

Raised when no row matches the given conditions.

Similar to Oracle’s``NO_DATA_FOUND``

exception sibilla.dataset.QueryError¶: Database query error.

class sibilla.dataset.Row(dataset, kwargs)¶

db¶: Get the underlying database.

get(name, default=None)¶

Attribute getter hook.

This method provides a convenient customisation interface for subclasses of Row. Implement it to define custom behaviour when retrieving row attributes.

When the requested attribute cannot be determined, this method should raise the RowGetterError exception.

exception sibilla.dataset.RowAttributeError¶: Row attribute access error.

exception sibilla.dataset.RowError¶: Dataset row error.

exception sibilla.dataset.RowGetterError¶

Row getter method error.

Raised when the requested attribute on the row cannot be determined.

sibilla.dataset.rowattribute(func)¶

sibilla.dataset.rowmethod(func)¶

sibilla.datatypes module¶

class sibilla.datatypes.Record¶

sibilla.function module¶

class sibilla.function.Function(db, name, schema, package=None)¶

Oracle stored function.

This class implements Oracle stored functions, including those stored inside packages as Python callable objects so that they can be called as native Python functions and methods.

return_type¶: The function return type.

sibilla.object module¶

exception sibilla.object.ObjectError¶

Oracle object error.

Defines an error specific to object lookup within the Oracle database associated to an Database object.

class sibilla.object.ObjectLookup(db)¶

Oracle object look-up.

Instances of this class allow customising the way objects (e.g. tables, views, packages, …) are looked up from the Oracle database.

Every instance of Database comes with a default ObjectLookup instance accessible through the read-only property __lookup__. This can be used to specify the custom classes to handle general Oracle object, as well as specific items, like individual tables, packages, etc…

Example

Assume that the database we are connected to has families of tables with a certain prefix, say DATA_, that can be treated as a sort of namespace. One might want to be able to access such tables (e.g. the DATA_CUSTOMER table) with an attribute access on a Database object as:

>>> from sibilla import Database
>>> db = Database("username", "password", "dsn")
>>> db.data.customer

This can be achieved as shown below. First of all, one should subclass ObjectLookup and override the renaming method:

from sibilla import ObjectLookup

class DataLookup(ObjectLookup):

    def renaming(self, name):
        return "data_" + name

Then, one either assigns a new instance of DataLookup to db.data:

>>> db.data = DataLookup()

or one subclasses Database and initializes data as an object attribute:

class MyDB(Database):
    def __init__(self, *args, **kwargs):
        super(MyDB, self).__init__(*args, **kwargs)
        self.data = DataLookup(self)


db = MyDB("username", "password", "dsn")

Thus, db.data.customer becomes equivalent to db.data_customer.

By default, the look-up will create an object of type Table from sibilla for the table accessed as db.data_customer. To change this behaviour, subclass Table and pass the class to the replace method as a dictionary:

from sibilla import ObjectType, Table

MyTable(Table):

pass

db.__lookup__.replace({ObjectType.TABLE: MyTable})

Custom table classes can also be assigned to general attributes of a Database object by passing the attribute name as key in the dictionary:

>>> db.__lookup__.replace({"customer": MyTable})

This way, the custom table class MyTable is used only when accessing the attribite customer from db rather than for every table. Observe that this is not the same as assigning MyTable directly to the attribute customer of db.

get_class(type_name)¶

The class assigned to an Oracle object type.

Returns the class associated with the given type or attribute.

Parameters:	type_name (str) – The type name to whose associated class is to be retrieved. This is meant to be either one of the values exposed by the `ObjectType` class, or a custom attribute.
Returns:	The class associated with the given type/attribute.
Return type:	class

renaming(name: str) → str¶

Rename attribute before performing the look-up.

This methods offers a customisation interface for implementing naming patterns and character substitution in look-ups. Any attribute accessed on an ObjectLookup object is preprocessed by this method. This can be useful when database objects have characters which are not allowed in Python to appear in identifiers. For example, my_package# is a valid Oracle package name, however db.my_package# does not produce the desired result in Python. To overcome this situation one can subclass ObjectLookup with an overridden method renaming that does a suitable conversion.

Example

To access the Oracle package my_package# from Python, one could use the convention of replacing any occurrence of # with __.

from sibilla import ObjectLookup

class MyLookup(ObjectLookup):

def renaming(self, name):

return name.replace(‘__’, ‘#’)

Any access to the attribute my_package__ from a MyLookup object is translated into my_package# before being actually looked up from the database.

Parameters:	name (str) – The attribute accessed on an instance of the class.
Returns:	The translated attribute for lookup on the database.
Return type:	str

replace(types: dict)¶

Replace Python classes for Oracle objects.

Overrides the classes used to handle types/attributes on the lookup instance.

Parameters:	types (dict) – A dictionary of key-value pairs where the key indicates the type/attribute and the value the class to use to handle the specified type/attribute. The standard Oracle object types can be overridden by using the constants exposed by the `ObjectType` class as key values.

exception sibilla.object.ObjectLookupError¶: Raised when an object look-up fails.

class sibilla.object.ObjectType¶

Supported Oracle object types.

Their main purpose is to allow changing the default class used to wrap Oracle object with Python objects by ObjectLookup.

FUNCTION = 'FUNCTION'¶

PACKAGE = 'PACKAGE'¶

PROCEDURE = 'PROCEDURE'¶

RECORD = 'RECORD'¶

TABLE = 'TABLE'¶

VIEW = 'VIEW'¶

exception sibilla.object.ObjectTypeError¶: Problems with Oracle object types.

class sibilla.object.OracleObject(db, object_name, object_type, schema)¶

Base Oracle object.

This abstract class represents a generic stored Oracle object and makes the base for concrete objects, like sibilla.table.Table, sibilla.package.Package etc….

db¶

name¶

object_type¶

renaming(name)¶

sibilla.package module¶

class sibilla.package.Package(db, name, schema)¶

Oracle package class.

This class models a stored Oracle package, providing access to functions and procedures.

exception sibilla.package.PackageAttributeError¶

Package attribute error.

Raised when unable to resolve the requested attribute from a package.

sibilla.procedure module¶

class sibilla.procedure.Procedure(db, name, schema, package=None)¶

Oracle stored procedure.

This class implements Oracle stored procedures, including those stored inside packages as Python callable objects so that they can be called as native Python procedures and methods.

sibilla.schema module¶

class sibilla.schema.Schema(db, schema)¶

Database schema class.

name¶: The schema name.

exception sibilla.schema.SchemaError¶

Database schema error.

Raised when unable to reference a schema.

sibilla.table module¶

exception sibilla.table.PrimaryKeyError¶: Raised when unable to make use of a primary key.

class sibilla.table.SmartRow(dataset, kwargs)¶

Smart row class.

A smart row is a table row that can follow foreign key references and return the referenced row instead of the raw value.

get(name, default=None)¶

Attribute getter hook.

This method provides a convenient customisation interface for subclasses of Row. Implement it to define custom behaviour when retrieving row attributes.

When the requested attribute cannot be determined, this method should raise the RowGetterError exception.

class sibilla.table.Table(db, name=None, schema=None)¶

Oracle table class.

A table is a data set that can have primary and foreign key constraints. For tables with a primary key constraint, rows can be accessed from a table as if this was indexed by the primary key values.

drop(flush_cache=True)¶

Drop the table.

By default, the internal table cache is flushed to allow changes to be synchronised with the database.

insert(values)¶

Insert values into the table.

The passed values can either be a single row to add or a list of multiple row to insert as a batch. A row in this case is either a dictionary with the name of the columns and the corresponding values to set, or a tuple with as many entries as the columns of the table.

truncate()¶: Truncate the table.

exception sibilla.table.TableEntryError¶: Raised when failed to access a table entry.

exception sibilla.table.TableError¶: Table-related database error.

exception sibilla.table.TableInsertError¶

class sibilla.table.TableRow(dataset, kwargs)¶

Table row class.

Contrary to a normal row, a table row can have a primary key associated to it.

sibilla.view module¶

class sibilla.view.View(db, name=None, schema=None)¶: Oracle view class.

exception sibilla.view.ViewError¶: View-related database error.

API Reference¶

sibilla module¶

Submodules¶

sibilla.caching module¶

sibilla.callable module¶

sibilla.dataset module¶

sibilla.datatypes module¶

sibilla.function module¶

sibilla.object module¶

sibilla.package module¶

sibilla.procedure module¶

sibilla.schema module¶

sibilla.table module¶

sibilla.view module¶

Table Of Contents

Related Topics