datconv.outconn.postgresql package

Module supporting storing data to PostreSQL database.

General interface description: Output Connector interface.

datconv.outconn.postgresql.obj2db(obj)

Converts Python object to object acceptable as parameter replacement in execute() function

datconv.outconn.postgresql.obj2str(obj, quota="'")

Converts Python object to string to be used in INSERT clause

datconv.outconn.postgresql.ddl module

This module implements Datconv Output Connector which generates CREATE TABLE cause in PostreSQL 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.postgresql.ddl.DCConnector(table, path, mode='w', schema='public', check_keywords=True, lowercase=0, column_constraints={}, common_column_constraints=[], table_constraints=[])

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.postgresql.jinsert module

This module implements Datconv Output Connector which directly inserts data to PostreSQL database. This connector should be used with Writer: datconv.writers.dcjson module. It requires Python package psycopg2 to be installed.

class datconv.outconn.postgresql.jinsert.DCConnector(connstring, table, schema='public', dump_sql=False, autocommit=False, check_keywords=True, lowercase=0, cast=None, on_conflict=None, options=[])

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.
• table – table name 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 and not inserted to database (option to be used for debugging).
• autocommit – parameter passed to connection, 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 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, short, int, long, float or array. Where short stands for 16 bit integer, int - 32 bit integer and long - 64 bit integer. Field names shold be given after all other configured transformations (lowercase, no_underscore, check_keywords).
• on_conflict – specify what to do when record with given primary key exist in the table; one of strings ‘ignore’, ‘update’ or None (raise error in such situation).
• options – array or additional options added to INSERT caluse (see Posgresql documentation).

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.postgresql.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: public
      schema: public
      
      # 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
      
      # 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, short, int, long or float. 
      # Where short stands for 16 bit integer, int - 32 bit integer and long - 64 bit integer. 
      # Field names shold be given after all other configured transformations (lowercase,  check_keywords)
      # default: none
      cast:
        - [['rec', 'value'], str]
      
      # dictionary: key=column name, value=column constraint; optional.
      # default: {}
      column_constraints:
        cdc: NOT NULL
      
      # column constatins to be added after column definitions. Should be declared as string
      # default: []
      common_column_constraints: |
        PRIMARY KEY("cdc", "isn")
        UNIQUE("id")
      
      # table constatins and creation options. Should be declared as string
      # default: []
      table_constraints: |
        PARTITION BY RANGE ("cdc")
        TABLESPACE diskvol1

OutConnector:
    Module: datconv.outconn.postgresql.jddl
    # This module has the same parameters than ddl (see above).

OutConnector:
    Module: datconv.outconn.postgresql.jinsert
    CArg:
      # connection string to database; obligatory
      connstring: host='192.168.1.15' dbname='postgres' user='postgres' password='postgres'
      
      # name of the table; obligatory
      table: product
            
      # schema of the table
      # default: public
      schema: public
      
      # 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.
      # default: false
      dump_sql: true
      
      # parameter passed to connection, 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
      
      # 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 None (raise error in such situation).
      # default: none
      on_conflict: update
      
      # array or additional options added to INSERT caluse (see Posgresql documentation).
      # default: []
      options: []