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

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.LOBStream

Bases: object

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

readall()

Read all remaining data from the stream and return it.

readchunk()

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

read(size=None)

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

reset()

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

seek(offset, whence=0)

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.Record

Bases: tuple, 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)

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

keys()

Return an iterator over field names.

items()

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

replace(**kwargs)

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.SessionPool

Bases: SessionPool

SessionPool is a subclass of cx_Oracle.SessionPool.

ll.orasql.owned(obj, owner)

Do we own the object obj according to the owner specification owner?

owner can be:

None

The current user (i.e. via the view USER_OBJECTS);

ALL

Any user (via the views ALL_OBJECTS or DBA_OBJECTS);

usernamestring

The specified user

set or tuple

A set or tuple of usernames. An object must belong to any of the users to be considered owned.

class ll.orasql.Connection

Bases: Connection

Connection is a subclass of cx_Oracle.Connection.

__init__(*args, **kwargs)

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

readlobsbool or integer

If readlobs is False all cursor fetches return LOBStream objects for LOB objects. 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.

decimalbool

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)

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

tables(owner=None, mode='flat')

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.

sequences(owner=None)

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

fks(owner=None)

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

privileges(owner=None)

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

synonyms(owner=None, object_owner=ll.orasql.ALL)

Generator that yields synonyms. owner and object_owner can be None (the default), ALL, a user name or a set or tuple of user names.

users()

Generator that yields all users.

objects(owner=None, mode='create')

Generator that yields the sequences, tables, primary keys, foreign keys, table and columns comments, unique constraints, indexes, views, functions, procedures, packages, types and jobsin 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. For more information see owned().

objects_named(name, owner=None)

Return all objects 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.

There might be multiple object with the same name, if these names only differ in casing. Also there will be multiple object with the same name for packages and package bodies.

object_named(name, owner=None)

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.

If there are multiple objects with the same name, which one gets returned is undefined.

If there is no such object an SQLNoSuchObjectError exception will be raised.

ll.orasql.connect(*args, **kwargs)

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

class ll.orasql.Cursor

Bases: 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)

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

ddprefix()

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

ddprefixargs()

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

class ll.orasql.MixinNormalDates

Bases: object

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

class ll.orasql.MixinCodeSQL

Bases: object

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

class ll.orasql.SchemaObject

Bases: object

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

createsql(connection=None, term=True)

Return SQL code to create this object.

dropsql(connection=None, term=True)

Return SQL code to drop this object

fixname(code)

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

exists(connection=None)

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

cdate(connection=None)

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

udate(connection=None)

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

references(connection=None, done=None)

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)

All objects used by self (recursively).

For the meaning of connection see references().

done is used internally and shouldn’t be passed.

referencedby(connection=None)

Objects using self.

For the meaning of connection see references().

referencedbyall(connection=None, done=None)

All objects depending on self (recursively).

For the meaning of connection see references().

done is used internally and shouldn’t be passed.

class ll.orasql.OwnedSchemaObject

Bases: SchemaObject

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

classmethod names(connection, owner=None)

Generator that yields the names of all objects of this type. The argument owner specifies whose objects are yielded. For more information see owned().

Names will be in ascending order.

classmethod objects(connection, owner=None)

Generator that yields all objects of this type in the current users schema. The argument owner specifies whose objects are yielded. For more information see owned().

synonyms(connection=None)

Generator that yields all synonyms for this object.

privileges(connection=None)

Generator that yields all privileges on this object.

class ll.orasql.Sequence

Bases: MixinNormalDates, OwnedSchemaObject

Models a sequence in the database.

createsqlcopy(connection=None, term=True)

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

class ll.orasql.Table

Bases: MixinNormalDates, OwnedSchemaObject

Models a table in the database.

mview(connection=None)

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

ismview(connection=None)

Is this table a materialized view?

organization(connection=None)

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

logging(connection=None)

Return whether the table is in logging mode or not.

compression(connection=None)

Return the compression mode of the table.

(None, "BASIC" or "ADVANCED").

columns(connection=None)

Generator that yields all column objects of this table.

records(connection=None)

Generator that yields all records of this table.

comment(connection=None)

Return the table comment

comments(connection=None)

Generator that yields all column comments of this table.

constraints(connection=None)

Generator that yields all constraints for this table.

pk(connection=None)

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

fks(connection=None)

Return the foreign key constraints for this table.

uniques(connection=None)

Return the unique constraints for this table.

checks(connection=None)

Return the unique constraints for this table.

class ll.orasql.TableComment

Bases: OwnedSchemaObject

Models a table comment in the database.

comment(connection=None)

Return the comment text for this table.

class ll.orasql.ColumnComment

Bases: OwnedSchemaObject

Models a column comment in the database.

comment(connection=None)

Return the comment text for this column.

class ll.orasql.Constraint

Bases: OwnedSchemaObject

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

isenabled(connection=None)

Return whether this constraint is enabled.

table(connection=None)

Return the Table self belongs to.

class ll.orasql.PrimaryKey

Bases: Constraint

Models a primary key constraint in the database.

columns(connection=None)

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

