ll.pysql – Database import script

Overview

The module/script pysql can be used to import data into one or more Oracle or Postgres databases. It reads pysql files which are an extension of normal Oracle or Postgres SQL files.

A PySQL file can contain different types of commands.

SQL commands

A PySQL file may contain normal SQL commands. For the pysql script to be able to execute these commands they must be terminated with a comment line -- @@@. pysql will prepare the command for execution and execute it. Any exception that is raised as a result of executing the command will stop the script and be reported. This is in contrast to how Oracle’s sqlplus executes SQL commands. sqlplus would continue after an error and exit with status code 0 even if there were errors. (For Oracle pysql can also explicitely ignore any exception raised by commands by specifying a different exception handling mode.)

A PySQL file that only contains SQL commands is still a valid Oracle or Postgres SQL file, so it still can be executed via sqlplus or psql.

Literal Python blocks

A literal Python block starts with a line that only contains #>>> and ends with a line that only contains #<<<. Python code within the block gets executed when the block is encountered. The following objects are available within the block as global variables:

connection

The active database connection (or None if there is no active database connection).

DB_TYPE_CLOB

cx_Oracle.DB_TYPE_CLOB, i.e. cx_Oracles type for CLOB parameters;

DB_TYPE_NCLOB

cx_Oracle.DB_TYPE_NCLOB, i.e. cx_Oracles type for NCLOB parameters;

DB_TYPE_BLOB

cx_Oracle.DB_TYPE_BLOB, i.e. cx_Oracles type for BLOB parameters;

sqlexpr

Can be used to specify that an argument for a procedure should be an SQL expression instead of a Python value or a var object;

datetime

Python’s datetime module;

Furthermore all PySQL commands (see below) are available.

Variables that get set within a literal Python block will be available (and retain their value) in subsequent literal Python blocks or other PySQL commands.

PySQL commands

