`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 an extension of normal Oracle 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 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.

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:

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;
connection: The active database connection (or None if there is no active database connection).

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: Includes another PySQL file;
connect: Connects to a database;
disconnect: Disconnects from the active database connection;
procedure: Call a procedure in the database (and handles OUT parameter via var objects);
sql: Executes an SQL statement in the database (and handles OUT parameter via var objects);
literalsql: Executes an SQL statement in the database (this is what SQL commands get converted to);
commit: Commits the transaction in the active database connection;
rollback: Rolls back the transaction in the active database connection;
literalpy: Executes Python code (this is what literal Python blocks get converted to);
setvar: Sets a variable;
unsetvar: Deletes a variable;
raiseexceptions: Set the exception handling mode;
pushraiseexceptions: Temporarily modifies the exception handling mode;
popraiseexceptions: Reverts to the previously active exception handling mode;
checkerrors: Checks whether there are invalid database objects;
scp: Creates a file on a remote host via scp;
file: Creates a file on the local machine;
resetsequence: Resets a database sequence to the maximum value of a field in a table;
user_exists: Tests whether a database user exists;
object_exists: Tests whether a database object (table, package, procedure, etc.) exists;
constraint_exists: Tests whether a database constraint (primary key, foriegn key, unique or check constraint) exists;
drop_types: Drops all database objects of a certain type;
comment: A comment
loadbytes: Loads the binary content of a file;
loadstr: Loads the text content of a file;
var: Marks an argument for a procedure or sql command as being an OUT parameter (or passes the value of the variable in subsequent procedure/sql commands);
env: Returns 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...',
)

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

checkerrors()

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

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

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 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 (which might not exist). An example looks like this:

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

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.

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 string 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 and can be used as a script for importing a PySQL file into the database (However some commands require ll.orasql). 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 value is an Oracle connectstring to specify the initial database connection that will be used before any additional connect commands.

-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 the database. The default is to commit at the end or on each disconnect. (But note that DDL in the script will still commit everything up to the DDL 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 /.

-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.

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 immediatetel 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:

raiseexceptionsbool (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 – Oracle import script