module 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

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)

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)

Cleans string.

Parameters:

s – string

Returns:

remove rtn

source on GitHub

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

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')

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')

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)

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