A PySQL file may also contain PySQL commands. A PySQL command looks and behaves like a Python function call. This function call must either be contained in a single line (i.e. start with name( and end with ) or it must start with a line that only contains name( and end at a line that only contains ). (name must be the name of a PySQL command).

The following commands are available:

include

Include another PySQL file;

connect

Connect to a database;

disconnect

Disconnect from the active database connection;

procedure

Call a procedure in the database (and handle OUT parameters via var objects);

sql

Execute an SQL statement in the database (and handle OUT parameter via var objects);

literalsql

Execute an SQL statement in the database (this is what SQL commands get converted to);

commit

Commit the transaction in the active database connection;

rollback

Roll back the transaction in the active database connection;

literalpy

Execute Python code (this is what literal Python blocks get converted to);

setvar

Set a variable;

unsetvar

Delete a variable;

raise_exceptions

Set the exception handling mode;

push_raise_exceptions

Temporarily modifies the exception handling mode;

pop_raise_exceptions

Reverts to the previously active exception handling mode;

check_errors

Checks whether there are invalid database objects;

scp

Create a file on a remote host via scp;

file

Create a file on the local machine;

reset_sequence

Resets a database sequence to the maximum value of a field in a table;

user_exists

Test whether a database user exists;

schema_exists

Tests whether a database schema exists (which is the same as a user for Oracle);

object_exists

Test whether a database object (table, package, procedure, etc.) exists;

constraint_exists

Test whether a database constraint (primary key, foriegn key, unique or check constraint) exists;

drop_types

Drop all database objects of a certain type;

comment

A comment;

loadbytes

Load the binary content of a file;

loadstr

Load the text content of a file;

var

Mark an argument for a procedure or sql command as being an OUT parameter (or pass the value of the variable in subsequent procedure/sql commands);

env

Return the value of an environment variable.

Comments

A line starting with # (outside of a SQL command or literal Python block) is considered a comment and will be ignored.

Example

The following is a complete PySQL file that will create a sequence, table and procedure and will call the procedure to insert data into the table:

create sequence person_seq
   increment by 10
   start with 10
   maxvalue 1.0e28
   minvalue 10
   nocycle
   cache 20
   noorder
;

-- @@@

create sequence contact_seq
   increment by 10
   start with 10
   maxvalue 1.0e28
   minvalue 10
   nocycle
   cache 20
   noorder
;

-- @@@

create table person
(
   per_id integer not null,
   per_firstname varchar2(200),
   per_lastname varchar2(200)
);

-- @@@

alter table person add constraint person_pk primary key(per_id);

-- @@@

create table contact
(
   con_id integer not null,
   per_id integer not null,
   con_type varchar2(200),
   con_value varchar2(200)
);

-- @@@

alter table contact add constraint contact_pk primary key(con_id);

-- @@@

create or replace procedure person_insert
(
   c_user in varchar2,
   p_per_id in out integer,
   p_per_firstname in varchar2 := null,
   p_per_lastname in varchar2 := null
)
as
begin
   if p_per_id is null then
      select person_seq.nextval into p_per_id from dual;
   end if;

   insert into person
   (
      per_id,
      per_firstname,
      per_lastname
   )
   values
   (
      p_per_id,
      p_per_firstname,
      p_per_lastname
   );
end;
/

-- @@@

create or replace procedure contact_insert
(
   c_user in varchar2,
   p_con_id in out integer,
   p_per_id in integer := null,
   p_con_type in varchar2 := null,
   p_con_value in varchar2 := null
)
as
begin
   if p_con_id is null then
      select contact_seq.nextval into p_con_id from dual;
   end if;

   insert into contact
   (
      con_id,
      per_id,
      con_type,
      con_value
   )
   values
   (
      p_con_id,
      p_per_id,
      p_con_type,
      p_con_value
   );
end;
/

-- @@@

# import data

procedure(
   'person_insert',
   args=dict(
      c_user='import',
      p_per_id=var('per_id_max'),
      p_per_firstname='Max',
      p_per_lastname='Mustermann',
   )
)

procedure(
   'contact_insert',
   args=dict(
      c_user='import',
      p_per_id=var('per_id_max'),
      p_con_id=var('con_id_max'),
      p_con_type='email',
      p_con_value='max@example.org',
   )
)

file(
   'portrait_{per_id_max}.png',
   b'\x89PNG\r\n\x1a\n...',
)

reset_sequence(
   'person_seq',
   table='person',
   field='per_id',
}

check_errors()

This file can then be imported into an Oracle database with the following command:

python -m ll.pysql -d user/pwd@database data.pysql

This will create two sequences, two tables and two procedures. Then it will import two records, one by calling person_insert and one by calling contact_insert. The PL/SQL equivalent of procedure calls is:

declare
   v_per_id_max integer;
   v_con_id_max integer;
begin
   person_insert(
      per_id=v_per_id_max,
      per_firstname='Max',
      per_lastname='Mustermann'
   );
   contact_insert(
      con_id=v_con_id_max,
      per_id=v_per_id_max,
      con_type='email',
      con_value='max@example.org'
   )
end;

Furthermore it will create one file (named something like portrait_42.png) and reset the sequence person_seq to the maximum value of the field per_id in the table person. Finally it will make sure that no errors exist in the schema.

Multiple database connections

PySQL can handle multiple database connections. New database connections can be opened with the connect command. This command opens a new database connection. Subsequent commands that talk to the database will use this connection until a disconnect command disconnects from the database and reverts to the previous connection (or None if this was the outermost open database connection). An example looks like this:

connect("oracle:user/pwd@db")
procedure("test")
disconnect()

for Oracle or like this:

connect("postgres:host=localhost dbname=db user=me password=secret")
procedure("test")
disconnect()

for Postgres.

Variables

Variable objects can be used to receive OUT parameters of procedure calls or SQL statements. A variable object can be specified like this: var("foo"). "foo" is the “name” of the variable. When a variable object is passed to a procedure the first time (i.e. the variable object is uninitialized), the resulting value after the call will be stored under the name of the variable. When the variable is used in a later command the stored value will be used instead. (Note that it’s not possible to use the same variable twice in the same procedure call, if it hasn’t been used before, however in later commands this is no problem).

The type of the variable defaults to int, but a different type can be passed when creating the object by passing the Python type like this: var("foo", str).

It is also possible to create variable objects via command line parameters.

As a PySQL command is a Python literal, it is possible to use Python expressions inside a PySQL command.

External files

Inside a PySQL command it is possible to load values from external files. The loadbytes command loads a bytes object from an external file like this:

loadbytes("path/to/file.png")

A str object can be loaded with the loadstr command like this:

loadstr("path/to/file.txt", encoding="utf-8", errors="replace")

The second and third argument are the encoding and error handling name respectively.

The filename is treated as being relative to the file containing the loadbytes or loadstr call.

This file content can then be used in other PySQL commands (e.g. as parameters in procedure commands, or as file content in scp or file commands).

Command line usage

pysql.py has no external dependencies except for cx_Oracle (for Oracle) or psycopg (for Postgres) and can be used as a script for importing a PySQL file into the database (However some commands require ll.orasql for an Oracle database). As a script it supports the following command line options:

file

The name of one or more PySQL files that will be read and imported. If no filename is given, commands are read from stdin.

-v, --verbose

Gives different levels of output while data is being imported to the database. The default is no output (unless an exception occurs). Possible modes are: dot (one dot for each command), type (each command type), file (the file names and line numbers from which code gets executed), log (the log messages output by the commands) or full (source code that will be executed and the log messages output by the commands).

-d, --database

The initial database connection that will be used before any additional connect commands.

For Postgres the value must start with postgres: the rest of the value will be passed to psycopg.Connection.connect() as a positional argument. For example:

postgres:host=localhost dbname=test user=me password=secret

For Oracle the value may start with oracle:. The rest can be a standard Oracle connectstring. For example:

me/secret@database
-z, --summary

Give a summary of the number of commands executed and procedures called.

-r, --rollback

Specifies that transactions should be rolled back at the end of the script run, or when a disconnect command disconnects from a database. The default is to commit at the end or on each disconnect. (But note that for Oracle when a DDL statement is in the script, Oracle will still implicitely commit everything up to the statement.)

-s, --scpdirectory

The base directory for scp file copy commands. As files are copied via scp this can be a remote filename (like root@www.example.org:~/uploads/) and must include a trailing /.

If it is a local directory it should be absolute (otherwise PySQL scripts included from other directories won’t work).

-f, --filedirectory

The base directory for file file save commands. It must include a trailing /.

--tabsize

The tab size when PySQL source is printed in full mode.

--context

The number of lines at the start and end of the source code of a block to print in full mode. The default is to print the complete source code.

-D, --define

Can be used multiple times to define variables. Supported formats are:

name

Defines a string variable named name and sets the value to the empty string.

name=value

Defines a string variable named name and sets the value to value.

name:type

Defines a variable named name of type type and sets the value to False, 0, 0.0 or the empty string depending on the type. Supported types are str, bool, int and float.

name:type=value

Defines a variable named name of type type and sets the value to value. For type bool supported values are 0, no, false, False, 1, yes, true and True.

ll.pysql.format_class(obj)[source]

Return the name for the class obj.

ll.pysql.shortrepr(value)[source]

Return a short “repr” output for a str or bytes value.

If the value value is sort enough, the normal repr() output will be returned, otherwise an abbreviated value will be returned, i.e. something like this:

<'foobarbaz' ... (1234 characters)>
class ll.pysql.Handler[source]

Bases: object

A Handler object is responsible for executing PySQL commands.

Handler can not execute commands that require a database connection. That is the job of the subclasses OracleHandler, OraSQLHandler and PostgresHandler.

static from_connectstring(connectstring, mode=None)[source]

Create an appropriate Handler from a connectstring.

If connectstring is None, a Handler object will be returned.

If connectstring starts with postgres:, a PostgresHandler will be returned.

Otherwise on OracleHandler will be returned.

static from_command(command)[source]

Create an appropriate Handler from connect command.

connectstring()[source]

Return a string identifying the database connection for this handler.

connect(context, command)[source]

Execute the connect command command.

include(context, command)[source]

Execute the include command command.

user_exists(context, command)[source]

Execute the user_exists command command.

schema_exists(context, command)[source]

Execute the schema_exists command command.

object_exists(context, command)[source]

Execute the object_exists command command.

constraint_exists(context, command)[source]

Execute the constraint_exists command command.

check_errors(context, command)[source]

Execute the check_errors command command.

literalsql(context, command)[source]

Execute the literalsql command command.

literalpy(context, command)[source]

Execute the literalpy command command.

procedure(context, command)[source]

Execute the procedure command command.

sql(context, command)[source]

Execute the sql command command.

reset_sequence(context, command)[source]

Execute the reset_sequence command command.

drop_types(context, command)[source]

Execute the drop_types command command.

rollback(context, command)[source]

Execute the rollback command command.

commit(context, command)[source]

Execute the commit command command.

disconnect(context, command)[source]

Execute the disconnect command command.

setvar(context, command)[source]

Execute the setvar command command.

unsetvar(context, command)[source]

Execute the unsetvar command command.

raise_exceptions(context, command)[source]

Execute the raise_exceptions command command.

push_raise_exceptions(context, command)[source]

Execute the push_raise_exceptions command command.

pop_raise_exceptions(context, command)[source]

Execute the pop_raise_exceptions command command.

scp(context, command)[source]

Execute the scp command command.

file(context, command)[source]

Execute the file command command.

comment(context, command)[source]

Execute the comment command command.

loadbytes(context, command)[source]

Execute the loadbytes command command.

loadstr(context, command)[source]

Execute the loadstr command command.

var(context, command)[source]

Execute the var command command.

env(context, command)[source]

Execute the env command command.

log(context, command)[source]

Execute the log command command.

class ll.pysql.DBHandler[source]

Bases: Handler

Subclass of Handler that has a real database connection.

class ll.pysql.OracleHandler[source]

Bases: DBHandler

Subclass of DBHandler that executes database commands via cx_Oracle.

However drop_types is not supported, for this OraSQLHandler is required, which requires that ll.orasql is available).

