MercurySQL.gensql

Operate SQL in a more pythonic way.

This module contains the implementation of python to SQL. It provides a high-level API for operating SQL databases, which allows you to create tables, insert rows, execute queries, etc. in a more pythonic way.

For each sql engine (e.g. sqlite, mysql, …), a Driver is needed. For more details about the Driver, please refer to the MercurySQL.drivers (Drivers) section.

Warning

This module provides built-in support for anti-SQL-injection. That is, all the query values are replaced with safe queries (’?’ or else).

However, the inspection of the table name & column name is implemented by Driver s. So becareful especially when using third-party drivers.

It is recommended to hard-code the DB name, table name & column name in your code, instead of using user inputs directly.

Advanced APIs

class MercurySQL.gensql.DataBase(db_name: str, driver=None, **kwargs)

Select/Create/Connect a SQL database.

The instance of this class represents a SQL database, and provides methods for creating tables, executing SQL, and retrieving table objects.

__delitem__(key: str) → None

Delete a table from the database, same as deleteTable().

Parameters:: key (str) – The name of the table.

Example Usage:

db = DataBase('test.db')
del db['test']  # same as db.deleteTable('test')

__getitem__(key: str) → Table

Create / Choose a table from the database.

Parameters:: key (str) – The name of the table.
Returns:: A Table object.
Return type:: Table

Example Usage:

db = DataBase('test.db')
table = db['test']

How It Works:

if exists, return the existing table (the OLD Table Object)
if not exists, create a new table by createTable().

Note

The only difference between __getitem__() and createTable() is that __getitem__() will return the OLD Table Object if exists, while createTable() will return a NEW Table Object.

See the “How It Works” section in `createTable()` for more details.

__init__(db_name: str, driver=None, **kwargs)

Create a new database object.

Parameters:: db_name (str) – The name of the database.

Example Usage:

db = DataBase('test.db')

How It Works:

start a connection to the SQL database, using the driver specified by driver parameter or set_driver() method.
create a new CommandQueue to handle all SQL commands in this DB.
gather all infomations of the database, for further usages.

createTable(*table_names: str, force: bool = False, template=None) → Table

create a table in the database.

Parameters:

table_names (str) – The name of the table.
force (bool) – Allow to return an existing table.

Returns:

A Table object.

Return type:

Table

Example Usage:

db = DataBase('test.db')
table = db.createTable('test')

How It Works:

create table(s) if not exists
if already exists, return the existing table(s) in a NEW Table Object.
if exists and force set to False, raise an Exception.

deleteTable(*table_names: str) → None

delete a table in the database.

Parameters:: table_names (str) – The name of the table.

Example Usage:

db = DataBase('test.db')
db.deleteTable('test')

How It Works:

delete table(s) if exists
raise an Exception if not exists.

do(*sql: str, paras: List[tuple] = [])

Execute a sql command on the database.

Parameters:

sql (str) – The sql command(s).
paras (List[tuple]) – The parameters for the sql command(s).

Returns:

The cursor of the database.

Return type:

Driver.Cursor

Example Usage:

db = DataBase('test.db')
db.do("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)")
db.do("INSERT INTO test (name) VALUES (?)", paras=[('Bernie',)])
db.do(
    "SQL1",
    "SQL2",
    paras=[
           (paras1, paras2),
           (paras3, paras4)
    ])

How It Works:

execute sql commands one by one, with parameters
commit after all commands are executed

setTemplate(template: dict, **kwargs) → None

Set the template, so new table’s structure will be setted to this template. You can specifd the template by using template=… parameter into db.createTable() method. Or set it after the table is created, using table.struct().

Parameters:

template (dict) – The template to set.
**kwargs – The parameters for the template. E.g., primaryKey=’id’.

Example Usage:

db = DataBase('test.db')
db.setTemplate({
    'id': int,
    'name': str
})
table = db.createTable('test')

class MercurySQL.gensql.Table(db, table_name: str)

Represents a table in a SQL database. Can be created by db.createTable().

It provides methods for adding columns, deleting columns, inserting rows, and executing queries, etc.

__delitem__(key: str) → None

