module sql.database_import_export

Inheritance diagram of pyensae.sql.database_import_export

Short summary

module pyensae.sql.database_import_export

Database

source on GitHub

Classes

class

truncated documentation

DatabaseImportExport

This class is not meant to be working alone. It contains import, export function for a database, in various formats. …

Methods

method

truncated documentation

_append_table

Appends element to a database.

_clean_string

Cleans string.

append_values

Uses _append_table() to fill a table will the values contained in values (as list).

export_table_into_flat_file

Exports a table into a flat file.

export_view_into_flat_file

Exports a table into a flat file.

import_table_from_flat_file

Adds a table to database from a file.

Documentation

Database

source on GitHub

class pyensae.sql.database_import_export.DatabaseImportExport[source]

Bases: object

This class is not meant to be working alone. It contains import, export function for a database, in various formats.

source on GitHub

_append_table(file, table, columns, format='tsv', header=False, stop=- 1, lower_case=False, cursor=None, fill_missing=0, unique=None, filter_case=None, strict_separator=False, skip_exception=False, changes=None, encoding='utf-8', **params)[source]

Appends element to a database.

Parameters
  • file – file name or a matrix (this matrix can be an iterator)

  • table – table name

  • columns – columns definition (see below)

  • format – tsv, the only one accepted for the time being, it can be a function (line, **params)

  • header – the file has a header of not, if True, skip the first line

  • stop – if -1, insert every line, otherwise stop when the number of inserted lines is stop

  • lower_case – put every str string in lower_case before inserting it

  • cursor – if None, create a new one

  • fill_missing – fill the missing values by a default value, at least not more than fill_missing values

  • unique – if unique is a column number, the function will not take into account another containing a value already seen on this column

  • filter_case – process every case information (used to replace space for example)

  • strict_separator – strict number of columns, it assumes there is no separator in the content of every column

  • params – see format

  • skip_exception – skip exception while inserting an element

  • changes – to rewrite column names

  • encoding – encoding

Returns

number of inserted elements

The columns definition must follow the schema:
  • dictionary { key:(column_name,python_type) }

  • or { key:(column_name,python_type,preprocessing_function) }

preprocessing_function is a function whose prototype is for example:

def preprocessing_score (s) :
    return s.replace (",",".")
And:
  • if PRIMARYKEY is added, the key is considered as the primary key

  • if AUTOINCREMENT is added, the key will automatically filled (like an id)

source on GitHub

_clean_string(s)[source]

Cleans string.

Parameters

s – string

Returns

remove rtn

source on GitHub

append_values(values, tablename, schema, cursor=None, skip_exception=False, encoding='utf-8')[source]

Uses _append_table to fill a table will the values contained in values (as list).

Parameters
  • values – list of list (each cell is a value)

  • tablename – name of the table to fill

  • schema – schema of the database, it must be present in case on the columns includes the tag “PRIMARYKEY”, in that case, the value for this field will be automatically set up.

  • cursor – if None, create a new one

  • skip_exception – skip exception while inserting an element

  • encoding – encoding

source on GitHub

export_table_into_flat_file(table, filename, header=False, columns=None, post_process=None, encoding='utf8')[source]

Exports a table into a flat file.

Parameters
  • table – table name

  • filename – filename

  • header – add a header on the first line

  • columns – export only columns in this list (if None, export all)

  • post_process – post_process a line: - input: list, dictionary (for your own use, same one all the time) - output: list

  • encoding – encoding

Export the results of a SQL query into a flat file

from pyensae.sql.database_main import Database
dbfile = "filename.db3"
filetxt = "fileview.txt"
sql = "..."
db = Database(dbfile)
db.connect()
db.export_view_into_flat_file (sql, fileview, header = True)
db.close()

source on GitHub

export_view_into_flat_file(view_sql, filename, header=False, post_process=None, encoding='utf8')[source]

Exports a table into a flat file.

Parameters
  • view_sql – SQL request

  • filename – filename

  • header – if != None, add a header on the first line (header is a list of string)

  • post_process – if != None, use this function to post-process a text line extracted from the file

  • encoding – if != None, use this as a parameter to convert any value into str

source on GitHub

import_table_from_flat_file(file, table, columns, format='tsv', header=False, display=False, lower_case=False, table_exists=False, temporary=False, fill_missing=False, indexes=None, filter_case=None, change_to_text=None, strict_separator=False, add_key=None, encoding='utf-8', **params)[source]

Adds a table to database from a file.

Parameters
  • file – file name or matrix

  • table – table name

  • columns – columns definition (see below) if None: columns are guessed

  • format – tsv, the only one accepted for the time being, it can be a function whose parameter are a line and **params

  • header – the file has a header of not, if True, skip the first line

  • lower_case – put every string in lower case before inserting it

  • table_exists – if True, do not create the table

  • temporary – adding a temporary table

  • fill_missing – fill the missing values

  • indexes – add indexes before appending all the available observations

  • filter_case – process every case information (used to replace space for example)

  • encoding – encoding

  • params – see format

  • change_to_text – changes the format from any to TEXT

  • display – if True, print more information on stdout

  • strict_separator – strict number of columns, it assumes there is no separator in the content of every column

  • add_key – name of a key to add (or None if nothing to add)

Returns

the number of added rows

The columns definition must follow the schema:

  • dictionary { key: (column_name,python_type) }

  • or { key: (column_name,python_type,preprocessing_function) }

preprocessing_function is a function whose prototype is for example:

def preprocessing_score (s) :
    return s.replace (",",".")

And:

  • if PRIMARYKEY is added, the key is considered as the primary key

  • if AUTOINCREMENT is added, the key will automatically filled (like an id)

Warning

The function does not react well when a column name includes a space.

source on GitHub