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.

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_firstnane varchar2(200),
   per_lastnane 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_firstnane in varchar2 := null,
   p_per_lastnane 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_firstnane,
      per_lastnane
   )
   values
   (
      p_per_id,
      p_per_firstnane,
      p_per_lastnane
   );
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': {
      'per_id': var('per_id_max'),
      'per_firstname': 'Max',
      'per_lastname': 'Mustermann',
   }
}

{
   'type': 'procedure',
   'name': 'contact_insert',
   'args': {
      'per_id': var('per_id_max'),
      'con_id': var('con_id_max'),
      'con_type': 'email',
      '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.

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

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

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.

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: