datconv.outconn.sqlite package¶

Module supporting storing data to SQLIte database files.

General interface description: Output Connector interface.

datconv.outconn.sqlite.ddl module¶

This module implements Datconv Output Connector which generates CREATE TABLE cause in SQLite dialect. Output of this connector should be treated as starting point for further manual edition, it is not recommended to use it for automatic table generation. This connector should be used with Writer: datconv.writers.dcxpaths module with options add_header: false and add_type: true.

class datconv.outconn.sqlite.ddl.DCConnector(table, path, mode='w', schema='main', check_keywords=True, lowercase=0, column_constraints={}, common_column_constraints=[], table_constraints=[])[source]¶

Bases: object

Please see constructor description for more details.

Parameters are usually passed from YAML file as subkeys of OutConnector:CArg key.

Parameters:

table – name of the table.
path – relative or absolute path to output file.
mode – output file opening mode.
schema – schema of the table.
check_keywords – if true, prevents conflicts with SQL keywords. Data field names that are in conflict will be suffixed with undderscore.
lowercase – if >1, all JSON keys will be converted to lower-case; if =1, only first level keys; if =0, no conversion happen.
column_constraints – dictionary: key=column name, value=column constraint.
common_column_constraints – column constatins to be added after column definitions. Should be a list.
table_constraints – table constatins and creation options. Should be a list.

For more detailed descriptions see conf_template.yaml file in this module folder.

datconv.outconn.sqlite.jinsert module¶

This module implements Datconv Output Connector which directly inserts data to SQLite database. This connector should be used with Writer: datconv.writers.dcjson module.

class datconv.outconn.sqlite.jinsert.DCConnector(connstring, table, schema='main', dump_sql=False, autocommit=False, bulk_size=10000, check_keywords=True, lowercase=0, cast=None, on_conflict=None)[source]¶

Bases: object

Please see constructor description for more details.

Parameters are usually passed from YAML file as subkeys of OutConnector:CArg key.

Parameters:

connstring – connection string to database (path to file in this case).
table – table name where to insert records.
schema – table schema name where to insert records.
dump_sql – if true, insert statements are being saved to file specified as connstring with added ‘.sql’ extension and not applied to database (option to be used for debugging).
bulk_size – if consequtive records have similar structure (i.e. have the same fields) - they are groupped into one pack (up to the size specified as this parameter) and inserted in one command. If set value is 0 than every insert is done individaually - warning: it is quite slow operation.
autocommit – if true, every insert is automatically commited (slows down insert operations radically); if false, chenges are commited at the end - i.e. if any insert fail everything is rolled back and no records are added.
check_keywords – if true prevents conflicts with SQL keywords.
lowercase – if >1, all JSON keys will be converted to lower-case; if =1, only first level keys; if =0, no conversion happen.
cast – array of arrays of the form: [[‘rec’, ‘value’], str], what means that record: {“rec”: {“value”: 5025}} will be treated as {“rec”: {“value”: “5025”}} - i.e. it is ensured that “value” will allways be string. First position determines address of data to be converted, last position specifies the type: str, bool, int, long or float. Field names shold be given after all other configured transformations (lowercase, check_keywords).
on_conflict – specify what to do when record with given primary key exist in the table; one of strings ‘ignore’, ‘update’ (or ‘replace’ with the same effect), ‘rollback’, ‘abort’, fail’ or None (raise error in such situation).

For more detailed descriptions see conf_template.yaml file in this module folder.

Configuration keys¶

Listing of all possible configuration keys to be used with output connectors contained in this package.

There are sample values given, if key is not specified in configuration file, than default value is assumed.

OutConnector:
    Module: datconv.outconn.sqlite.ddl
    CArg:
      # name of the table; obligatory
      table: product
      
      # relative or absolute path to output file; obligatory
      path: "out/AddnDrawNbrs_c5019_s38_2.sql"
        
      # output file opening mode (w or a); optional
      # default: w
      mode: a
      
      # schema of the table
      # default: main
      schema: main
      
      # if true, prevents conflicts with SQL keywords;
      # data field names that are in conflict will be suffixed with undderscore.
      # default: true
      check_keywords: true
      
      # if >1, all JSON keys will be converted to lower-case;
      # if =1, only first level keys; 
      # if =0, no conversion happen.
      # default: 0
      lowercase: 1
      
      # dictionary: key=column name, value=column constraint; optional.
      # default: {}
      column_constraints:
        cdc: NOT NULL
      
      # column constatins to be added after column definitions. Should be a list
      # default: []
      common_column_constraints:
        - PRIMARY KEY(cdc, isn)
      
      # table constatins and creation options. Should be a list
      # default: []
      table_constraints: []

OutConnector:
    Module: datconv.outconn.sqlite.jinsert
    CArg:
      # connection string to database (path to file in this case); obligatory
      connstring: "out/AddnDrawNbrs_c5019_s38_2.sqlite"
      
      # name of the table; obligatory
      table: product
            
      # schema of the table
      # default: main
      schema: main
      
      # if true, prevents conflicts with SQL keywords;
      # data field names that are in conflict will be suffixed with undderscore.
      # default: true
      check_keywords: true
      
      # if >1, all JSON keys will be converted to lower-case;
      # if =1, only first level keys; 
      # if =0, no conversion happen.
      # default: 0
      lowercase: 1
      
      # if true, insert statements are being saved to file specified as connstring
      # with added '.sql' extension and not applied to database.
      # default: false
      dump_sql: true
      
      # if true, every insert is automatically commited (slows down insert operations radically), 
      # if false, chenges are commited at the end - i.e. if any insert fail 
      # everything is rolled back and no records are added.
      # default: false
      autocommit: true
      
      # if consequtive records have similar structure (i.e. have the same fields), 
      # they are groupped into one pack (up to the size specified as this parameter) 
      # and inserted in one command. 
      # If set value is 0 than every insert is done individaually - warning: it is slow operation.
      # default: 10000
      bulk_size: 5000
      
      # array of arrays of the form: [['rec', 'value'], str], what means that record: {"rec": {"value": 5025}} 
      # will be writen as {"rec": {"value": "5025"}} - i.e. it is ensured that "value" will allways be string. 
      # First position determines address of data to be converted, last position specifies the type: str, bool, int, long or float. 
      # Field names shold be given after all other configured transformations (lowercase,  check_keywords)
      # default: none
      cast:
          - [['rec', 'value'], str]

      # specify what to do when record with given primary key exist in the table; 
      # one of strings 'ignore', 'update' (or 'replace' with the same effect), 'rollback', 'abort', fail' or None (raise error in such situation).
      # default: none
      on_conflict: update

Table of Contents

Sections

datconv.outconn.sqlite package¶

datconv.outconn.sqlite.ddl module¶

datconv.outconn.sqlite.jinsert module¶

Configuration keys¶