Delete an existing column in this table. An Exception will be raised if column not exist. Same as delColumn().

Parameters:: key (str) – The name of the column.

Example Usage:

table = db['test']
del table['name']  # same as table.delColumn('name')

__getitem__(key: str) → Exp

get a column from the table, mainly used to construct query.

Parameters:: key (str) – The name of the column.
Returns:: An Exp object.

Example Usage:

Construct a query expression.

table = db['test']
exp = table['id'] == 1
res = table.select(exp)

Get the definition of a column.

table = db['test']
print(table['id'])  # INTEGER PRIMARY KEY

How It Works:

return an Exp object with table and _str attributes setted.
table is used to execute the query using list(…), even if table not specified. [NOT RECOMMENDED]
_str is used to print the definition of a column using str(…).
raise an Exception if column not exists.

__init__(db, table_name: str)

Initialize a table object.

Parameters:

db (DataBase) – The database object.
table_name (str) – The name of the table.

Example Usage:

db = DataBase('test.db')
table = db['test']

How It Works:

gather all infomations of the table, including columns name and their types.
set isEmpty to True if the table doesn’t have any columns. This variable will effect how the newColumn() method works.

__setitem__(key: str, value: Any) → None

Create a new column in the table.

Parameters:

key (str) – The name of the column.
value (Any. Can be single un-parsed type (e.g. int, str(5), …) OR a tuple in the form of (type, options). (e.g. (int, ‘primary key’, ‘auto increment’))) – The type of the column.

Options in value:

‘primary key’ — Set the column as the primary key of the table.
‘auto increment’ — Set the column as an auto-incremented column.

Example Usage:

table = db['test']
table['name'] = str
table['id'] = int, 'primary key', 'auto increment'

How It Works:

get options from value if it has parameters (Judge it by whether it’s a tuple, so you can use it as the L3 of example showed).
Actually create the column, using newColumn() method.

insert(_Table__auto=False, **kwargs) → None

Insert a row into the table.

Parameters:

__auto (bool) – Whether to update the row if it already exists.
**kwargs – The data to insert.

Example Usage:

table = db['test']
table.insert(id=1, name='Bernie', age=15, __auto=True)

newColumn(name: str, type_: Any, force=False, primaryKey=False, autoIncrement=False) → None

Add a new column to the table.

Parameters:

name (str) – The name of the column.
type (Any) – The type of the column.
force (bool) – Allow to skip when processing an existing column.
primaryKey (bool) – Whether the column is a primary key.
autoIncrement (bool) – Whether the column is auto-incremented.

Example Usage:

table = db['test']
table.newColumn('name', str)
table.newColumn('id', int, primaryKey=True)

How It Works:

Create a new table with this column, if table is empty.
Add the column to an existing table.
Set as the primary key column if primaryKey is True.
Record its name and type in self.columns and self.columnsType.

select(exp: Exp | None = None, selection: str = '*') → QueryResult

Select data from the table.

Parameters:

exp (Exp) – The query expression.
selection (str) – The columns to select, default is ‘*’(all columns).

Returns:

A list of data.

Return type:

list

Example Usage:

table = db['test']
table.select(table['id'] == 1)  # select all columns where id = 1

How It Works:

Construct a QueryResult object, which will execute the query whthin it’s QueryResult.__init__() method.
return a QueryResult object with results.

setPrimaryKey(keyname: str, keytype: str) → None

Set a column as the primary key of the table.

Parameters:

keyname (str) – The name of the column.
keytype (str) – The type of the column, been parsed by TypeParser.

Example Usage:

table = db['test']
table.setPrimaryKey('id')

struct(columns: dict, skipError=True, primaryKey: str | None = None, autoIncrement=False, force=True) → None

Set the structure of the table.

Parameters:

columns (dict) – The structure of the table.
skipError (bool) – Allow to skip when column exist and have the same type at the same time.
primaryKey (str) – The primary key of the table.

Example Usage:

table = db['test']
table.struct({
    'id': int,
    'name': str
}, primaryKey='id')

update(exp: Exp, data: dict = {}, **kwargs) → None

Update the table.

Note

We Recommend to use (tb[‘id’] == 1).update(…) then this function, for cleaner code style.

