module sql.database_join_group

Inheritance diagram of pyensae.sql.database_join_group

Short summary

module pyensae.sql.database_join_group

Database

source on GitHub

Classes

class

truncated documentation

DatabaseJoinGroup

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

__init__

constructor

_build_where_condition

builds a where condition (including the WHERE keyword)

histogram

create a SQL request to compute an histogram

inner_join

create a SQL inner join request

inner_joins

create several SQL inner join requests (included into each others)

Documentation

Database

source on GitHub

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

source on GitHub

constructor

source on GitHub

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

source on GitHub

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 ...

source on GitHub

__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 ...

source on GitHub

__str__()

usual

source on GitHub

_build_join(db, fas, select, n)

see build_sql

Parameters:
  • db – database

  • fas – list [(new_name, table, name)]

  • select – condition

  • n – node

source on GitHub

_build_predecessor_prefix()

private method

source on GitHub

_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

source on GitHub

_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

source on GitHub

_in_select(db)

return the SQL select on the table

Parameters:

db – database

Returns:

list of tuple (fieldas, table, field, which), where

source on GitHub

append(n)

add a successor

Parameters:

n – new successor

source on GitHub

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)

source on GitHub

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

source on GitHub

clean()

remove all sql,fields members

source on GitHub

get_nb_successor()
Returns:

the number of successors

source on GitHub

__init__()

constructor

source on GitHub

_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.)

source on GitHub

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

source on GitHub

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”)

source on GitHub

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

source on GitHub