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