module sql.database_core

Short summary

module pyensae.sql.database_core

Database

source on GitHub

Classes

class	truncated documentation
DatabaseCore	Core methods for class Database.

Static Methods

staticmethod	truncated documentation
_special_function_init_
idaytodate	Date conversion. Static method to insert in a SQL query.
isectoday	Date conversion. Static method to insert in a SQL query.
itimestamp	Date conversion. Static method to insert in a SQL query.
regex_match	Applies a regular expression. Static method to insert in a SQL query.
string_to_date	Date conversion. Static method to insert in a SQL query.

Methods

method	truncated documentation
__init__	Creates a database object.
_analyse	Analyses the request does it contains cross product.
_insert_sql	Builds the sql for an insert request.
add_function	Adds a function which can be used as any other SQL function (strim, …).
attach_database	Attaches another database.
close	Closes the database.
commit	Calls this function after any insert request.
connect	Opens a connection to the database.
create_index	Creates an index on a table using some columns.
create_table	Creates a table.
execute	Opens a cursor with a query and return it to the user.
execute_script	Opens a cursor and run a script.
execute_view	Opens a cursor with a query and returns the result into a list.
get_attached_database_list	Returns all the attached database (avoid the temporary ones and the main one).
get_column_type	Returns the column type of a table.
get_file	Gets database file.
get_index_list	Returns the list of indexes.
get_index_on_table	Returns the list of indexes on a specific table.
get_sql_columns	Returns the columns name for a SQL request.
get_table_columns	See get_table_columns_list(). Example (dictionary == False):
get_table_columns_list	Returns all the columns for a table.
get_table_list	Returns the list of tables.
get_table_nb_lines	Returns the number of lines in a table (or number of observations).
get_table_nfirst_lines	Returns the n first lines.
has_index	Says if the index belongs to the database.
has_table	Says if the table belongs to the database.
insert	Inserts into a table.
is_connected	Says if the database is connected.
isMemory	Tells if the Database takes place in memory (:memory:).
isMSSQL	Says if the syntax is MS SQL Server.
len	Returns the number of lines of table table.
remove_table	Removes a table.
SetBufferInsert	This function offers the possibility to postpone the insertion, they will be processed all at the time during when …
update	Updates some values WHERE key=value.

Documentation

Database

source on GitHub

class pyensae.sql.database_core.DatabaseCore(sql_file, engine='SQLite', user=None, password=None, host='localhost', LOG=None, attach=None)

Bases: DatabaseCore2

Core methods for class Database.

attribute	meaning
_engine	engine type (SQLite is the only available)
_sql_file	database file, if it does not exist, it will be created.

source on GitHub

Creates a database object.

Parameters:

• sql_file – database file database file (use :memory: to avoid creating a file and using only memory) it can also contain several files separated by ; name_file ; nickname,second_file ; ...

• engine – SQLite or MySQL (if it is installed), ODBCMSSQL

• user – user if needed

• host – to connect to a MSSQL database

• password – password if needed

• LOG – LOG function, if None, choose print

• attach – dictionary {nickname: filename}, list of databases to attach

Warning

If the folder does not exist, it will be created

Parameter dbfile can be of type sqlite3.Connection.

source on GitHub

SetBufferInsert(n)

This function offers the possibility to postpone the insertion, they will be processed all at the time during when method commit is called.

Parameters:

n – number of insertion to postpone

source on GitHub

_SQL_conversion_types = {'': <class 'float'>, 'DATETIME': <class 'datetime.datetime'>, 'Decimal': <class 'decimal.Decimal'>, 'FLOAT': <class 'float'>, 'INTEGER': <class 'int'>, 'LONG': <class 'int'>, 'REAL': <class 'float'>, 'TEXT': <class 'str'>, 'VARCHAR': <class 'str'>, 'bigint': <class 'float'>, 'float': <class 'float'>, 'int': <class 'int'>, 'numeric': <class 'float'>, 'smallint': <class 'int'>, 'text': <class 'str'>, 'varchar': <class 'str'>}

__init__(sql_file, engine='SQLite', user=None, password=None, host='localhost', LOG=None, attach=None)

Creates a database object.

Parameters:

• sql_file – database file database file (use :memory: to avoid creating a file and using only memory) it can also contain several files separated by ; name_file ; nickname,second_file ; ...

• engine – SQLite or MySQL (if it is installed), ODBCMSSQL

• user – user if needed

• host – to connect to a MSSQL database

• password – password if needed

• LOG – LOG function, if None, choose print

• attach – dictionary {nickname: filename}, list of databases to attach

Warning

If the folder does not exist, it will be created

Parameter dbfile can be of type sqlite3.Connection.

source on GitHub

_analyse(request, header=False)

Analyses the request does it contains cross product.

Parameters:

• request – request

• header – add a header in the first line

Returns:

None or an iterator

Example:

CROSS f1,f2,f3
PLACE a,b,c
FROM table
ORDER BY f8
WHERE f9 == ' '  -- optional

source on GitHub

class _cross_product_iter(db, request)

Bases: object

Iterator for CROSS.

source on GitHub

__init__(db, request)

__iter__()

iterator

source on GitHub

__next__()

iterator

source on GitHub

_engines = ['SQLite', 'MySQL', 'ODBCMSSQL']

_field_option = ['PRIMARYKEY', 'AUTOINCREMENT', 'AUTOFILL']

_insert_sql(table, insert_values)

Builds the sql for an insert request.

Parameters:

• table – table name

• insert_values – dictionary or a list

Returns:

string

source on GitHub

static _special_function_init_()

_sql_keywords = ['order', 'by', 'select', 'from', 'group', 'where', 'as', 'like', 'upper', 'collapse', 'join', 'union', 'inner', 'default', 'id', 'double', 'text', 'varchar', 'float', 'long', 'Decimal']

add_function(name, nbparam, function)

Adds a function which can be used as any other SQL function (strim, …).

Parameters:

• name – function name (it does not allow _)

• nbparam – number of parameters

• function – function to add

source on GitHub

attach_database(db, alias)

Attaches another database.

Parameters:

• db – database to attach

• alias – database alias

source on GitHub

close()

Closes the database.

source on GitHub

commit()

Calls this function after any insert request.

source on GitHub

connect(odbc_string=None)

Opens a connection to the database.

Parameters:

odbc_string – use a different odbc string

source on GitHub

create_index(indexname, table, columns, unique=False)

Creates an index on a table using some columns.

Parameters:

• indexname – index name

• table – table name

• columns – list of columns

• unique – any value in the columns is unique?

source on GitHub

create_table(table, columns, temporary=False, nolog=False)

Creates a table.

Parameters:

• table – table name

• columns – columns definition, dictionary { key:(column_name,python_type) } if PRIMARYKEY is added, the key is considered as the primary key.

• temporary – if True the table is temporary

• nolog – execute()

Returns:

cursor

Example for columns:

columns = { -1:("key", int, "PRIMARYKEY", "AUTOINCREMENT"),
             0:("name",str), 1:("number", float) }

source on GitHub

execute(request, nolog=False)

Opens a cursor with a query and return it to the user.

Parameters:

• request – SQL request

• nolog – if True, do not log anything

Returns:

cursor

run a select command on a table

t = Database (file)
cur = t.execute ("SELECT * FROM table1 ;")
for f in cur :
    print(f)
cur.close ()

There is another case outside SQL syntax to build cross product. Syntax:

CROSS f1,f2,f3
FROM table
PLACE a,b,c
ORDER BY f8
WHERE f9 == ' '  -- optional

The request must begin by CROSS

source on GitHub

execute_script(script, nolog=True, close=True)

Opens a cursor and run a script.

Parameters:

• script – SQL script

• nolog – if True, do not log anything

• close – close the cursor

Returns:

cursor

source on GitHub

execute_view(request, add_column_name=False, nolog=True)

Opens a cursor with a query and returns the result into a list.

Parameters:

• request – SQL request

• add_column_name – add the column name before the first line

• nolog – if True, do not log anything

Returns:

cursor

Example:

t = Database (file)
view = t.execute_view ("SELECT * FROM table1 ;")

source on GitHub

get_attached_database_list(file=False)

Returns all the attached database (avoid the temporary ones and the main one).

