module `sql.database_core`¶

Short summary¶

module pyensae.sql.database_core

Database

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: pyensae.sql.database_core2.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)¶: Initialize self. See help(type(self)) for accurate signature.

__iter__()¶

iterator

source on GitHub

__next__()¶

iterator

source on GitHub

close()¶

is_working()¶

_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

module `sql.database_core`¶

Short summary¶

Classes¶

Static Methods¶

Methods¶

Documentation¶

Links

Contents

Information

Related Topics

This Page

module sql.database_core¶

Short summary¶

Classes¶

Static Methods¶

Methods¶

Documentation¶

module `sql.database_core`¶