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

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

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

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

usual

source on GitHub

_build_join(db, fas, select, n)[source]

see build_sql

Parameters
  • db – database

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

  • select – condition

  • n – node

source on GitHub

_build_predecessor_prefix()[source]

private method

source on GitHub

_build_select(db, fas, where, tfrom=None)[source]

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

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

return the SQL select on the table

Parameters

db – database

Returns

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

source on GitHub

append(n)[source]

add a successor

Parameters

n – new successor

source on GitHub

build_sql(db)[source]

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

remove all sql,fields members

source on GitHub

get_nb_successor()[source]
Returns

the number of successors

source on GitHub

__init__()[source]

constructor

source on GitHub

_build_where_condition(where, add_keyword_where=True)[source]

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

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

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

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