Parameters:

file – ask for file also

Returns:

a list of tuple (alias, file)

source on GitHub

get_column_type(table, column)

Returns the column type of a table.

Parameters:

• table – table name

• column – column name

Returns:

type (python class)

source on GitHub

get_file(attached_db=False)

Gets database file.

Parameters:

attached_db – if True, add the list of attached databases

Returns:

the database file

source on GitHub

get_index_list(attached='main')

Returns the list of indexes.

Parameters:

attached – if main, returns the index for the main database, otherwise, for an attached database

Returns:

list of tuple (index_name, table, sql_request, fields)

source on GitHub

get_index_on_table(table, full=False)

Returns the list of indexes on a specific table.

Parameters:

• table – table

• full – if True returns all fields, otherwise, returns only the index names

Returns:

list of the index on this table

source on GitHub

get_sql_columns(request)

Returns the columns name for a SQL request.

Parameters:

request – SQL request

Returns:

list of columns name

source on GitHub

get_table_columns(table, dictionary=False)

See get_table_columns_list.

Example (dictionary == False):

[('fid', <type 'int'>), ('fidp', <type 'int'>), ('field', <type 'str'>)]

Or (dictionary = True):

{0: ('fid', <type 'int'>), 1: ('fidp', <type 'int'>), 2: ('field', <type 'str'>)}

source on GitHub

get_table_columns_list(table, dictionary=False)

Returns all the columns for a table.

Parameters:

• table – table name

• dictionary – returns the list as a dictionary

Returns:

a list of tuple (column name, Python type)

Example (dictionary == False):

::

[(‘fid’, <type ‘int’>), (‘fidp’, <type ‘int’>), (‘field’, <type ‘str’>)]

Or (dictionary = True):

{0: ('fid', <type 'int'>), 1: ('fidp', <type 'int'>), 2: ('field', <type 'str'>)}

source on GitHub

get_table_list(add_attached=False)

Returns the list of tables.

Parameters:

add_attached – if True, add the list of tables included in the attached databases

Returns:

the table list

source on GitHub

get_table_nb_lines(table)

Returns the number of lines in a table (or number of observations).

Parameters:

table – table name

Returns:

integer

source on GitHub

get_table_nfirst_lines(table, n=1)

Returns the n first lines.

Parameters:

• table – table name

• n – number of asked lines

Returns:

integer

source on GitHub

has_index(index)

Says if the index belongs to the database.

Parameters:

index – index name

Returns:

boolean

source on GitHub

has_table(table)

Says if the table belongs to the database.

Parameters:

table – table name

Returns:

boolean

source on GitHub

static idaytodate(dayint, year, month, day)

Date conversion. Static method to insert in a SQL query.

insert(table, insert_values, cursor=None, nolog=True)

Inserts into a table.

Parameters:

• table – table name

• insert_values – values to insert (a list of dictionary or a single dictionary)

• cursor – if cursor is not None, use it, otherwise creates a new one

• nolog – if True, do not log anything

Returns:

sql request or None if several insertion were sent (result is too long)

Warning

The commit is not done and must be done to stored these modifications.

source on GitHub

isMSSQL()

Says if the syntax is MS SQL Server.

source on GitHub

isMemory()

Tells if the Database takes place in memory (:memory:).

source on GitHub

is_connected()

Says if the database is connected.

Returns:

“_connection” in self.__dict__

source on GitHub

static isectoday(sec)

Date conversion. Static method to insert in a SQL query.

static itimestamp(t, year, month, day)

Date conversion. Static method to insert in a SQL query.

len(table)

Returns the number of lines of table table.

Parameters:

table – table

Returns:

int

source on GitHub

static regex_match(exp, st)

Applies a regular expression. Static method to insert in a SQL query.

remove_table(table)

Removes a table.

Parameters:

table – table name

Returns:

return a cursor

source on GitHub

static string_to_date(s)

Date conversion. Static method to insert in a SQL query.

update(table, key, value, values)

Updates some values WHERE key=value.

Parameters:

• table – table to update

• key – key

• value – WHERE key = value

• values – values to be updated

Warning

The commit is not done and must be done to stored these modifications.

source on GitHub