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
andFunction
).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
toll.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[source]
Bases:
dict
An
Args
object is a subclass ofdict
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[source]
Bases:
object
A
LOBStream
object provides streamlike access to aBLOB
orCLOB
.- 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.
- read(size=None)[source]
Read
size
bytes/characters from the stream and return them. Ifsize
isNone
all remaining data will be read.
- seek(offset, whence=0)[source]
Seek to the position
offset
in the LOB. Thewhence
argument is optional and defaults to0
(absolute file positioning); The other allowed value is1
(seek relative to the current position).
- class ll.orasql.Record[source]
-
A
Record
is a subclass oftuple
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 inself
, returndefault
instead.
- keys()[source]
Return an iterator over field names.
- items()[source]
Return an iterator over (field name, field value) tuples.
- class ll.orasql.SessionPool[source]
Bases:
SessionPool
SessionPool
is a subclass ofcx_Oracle.SessionPool
.
- ll.orasql.owned(obj, owner)[source]
Do we own the object
obj
according to the owner specificationowner
?owner
can be:
- class ll.orasql.Connection[source]
Bases:
Connection
Connection
is a subclass ofcx_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 integerIf
readlobs
isFalse
all cursor fetches returnLOBStream
objects for LOB objects. Ifreadlobs
is anint
LOBs with a maximum size ofreadlobs
will be returned asbytes
/str
objects. Ifreadlobs
isTrue
all LOB values will be returned asbytes
/str
objects.decimal
boolIf
decimal
isTrue
numbers will be returned asdecimal.Decimal
objects, elsefloat
will be used.
Furthermore the
clientinfo
will be automatically set to the name of the currently running script (except if theclientinfo
keyword argument is given andNone
).
- cursor(readlobs=None)[source]
Return a new cursor for this connection. For the meaning of
readlobs
see__init__()
.
- tables(owner=None, 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 beNone
(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)[source]
Generator that yields sequences.
owner
can beNone
(the default),ALL
or a user name.
- fks(owner=None)[source]
Generator that yields all foreign key constraints.
owner
can beNone
(the default),ALL
or a user name.
- privileges(owner=None)[source]
Generator that yields object privileges.
owner
can beNone
(the default),ALL
, a user name or a set or tuple of user names.
- synonyms(owner=None, object_owner=ll.orasql.ALL)[source]
Generator that yields synonyms.
owner
andobject_owner
can beNone
(the default),ALL
, a user name or a set or tuple of user names.
- users()[source]
Generator that yields all users.
- objects(owner=None, mode='create')[source]
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 seeowned()
.
- objects_named(name, owner=None)[source]
Return all objects named
name
from the schema. Ifowner
isNone
the current schema is queried, else the specified one is used.name
andowner
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)[source]
Return the object named
name
from the schema. Ifowner
isNone
the current schema is queried, else the specified one is used.name
andowner
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)[source]
Create a connection to the database and return a
Connection
object.
- class ll.orasql.Cursor[source]
Bases:
Cursor
A subclass of the cursor class in
cx_Oracle
. The “fetch” methods will return records asRecord
objects andLOB
values will be returned asLOBStream
objects orstr
/bytes
objects (depending on the cursorsreadlobs
attribute).- __init__(connection, readlobs=None)[source]
Return a new cursor for the connection
connection
. For the meaning ofreadlobs
seeConnection.__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.MixinNormalDates[source]
Bases:
object
Mixin class that provides methods for determining creation and modification dates for objects.
- class ll.orasql.MixinCodeSQL[source]
Bases:
object
Mixin class that provides methods returning the create and drop statements for various objects.
- class ll.orasql.SchemaObject[source]
Bases:
object
The base class for all Python classes modelling schema objects in the database.
- 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
- fixname(code)[source]
Replace the name of the object in the SQL code
code
with the name ofself
.
- exists(connection=None)[source]
Return whether the object
self
really exists in the database specified byconnection
.
- cdate(connection=None)[source]
Return a
datetime.datetime
object with the creation date ofself
in the database specified byconnection
(orNone
if that information is not available).
- udate(connection=None)[source]
Return a
datetime.datetime
object with the last modification date ofself
in the database specified byconnection
(orNone
if that information is not available).
- references(connection=None, done=None)[source]
Objects directly used by
self
.If
connection
is notNone
it will be used as the database connection from which to fetch data. Ifconnection
isNone
the connection from whichself
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
seereferences()
.done
is used internally and shouldn’t be passed.
- referencedby(connection=None)[source]
Objects using
self
.For the meaning of
connection
seereferences()
.
- referencedbyall(connection=None, done=None)[source]
All objects depending on
self
(recursively).For the meaning of
connection
seereferences()
.done
is used internally and shouldn’t be passed.
- class ll.orasql.OwnedSchemaObject[source]
Bases:
SchemaObject
The base class for all Python classes modelling schema objects in the database.
- classmethod names(connection, owner=None)[source]
Generator that yields the names of all objects of this type. The argument
owner
specifies whose objects are yielded. For more information seeowned()
.Names will be in ascending order.
- classmethod objects(connection, owner=None)[source]
Generator that yields all objects of this type in the current users schema. The argument
owner
specifies whose objects are yielded. For more information seeowned()
.
- synonyms(connection=None)[source]
Generator that yields all synonyms for this object.
- privileges(connection=None)[source]
Generator that yields all privileges on this object.
- class ll.orasql.Sequence[source]
Bases:
MixinNormalDates
,OwnedSchemaObject
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.Table[source]
Bases:
MixinNormalDates
,OwnedSchemaObject
Models a table in the database.
- mview(connection=None)[source]
The materialized view this table belongs to (or
None
if it’s a real table).
- ismview(connection=None)[source]
Is this table a materialized view?
- organization(connection=None)[source]
Return the organization of this table: either
"heap"
(for “normal” tables) or"index"
(for index organized tables).
- logging(connection=None)[source]
Return whether the table is in logging mode or not.
- compression(connection=None)[source]
Return the compression mode of the table.
(
None
,"BASIC"
or"ADVANCED"
).
- columns(connection=None)[source]
Generator that yields all column objects of this table.
- records(connection=None)[source]
Generator that yields all records of this table.
- comment(connection=None)[source]
Return the table comment
- 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.
- pk(connection=None)[source]
Return the primary key constraint for this table (or
None
if the table has no primary key constraint).
- fks(connection=None)[source]
Return the foreign key constraints for this table.
- uniques(connection=None)[source]
Return the unique constraints for this table.
- checks(connection=None)[source]
Return the unique constraints for this table.
- class ll.orasql.TableComment[source]
Bases:
OwnedSchemaObject
Models a table comment in the database.
- comment(connection=None)[source]
Return the comment text for this table.
- class ll.orasql.ColumnComment[source]
Bases:
OwnedSchemaObject
Models a column comment in the database.
- comment(connection=None)[source]
Return the comment text for this column.
- class ll.orasql.Constraint[source]
Bases:
OwnedSchemaObject
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.
- class ll.orasql.PrimaryKey[source]
Bases:
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.ForeignKey[source]
Bases:
Constraint
Models a foreign key constraint in the database.
- refconstraint(connection=None)[source]
Return the constraint referenced by
self
.In most cases this is a
PrimaryKey
, but it also might be aUniqueConstraint
.
- columns(connection=None)[source]
Return an iterator over the columns this foreign key consists of.
- class ll.orasql.UniqueConstraint[source]
Bases:
Constraint
Models a unique constraint in the database.
- class ll.orasql.CheckConstraint[source]
Bases:
Constraint
Models a check constraint in the database.
- class ll.orasql.Index[source]
Bases:
MixinNormalDates
,OwnedSchemaObject
Models an index in the database.
- rebuildsql(connection=None, term=True)[source]
Return SQL code to rebuild this index.
- 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?
- columns(connection=None)[source]
Return an iterator over the columns this index consists of.
- class ll.orasql.Synonym[source]
Bases:
OwnedSchemaObject
Models a synonym in the database.
- object(connection=None)[source]
Return the object for which
self
is a synonym.
- classmethod names(connection, owner=None, object_owner=ll.orasql.ALL)[source]
Generator that yields the names of all synonyms. For the meaning of
owner
andobject_owner
seeobjects()
.Names will be in ascending order.
- classmethod objects(connection, owner=None, object_owner=ll.orasql.ALL)[source]
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 argumentobject_owner
specifies to which owner the object must belong to to be yielded. For more information seeowned()
.
- class ll.orasql.View[source]
Bases:
MixinNormalDates
,OwnedSchemaObject
Models a view in the database.
- class ll.orasql.Library[source]
Bases:
OwnedSchemaObject
Models a library in the database.
- class ll.orasql.Argument[source]
Bases:
object
Argument
objects hold information about the arguments of a stored procedure.
- class ll.orasql.Callable[source]
Bases:
MixinNormalDates
,MixinCodeSQL
,OwnedSchemaObject
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.Procedure[source]
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)[source]
Call the procedure with arguments
args
and keyword argumentskwargs
.cursor
must be all.orasql.Cursor
object. This will return aRecord
object containing the result of the call (i.e. this record will contain all specified and all out parameters).
- class ll.orasql.Function[source]
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)[source]
Call the function with arguments
args
and keyword argumentskwargs
.cursor
must be anll.orasql.Cursor
object. This will return a tuple containing the result and aRecord
object containing the modified parameters (i.e. this record will contain all specified and out parameters).
- class ll.orasql.Package[source]
Bases:
MixinNormalDates
,MixinCodeSQL
,OwnedSchemaObject
Models a package in the database.
- class ll.orasql.PackageBody[source]
Bases:
MixinNormalDates
,MixinCodeSQL
,OwnedSchemaObject
Models a package body in the database.
- class ll.orasql.Type[source]
Bases:
MixinNormalDates
,MixinCodeSQL
,OwnedSchemaObject
Models a type definition in the database.
- class ll.orasql.TypeBody[source]
Bases:
MixinNormalDates
,MixinCodeSQL
,OwnedSchemaObject
Models a type body in the database.
- class ll.orasql.Trigger[source]
Bases:
MixinNormalDates
,MixinCodeSQL
,OwnedSchemaObject
Models a trigger in the database.
- class ll.orasql.JavaSource[source]
Bases:
MixinNormalDates
,OwnedSchemaObject
Models Java source code in the database.
- class ll.orasql.Privilege[source]
Bases:
object
Models a database object privilege (i.e. a grant).
A
Privilege
object has the following attributes:privilege
stringThe type of the privilege (
EXECUTE
etc.)name
stringThe 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
Connection
orNone
The database connection
- object(connection=None)[source]
Return the object on which
self
grants a privilege.
- classmethod objects(connection, owner=None)[source]
Generator that yields object privileges. For the meaning of
owner
seeowned()
.
- grantsql(connection=None, term=True, mapgrantee=True)[source]
Return SQL code to grant this privilege. If
mapgrantee
is a list or a dictionary andself.grantee
is not in this list (or dictionary) no command will be returned. If it’s a dictionary andself.grantee
is in it, the privilege will be granted to the user specified as the value instead of the original one. Ifmapgrantee
is true (the default) the privilege will be granted to the original grantee.
- class ll.orasql.Column[source]
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)[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?
- compression(connection=None)[source]
The compression mode for this LOB column.
Return
None
if this is not a LOB column, or it isn’t compressed.
- comment(connection=None)[source]
The comment for this column.
- class ll.orasql.User[source]
Bases:
SchemaObject
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.Preference[source]
Bases:
OwnedSchemaObject
Models a preference in the database.
- classmethod names(connection, owner=None)[source]
Generator that yields the names of all preferences.
- classmethod objects(connection, owner=None)[source]
Generator that yields all preferences.
- class ll.orasql.JobClass[source]
Bases:
SchemaObject
Models a job class (from the
dbms_scheduler
package) in the database.- classmethod names(connection)[source]
Generator that yields the names of all job classes.
- classmethod objects(connection)[source]
Generator that yields all job classes.
- class ll.orasql.Job[source]
Bases:
OwnedSchemaObject
Models a job (from the
dbms_scheduler
package) in the database.- classmethod names(connection, owner=None)[source]
Generator that yields the names of all jobs.
- classmethod objects(connection, owner=None)[source]
Generator that yields all jobs.
- class ll.orasql.OracleFileResource[source]
Bases:
Resource
An
OracleFileResource
wraps an Oracle database object (like a table, view, function, procedure etc.) in a file-like API for use withll.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