module sql.database_join_group
¶
Short summary¶
module pyensae.sql.database_join_group
Database
Classes¶
class |
truncated documentation |
---|---|
This class is not neant to be working alone. It contains functions for a database able to build SQL requests for … |
Methods¶
method |
truncated documentation |
---|---|
constructor |
|
builds a where condition (including the WHERE keyword) |
|
create a SQL request to compute an histogram |
|
create a SQL inner join request |
|
create several SQL inner join requests (included into each others) |
Documentation¶
- class pyensae.sql.database_join_group.DatabaseJoinGroup¶
Bases:
object
This class is not neant to be working alone. It contains functions for a database able to build SQL requests for frequent needs such as join SQL requests.
Database
constructor
- class JoinTreeNode(table, parent_key=None, key=None, where=None, prefix=None, avoid_prefix=False)¶
Bases:
object
define a node meant to be included in a graph to define a big join
constructor this node defines a join on two tables (parent_table, table) on two keys (parent_key, key). The keys can be tuple or string.
- Parameters:
table – table name
parent_key – None if it is the root
key – None if it is the root
where –
clause where where is a where clause defined as a dictionary: example:
{ "field": ("==", value), ("table","field"): (">=", value) }
You may add field not connected to a table, they will not taken into account.
prefix – add a prefix, avoid different fields collide
avoid_prefix – avoid using a prefix to build SQL queries, use syntax
( ... ) AS ...
- __init__(table, parent_key=None, key=None, where=None, prefix=None, avoid_prefix=False)¶
constructor this node defines a join on two tables (parent_table, table) on two keys (parent_key, key). The keys can be tuple or string.
- Parameters:
table – table name
parent_key – None if it is the root
key – None if it is the root
where –
clause where where is a where clause defined as a dictionary: example:
{ "field": ("==", value), ("table","field"): (">=", value) }
You may add field not connected to a table, they will not taken into account.
prefix – add a prefix, avoid different fields collide
avoid_prefix – avoid using a prefix to build SQL queries, use syntax
( ... ) AS ...
- __str__()¶
usual
- _build_join(db, fas, select, n)¶
see
build_sql
- Parameters:
db – database
fas – list [(new_name, table, name)]
select – condition
n – node
- _build_predecessor_prefix()¶
private method
- _build_select(db, fas, where, tfrom=None)¶
build a select SQL request
- Parameters:
db – database
fas – list of tuple table,f,fas
where – where clause
tfrom – from clause, if None, –> self.table
- Returns:
string
- _find_in_fas(fas, a, b)¶
find a,b in fas (column 1 and 2)
- Parameters:
fas – list [ (new_name, table, name)]
a – table name
b – name
- _in_select(db)¶
return the SQL select on the table
- Parameters:
db – database
- Returns:
list of tuple (fieldas, table, field, which), where
- append(n)¶
add a successor
- Parameters:
n – new successor
- build_sql(db)¶
build the sql request
- Parameters:
db – database
- The function adds two attributes:
SELECT: sql request for a node
FIELDS: list of [ (final_name, table, original_name)
- check_prefix(nb=-1)¶
- Parameters:
nb – index of this node is the predecessor list of successor
check the prefixes, all one if there is none
- clean()¶
remove all sql,fields members
- get_nb_successor()¶
- Returns:
the number of successors
- __init__()¶
constructor
- _build_where_condition(where, add_keyword_where=True)¶
builds a where condition (including the WHERE keyword)
- Parameters:
where –
condition where to interpret:
{ "field": ("==", value) }
add_keyword_where – add the keyword where ?
- Returns:
sql syntax
Todo
This function should deal with a tree to express AND and OR logical links. (However, this probably won’t happen.)
- histogram(table, columns, col_sums=None, values=None, sql_add=None, execute=False, created_table=None, new_column='histogram', nolog=False)¶
create a SQL request to compute an histogram
- Parameters:
table – table
columns – column or columns (in a tuple) to be histogrammized
col_sums – candidate columns for a sum
values –
specific values, several cases: - if None: does a GROUP BY - if dictionary of tuple:
{'cat1':('val1', 'val2', ...) }
then groups together several values into one category
if list of float: does an histogram on a real variable
new_column – name of the new column
sql_add – string to be added at the end of the SQL request
execute – if True, execute the request
created_table – the histogram can be stored into a table whose name is given by this parameter
nolog – if True, do not log the query
- Returns:
SQL request
- inner_join(table1, table2, field1, field2=None, where=None, execute=False, create_index=True, created_table=None, prefix='', duplicate_column=True, prefix_all='', order=None, unique=True, params=None, nolog=True)¶
create a SQL inner join request
- Parameters:
table1 – first table
table2 – second table
field1 – inner join on field1 from table1
field2 – inner join on field2 from table2 (if None –> field2 = field1
where – where clause (if None, do not add it), dictionary or string
execute – if True, execute the query
create_index – if True, creates an index on the second table if it does not exist: it accelerates the inner join
created_table – if execute is True, you must specify a table name to be created
prefix – prefix for fields from the second table
duplicate_column – do not include columns from the second table if their name is already in the first one
prefix_all – prefix for all fields
order – order clause, list of 2-tuple (column, way) way is None or DESC
unique – unique or not
params – special parameters for inner_joins method
nolog – if True, do not log the query, otherwise, skip that part
- Returns:
SQL request, list of fields (“source”, “new name”)
- inner_joins(root, execute=False, create_index=False, created_table=None, duplicate_column=True, order=None, unique=False, distinct=False, fields=None, nolog=True)¶
create several SQL inner join requests (included into each others)
- Parameters:
root – JoinTreeNode (the root)
execute – if True, execute the query
create_index – if True, creates an index on the second table if it does not exist: it accelerates the inner join
created_table – if execute is True, you must specify a table name to be created
duplicate_column – do not include columns from the second table if their name is already in the first one
order – order clause, list of 2-tuple (column, way) way is None or DESC
unique – unique or not
distinct – add the keyword DISTINCT
fields – restriction to fields given by fields or no restriction if None
nolog – if True, do not log the query
- Returns:
SQL request, list of fields (“source”, “new name”)
Warning
Some options are not available yet: - create_index True - duplicate_column False - order != [] - unique True
Todo
Three tasks (however, this won’t probably happen) - Finish The function inner_joins (parameters create_index, duplicate_column, order, unique). - Improve the handling of keyword DISTINCT - Handle keyword fields