SQLite

Some helper functions for aiosqlite.

class ezcord.sql.DBHandler(path, *, connection=None, auto_connect=False, auto_setup=True, conv_json=False, foreign_keys=False, **kwargs)

A class that provides helper methods for SQLite databases.

Parameters:

• path (str) – The path to the database file.

• connection (Connection | None) – A connection to the database. If not provided, a new connection will be created. If auto_connect is True, this will be ignored.

• auto_connect (bool) –

  Automatically create a new connection that will be used for all queries. This is used by start().

  When used without a context manager, this must be closed with close() or by using end=True in exec().

• auto_setup (bool) – Whether to call setup() when the first instance of this class is created. Defaults to True. This is called in the on_ready event of the bot.

• conv_json (bool) – Whether to auto-convert JSON. Defaults to False.

• foreign_keys (bool) – Whether to enforce foreign keys. Defaults to False.

• **kwargs – Keyword arguments for aiosqlite.connect().

Example

You can use this class with an asynchronous context manager:

async with DBHandler("ezcord.db") as db:
    await db.exec("CREATE TABLE IF NOT EXISTS vip (id INTEGER PRIMARY KEY, name TEXT)")
    await db.exec("INSERT INTO vip (name) VALUES (?)", "Timo")

start(*, conv_json=None, foreign_keys=None, **kwargs)

Opens a new connection with the current DB settings. Additional settings can be provided as keyword arguments.

This should be used as an asynchronous context manager. The connection will commit automatically after exiting the context manager.

Parameters:

• conv_json (bool | None) – Whether to auto-convert JSON.

• foreign_keys (bool | None) – Whether to enforce foreign keys.

• **kwargs – Additional keyword arguments for aiosqlite.connect().

Return type:

DBHandler

Example

class VipDB(DBHandler):
    def __init__(self):
        super().__init__("ezcord.db")

    async def setup(self):
        async with self.start() as db:
            await db.exec(
                "CREATE TABLE IF NOT EXISTS vip (id INTEGER PRIMARY KEY, name TEXT)"
            )
            await db.exec("INSERT INTO vip (name) VALUES (?)", "Timo")

async connect(**kwargs)

Alias for start().

Return type:

DBHandler

async close()

Commits and closes the current connection to the database.

This is called automatically when using start() as a context manager.

async one(sql, *args, fill=False, **kwargs)

Returns one result row. If no row is found, None is returned.

If the query returns only one column, the value of that column is returned.

Parameters:

• sql (str) – The SQL query to execute.

• *args – Arguments for the query.

• fill (bool) – Whether to return None for all selected values if no row is found. Defaults to False.

• **kwargs – Keyword arguments for the connection.

Return type:

The result row or None. A result row is either a tuple or a single value.

async all(sql, *args, **kwargs)

Returns all result rows.

If the query returns only one column, the values of that column are returned.

Parameters:

• sql (str) – The SQL query to execute.

• *args – Arguments for the query.

• **kwargs – Keyword arguments for the connection.

Return type:

A list of result rows. A result row is either a tuple or a single value.

async exec(sql, *args, end=False, **kwargs)

Executes a SQL query.

Parameters:

• sql (str) – The SQL query to execute.

• end (bool) – Whether to commit and close the connection after executing the query.

• *args – Arguments for the query.

• **kwargs – Keyword arguments for the connection.

Return type:

Cursor

async execute(sql, *args, end=False, **kwargs)

Alias for exec().

Return type:

Cursor

async executemany(sql, args, **kwargs)

Executes a SQL multiquery.

Parameters:

• sql (str) – The mutliquery to execute.

• *args (Iterable[Iterable[Any]]) – Arguments for the mutliquery.

• **kwargs – Keyword arguments for the connection.

Return type:

Cursor