module sql.database_core

Inheritance diagram of pyensae.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)[source]

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)[source]

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)[source]

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)[source]

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)[source]

Bases: object

Iterator for CROSS.

source on GitHub

__init__(db, request)[source]

Initialize self. See help(type(self)) for accurate signature.

__iter__()[source]

iterator

source on GitHub

__next__()[source]

iterator

source on GitHub

_engines = ['SQLite', 'MySQL', 'ODBCMSSQL']
_field_option = ['PRIMARYKEY', 'AUTOINCREMENT', 'AUTOFILL']
_insert_sql(table, insert_values)[source]

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_()[source]
_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)[source]

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)[source]

Attaches another database.

Parameters:
  • db – database to attach
  • alias – database alias

source on GitHub

close()[source]

Closes the database.

source on GitHub

commit()[source]

Calls this function after any insert request.

source on GitHub

connect(odbc_string=None)[source]

Opens a connection to the database.

Parameters:odbc_string – use a different odbc string

source on GitHub

create_index(indexname, table, columns, unique=False)[source]

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)[source]

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
  • nologexecute()
Returns:

cursor

Example for columns:

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

source on GitHub

execute(request, nolog=False)[source]

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)[source]

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)[source]

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)[source]

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)[source]

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)[source]

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')[source]

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)[source]

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)[source]

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)[source]

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)[source]

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)[source]

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)[source]

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)[source]

Returns the n first lines.

Parameters:
  • table – table name
  • n – number of asked lines
Returns:

integer

source on GitHub

has_index(index)[source]

Says if the index belongs to the database.

Parameters:index – index name
Returns:boolean

source on GitHub

has_table(table)[source]

Says if the table belongs to the database.

Parameters:table – table name
Returns:boolean

source on GitHub

static idaytodate(dayint, year, month, day)[source]

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

insert(table, insert_values, cursor=None, nolog=True)[source]

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()[source]

Says if the syntax is MS SQL Server.

source on GitHub

isMemory()[source]

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

source on GitHub

is_connected()[source]

Says if the database is connected.

Returns:“_connection” in self.__dict__

source on GitHub

static isectoday(sec)[source]

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

static itimestamp(t, year, month, day)[source]

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

len(table)[source]

Returns the number of lines of table table.

Parameters:table – table
Returns:int

source on GitHub

static regex_match(exp, st)[source]

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

remove_table(table)[source]

Removes a table.

Parameters:table – table name
Returns:return a cursor

source on GitHub

static string_to_date(s)[source]

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

update(table, key, value, values)[source]

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