class ll.orasql.ForeignKey

Bases: Constraint

Models a foreign key constraint in the database.

refconstraint(connection=None)

Return the constraint referenced by self.

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

columns(connection=None)

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

class ll.orasql.UniqueConstraint

Bases: Constraint

Models a unique constraint in the database.

class ll.orasql.CheckConstraint

Bases: Constraint

Models a check constraint in the database.

class ll.orasql.Index

Bases: MixinNormalDates, OwnedSchemaObject

Models an index in the database.

rebuildsql(connection=None, term=True)

Return SQL code to rebuild this index.

constraint(connection=None)

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

isconstraint(connection=None)

Is this index generated by a constraint?

table(connection=None)

Return the Table self belongs to.

columns(connection=None)

Return an iterator over the columns this index consists of.

class ll.orasql.Synonym

Bases: OwnedSchemaObject

Models a synonym in the database.

object(connection=None)

Return the object for which self is a synonym.

classmethod names(connection, owner=None, object_owner=ll.orasql.ALL)

Generator that yields the names of all synonyms. For the meaning of owner and object_owner see objects().

Names will be in ascending order.

classmethod objects(connection, owner=None, object_owner=ll.orasql.ALL)

Generator that yields all synonym in the current users schema. The argument owner specifies to which owner the synonym must belong to to be yielded. The argument object_owner specifies to which owner the object must belong to to be yielded. For more information see owned().

class ll.orasql.View

Bases: MixinNormalDates, OwnedSchemaObject

Models a view in the database.

class ll.orasql.MaterializedView

Bases: View

Models a meterialized view in the database.

class ll.orasql.Library

Bases: OwnedSchemaObject

Models a library in the database.

class ll.orasql.Argument

Bases: object

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

class ll.orasql.Callable

Bases: MixinNormalDates, MixinCodeSQL, OwnedSchemaObject

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

arguments(connection=None)

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

class ll.orasql.Procedure

Bases: 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)

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.Function

Bases: 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)

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.Package

Bases: MixinNormalDates, MixinCodeSQL, OwnedSchemaObject

Models a package in the database.

class ll.orasql.PackageBody

Bases: MixinNormalDates, MixinCodeSQL, OwnedSchemaObject

Models a package body in the database.

class ll.orasql.Type

Bases: MixinNormalDates, MixinCodeSQL, OwnedSchemaObject

Models a type definition in the database.

class ll.orasql.TypeBody

Bases: MixinNormalDates, MixinCodeSQL, OwnedSchemaObject

Models a type body in the database.

class ll.orasql.Trigger

Bases: MixinNormalDates, MixinCodeSQL, OwnedSchemaObject

Models a trigger in the database.

class ll.orasql.JavaSource

Bases: MixinNormalDates, OwnedSchemaObject

Models Java source code in the database.

class ll.orasql.Privilege

Bases: object

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

A Privilege object has the following attributes:

privilegestring

The type of the privilege (EXECUTE etc.)

namestring

The name of the object for which this privilege grants access

ownerstring or None

the owner of the object

grantorstring or None

Who granted this privilege?

granteestring or None

To whom has this privilege been granted?

connectionConnection or None

The database connection

object(connection=None)

Return the object on which self grants a privilege.

classmethod objects(connection, owner=None)

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

grantsql(connection=None, term=True, mapgrantee=True)

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.

class ll.orasql.Column

Bases: OwnedSchemaObject

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

datatype(connection=None)

The SQL type of this column.

default(connection=None)

The SQL default value for this column.

nullable(connection=None)

Is this column nullable?

compression(connection=None)

The compression mode for this LOB column.

Return None if this is not a LOB column, or it isn’t compressed.

comment(connection=None)

The comment for this column.

class ll.orasql.User

Bases: SchemaObject

Models a user in the database.

classmethod names(connection)

Generator that yields the names of all users in ascending order

classmethod objects(connection)

Generator that yields all user objects.

class ll.orasql.Preference

Bases: OwnedSchemaObject

Models a preference in the database.

classmethod names(connection, owner=None)

Generator that yields the names of all preferences.

classmethod objects(connection, owner=None)

Generator that yields all preferences.

class ll.orasql.JobClass

Bases: SchemaObject

Models a job class (from the dbms_scheduler package) in the database.

classmethod names(connection)

Generator that yields the names of all job classes.

classmethod objects(connection)

Generator that yields all job classes.

class ll.orasql.Job

Bases: OwnedSchemaObject

Models a job (from the dbms_scheduler package) in the database.

classmethod names(connection, owner=None)

Generator that yields the names of all jobs.

classmethod objects(connection, owner=None)

Generator that yields all jobs.

class ll.orasql.OracleFileResource

Bases: 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.

• scripts – Oracle related scripts
  • oracreate – Printing a schema definition
  • oradrop – Deleting a schema definition
  • oradelete – Deleting all records
  • oragrant – Printing permissions for a schema
  • orafind – Finding records in a schema
  • oradiff – Diffing two schemas
  • oramerge – Three-way merging of schemas
  • orareindex – Recreating indexes/constraints
  • oracycles – Finding cyclic foreign keys