ll.pysql – Oracle import script

Overview

The module/script pysql can be used to import data into an Oracle database. It reads pysql files which are a variant of normal Oracle SQL files.

A PySQL file can contain two 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 that starts with -- @@@. pysql will strip off a trailing ; or / from the command 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 sqlplus executes SQL commands. sqlplus would continue after an error and exit with status code 0 even if there were errors. It is also possible to explicitely ignore any exception raised by the command by specifying a different exception handling mode.

A PySQL file that only contains SQL commands is still a valid SQL file from the perspective of Oracle, so it still can be executed via sqlplus.

PySQL commands

A PySQL file may also contain PySQL commands. A PySQL command looks like a Python dictionary literal. This literal must either be contained in a single line or it must start with a line that only contains { and end at a line that only contains }.

For further information about the different commands and which keys they support, see the class Command and its subclasses.

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
;

-- @@@ person: table

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

-- @@@ person: primary key

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

-- @@@ contact: table

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

-- @@@ contact: primary key

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

-- @@@ person: insert procedure

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;
/

-- @@@ contact: insert procedure

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

{
        'type': 'procedure',
        'name': 'person_insert',
        'args': {
                'c_user': 'import',
                'p_per_id': var('per_id_max'),
                'p_per_firstname': 'Max',
                'p_per_lastname': 'Mustermann',
        }
}

{
        'type': 'procedure',
        'name': 'contact_insert',
        'args': {
                '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',
        }
}

{
        'type': 'file',
        'name': 'portrait_{per_id_max}.png',
        'content': b'\x89PNG\r\n\x1a\n...',
}

{
        'type': 'resetsequence',
        'sequence': 'person_seq',
        'table': 'person',
        'field': 'per_id',
}

{"type": "compileall"}

{"type": "checkerrors"}

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

python pysql.py 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 the above 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 recompile all schema objects and then 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 pushconnnection command. This command opens a new database connection and stores it under a name. Subsequent commands can refer to that name to specify the database connection to use. The popconnection command disconnects from the database and reverts to the previous connection for that name (which might not exist). An example looks like this:

{
        "type": "pushconnection",
        "connectstring": "user/pwd@db",
        "connectname": "db",
}

{
        "type": "procedure",
        "name": "test",
        "connectname": "db",
}

{
        "type": "popconnection",
        "connectname": "db",
}

The connection with the name None is the “default connection”. This connection will be used for all normal SQL commands and all PySQL commands that don’t have a "connectname" key (or where the "connectname" key is None).

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 cx_Oracle 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 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. A variable object that has a value will be replaced by that value in such an expression, so stuff like 2*var("foo") can be used.

An uninitialized variable object is considered “false”, This makes it possible to default to another value if a variable is uninitialized:

var('foo', int) or 42

External files

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

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

A string can be loaded with the loadstr function like this:

loadstr("path/to/file.txt", "utf-8", "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.

Command line usage

pysql.py has no external dependencies except for cx_Oracle and can be used as a script for importing a PySQL file into the database. As a script it supports the following command line options:

connectstring
An Oracle connectstring.
file
The name of the PySQL file that will be read and imported. If file isn’t specified the 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) or full (detailed output for each command/procedure call)
-z, --summary
Give a summary of the number of commands executed and procedures called.
-c, --commit
Specifies when to commit database transactions. record commits after every command. once (the default) commits at the end of the script (or when a connection is popped) and never rolls back the transaction after all commands.
-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 /.
-f, --filedirectory
The base directory for file file save commands. It must include a trailing /.
-t, --terminator
The terminator after an SQL command (should be a valid SQL comment; default -- @@@).
-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.
class ll.pysql.CheckErrorsCommand(location, *, raiseexceptions=None, comment=None, connectstring=None, connectname=None)[source]

Bases: ll.pysql._DatabaseCommand

The "checkerrors" command checks that there are no compilation errors in the target schema. If there are, an exception will be raised.

The keys "raiseexceptions", "comment", "connectname" and "connectstring" from the base classes are supported, but the value of the "raiseexceptions" key will be ignored.

class ll.pysql.Command(location, *, raiseexceptions=None, comment=None)[source]

Bases: object

The base class of all commands. A Command object is created from a command dictionary literal in a PySQL file. The keys in the command dictionary that are supported by all command types are the following:

type : string (optional)
This is either "procedure" (the default), "sql", "file", "scp", "resetsequence", "setvar", "include", "compileall", "checkerrors", "pushconnection", "popconnection", "raiseexceptions", "pushraiseexceptions" or "popraiseexceptions" and specifies the type of the PySQL command.
raiseexceptions : bool (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 uses the global configuration.
comment : string (optional)
This key will be ignored completely, but can be used to add a comment to a command.
class ll.pysql.CommentCommand(location, *, raiseexceptions=None, comment=None)[source]

Bases: ll.pysql.Command

The "comment" command does nothing.

The keys "raiseexceptions" and "comment" from the base class are supported.

exception ll.pysql.CompilationError(objects)[source]

Bases: Exception

Exception raised by CheckErrorsCommand when invalid database objects are encountered.

class ll.pysql.CompileAllCommand(location, *, raiseexceptions=None, comment=None, connectstring=None, connectname=None)[source]

Bases: ll.pysql._DatabaseCommand

The "compileall" command will recompile all objects in the schema.

The keys "raiseexceptions", "comment", "connectname" and "connectstring" from the base classes are supported.

class ll.pysql.Context(connectstring=None, scpdirectory='', filedirectory='', commit='once', terminator='-- @@@', raiseexceptions=True, verbose=0, summary=False, vars=None)[source]

Bases: object

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

executeall(stream)[source]

Execute all command in stream. stream must be an iterable over lines that contain the PySQL commands.

class ll.pysql.FileCommand(location, *, name, content, raiseexceptions, comment=None, mode=None, owner=None, group=None)[source]

Bases: ll.pysql.Command

The "file" command creates a file by directly saving it from Python. The following keys are supported in the command dictionary:

"name" : string (required)
The name of the file to be created. It may contain format() style specifications containing any key that appeared in a "procedure" or "sql" command. These specifiers will be replaced by the correct key values.
"content": bytes (required)
The content of the file to be created. This can also be a load object, to load the content from an external file.
"mode": integer (optional)
The file mode for the new file. If the mode is specified os.chmod() will be called on the file.
"owner": integer or string (optional)
The owner of the file (as a user name or a uid).
"group": integer 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.

Additionally the keys "raiseexceptions" and "comment" from the base class are supported.

class ll.pysql.IncludeCommand(location, *, name, raiseexceptions=None, comment=None)[source]

Bases: ll.pysql.Command

The "include" command includes another PySQL file. The filename is read from the key "name". This name is interpreted as being relative to the directory with the file containing the include command.

Additionally the keys "raiseexceptions" and "comment" from the base class are supported.

class ll.pysql.Location(filename, startline, endline)[source]

Bases: object

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

class ll.pysql.PopConnectionCommand(location, *, connectname=None, raiseexceptions=None, comment=None)[source]

Bases: ll.pysql.Command

The "popconnection" command disconnects from the database connection with the name in the key "connectname" and reverts to the previous connection registered for that name. (If "connectname" is None the default connection will be used). If the commit mode for the connection is "once" the transaction will be committed before closing the connection.

Additionally the keys "raiseexceptions" and "comment" from the base class are supported.

class ll.pysql.PopRaiseExceptionsCommand(location, *, raiseexceptions=None, comment=None)[source]

Bases: ll.pysql.Command

The "popraiseexceptions" command restores the previously active exception handling mode (i.e. the one active before the last "pushraiseexceptions" command).

The keys "raiseexceptions" and "comment" from the base class are supported in the command dictionary.

class ll.pysql.ProcedureCommand(location, *, name, raiseexceptions=None, comment=None, connectstring=None, connectname=None, args=None)[source]

Bases: ll.pysql._SQLCommand

A "procedure" command calls an Oracle procedure in the database. The following keys are supported in the command dictionary:

"name" : string (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).
"args" : dictionary (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:

  • sql objects can be used to specify that the paramater should be literal SQL. So e.g. sql("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 loadbytes() and loadstr() objects can be used to load values from external files (as long as they are of type bytes or str). loadbytes("foo/bar.txt") will be replaced 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", "utf-8", "replace")
    

Additionally the keys "raiseexceptions", "comment", "connectname" and "connectstring" from the base classes are supported.

class ll.pysql.PushConnectionCommand(location, *, connectstring, raiseexceptions=None, comment=None, connectname=None, commit=None)[source]

Bases: ll.pysql.Command

The "pushconnection" command connects to the database given in the connectstring in the key "connectstring" and pushes the connection under the name from the key "connectname". (If "connectname" is not given or is None, the connection will be pushed as the default connection). "commit" can be given to specify the commit mode for this connection ("record", "once" or "never").

Additionally the keys "raiseexceptions" and "comment" from the base class are supported.

class ll.pysql.PushRaiseExceptionsCommand(location, *, value, raiseexceptions=None, comment=None)[source]

Bases: ll.pysql.Command

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

{"type": "pushraiseexceptions", "value": False}

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

{"type": "popraiseexceptions"}

Note that this global configuration will only be relavant for commands that don’t specify the "raiseexceptions" key themselves.

Additionally the keys "raiseexceptions" and "comment" from the base class are supported.

class ll.pysql.RaiseExceptionsCommand(location, *, value, raiseexceptions=None, comment=None)[source]

Bases: ll.pysql.Command

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

{"type": "raiseexceptions", "value": False}

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

{"type": "raiseexceptions", "value": 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 "raiseexceptions" key themselves.

Additionally the keys "raiseexceptions" and "comment" from the base class are supported.

class ll.pysql.ResetSequenceCommand(location, *, sequence, table, field, raiseexceptions=None, comment=None, minvalue=None, increment=None, connectstring=None, connectname=None)[source]

Bases: ll.pysql._DatabaseCommand

The "resetsequence" command resets a sequence in the Oracle database to the maximum value of a field in a table. The following keys are supported in the command dictionary:

"sequence": string (required)
The name of the sequence to reset.
"table": string (required)
The name of the table that contains the field.
"field": string (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.
"minvalue": integer (optional, default taken from sequence)
The minimum value for the sequence.
"increment": integer (optional, default taken from sequence)
The increment (i.e. the step size) for the sequence.

Additionally the keys "raiseexceptions", "comment", "connectname" and "connectstring" from the base classes are supported.

class ll.pysql.SCPCommand(location, *, name, content, raiseexceptions=None, comment=None)[source]

Bases: ll.pysql.Command

The "scp" command creates a file by copying it via the scp command. The following keys are supported in the command dictionary:

"name" : string (required)
The name of the file to be created. It may contain format() style specifications containing any key that appeared in a "procedure" or "sql" command. These specifiers will be replaced by the correct key values. As these files will be copied via the scp command, ssh file names can be used.
"content": bytes (required)
The content of the file to be created. This can also be a loadbytes object, to load the content from an external file.

Additionally the keys "raiseexceptions" and "comment" from the base class are supported.

exception ll.pysql.SCPError(status, msg)[source]

Bases: Exception

Exception raised by SCPCommand when a call to the scp comamnd fails.

class ll.pysql.SQLCommand(location, *, sql, raiseexceptions=None, comment=None, connectstring=None, connectname=None, args=None)[source]

Bases: ll.pysql._SQLCommand

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

"sql" : string (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.
"args" : dictionary (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 sql objects are not supported (they will be ignored).

Additionally the keys "raiseexceptions", "comment", "connectname" and "connectstring" from the base classes are supported.

class ll.pysql.SetVarCommand(location, *, name, value, raiseexceptions=None, comment=None)[source]

Bases: ll.pysql.Command

The "setvar" command sets a variable to a fixed value. The following keys are supported in the command dictionary:

"name": string (required)
The name of the variable to set.
"value": object (required)
The value of the variable.

Additionally the keys "raiseexceptions" and "comment" from the base class are supported.

class ll.pysql.UnsetVarCommand(location, *, name, raiseexceptions=None, comment=None)[source]

Bases: ll.pysql.Command

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

Additionally the keys "raiseexceptions" and "comment" from the base class are supported.

class ll.pysql.loadbytes(filename)[source]

Bases: object

A loadbytes object can be used to load a bytes object from an external file.

__init__(filename)[source]

Create a new loadbytes object. filename is 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 PySQL command with the loadbytes object.

execute(basefilename)[source]

Read the file and return the file content as a bytes or str object. basefilename is the filename containing the PySQL command with the load object (i.e. this determines the base directory).

class ll.pysql.loadstr(filename, encoding=None, errors='strict')[source]

Bases: object

A loadstr object can be used to load a str object from an external file.

__init__(filename, encoding=None, errors='strict')[source]

Create a new loadbytes object. filename is 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 PySQL command with the loadstr object. encoding and errors will be used for the file content into a string.

execute(basefilename)[source]

Read the file and return the file content as a bytes or str object. basefilename is the filename containing the PySQL command with the load object (i.e. this determines the base directory).

class ll.pysql.sql(expression)[source]

Bases: object

An sql 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 sql("sysdate").

class ll.pysql.var(key=None, type=<class 'int'>)[source]

Bases: object

var objects 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 under the unique key specified in the constructor. When a var object is used a second time its value will be passed to the procedure as a normal IN parameter instead. This also means that it is possible to have Python expressions as parameter values that transform the variable value.

__bool__()[source]

Variables without values are always false.

__init__(key=None, type=<class 'int'>)[source]

Create a var instance. key is a unique name for the value. type is 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.