Warning

The argument of Exp.update(…) doesn’t supports kwargs format (name=bernie). It only supports dict format ({“name”: “bernie”}). We recommended to use ‘dict format’ in both table.update and Exp.update.

Parameters:

exp (Exp) – The query expression.
data (dict) – The data to update.

Example Usage:

table = db['test']
table.update(table['id'] == 1, name='Bernie', age=15)
# OR
(table['id'] == 1).update({"name": "Bernie", "age": 15})    # recommended

class MercurySQL.gensql.Exp(o1, op='', o2='', **kwargs)

Subclass of BasicExp, representing a query expression, which can be used to construct complex queries.

Supported Operations: * ==: equality * !=: inequality * <: less than * <=: less than or equal to * >: greater than * >=: greater than or equal to * .in_(): in * .like(): like * &: and * |: or * not: not

Note

you should mind the priority of operations when using some of the operators, especially & and |.

__eq__(_Exp__value: Exp | int | str) → Exp: Return self==value.

__ge__(_Exp__value: Exp | int | str) → Exp: Return self>=value.

__gt__(_Exp__value: Exp | int | str) → Exp: Return self>value.

__hash__ = None

__init__(o1, op='', o2='', **kwargs): Acceptable addition attributes: - table: Table …………………. the table to search - _str: str ……………………. the string to show when print the object

__iter__(): use magic method __iter__ to search.

__le__(_Exp__value: Exp | int | str) → Exp: Return self<=value.

__lt__(_Exp__value: Exp | int | str) → Exp: Return self<value.

__ne__(_Exp__value: Exp | int | str) → Exp: Return self!=value.

__str__(): Return str(self).

static convert(value: Any) → Tuple[str, tuple]

Convert value into a form that can be used in a SQL query.

Parameters:: value (Any) – The value to convert.
Returns:: The converted value in the form of (sql_command, paras).
Return type:: Tuple[str, tuple]

Example Usage:

BasicExp.convert(1)                 # ('?', (1,))
BasicExp.convert('Bernie')          # ('?', ('Bernie',))
BasicExp.convert(None)              # ('', ())
BasicExp.convert(Exp('id') == 1)    # ('(id = ?)', (1,))
...

delete(table=None) → None: Execute delete.

formula() → Tuple[str, tuple]: Return the formula of the expression in the form of (sql_command, paras).

gen_formula() → None: [Helper] Generate the formula of the expression.

Note

You can also construct a formula by yourself, just set the _formula attribute to a tuple in the form of (sql_command, paras).

query(table=None, select='*') → list: Execute query.

update(data, /, table=None) → None: Execute update.

Low-level APIs

class MercurySQL.gensql.exp.BasicExp(exp1, oper='', exp2=None)

Basic class of Exp, representing a basic query expression that can be used to construct complex queries.

__dict__ = mappingproxy({'__module__': 'MercurySQL.gensql.exp', '__init__': <function BasicExp.__init__>, 'convert': <staticmethod object>, 'gen_formula': <function BasicExp.gen_formula>, 'formula': <function BasicExp.formula>, '__dict__': <attribute '__dict__' of 'BasicExp' objects>, '__weakref__': <attribute '__weakref__' of 'BasicExp' objects>, '__doc__': None, '__annotations__': {}})

__init__(exp1, oper='', exp2=None)

static convert(value: Any) → Tuple[str, tuple]

Convert value into a form that can be used in a SQL query.

Parameters:: value (Any) – The value to convert.
Returns:: The converted value in the form of (sql_command, paras).
Return type:: Tuple[str, tuple]

Example Usage:

BasicExp.convert(1)                 # ('?', (1,))
BasicExp.convert('Bernie')          # ('?', ('Bernie',))
BasicExp.convert(None)              # ('', ())
BasicExp.convert(Exp('id') == 1)    # ('(id = ?)', (1,))
...

formula() → Tuple[str, tuple]: Return the formula of the expression in the form of (sql_command, paras).

gen_formula() → None: [Helper] Generate the formula of the expression.

Note

You can also construct a formula by yourself, just set the _formula attribute to a tuple in the form of (sql_command, paras).