`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: oracledb.DB_TYPE_CLOB, i.e. oracledbs type for CLOB parameters;
DB_TYPE_NCLOB: oracledb.DB_TYPE_NCLOB, i.e. oracledbs type for NCLOB parameters;
DB_TYPE_BLOB: oracledb.DB_TYPE_BLOB, i.e. oracledbs 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), a oracledb var object will be passed and 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 oracledb (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 oracledb.

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]

This is used as a class decorator.

class ll.pysql.include[source]

ll.pysql – Database import script