class ll.pysql.OraSQLHandler[source]

Bases: OracleHandler

Subclass of DBHandler that executes database commands via ll.orasql.

class ll.pysql.PostgresHandler[source]

Bases: DBHandler

Subclass of DBHandler that executes database commands for Postgres.

class ll.pysql.Command[source]

Bases: object

The base class of all commands. A Command object is created from a function call in a PySQL file and then immediatly the method execute() will be called to execute the command.

The only parameters in the call that is supported by all commands are the following:

raise_exceptionsbool (optional)

Specifies whether exceptions that happen during the execution of the command should be reported and terminate the script (True), or should be ignored (False). None (the default) uses the global configuration.

condbool (optional)

Specifies whether this command should be executed or not. If cond is True (the default), the command will be executed, else it won’t.

ll.pysql.register(cls)[source]

Register a Command subclass as a PySQL command.

This is used as a class decorator.

class ll.pysql.include[source]

Bases: Command

The include command includes another PySQL file. The filename is passed in the first parameter filename. This filename is interpreted as being relative to the directory with the file containing the include command.

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.connect[source]

Bases: Command

The connect command connects to the database given in the connectstring in the parameter connectstring. After the connect command until the matching disconnect command, all commands that talk to the database will use this connection. After a disconnect command pysql will revert back to the previously active database connection. Parameter have the following meaning:

modestring or None (optional)

The connection mode: This can be either 'sysdba' or None (the default).

retryint (optional)

The number of times PySQL tries to get a database connection.

retrydelayint (optional)

The number of seconds to wait between connection tries.

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.disconnect[source]

Bases: Command

The disconnect command disconnects from the active database connection and reverts back to the previously active database connection.

commit specifies whether the transaction should be committed. If commit is None, the default commit mode is used (which can be changed on the command line via the -r/--rollback option).

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.procedure[source]

Bases: _SQLCommand

A procedure command calls an Oracle procedure in the database. The following parameters are supported:

namestring (required)

The name of the procedure to be called (This may include . for calling a procedure in a package or one owned by a different user).

argsdictionary (optional)

A dictionary with the names of the parameters as keys and the parameter values as values. PySQL supports all types as values that cx_Oracle supports. In addition to those, three special classes are supported:

  • sqlexpr objects can be used to specify that the paramater should be literal SQL. So e.g. sqlexpr("sysdate") will be the date when the PySQL script was executed.

  • var objects can be used to hold values that are OUT parameters of the procedure. For example on first use of var("foo_10") the value of the OUT parameter will be stored under the key "foo_10". The next time var("foo_10") is encountered the value stored under the key "foo_10" will be passed to the procedure. The type of the variable defaults to int. If a different type is required it can be passed as the second argument to var, e.g. var("foo_10", str).

  • Finally all other commands can be called to get a value (for example the two commands loadbytes and loadstr to load values from external files (as long as they are of type bytes or str). loadbytes("foo/bar.txt") will return with the content of the external file foo/bar.txt (as a bytes object). If a str object is required, loadstr can be used. Encoding info can be passed like this:

    loadstr("foo/bar.txt", encoding="utf-8", errors="replace")
    
argtypesdictionary (optional)

A dictionary with the names of the parameters as keys and Postgres datatypes as the values. This is used for adding a cast to the parameter value in the call to guide Postgres to find the correct overloaded version of the procedure. For Oracle argtypes will be ignored.

For the rest of the parameters see the base class _DatabaseCommand.

class ll.pysql.sql[source]

Bases: _SQLCommand

An sql command directly executes an SQL statement in the Oracle database. The following parameters are supported:

sqlstring (required)

The SQL to be executed. This may contain parameters in the form of :paramname. The values for those parameters will be taken from args.

argsdictionary (optional)

A dictionary with the names of the parameters as keys and the parameter values as values. Similar to procedure calls var, loadbytes and loadstr objects are supported. However sqlexpr objects are not supported (they will be ignored).

argtypesdictionary (optional)

A dictionary with the names of the parameters as keys and Postgres datatypes as the values. This is used for adding a cast to the parameter value in the call to try to convert the value to the proper Postgres datatype. For Oracle argtypes will be ignored.

For the rest of the parameters see the base class _DatabaseCommand.

If you have arguments you can reference them in Oracle code like this:

sql(
   "insert into foo (bar) values (:bar)",
   args=dict(
      bar="bar",
   )
)

or (if you want to use a variable) like this:

sql(
   "insert into foo (bar) values (:bar)",
   args=dict(
      bar=var("bar", str),
   )
)

Or like this when you wnat to get a value out from an SQL command:

sql(
   "begin; :now := to_char(sysdate); end;",
   args=dict(
      now=var("now", date),
   )
)

For Postgres you must reference parameters in the query like this:

sql(
   "insert into foo (bar) values (%(bar)s)",
   args=dict(
      bar="bar",
   )
)

or (if you want to use a variable) like this:

sql(
   "insert into foo (bar) values (%(bar)s)",
   args=dict(
      bar=var("bar", str),
   )
)

However to get variables out of a Postgres SQL statement you must use a select:

sql(
   "select baz as baz from foo where bar = %(bar)s",
   args=dict(
      bar=var("bar", str),
   )
)

Specify the target variable name via the output name of the field expressions.

In the about example a variable baz will be set.

class ll.pysql.literalsql[source]

Bases: _SQLCommand

A literalsql is used for SQL that appears literally in the PySQL file. Apart from the sql attribute it supports no further parameters.

class ll.pysql.commit[source]

Bases: _SQLCommand

A commit command commits the current transaction in the activate database connection (or the one specified via the connection parameter).

For the rest of the parameters see the base class _DatabaseCommand.

class ll.pysql.rollback[source]

Bases: _SQLCommand

A rollback command rolls back the current transaction in the activate database connection (or the one specified via the connection parameter).

For the rest of the parameters see the base class _DatabaseCommand.

class ll.pysql.literalpy[source]

Bases: _DatabaseCommand

A literalpy is used for Python code that appears literally in the PySQL file. Apart from the code attribute it supports the no further parameters.

class ll.pysql.setvar[source]

Bases: Command

The setvar command sets a variable to a fixed value. The following parameters are supported:

namestring (required)

The name of the variable to set.

valueobject (required)

The value of the variable.

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.unsetvar[source]

Bases: Command

The unsetvar command deletes a variable. The parameter name must be given and must contain the name of the variable.

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.raise_exceptions[source]

Bases: Command

The raise_exceptions command changes the global error reporting mode for all subsequent commands. After:

raise_exceptions(False)

for all subsequent commands any exception will be ignored and reported and command execution will continue with the next command.

raise_exceptions(True)

will switch back to aborting the execution of the PySQL script once an exception is encountered.

Note that the global configuration will only be relevant for commands that don’t specify the raise_exceptions parameter themselves.

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.push_raise_exceptions[source]

Bases: Command

The push_raise_exceptions command changes the global error reporting mode for all subsequent commands, but remembers the previous exception handling mode. After:

push_raise_exceptions(False)

for all subsequent commands any exception will be ignored and reported and command execution will continue with the next command. It is possible to switch back to the previous exception handling mode via:

pop_raise_exceptions()

Note that this global configuration will only be relevant for commands that don’t specify the raise_exceptions parameter themselves.

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.pop_raise_exceptions[source]

Bases: Command

The pop_raise_exceptions command restores the previously active exception handling mode (i.e. the one active before the last push_raise_exceptions command).

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.check_errors[source]

Bases: _DatabaseCommand

The check_errors command checks that there are no compilation errors in the active database schema. If there are, an exception will be raised.

For the rest of the parameters see the base class _DatabaseCommand (but the value of the raise_exceptions key will be ignored).

class ll.pysql.scp[source]

Bases: Command

The scp command creates a file by copying it via the scp program. The following parameters are supported:

namestring (required)

The name of the file to be created. It may contain format() style specifications containing any variable (for example those that appeared in a procedure or sql command). These specifiers will be replaced by the correct variable values. As these files will be copied via the scp program, ssh file names can be used.

contentbytes (required)

The content of the file to be created. This can also be a loadbytes command to load the content from an external file.

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.file[source]

Bases: Command

The file command creates a file by directly saving it from Python. The following parameters are supported:

namestring (required)

The name of the file to be created. It may contain format() style specifications containing any variable (for example those that appeared in a procedure or sql command). These specifiers will be replaced by the correct variable values.

contentbytes (required)

The content of the file to be created. This can also be a loadbytes command to load the content from an external file.

modeinteger (optional)

The file mode for the new file. If the mode is specified, os.chmod() will be called on the file.

ownerinteger or string (optional)

The owner of the file (as a user name or a uid).

groupinteger or string (optional)

The owning group of the file (as a group name or a gid). If owner or group is given, os.chown() will be called on the file.

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.reset_sequence[source]

Bases: _DatabaseCommand

The reset_sequence command resets a sequence in the database to the maximum value of a field in a table. The following parameters are supported:

sequencestring (required)

The name of the sequence to reset.

tablestring (required)

The name of the table that contains the field.

fieldstring (required)

The name of the field in the table table. The sequence will be reset to a value so that fetching the next value from the sequence will deliver a value that is larger than the maximum value of the field field in the table table.

minvalueinteger (optional, default taken from sequence)

The minimum value for the sequence.

incrementinteger (optional, default taken from sequence)

The increment (i.e. the step size) for the sequence.

For the rest of the parameters see the base class _DatabaseCommand.

class ll.pysql.user_exists[source]

Bases: _DatabaseCommand

The user_exists command returns whether a user with a specified name exists in the database. It supports the following parameters:

namestring (required)

The name of the user to be checked for existence.

For the rest of the parameters see the base class _DatabaseCommand.

class ll.pysql.schema_exists[source]

Bases: _DatabaseCommand

The schema_exists command returns whether a schema with a specified name exists in the database. It supports the following parameters:

name: string (required)

The name of the schema to be checked for existence.

For the rest of the parameters see the base class _DatabaseCommand.

class ll.pysql.object_exists[source]

Bases: _DatabaseCommand

The object_exists command returns whether an object with a specified name exists in the database. It supports the following parameters:

namestring (required)

The name of the object to be checked for existence.

ownerstring (optional)

The owner of the object (defaults to the current user if not specified or None).

For the rest of the parameters see the base class _DatabaseCommand.

Note that object_exists won’t test for constraints. For this use constraint_exists.

class ll.pysql.constraint_exists[source]

Bases: _DatabaseCommand

The constraint_exists command returns whether a constraint (i.e. a primary key, foreign key, unique or check constraint) with a specified name exists in the database. It supports the following parameters:

namestring (required)

The name of the object to be checked for existence.

ownerstring (optional)

The owner of the constraint (defaults to the current user if not specified or None).

For the rest of the parameters see the base class _DatabaseCommand.

class ll.pysql.drop_types[source]

Bases: _DatabaseCommand

The drop_types command drops database objects.

Unlike all other commands this command requires the ll.orasql module.

drop_types supports the following parameters:

droplist of strings (optional)

The types of objects to drop (value must be names for ll.orasql object types.

keeplist of strings (optional)

The types of objects to keep (value must be names for ll.orasql object types.

drop and keep are mutually exclusive. When neither of them is specified all database objects will be dropped.

For the rest of the parameters see the base class _DatabaseCommand.

class ll.pysql.comment[source]

Bases: Command

The comment command does nothing.

class ll.pysql.loadbytes[source]

Bases: Command

The loadbytes command can be used to load a bytes object from an external file. The following parameters are supported:

filenamestring (required)

The name of the file to be loaded. The filename is treated as being relative to the directory containing the PySQL file that contains loadbytes command.

For the parameters raise_exceptions and cond see the base class Command.

class ll.pysql.loadstr[source]

Bases: Command

The loadstr command can be used to load a str object from an external file. The following parameters are supported:

filenamestring (required)

The name of the file to be loaded. The filename is treated as being relative to the directory containing the PySQL file that contains the loadstr command.

encodingstring (optional)

The encoding used for decoding the bytes in the file to text.

errorsstring (optional)

The error handling mode for decoding.

For the parameters raise_exceptions and cond see the base class Command.

__init__(filename, *, encoding=None, errors='strict', raise_exceptions=None, cond=True)[source]

Create a new loadstr object.

class ll.pysql.var[source]

Bases: Command

var commands are used to mark procedure values that are OUT parameters. On first use the parameter is used as an OUT parameter and PySQL will remembers the OUT value as a local variable under the unique name specified in the constructor. When a var object is used a second time a variable object will be passed to the procedure with it’s value set to the value of the local variable. The following parameters are supported:

keystring (required)

A unique name for the value.

typeclass (optional)

The type of the value (defaulting to int).

Note that when the key is None, PySQL will not remember the value, instead each use of var(None) will create a new OUT parameter. This can be used for OUT parameters whose values is not required by subsequent commands.

class ll.pysql.env[source]

Bases: Command

A env command returns the value of an environment variable.

The following parameters are supported:

namestring (required)

The name of the environment variable.

defaultstring (optional)

The default to use, if the environment variable isn’t set. This defaults to None.

class ll.pysql.log[source]

Bases: Command

log commands generate logging output.

The following parameters are supported:

objectsAny

The objects to log. Strings will be logged directly. For all other objects repr() will be called.

class ll.pysql.CommandExecutor[source]

Bases: object

A CommandExecutor object wraps executing a Command object in a callable. Calling the CommandExecutor object executes the command using the specified context and returns the command result.

This class exists because Command objects serve two purposes:

  1. They can be created to print them to a file (via the method Command.source());

  2. They can be put into a PySQL file which will then be read and executed, with must then create the Command object and execute it immediately. This is the job of CommandExecutor objects.

class ll.pysql.Context[source]

Bases: object

A Context objects contains the configuration and run time information required for importing a PySQL file.

executeall(*filenames)[source]

Execute all commands in the PySQL files specified by filenames. If filenames is empty sys.stdin is read.

class ll.pysql.sqlexpr[source]

Bases: object

An sqlexpr object can be used to specify an SQL expression as a procedure parameter instead of a fixed value. For example passing the current date (i.e. the date of the import) can be done with sqlexpr("sysdate").

class ll.pysql.pyexpr[source]

Bases: object

A pyexpr object can be used to embed literal Python source code in a PySQL file.

Note

As PySQL source code is evaluated via eval()/exec() anyway, it it always possible to embed Python expressions in PySQL source code. However this doesn’t roundtrip, i.e. printing the PySQL command via source() outputs the value of a “literal” Python expression.

exception ll.pysql.CompilationError[source]

Bases: Exception

Exception raised by check_errors when invalid database objects are encountered.

exception ll.pysql.SCPError[source]

Bases: Exception

Exception raised by scp when a call to the scp command fails.

class ll.pysql.Location[source]

Bases: object

The location of a PySQL/SQL command in a PySQL file.