======== 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 contains 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_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``. ========= 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 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 ============== 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. Possible levels are: ``0`` (no output), ``1`` (one dot for each command), ``2`` (each command name) or ``3`` (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) at the end of the script 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 the ``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`` supprted values are ``0``, ``no``, ``false``, ``False``, ``1``, ``yes``, ``true`` and ``True``. =============================== def ``format_class``​(``obj``): =============================== =============================== class ``Context``​(``object``): =============================== A ``Context`` objects contains the configuration and run time information required for importing a PySQL file. def ``__init__``​(``self``, ``db``=``None``, ``scpdirectory``=``''``, ``filedirectory``=``''``, ``commit``=``'once'``, ``terminator``=``'-- @@@'``, ``raiseexceptions``=``True``, ``verbose``=``0``, ``summary``=``False``, ``vars``=``None``): ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- def ``var``​(``self``, ``key``, ``type``=````): ------------------------------------------------------------ def ``loadbytes``​(``self``, ``filename``): ------------------------------------------- def ``loadstr``​(``self``, ``filename``, ``encoding``=``None``, ``errors``=``'strict'``): ----------------------------------------------------------------------------------------- def ``_load``​(``self``, ``stream``): ------------------------------------- Load a PySQL file from ``stream``. ``stream`` must be an iterable over lines that contain the PySQL commands. This function is a generator. Its output are the PySQL command objects (i.e. instances of ``Command``). def ``executeall``​(``self``, ``stream``): ------------------------------------------ Execute all command in ``stream``. ``stream`` must be an iterable over lines that contain the PySQL commands. def ``_printsummary``​(``self``): --------------------------------- =============================== class ``Command``​(``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 dictorionary 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"`` or ``"checkerrors"`` 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``, the default), or should be ignored (``False``). def ``__init__``​(``self``, ``location``, ``raiseexceptions``): --------------------------------------------------------------- def ``__str__``​(``self``): --------------------------- =========================== def ``register``​(``cls``): =========================== ======================================= class ``IncludeCommand``​(``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. def ``__init__``​(``self``, ``location``, ``raiseexceptions``, ``name``): ------------------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- ==================================== class ``_SQLCommand``​(``Command``): ==================================== Common base class of ``ProcedureCommand`` and ``SQLCommand``. def ``_createvar``​(``cursor``, ``type``, ``value``): ----------------------------------------------------- def ``_executesql``​(``self``, ``context``, ``query``): ------------------------------------------------------- def ``_formatargs``​(``self``, ``context``): -------------------------------------------- ============================================= class ``ProcedureCommand``​(``_SQLCommand``): ============================================= A ``"procedure"`` command calls an Oracle procedure in the database. In addition to ``"type"`` and ``"raiseexceptions"`` 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") def ``__init__``​(``self``, ``location``, ``raiseexceptions``, ``name``, ``args``=``None``): -------------------------------------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- def ``_formatprocedurecall``​(``self``, ``context``): ----------------------------------------------------- def ``execute``​(``self``, ``context``): ---------------------------------------- ======================================= class ``SQLCommand``​(``_SQLCommand``): ======================================= An ``"sql"`` command directly executes an SQL statement in the Oracle database. In addition to ``"type"`` and ``"raiseexceptions"`` 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`` and ``load`` objects are supported. However ``sql`` objects are not supported (they will be ignored). def ``__init__``​(``self``, ``location``, ``raiseexceptions``, ``sql``, ``args``=``None``): ------------------------------------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- def ``_formatsql``​(``self``, ``context``): ------------------------------------------- def ``execute``​(``self``, ``context``): ---------------------------------------- ====================================== class ``SetVarCommand``​(``Command``): ====================================== The ``"setvar"`` command sets a variable to a fixed value. In addition to ``"type"`` and ``"raiseexceptions"`` the following keys are supported in the command dictionary: ``name``: string (required) The name of the variable to set. ``value``: (required) The value of the variable. def ``__init__``​(``self``, ``location``, ``raiseexceptions``, ``name``, ``value``): ------------------------------------------------------------------------------------ def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- def ``execute``​(``self``, ``context``): ---------------------------------------- ======================================== class ``UnsetVarCommand``​(``Command``): ======================================== The ``"unsetvar"`` command deletes a variable. In addition to ``"type"`` and ``"raiseexceptions"`` the key ``name`` is supported and must contain the name of the variable. def ``__init__``​(``self``, ``location``, ``raiseexceptions``, ``name``): ------------------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- def ``execute``​(``self``, ``context``): ---------------------------------------- =============================================== class ``RaiseExceptionsCommand``​(``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 relavant for commands that don't specify the ``"raiseexceptions"`` key themselves. def ``__init__``​(``self``, ``location``, ``raiseexceptions``, ``value``): -------------------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- def ``execute``​(``self``, ``context``): ---------------------------------------- =========================================== class ``CheckErrorsCommand``​(``Command``): =========================================== The ``"checkerrors"`` command checks that there are no compilation errors in the target schema. If there are, an exception will be raised. (The ``raiseexceptions`` key is supported, but its value will be ignored). def ``__init__``​(``self``, ``location``, ``raiseexceptions``): --------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- def ``execute``​(``self``, ``context``): ---------------------------------------- ========================================== class ``CompileAllCommand``​(``Command``): ========================================== The ``"compileall"`` command will recompile all objects in the schema. def ``__init__``​(``self``, ``location``, ``raiseexceptions``): --------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- def ``execute``​(``self``, ``context``): ---------------------------------------- =================================== class ``SCPCommand``​(``Command``): =================================== The ``"scp"`` command creates a file by copying it via the ``scp`` command. In addition to ``"type"`` and ``"raiseexceptions"`` 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 ``load`` object, to load the content from an external file. def ``__init__``​(``self``, ``location``, ``raiseexceptions``, ``name``, ``content``): -------------------------------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- def ``execute``​(``self``, ``context``): ---------------------------------------- ==================================== class ``FileCommand``​(``Command``): ==================================== The ``"file"`` command creates a file by directly saving it from Python. In addition to ``"type"`` and ``"raiseexceptions"`` 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. def ``__init__``​(``self``, ``location``, ``raiseexceptions``, ``name``, ``content``, ``mode``=``None``, ``owner``=``None``, ``group``=``None``): ------------------------------------------------------------------------------------------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- def ``execute``​(``self``, ``context``): ---------------------------------------- ============================================= class ``ResetSequenceCommand``​(``Command``): ============================================= The ``"resetsequence"`` command resets a sequence in the Oracle database to the maximum value of a field in a table. In addition to ``"type"`` and ``"raiseexceptions"`` 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 stop size) for the sequence. def ``__init__``​(``self``, ``location``, ``raiseexceptions``, ``sequence``, ``table``, ``field``, ``minvalue``=``None``, ``increment``=``None``): -------------------------------------------------------------------------------------------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- def ``execute``​(``self``, ``context``): ---------------------------------------- =========================== class ``var``​(``object``): =========================== ``var`` instances 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. Note that def ``__init__``​(``self``, ``key``, ``type``=````): ----------------------------------------------------------------- Create a ``var`` instance. ``key`` is a unique name for the value. ``type`` is the type of the value (defaulting to ``int``). def ``__repr__``​(``self``): ---------------------------- def ``__bool__``​(``self``): ---------------------------- Variables without values are always false. =============================== def ``_reprbytes``​(``value``): =============================== ============================= def ``_reprstr``​(``value``): ============================= ============================= class ``strvalue``​(``str``): ============================= def ``__new__``​(``cls``, ``key``, ``value``=``''``): ----------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- ================================= class ``bytesvalue``​(``bytes``): ================================= def ``__new__``​(``cls``, ``key``, ``value``=``''``): ----------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- ============================= class ``intvalue``​(``int``): ============================= def ``__new__``​(``cls``, ``key``, ``value``=``0``): ---------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- ================================= class ``floatvalue``​(``float``): ================================= def ``__new__``​(``cls``, ``key``, ``value``=``0.0``): ------------------------------------------------------ def ``__repr__``​(``self``): ---------------------------- ========================================= def ``_makevalue``​(``name``, ``value``): ========================================= =========================== class ``sql``​(``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")``. def ``__init__``​(``self``, ``expression``): -------------------------------------------- def ``__repr__``​(``self``): ---------------------------- ================================= class ``loadbytes``​(``object``): ================================= A ``loadbytes`` object can be used to load a ``bytes`` object from an external file. def ``__init__``​(``self``, ``filename``): ------------------------------------------ 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. def ``__repr__``​(``self``): ---------------------------- def ``execute``​(``self``, ``basefilename``): --------------------------------------------- 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 ``loadstr``​(``object``): =============================== A ``loadstr`` object can be used to load a ``str`` object from an external file. def ``__init__``​(``self``, ``filename``, ``encoding``=``None``, ``errors``=``'strict'``): ------------------------------------------------------------------------------------------ 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. def ``__repr__``​(``self``): ---------------------------- def ``execute``​(``self``, ``basefilename``): --------------------------------------------- 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 ``loadedbytes``​(``bytes``): ================================== def ``__new__``​(``cls``, ``filename``, ``value``): --------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- ============================== class ``loadedstr``​(``str``): ============================== def ``__new__``​(``cls``, ``filename``, ``value``): --------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- ================================ class ``Error``​(``Exception``): ================================ def ``__init__``​(``self``, ``location``): ------------------------------------------ def ``__str__``​(``self``): --------------------------- =========================================== class ``CompilationError``​(``Exception``): =========================================== Exception raised by ``CheckErrorsCommand`` when invalid database objects are encountered. def ``__init__``​(``self``, ``objects``): ----------------------------------------- def ``__str__``​(``self``): --------------------------- =================================== class ``SCPError``​(``Exception``): =================================== Exception raised by ``SCPCommand`` when a call to the ``scp`` comamnd fails. def ``__init__``​(``self``, ``status``, ``msg``): ------------------------------------------------- def ``__str__``​(``self``): --------------------------- ================================ class ``Location``​(``object``): ================================ The location of a PySQL/SQL command in a pysql file. def ``__init__``​(``self``, ``filename``, ``startline``, ``endline``): ---------------------------------------------------------------------- def ``__repr__``​(``self``): ---------------------------- def ``__str__``​(``self``): --------------------------- ========================= def ``define``​(``arg``): ========================= ================================= def ``main``​(``args``=``None``): =================================