ll.orasql contains utilities for working with cx_Oracle:
It allows calling procedures and functions with keyword arguments (via the classes
ProcedureandFunction).Query results will be put into
Recordobjects, where database fields are accessible as object attributes.The
Connectionclass provides methods for iterating through the database metadata.Importing this module adds support for URLs with the scheme
oracletoll.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 SQLObjectNotFoundError(OSError):
def __init__(self, obj):
class SQLNoSuchObjectError(Exception):
def __init__(self, name, owner):
def __repr__(self):
def __str__(self):
class UnknownModeError(ValueError):
def __init__(self, mode):
def __repr__(self):
def __str__(self):
class ConflictError(ValueError):
def __init__(self, object, message):
def __repr__(self):
def __str__(self):
class Args(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.
def __init__(self, arg=None, **kwargs):
def update(self, arg=None, **kwargs):
def __getitem__(self, name):
def __setitem__(self, name, value):
def __delitem__(self, name):
def __getattr__(self, name):
def __setattr__(self, name, value):
def __delattr__(self, name):
def __repr__(self):
class LOBStream(object):
A LOBStream object provides streamlike access to a BLOB or CLOB.
def __init__(self, value):
def readall(self):
Read all remaining data from the stream and return it.
def readchunk(self):
Read a chunk of data from the stream and return it. Reading is done in optimally sized chunks.
def read(self, size=None):
Read size bytes/characters from the stream and return them.
If size is None all remaining data will be read.
def reset(self):
Reset the stream so that the next read call starts at the
beginning of the LOB.
def seek(self, 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).
def _decodelob(value, readlobs):
class RecordMaker(object):
def __init__(self, cursor):
def __call__(self, *row):
def CLOB(self, value):
def NCLOB(self, value):
def BLOB(self, value):
def DEFAULT(self, value):
class Record(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.
def __new__(cls, index2name, name2index, values):
def __getitem__(self, arg):
def __getattr__(self, name):
def get(self, name, default=None):
Return the value for the field named name. If this field doesn't
exist in self, return default instead.
def __contains__(self, name):
def keys(self):
Return an iterator over field names.
def items(self):
Return an iterator over (field name, field value) tuples.
def __repr__(self):
class SessionPool(OracleSessionPool):
SessionPool is a subclass of cx_Oracle.SessionPool.
def __init__(self, user, password, database, min, max, increment, connectiontype=None, threaded=False, getmode=1, homogeneous=True):
def connectstring(self):
def __repr__(self):
class Connection(cx_Oracle.Connection):
Connection is a subclass of cx_Oracle.Connection.
def __init__(self, *args, **kwargs):
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
readlobsisFalseall cursor fetches returnLOBStreamobjects for LOB object. Ifreadlobsis anintLOBs with a maximum size ofreadlobswill be returned asbytes/strobjects. IfreadlobsisTrueall LOB values will be returned asbytes/strobjects.
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).
def connectstring(self):
def cursor(self, readlobs=None):
Return a new cursor for this connection. For the meaning of
readlobs see __init__.
def __repr__(self):
def tables(self, owner=ll.orasql.ALL, 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.
def sequences(self, owner=ll.orasql.ALL):
Generator that yields sequences. owner can be None,
ALL (the default) or a user name.
def fks(self, owner=ll.orasql.ALL):
Generator that yields all foreign key constraints. owner can be
None, ALL (the default) or a user name.
def privileges(self, owner=ll.orasql.ALL):
Generator that yields object privileges. owner can be None,
ALL (the default) or a user name.
def users(self):
Generator that yields all users.
def objects(self, owner=ll.orasql.ALL, mode='create'):
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:
NoneAll objects belonging to the current user (i.e. via the view
USER_OBJECTS);ALLAll objects for all users (via the views
ALL_OBJECTSorDBA_OBJECTS);- username (string)
All objects belonging to the specified user
def _getobject(self, name, owner=None):
def getobject(self, 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.
class Date(object):
date(year, month, day) --> date object
def connect(*args, **kwargs):
Create a connection to the database and return a Connection object.
class Cursor(OracleCursor):
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).
def __init__(self, connection, readlobs=None):
Return a new cursor for the connection connection. For the meaning
of readlobs see Connection.__init__.
def ddprefix(self):
Return whether the user has access to the DBA_* views ("dba") or
not ("all").
def ddprefixargs(self):
Return whether the user has access to the DBA_ARGUMENTS view
("dba") or not ("all").
def execute(self, statement, parameters=None, **kwargs):
def executemany(self, statement, parameters):
def __repr__(self):
def formatstring(value, latin1=False):
def makeurl(name):
class MixinNormalDates(object):
Mixin class that provides methods for determining creation and modification dates for objects.
def cdate(self, connection=None):
def udate(self, connection=None):
class MixinCodeSQL(object):
Mixin class that provides methods returning the create and drop statements for various objects.
def exists(self, connection=None, term=True):
def createsql(self, connection=None, term=True):
def dropsql(self, connection=None, term=True):
def fixname(self, code):
def getfullname(name, owner):
class _Object_meta(type):
def __new__(mcl, name, bases, dict):
class Object(object):
The base class for all Python classes modelling schema objects in the database.
def __init__(self, name, owner=None, connection=None):
def __repr__(self):
def __str__(self):
def __eq__(self, other):
def __ne__(self, other):
def __hash__(self):
def getfullname(self):
def createsql(self, *args, **kwargs):
Return SQL code to create this object.
def dropsql(self, *args, **kwargs):
Return SQL code to drop this object
def fixname(self, *args, **kwargs):
Replace the name of the object in the SQL code code with
the name of self.
def exists(self, *args, **kwargs):
Return wether the object self really exists in the database
specified by connection.
def cdate(self, *args, **kwargs):
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).
def udate(self, *args, **kwargs):
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).
def references(self, connection=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.
def referencesall(self, 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.
def referencedby(self, connection=None):
Objects using self.
For the meaning of connection see references.
def referencedbyall(self, 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.
def getconnection(self, connection):
def getcursor(self, connection):
property connectstring:
def __get__(self):
def getconnectstring(self):
class Sequence(MixinNormalDates, Object):
Models a sequence in the database.
def _createsql(self, connection, term, copyvalue):
def exists(self, connection=None):
def createsql(self, connection=None, term=True):
def createsqlcopy(self, connection=None, term=True):
Return SQL code to create an identical copy of this sequence.
def dropsql(self, connection=None, term=True):
def fixname(self, code):
def references(self, connection=None, done=None):
def _columntype(rec, data_precision=None, data_scale=None, char_length=None):
class Timestamp(date):
datetime(year, month, day[, hour[, minute[, second[, microsecond[,tzinfo]]]]])
The year, month and day arguments are required. tzinfo may be None, or an instance of a tzinfo subclass. The remaining arguments may be ints or longs.
def _columndefault(rec):
class Table(MixinNormalDates, Object):
Models a table in the database.
def createsql(self, connection=None, term=True):
def exists(self, connection=None):
def dropsql(self, connection=None, term=True):
def fixname(self, code):
def mview(self, connection=None):
The materialized view this table belongs to (or None if it's a
real table).
def ismview(self, connection=None):
Is this table a materialized view?
def organization(self, connection=None):
Return the organization of this table: either "heap" (for "normal"
tables) or "index" (for index organized tables).
def columns(self, connection=None):
Generator that yields all column objects of this table.
def records(self, connection=None):
Generator that yields all records of this table.
def comments(self, connection=None):
Generator that yields all column comments of this table.
def _iterconstraints(self, connection, cond):
def constraints(self, connection=None):
Generator that yields all constraints for this table.
def pk(self, connection=None):
Return the primary key constraint for this table (or None if the
table has no primary key constraint).
def references(self, connection=None):
def referencedby(self, connection=None):
class Comment(Object):
Models a column comment in the database.
def exists(self, connection=None):
def comment(self, connection=None):
Return the comment text for this column.
def createsql(self, connection=None, term=True):
def dropsql(self, connection=None, term=True):
def fixname(self, code):
def cdate(self, connection=None):
def udate(self, connection=None):
def references(self, connection=None):
def referencedby(self, connection=None):
class Constraint(Object):
Base class of all constraints (primary key constraints, foreign key constraints, unique constraints and check constraints).
def exists(self, connection=None):
def cdate(self, connection=None):
def udate(self, connection=None):
def _sql(self, connection, term, command):
def dropsql(self, connection=None, term=True):
def enablesql(self, connection=None, term=True):
def disablesql(self, connection=None, term=True):
def isenabled(self, connection=None):
Return whether this constraint is enabled.
def fixname(self, code):
def table(self, connection=None):
Return the Table self belongs to.
class PrimaryKey(Constraint):
Models a primary key constraint in the database.
def columns(self, connection=None):
Return an iterator over the columns this primary key consists of.
def createsql(self, connection=None, term=True):
def referencedby(self, connection=None):
def references(self, connection=None):
class ForeignKey(Constraint):
Models a foreign key constraint in the database.
def createsql(self, connection=None, term=True):
def referencedby(self, connection=None):
def references(self, connection=None):
def pk(self, connection=None):
Return the primary key referenced by self.
def columns(self, connection=None):
Return an iterator over the columns this foreign key consists of.
class UniqueConstraint(Constraint):
Models a unique constraint in the database.
def createsql(self, connection=None, term=True):
def referencedby(self, connection=None):
def references(self, connection=None):
class CheckConstraint(Constraint):
Models a check constraint in the database.
def createsql(self, connection=None, term=True):
def referencedby(self, connection=None):
def references(self, connection=None):
class Index(MixinNormalDates, Object):
Models an index in the database.
def exists(self, connection=None):
def createsql(self, connection=None, term=True):
def dropsql(self, connection=None, term=True):
def rebuildsql(self, connection=None, term=True):
Return SQL code to rebuild this index.
def fixname(self, code):
def constraint(self, connection=None):
If this index is generated by a constraint, return the constraint
otherwise return None.
def isconstraint(self, connection=None):
Is this index generated by a constraint?
def references(self, connection=None):
def table(self, connection=None):
Return the Table self belongs to.
def columns(self, connection=None):
Return an iterator over the columns this index consists of.
class Synonym(Object):
Models a synonym in the database.
def exists(self, connection=None):
def createsql(self, connection=None, term=True):
def dropsql(self, connection=None, term=True):
def fixname(self, code):
def cdate(self, connection=None):
def udate(self, connection=None):
def references(self, connection=None, done=None):
def getobject(self, connection=None):
Get the object for which self is a synonym.
class View(MixinNormalDates, Object):
Models a view in the database.
def exists(self, connection=None):
def createsql(self, connection=None, term=True):
def dropsql(self, connection=None, term=True):
def fixname(self, code):
def records(self, connection=None):
class MaterializedView(View):
Models a meterialized view in the database.
def exists(self, connection=None):
def createsql(self, connection=None, term=True):
def dropsql(self, connection=None, term=True):
def fixname(self, code):
def references(self, connection=None):
def referencedby(self, connection=None):
class Library(Object):
Models a library in the database.
def exists(self, connection=None):
def createsql(self, connection=None, term=True):
def dropsql(self, connection=None, term=True):
def fixname(self, code):
class Argument(object):
Argument objects hold information about the arguments of a
stored procedure.
def __init__(self, name, position, datatype, isin, isout):
def __repr__(self):
class Callable(MixinNormalDates, MixinCodeSQL, Object):
Models a callable object in the database, i.e. functions and procedures.
def __init__(self, name, owner=None, connection=None):
def _calcargs(self, cursor):
def _getargs(self, cursor, *args, **kwargs):
def _wraparg(self, cursor, arginfo, arg):
def _unwraparg(self, arginfo, cursor, value):
def _makerecord(self, cursor, args):
def arguments(self, connection=None):
Generator that yields all arguments of the function/procedure self.
class Procedure(Callable):
Models a procedure in the database. A Procedure object can be
used as a wrapper for calling the procedure with keyword arguments.
def __call__(self, cursor, *args, **kwargs):
Call the procedure with arguments args and keyword arguments
kwargs. cursor must be a ll.orasql cursor. 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 Function(Callable):
Models a function in the database. A Function object can be
used as a wrapper for calling the function with keyword arguments.
def __call__(self, cursor, *args, **kwargs):
Call the function with arguments args and keyword arguments
kwargs. cursor must be an ll.orasql cursor.
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 Package(MixinNormalDates, MixinCodeSQL, Object):
Models a package in the database.
class PackageBody(MixinNormalDates, MixinCodeSQL, Object):
Models a package body in the database.
class Type(MixinNormalDates, MixinCodeSQL, Object):
Models a type definition in the database.
class TypeBody(MixinNormalDates, MixinCodeSQL, Object):
Models a type body in the database.
class Trigger(MixinNormalDates, MixinCodeSQL, Object):
Models a trigger in the database.
class JavaSource(MixinNormalDates, Object):
Models Java source code in the database.
def exists(self, connection=None):
def createsql(self, connection=None, term=True):
def dropsql(self, connection=None, term=True):
def fixname(self, code):
class Privilege(object):
Models a database object privilege (i.e. a grant).
A Privilege object has the following attributes:
privilege(string)The type of the privilege (
EXECUTEetc.)name(string)The name of the object for which this privilege grants access
owner(string orNone)the owner of the object
grantor(string orNone)Who granted this privilege?
grantee(string orNone)To whom has this privilege been granted?
connection(ConnectionorNone)The database connection
def __init__(self, privilege, name, grantor, grantee, owner=None, connection=None):
def __repr__(self):
def __str__(self):
def getconnection(self, connection):
def getcursor(self, connection):
property connectstring:
def __get__(self):
def getconnectstring(self):
def grantsql(self, 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 Column(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.
def exists(self, connection=None):
def _getcolumnrecord(self, cursor):
def addsql(self, connection=None, term=True):
def modifysql(self, connection, cursorold, cursornew, term=True):
def dropsql(self, connection=None, term=True):
def table(self):
def cdate(self, connection=None):
def udate(self, connection=None):
def references(self, connection=None):
def referencedby(self, connection=None):
def datatype(self, connection=None):
The SQL type of this column.
def default(self, connection=None):
The SQL default value for this column.
def nullable(self, connection=None):
Is this column nullable?
def comment(self, connection=None):
The comment for this column.
class User(object):
Models a user in the database.
def __init__(self, name, connection=None):
def __repr__(self):
def __str__(self):
def __eq__(self, other):
def __ne__(self, other):
def __hash__(self):
def getconnection(self, connection):
def getcursor(self, connection):
property connectstring:
def __get__(self):
def getconnectstring(self):
def exists(self, connection=None):
class Preference(Object):
Models a preference in the database.
def exists(self, connection=None):
def createsql(self, connection=None, term=True):
def dropsql(self, connection=None, term=True):
def cdate(self, connection=None):
def udate(self, connection=None):
def referencedby(self, connection=None):
def references(self, connection=None, done=None):
class OracleURLConnection(ll.url.Connection):
def __init__(self, context, connection, mode):
def open(self, url, mode='rb', encoding='utf-8', errors='strict'):
def close(self):
def _type(self, url):
def _infofromurl(self, url):
def _objectfromurl(self, url):
def isdir(self, url):
def isfile(self, url):
def mimetype(self, url):
def owner(self, url):
def exists(self, url):
def cdate(self, url):
def mdate(self, url):
def _walk(self, cursor, url):
def walk(self, url, beforedir=True, afterdir=False, file=True, enterdir=True):
def __repr__(self):
class OracleFileResource(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.