ll.orasql – Utilities for cx_Oracle

ll.orasql contains utilities for working with cx_Oracle:

  • It allows calling procedures and functions with keyword arguments (via the classes Procedure and Function).

  • Query results will be put into Record objects, where database fields are accessible as object attributes.

  • The Connection class provides methods for iterating through the database metadata.

  • Importing this module adds support for URLs with the scheme oracle to ll.url. Examples of these URLs are:

    oracle://user:pwd@db/
    oracle://user:pwd@db/view/
    oracle://user:pwd@db/view/USER_TABLES.sql
    oracle://sys:pwd:sysdba@db/
    
class ll.orasql.Args(arg=None, **kwargs)[source]

Bases: dict

An Args object is a subclass of dict that is used for passing arguments to procedures and functions. Both item and attribute access (i.e. __getitem__() and __getattr__()) are available. Names are case insensitive.

class ll.orasql.Argument(name, position, datatype, isin, isout)[source]

Bases: object

Argument objects hold information about the arguments of a stored procedure.

class ll.orasql.Callable(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.MixinCodeSQL, ll.orasql.Object

Models a callable object in the database, i.e. functions and procedures.

arguments(connection=None)[source]

Generator that yields all arguments of the function/procedure self.

class ll.orasql.CheckConstraint(name, owner=None, connection=None)[source]

Bases: ll.orasql.Constraint

Models a check constraint in the database.

class ll.orasql.Column(name, owner=None, connection=None)[source]

Bases: ll.orasql.Object

Models a single column of a table in the database. This is used to output ALTER TABLE statements for adding, dropping and modifying columns.

comment(connection=None)[source]

The comment for this column.

datatype(connection=None)[source]

The SQL type of this column.

default(connection=None)[source]

The SQL default value for this column.

nullable(connection=None)[source]

Is this column nullable?

class ll.orasql.Comment(name, owner=None, connection=None)[source]

Bases: ll.orasql.Object

Models a column comment in the database.

comment(connection=None)[source]

Return the comment text for this column.

class ll.orasql.Connection(*args, **kwargs)[source]

Bases: cx_Oracle.Connection

Connection is a subclass of cx_Oracle.Connection.

__init__(*args, **kwargs)[source]

Create a new connection. In addition to the parameters supported by cx_Oracle.connect() the following keyword argument is supported.

readlobs : bool or integer
If readlobs is False all cursor fetches return LOBStream objects for LOB object. If readlobs is an int LOBs with a maximum size of readlobs will be returned as bytes/str objects. If readlobs is True all LOB values will be returned as bytes/str objects.
decimal : bool
If decimal is True numbers will be returned as decimal.Decimal objects, else float will be used.

Furthermore the clientinfo will be automatically set to the name of the currently running script (except if the clientinfo keyword argument is given and None).

cursor(readlobs=None)[source]

Return a new cursor for this connection. For the meaning of readlobs see __init__().

fks(owner=ll.orasql.ALL)[source]

Generator that yields all foreign key constraints. owner can be None, ALL (the default) or a user name.

getobject(name, owner=None)[source]

Return the object named name from the schema. If owner is None the current schema is queried, else the specified one is used. name and owner are treated case insensitively.

objects(owner=ll.orasql.ALL, mode='create')[source]

Generator that yields the sequences, tables, primary keys, foreign keys, comments, unique constraints, indexes, views, functions, procedures, packages and types in the current users schema (or all users schemas) in a specified order.

mode specifies the order in which objects will be yielded:

"create"
Create order, i.e. recreating the objects in this order will not lead to errors;
"drop"
Drop order, i.e. dropping the objects in this order will not lead to errors;
"flat"
Unordered.

owner specifies from which schema objects should be yielded:

None
All objects belonging to the current user (i.e. via the view USER_OBJECTS);
ALL
All objects for all users (via the views ALL_OBJECTS or DBA_OBJECTS);
username : string
All objects belonging to the specified user
privileges(owner=ll.orasql.ALL)[source]

Generator that yields object privileges. owner can be None, ALL (the default) or a user name.

sequences(owner=ll.orasql.ALL)[source]

Generator that yields sequences. owner can be None, ALL (the default) or a user name.

tables(owner=ll.orasql.ALL, mode='flat')[source]

Generator that yields all table definitions in the current users schema (or all users schemas). mode specifies the order in which tables will be yielded:

"create"
Create order, inserting records into the table in this order will not violate foreign key constraints.
"drop"
Drop order, deleting records from the table in this order will not violate foreign key constraints.
"flat"
Unordered.

owner specifies from which user tables should be yielded. It can be None (for the current user), ALL (for all users (the default)) or a user name.

Tables that are materialized views will be skipped in all cases.

users()[source]

Generator that yields all users.

class ll.orasql.Constraint(name, owner=None, connection=None)[source]

Bases: ll.orasql.Object

Base class of all constraints (primary key constraints, foreign key constraints, unique constraints and check constraints).

isenabled(connection=None)[source]

Return whether this constraint is enabled.

table(connection=None)[source]

Return the Table self belongs to.

class ll.orasql.Cursor(connection, readlobs=None)[source]

Bases: cx_Oracle.Cursor

A subclass of the cursor class in cx_Oracle. The “fetch” methods will return records as Record objects and LOB values will be returned as LOBStream objects or str/bytes objects (depending on the cursors readlobs attribute).

__init__(connection, readlobs=None)[source]

Return a new cursor for the connection connection. For the meaning of readlobs see Connection.__init__().

ddprefix()[source]

Return whether the user has access to the DBA_* views ("dba") or not ("all").

ddprefixargs()[source]

Return whether the user has access to the DBA_ARGUMENTS view ("dba") or not ("all").

class ll.orasql.ForeignKey(name, owner=None, connection=None)[source]

Bases: ll.orasql.Constraint

Models a foreign key constraint in the database.

columns(connection=None)[source]

Return an iterator over the columns this foreign key consists of.

refconstraint(connection=None)[source]

Return the constraint referenced by self.

In most cases this is a PrimaryKey, but it also might be a UniqueConstraint.

class ll.orasql.Function(name, owner=None, connection=None)[source]

Bases: ll.orasql.Callable

Models a function in the database. A Function object can be used as a wrapper for calling the function with keyword arguments.

__call__(cursor, *args, **kwargs)[source]

Call the function with arguments args and keyword arguments kwargs. cursor must be an ll.orasql.Cursor object. This will return a tuple containing the result and a Record object containing the modified parameters (i.e. this record will contain all specified and out parameters).

class ll.orasql.Index(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.Object

Models an index in the database.

columns(connection=None)[source]

Return an iterator over the columns this index consists of.

constraint(connection=None)[source]

If this index is generated by a constraint, return the constraint otherwise return None.

isconstraint(connection=None)[source]

Is this index generated by a constraint?

rebuildsql(connection=None, term=True)[source]

Return SQL code to rebuild this index.

table(connection=None)[source]

Return the Table self belongs to.

class ll.orasql.JavaSource(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.Object

Models Java source code in the database.

class ll.orasql.LOBStream(value)[source]

Bases: object

A LOBStream object provides streamlike access to a BLOB or CLOB.

read(size=None)[source]

Read size bytes/characters from the stream and return them. If size is None all remaining data will be read.

readall()[source]

Read all remaining data from the stream and return it.

readchunk()[source]

Read a chunk of data from the stream and return it. Reading is done in optimally sized chunks.

reset()[source]

Reset the stream so that the next read() call starts at the beginning of the LOB.

seek(offset, whence=0)[source]

Seek to the position offset in the LOB. The whence argument is optional and defaults to 0 (absolute file positioning); The other allowed value is 1 (seek relative to the current position).

class ll.orasql.Library(name, owner=None, connection=None)[source]

Bases: ll.orasql.Object

Models a library in the database.

class ll.orasql.MaterializedView(name, owner=None, connection=None)[source]

Bases: ll.orasql.View

Models a meterialized view in the database.

class ll.orasql.MixinCodeSQL[source]

Bases: object

Mixin class that provides methods returning the create and drop statements for various objects.

class ll.orasql.MixinNormalDates[source]

Bases: object

Mixin class that provides methods for determining creation and modification dates for objects.

class ll.orasql.Object(name, owner=None, connection=None)[source]

Bases: object

The base class for all Python classes modelling schema objects in the database.

cdate(connection=None)[source]

Return a datetime.datetime object with the creation date of self in the database specified by connection (or None if such information is not available).

createsql(connection=None, term=True)[source]

Return SQL code to create this object.

dropsql(connection=None, term=True)[source]

Return SQL code to drop this object

exists(connection=None)[source]

Return wether the object self really exists in the database specified by connection.

fixname(code)[source]

Replace the name of the object in the SQL code code with the name of self.

classmethod names(connection, owner=ll.orasql.ALL)[source]

Generator that yields the names of all objects of this type. The argument owner specifies whose objects are yielded:

None
All objects belonging to the current user (i.e. via the view USER_OBJECTS).
ALL
All objects for all users (via the views ALL_OBJECTS or DBA_OBJECTS)
username : string
All objects belonging to the specified user

Names will be in ascending order.

classmethod objects(connection, owner=ll.orasql.ALL)[source]

Generator that yields all objects of this type in the current users schema. The argument owner specifies whose objects are yielded:

None
All objects belonging to the current user (i.e. via the view USER_OBJECTS).
ALL
All objects for all users (via the views ALL_OBJECTS or DBA_OBJECTS)
username : string
All objects belonging to the specified user
referencedby(connection=None)[source]

Objects using self.

For the meaning of connection see references().

referencedbyall(connection=None, done=None)[source]

All objects depending on self (recursively).

For the meaning of connection see references().

done is used internally and shouldn’t be passed.

references(connection=None)[source]

Objects directly used by self.

If connection is not None it will be used as the database connection from which to fetch data. If connection is None the connection from which self has been extracted will be used. If there is not such connection, you’ll get an exception.

referencesall(connection=None, done=None)[source]

All objects used by self (recursively).

For the meaning of connection see references().

done is used internally and shouldn’t be passed.

udate(connection=None)[source]

Return a datetime.datetime object with the last modification date of self in the database specified by connection (or None if such information is not available).

class ll.orasql.OracleFileResource(connection, url, mode='r', encoding='utf-8', errors='strict')[source]

Bases: ll.url.Resource

An OracleFileResource wraps an Oracle database object (like a table, view, function, procedure etc.) in a file-like API for use with ll.url.

class ll.orasql.Package(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.MixinCodeSQL, ll.orasql.Object

Models a package in the database.

class ll.orasql.PackageBody(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.MixinCodeSQL, ll.orasql.Object

Models a package body in the database.

class ll.orasql.Preference(name, owner=None, connection=None)[source]

Bases: ll.orasql.Object

Models a preference in the database.

classmethod names(connection, owner=ll.orasql.ALL)[source]

Generator that yields the names of all preferences.

classmethod objects(connection, owner=ll.orasql.ALL)[source]

Generator that yields all preferences.

class ll.orasql.PrimaryKey(name, owner=None, connection=None)[source]

Bases: ll.orasql.Constraint

Models a primary key constraint in the database.

columns(connection=None)[source]

Return an iterator over the columns this primary key consists of.

class ll.orasql.Privilege(privilege, name, grantor, grantee, owner=None, connection=None)[source]

Bases: object

Models a database object privilege (i.e. a grant).

A Privilege object has the following attributes:

privilege : string
The type of the privilege (EXECUTE etc.)
name : string
The name of the object for which this privilege grants access
owner : string or None
the owner of the object
grantor : string or None
Who granted this privilege?
grantee : string or None
To whom has this privilege been granted?
connection : Connection or None
The database connection
grantsql(connection=None, term=True, mapgrantee=True)[source]

Return SQL code to grant this privilege. If mapgrantee is a list or a dictionary and self.grantee is not in this list (or dictionary) no command will be returned. If it’s a dictionary and self.grantee is in it, the privilege will be granted to the user specified as the value instead of the original one. If mapgrantee is true (the default) the privilege will be granted to the original grantee.

classmethod objects(connection, owner=ll.orasql.ALL)[source]

Generator that yields object privileges. For the meaning of owner see Object.names().

class ll.orasql.Procedure(name, owner=None, connection=None)[source]

Bases: ll.orasql.Callable

Models a procedure in the database. A Procedure object can be used as a wrapper for calling the procedure with keyword arguments.

__call__(cursor, *args, **kwargs)[source]

Call the procedure with arguments args and keyword arguments kwargs. cursor must be a ll.orasql.Cursor object. This will return a Record object containing the result of the call (i.e. this record will contain all specified and all out parameters).

class ll.orasql.Record[source]

Bases: tuple, collections.abc.Mapping

A Record is a subclass of tuple that is used for storing results of database fetches and procedure and function calls. Both item and attribute access (i.e. __getitem__() and __getattr__()) are available. Field names are case insensitive.

get(name, default=None)[source]

Return the value for the field named name. If this field doesn’t exist in self, return default instead.

items()[source]

Return an iterator over (field name, field value) tuples.

keys()[source]

Return an iterator over field names.

replace(**kwargs)[source]

Return a new Record with the same fields as self, except for those fields given new values by whichever keyword arguments are specified.

class ll.orasql.Sequence(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.Object

Models a sequence in the database.

createsqlcopy(connection=None, term=True)[source]

Return SQL code to create an identical copy of this sequence.

class ll.orasql.SessionPool(user, password, database, min, max, increment, connectiontype=None, threaded=False, getmode=1, homogeneous=True)[source]

Bases: OracleSessionPool

SessionPool is a subclass of cx_Oracle.SessionPool.

class ll.orasql.Synonym(name, owner=None, connection=None)[source]

Bases: ll.orasql.Object

Models a synonym in the database.

getobject(connection=None)[source]

Get the object for which self is a synonym.

class ll.orasql.Table(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.Object

Models a table in the database.

columns(connection=None)[source]

Generator that yields all column objects of this table.

comments(connection=None)[source]

Generator that yields all column comments of this table.

constraints(connection=None)[source]

Generator that yields all constraints for this table.

ismview(connection=None)[source]

Is this table a materialized view?

logging(connection=None)[source]

Return whether to table is in logging mode or not.

mview(connection=None)[source]

The materialized view this table belongs to (or None if it’s a real table).

organization(connection=None)[source]

Return the organization of this table: either "heap" (for “normal” tables) or "index" (for index organized tables).

pk(connection=None)[source]

Return the primary key constraint for this table (or None if the table has no primary key constraint).

records(connection=None)[source]

Generator that yields all records of this table.

class ll.orasql.Trigger(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.MixinCodeSQL, ll.orasql.Object

Models a trigger in the database.

class ll.orasql.Type(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.MixinCodeSQL, ll.orasql.Object

Models a type definition in the database.

class ll.orasql.TypeBody(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.MixinCodeSQL, ll.orasql.Object

Models a type body in the database.

class ll.orasql.UniqueConstraint(name, owner=None, connection=None)[source]

Bases: ll.orasql.Constraint

Models a unique constraint in the database.

class ll.orasql.User(name, connection=None)[source]

Bases: object

Models a user in the database.

classmethod names(connection)[source]

Generator that yields the names of all users in ascending order

classmethod objects(connection)[source]

Generator that yields all user objects.

class ll.orasql.View(name, owner=None, connection=None)[source]

Bases: ll.orasql.MixinNormalDates, ll.orasql.Object

Models a view in the database.

ll.orasql.connect(*args, **kwargs)[source]

Create a connection to the database and return a Connection object.