Skip to content

Latest commit

 

History

History
471 lines (251 loc) · 18.2 KB

readme.md

File metadata and controls

471 lines (251 loc) · 18.2 KB
Raw

Welcome to the documentation for the SQLBuilder Package!

This module procedurally generates parameterized SQL statements from structured Python data. It is intended for use with Python DB API compatible libraries. It currently supports SELECT, UPDATE, INSERT INTO, and DELETE statements. See below for usage details and examples.

To install, open SQLBuilder directory in your terminal and run setup.py install. The module is written in pure Python with no outside dependencies for version 3.7.3.

General example of syntactic structure for this module

import sqlbuilder.builders as builders

condition = builders.Condition(args,kwargs)
select = builders.Select(args,kwargs)
update = builders.Update(args,kwargs)
insertInto = builders.InsertInto(args,kwargs)
delete = builders.Delete(args,kwargs)
class sqlbuilder.builders.Statement(type, table, condition=None)

Bases: object

This is the superclass used as a basis for our statement classes. Users should not instantiate this class directly. Rather, one of the more specific subclasses should be used.

add_condition(condition)

Attaches a Condition object to a previously instantiated Statement.

Parameters

condition – (Condition) object that contains specifications for fields that should be operated on by the Statements they are associated with.

class sqlbuilder.builders.Select(table, fields, condition=None_)

Bases: sqlbuilder.builders.Statement

This is the class used to construct SELECT statements.

Each instance contains a table name and list/tuple of fields to select data from. Optionally, they may also include a Condition object specifying which data should be selected. The class also has an inner join method which allows users to select data from two separate tables with one command.

E.g. to select the fields ‘id’, ‘age’, and ‘gender’ from table ‘demographics’ where the participant’s age is higher than 20:

select = Select('demographics', ['id','age','gender'],
                condition = Condition([{'age':('>',20)}]))
input = select.generate()
crsr.execute(*input)
output = crsr.fetchall()
TODO:

  1. Expand inner_join functionality to support selecting data from an arbitrary number of tables.

  2. Include support for other types of joins.

generate()

Generates parameterized sql statement and list of params for use with a Python DB API compatible library.

Returns

(str) SQL statement used to select specified data.

(list) containing parameters to be supplied with the accompanying statement.

inner_join(statementToJoin, joinKey)

Takes as input a second select statement and creates an inner join by combining them.

Parameters

  • statementToJoin – (Select) a second Select object to construct an Inner Join with.

  • joinKey – (list/tuple) a 3-element list used to specify how records from the two tables should be unified.

The argument (‘id’,’=’,’id’), for example, would correspond to FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id

Returns

(str) SQL statement that selects all specified fields from both tables, conjoined via the joinKey, which satisfy all

conditions of both statements.

class sqlbuilder.builders.Update(table, fields, values, condition=None )

Bases: sqlbuilder.builders.Statement

This is the class used to construct UPDATE statements.

Each instance contains a table, set of fields, set of values, and optionally, a condition.

E.g. to loop through an array of tuples containing ids, ages, and genders of participants and update your database, you could do the following

for i in demoData:
    update = Update('demographics',['age','gender'], [i[1],i[2]], condition = Condition([{'id':('=',i[0])}]))
    input = update.generate()
    crsr.execute(*input)
crsr.execute('commit;')
no_condition_warning()

If an Update object is instantiated without a condition this warning is given. This is a safe guard to prevent users from accidentally updating every record in their table if they did not mean to do so.

generate()
Returns

(str) SQL statement used to update specified data. (list) containing parameters to be supplied with accompanying statement.

class sqlbuilder.builders.InsertInto(table, fields, values)

Bases: sqlbuilder.builders.Statement

This is the class used to construct INSERT INTO statements.

Each instance contains a table name, array with fields to populate when creating records, and respective values to populate fields with.

E.g. to loop through an array of tuples containing ids, ages, and genders of participants and insert records into your database, you could do the following

for i in demoData:
    insert = InsertInto('demographics',['id','age','gender'],[i[0],i[1],i[2])
    input = insert.generate()
    crsr.execute(*input)
crsr.execute('commit;')
generate()
Returns

(str) SQL statement used to update specified data. (list) containing parameters to be supplied with accompanying statement.

class sqlbuilder.builders.Delete(table, condition=None_)

Bases: sqlbuilder.builders.Statement

This is the class used to construct DELETE statements.

Each instance contains a table name and, optionally, a condition

E.g. to delete all records from the table “demographics” with IDs below 20, you could do

delete = Delete('demographics',condition = Condition([{'id':('<',20)}]))
input = delete.generate()
crsr.execute(*input)
crsr.execute('commit;')
no_condition_warning()

If a Delete object is instantiated without a condition this warning is given. This is a safe guard to prevent users from accidentally deleting every record in their table if they did not mean to do so.

generate()
Returns

(str) SQL statement used to delete specified data. (list) containing parameters to be supplied with accompanying statement.

class sqlbuilder.builders.Condition(conditionSeed)

Bases: object

This class is used to construct the WHERE clauses for our statements. These are created through condition seeds. Currently, the seeds are in the form of lists of dicts. The key:value format for these dicts is FieldName: (Logical Operator, Value). This corresponds to one logical condition. A dict represents a set of logical tests which all must be true together to return a True value, i.e. they are “AND”-separated tests. Only one of the dicts within a list need to be evaluted as true for a given condition to be met, i.e. the list items themselves are “OR”-separated logical tests. Therefore, if I were to declare an instance with the following parameters:

myCondition = builder.Condition([
                                {'Age': ('>', 10),
                                'Id': ('<', 50)}
                                ,
                                {'Race':('=','Caucasian')}
                                ])

The string representation would be ‘(Age > 10 AND Id < 50) OR (Race = ‘Caucasian’)’

NOTE: If you actually run this code, the output you will get from the Condition’s generate method will be ‘(Age > ? AND Id < ?) OR (Race = ?)’. This is because the module generates parameterized statements. If you used the condition in conjunction with a Statement object to form a statement, you would be given the values (10, 50, and ‘Caucasian’) in a list to use as parameters.

initialize_params()

Method used by Statement objects to retrieve params from their Conditions.

Returns

(List) containing parameters to be piped into DB.

generate()

Method used to generate string representation of clause :return: (str) WHERE clause generated from condition seed

merge(conditionToMerge)

Method used for inner joins when both statements have conditions. Appends table names to condition fields to avoid ambiguity and merges both sets of conditions so that both must be true for data to be selected from resultant statement.

Parameters

conditionToMerge – (Condition) object to merge with.

Returns

(str) Where clause to be used with joined statements

©2019, Dillon Smith.