[Driver]: Base Driver

Warning

All Drivers MUST be defined as subclasses of this BaseDriver class.

Version & Informations

BaseDriver.version = '0.0.0'

Dependencies

None

Subclasses / Methods

BaseDriver.payload = '?'

class MercurySQL.drivers.base.BaseDriver.Conn

Note

The Conn class here is only used for type hints, indicating the return type or parameter type of functions. In practice, instances of the Cursor class are not directly created or called.

A Connection object for database operations.

Also, a SQL library will provide these methods for interacting with databases. E.g., sqlite3.Connection or pymysql.connections.Connection.

close() → None: This method is used to close the connection to the database.

commit() → None: This method is used to commit (it means ‘save’) the changes to the database.

cursor() → Cursor

This method is used to create a cursor object for database operations.

Returns:: A cursor object for database operations.

class MercurySQL.drivers.base.BaseDriver.Cursor

Note

The Cursor class here is only used for type hints, indicating the return type or parameter type of functions. In practice, instances of the Cursor class are not directly created or called.

A cursor object for database operations. Usually, SQL libraries will provide these methods for executing SQL queries and interacting with databases. E.g., sqlite3.Cursor or pymysql.cursors.Cursor.

execute(sql: str, paras: List[tuple] = []) → Cursor

This method is used to execute SQL queries.

Parameters:

sql – The SQL query to be executed.
paras – The parameters of the SQL query.

Returns:

The cursor object itself.

fetchall() → List[tuple]

This method is used to fetch all rows of the query result.

Returns:: All rows of the query result.

fetchone() → tuple

This method is used to fetch the next row of the query result.

Returns:: The next row of the query result.

class MercurySQL.drivers.base.BaseDriver.APIs

APIs is a class that provides SQL statements for database operations.

Warning

You must implement all the methods in this class.

Every definition will be followed by an example of it’s return value (in SQLite).

class gensql

APIs in this class will return a SQL code for database operations. Generally, these returned codes will be executed DIRECTLY.

static add_column(table_name: str, column_name: str, column_type: str) → str | List[str]

Add a column to a table.

Parameters:

table_name (str) – The name of the table to be added.
column_name (str) – The name of the column to be added.
column_type (str) – The type of the column to be added (has been parsed by TypeParser).

Example Implementation (SQLite):

return f"""
    ALTER TABLE {table_name} ADD COLUMN {column_name} {column_type}
"""

static create_table_if_not_exists(table_name: str, column_name: str, column_type: str, primaryKey=False, autoIncrement=False) → str | List[str]

Create a table if it does not exist.

Parameters:

table_name (str) – The name of the table to be created.
column_name (str) – The name of the column to be created.
column_type (str) – The type of the column to be created.

Example Implementation (SQLite):

return f"""
    CREATE TABLE IF NOT EXISTS {table_name} ({column_name} {column_type} {'PRIMARY KEY' if primaryKey else ''})
"""

static delete(table_name: str, condition: str) → str

Delete the rows in specified table, which matches the condition.

Parameters:

table_name (str) – The name of the table to be deleted.
condition (str) – The condition of the delete, in the general SQL format, generated by Exp.

Example Implementation (SQLite):

return f"DELETE FROM {table_name} WHERE {condition}"

static drop_column(table_name: str, column_name: str) → str | List[str]

Drop (Delete) a column from a table.

Parameters:

table_name (str) – The name of the target table.
column_name (str) – The name of the column to be dropped.

Example Implementation (SQLite):

return f"""
    ALTER TABLE {table_name} DROP COLUMN {column_name}
"""

static drop_table(table_name: str) → str

Drop (Delete) a table.

Parameters:: table_name (str) – The name of the table to be dropped.
Returns:: The SQL statement to drop the table.

Example Implementation (SQLite):

return f"DROP TABLE {table_name}"

static get_all_columns(table_name: str) → str

Get all column’s informations in the table.

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

Example Implementation (SQLite):

return f"PRAGMA table_info({table_name});"

static get_all_tables() → str

Get all table’s informations in the database.

Returns:: The SQL statement to get all table’s informations in the database.

Example Implementation (SQLite):

return "SELECT name FROM sqlite_master WHERE type='table';"

static insert(table_name: str, columns: str, values: str) → str

Insert a row into the specified table.

Parameters:

table_name (str) – The name of the table to be inserted.
columns (str) – The columns to be inserted.
values (str) – The values to be inserted.

Example Implementation (SQLite):

return f"INSERT INTO {table_name} ({columns}) VALUES ({values})"

static insert_or_update(table_name: str, columns: str, values: str) → str

Insert a row into the specified table or update it if it already exists.

Parameters:

table_name (str) – The name of the table to be inserted.
columns (str) – The columns to be inserted, already been seperated by ‘,’.
values (str) – The values to be inserted, already been seperated by ‘,’.

Example Implementation (SQLite):

return f"INSERT OR REPLACE INTO {table_name} ({columns}) VALUES ({values})"

static query(table_name: str, selection: str, condition: str) → str

Query the specified table.

Parameters:

table_name (str) – The name of the table to be queried.
selection (str) – The columns to be selected, seperated by ‘,’.
condition (str) – The condition of the query, in the general SQL format, generated by Exp.

Example Implementation (SQLite):

return f"SELECT {selection} FROM {table_name} WHERE {condition}"

static set_primary_key(table, keyname: str, keytype: str) → str | List[str]

Set a primary key for the specified table.

Parameters:

table (Table) – The table to be set.
keyname (str) – The name of the primary key.
keytype (str) – The type of the primary key.

Example Implementation (SQLite):

return [
    f"CREATE TABLE new_table ({keyname} {keytype} PRIMARY KEY, {', '.join([f'{name} {type_}' for name, type_ in table.columns.items() if name != keyname])})",
    f"INSERT INTO new_table SELECT * FROM {table.table_name}",
    f"DROP TABLE {table.table_name}",
    f"ALTER TABLE new_table RENAME TO {table.table_name}"
]

static update(table_name: str, columns: str, condition: str) → str

Update the specified table.

Parameters:

table_name (str) – The name of the table to be updated.
columns (str) – The columns to be updated. Values are already in the format of ‘column1=value1, column2=value2, …’, seperated by ‘,’.
condition (str) – The condition of the update, in the general SQL format, generated by Exp.

Example Implementation (SQLite):

return f"UPDATE {table_name} SET {columns} WHERE {condition}"

classmethod get_all_columns(conn: Conn, table_name: str) → List[str]

Get all column’s informations in the table.

The default implementation is based on the cls.gensql.get_all_columns(table_name) method.

Parameters:

conn (BaseDriver.Conn) – The connection object of the database.
table_name (str) – The name of the table.

Returns:

All column’s informations in the table.

Return type:

List[str]. Each element is a list of [column_name, column_type].

classmethod get_all_tables(conn: Conn) → List[str]

Get all table’s informations in the database.

The default implementation is based on the cls.gensql.get_all_tables() method.

Parameters:: conn (BaseDriver.Conn) – The connection object of the database.
Returns:: All table’s informations in the database

class MercurySQL.drivers.base.BaseDriver.TypeParser

Parse the type from Python Type -> SQL Type.

static parse(type_: Any) → str

Compile the type to SQLite type.

Parameters:: type (Any) – The type to parse.
Returns:: The SQLite type.
Return type:: str

Note

I’ve provided a example implementation of this method, for SQLite. Look at the source code.

Example Usage:

TypeParser.parse(str)       # TEXT
TypeParser.parse(int)       # INTEGER
TypeParser.parse(float)     # REAL
...