module sql.database_core
¶
Classes¶
class |
truncated documentation |
---|---|
Core methods for class |
Static Methods¶
staticmethod |
truncated documentation |
---|---|
Date conversion. Static method to insert in a SQL query. |
|
Date conversion. Static method to insert in a SQL query. |
|
Date conversion. Static method to insert in a SQL query. |
|
Applies a regular expression. Static method to insert in a SQL query. |
|
Date conversion. Static method to insert in a SQL query. |
Methods¶
method |
truncated documentation |
---|---|
Creates a database object. |
|
Analyses the request does it contains cross product. |
|
Builds the sql for an insert request. |
|
Adds a function which can be used as any other SQL function (strim, …). |
|
Attaches another database. |
|
Closes the database. |
|
Calls this function after any insert request. |
|
Opens a connection to the database. |
|
Creates an index on a table using some columns. |
|
Creates a table. |
|
Opens a cursor with a query and return it to the user. |
|
Opens a cursor and run a script. |
|
Opens a cursor with a query and returns the result into a list. |
|
Returns all the attached database (avoid the temporary ones and the main one). |
|
Returns the column type of a table. |
|
Gets database file. |
|
Returns the list of indexes. |
|
Returns the list of indexes on a specific table. |
|
Returns the columns name for a SQL request. |
|
See |
|
Returns all the columns for a table. |
|
Returns the list of tables. |
|
Returns the number of lines in a table (or number of observations). |
|
Returns the n first lines. |
|
Says if the index belongs to the database. |
|
Says if the table belongs to the database. |
|
Inserts into a table. |
|
Says if the database is connected. |
|
Tells if the Database takes place in memory ( |
|
Says if the syntax is MS SQL Server. |
|
Returns the number of lines of table |
|
Removes a table. |
|
This function offers the possibility to postpone the insertion, they will be processed all at the time during when … |
|
Updates some values |
Documentation¶
-
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.
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.
-
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
-
_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.
-
_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
-
class
_cross_product_iter
(db, request)¶ Bases:
object
Iterator for CROSS.
-
__init__
(db, request)¶ Initialize self. See help(type(self)) for accurate signature.
-
__iter__
()¶ iterator
-
__next__
()¶ iterator
-
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
-
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
-
attach_database
(db, alias)¶ Attaches another database.
- Parameters
db – database to attach
alias – database alias
-
close
()¶ Closes the database.
-
commit
()¶ Calls this function after any insert request.
-
connect
(odbc_string=None)¶ Opens a connection to the database.
- Parameters
odbc_string – use a different odbc string
-
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?
-
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) }
-
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
-
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
-
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 ;")
-
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)
-
get_column_type
(table, column)¶ Returns the column type of a table.
- Parameters
table – table name
column – column name
- Returns
type (python class)
-
get_file
(attached_db=False)¶ Gets database file.
- Parameters
attached_db – if True, add the list of attached databases
- Returns
the database file
-
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)
-
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
-
get_sql_columns
(request)¶ Returns the columns name for a SQL request.
- Parameters
request – SQL request
- Returns
list of columns name
-
get_table_columns
(table, dictionary=False)¶ -
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'>)}
-
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'>)}
-
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
-
get_table_nb_lines
(table)¶ Returns the number of lines in a table (or number of observations).
- Parameters
table – table name
- Returns
integer
-
get_table_nfirst_lines
(table, n=1)¶ Returns the n first lines.
- Parameters
table – table name
n – number of asked lines
- Returns
integer
-
has_index
(index)¶ Says if the index belongs to the database.
- Parameters
index – index name
- Returns
boolean
-
has_table
(table)¶ Says if the table belongs to the database.
- Parameters
table – table name
- Returns
boolean
-
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.
-
isMSSQL
()¶ Says if the syntax is MS SQL Server.
-
isMemory
()¶ Tells if the Database takes place in memory (
:memory:
).
-
is_connected
()¶ Says if the database is connected.
- Returns
“_connection” in self.__dict__
-
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
-
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
-
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.