Database

tibiawikisql.database

Contains all the SQL related model definitions.

Classes:

Name	Description
Column	Represents a column in a SQL table.
TableMeta	Metaclass for table classes.
Table	Represents a SQL table.
SQLType	An SQL type definition.
Timestamp	A timestamp, represented as a ISO 8601 string.
Date	A date, represented as a ISO 8601 string.
Integer	Integer type.
Real	Real type.
Text	Text type.
Blob	Blob type.
Boolean	Boolean type.
ForeignKey	Defines a foreign key.

Column

Column(
    column_type: type[SQLType] | SQLType,
    name: str | None = None,
    *,
    unique: bool = False,
    primary_key: bool = False,
    nullable: bool = True,
    default: Any | None = None,
    auto_increment: bool = False,
    index: bool = False,
    no_case: bool = False,
)

Represents a column in a SQL table.

Parameters:

Name	Type	Description	Default
column_type	type[SQLType] | SQLType	The SQL type of the column.	required
name	str | None	The name of the column. If unset, it will get it from the attribute's name.	None
unique	bool	Whether to create a unique index for the column or not.	False
primary_key	bool	Whether the column is a primary key or not.	False
nullable	bool	Whether the class can be null or not.	True
default	Any | None	The default value of the column if undefined.	None
auto_increment	bool	Whether the value should auto increment or not.	False
index	bool	Whether the column is indexed or not.	False
no_case	bool	Whether the column should be case-insensitive or not.	False

Methods:

Name	Description
get_column_definition	Get the SQL definition of this column, as used in a CREATE TABLE statement.

Source code in tibiawikisql/database.py

def __init__(
        self,
        column_type: type[SQLType] | SQLType,
        name: str | None = None,
        *,
        unique: bool = False,
        primary_key: bool = False,
        nullable: bool = True,
        default: Any | None = None,
        auto_increment: bool = False,
        index: bool = False,
        no_case: bool = False,
) -> None:
    """Create an instance of the class.

    Args:
        column_type: The SQL type of the column.
        name: The name of the column. If unset, it will get it from the attribute's name.
        unique: Whether to create a unique index for the column or not.
        primary_key: Whether the column is a primary key or not.
        nullable: Whether the class can be null or not.
        default: The default value of the column if undefined.
        auto_increment: Whether the value should auto increment or not.
        index: Whether the column is indexed or not.
        no_case: Whether the column should be case-insensitive or not.

    """
    if inspect.isclass(column_type):
        column_type = column_type()

    if not isinstance(column_type, SQLType):
        msg = "Cannot have a non-SQLType derived column_type"
        raise SchemaError(msg)

    self.column_type = column_type
    self.index = index
    self.unique = unique
    self.primary_key = primary_key
    self.nullable = nullable
    self.default = default
    self.name = name
    self.auto_increment = auto_increment
    self.no_case = no_case

    if self.auto_increment:
        self.primary_key = True

    if self.primary_key:
        self.nullable = False

    if not isinstance(self.column_type, Integer) and self.auto_increment:
        msg = "Only Integer columns can be autoincrement"
        raise SchemaError(msg)

    if sum(map(bool, (unique, primary_key, default is not None))) > 1:
        msg = "'unique', 'primary_key', and 'default' are mutually exclusive."
        raise SchemaError(msg)

get_column_definition

get_column_definition() -> str

Get the SQL definition of this column, as used in a CREATE TABLE statement.

Returns:

Type	Description
str	The statement that defines the column.

Source code in tibiawikisql/database.py

def get_column_definition(self) -> str:
    """Get the SQL definition of this column, as used in a `CREATE TABLE` statement.

    Returns:
        The statement that defines the column.

    """
    builder = [self.name, self.column_type.to_sql()]

    default = self.default
    if default is not None:
        builder.append("DEFAULT")
        if isinstance(default, str) and isinstance(self.column_type, Text):
            builder.append(f"'{default}'")
        elif isinstance(default, bool):
            builder.append(str(int(default)))
        else:
            builder.append(str(default))
    elif self.unique:
        builder.append("UNIQUE")

    if self.auto_increment:
        builder.append("AUTOINCREMENT")

    if not self.nullable:
        builder.append("NOT NULL")

    if self.no_case:
        builder.append("COLLATE NOCASE")

    return " ".join(builder)

TableMeta

TableMeta(
    name: str,
    parents: tuple[type, ...],
    dct: dict[str, Any],
    **kwargs: Any,
)

Bases: type

Metaclass for table classes.

Source code in tibiawikisql/database.py

def __init__(cls, name: str, parents: tuple[type, ...], dct: dict[str, Any], **kwargs: Any) -> None:
    super().__init__(name, parents, dct)

Table

Represents a SQL table.

Methods:

Name	Description
get_create_table_statement	Generate the CREATE TABLE statement.
all_tables	Get a list of all defined tables.
insert	Insert a row into this table.
get_drop_statement	Get the SQL query to drop this table.
get_one_by_field	Get a row by a specific column's value.
get_list_by_field	Get a list of rows matching the specified field's value.

get_create_table_statement classmethod

get_create_table_statement(
    *, exists_ok: bool = True
) -> str

Generate the CREATE TABLE statement.

Returns:

Type	Description
str	A SQL statement to create the table.

Source code in tibiawikisql/database.py

@classmethod
def get_create_table_statement(cls, *, exists_ok: bool = True) -> str:
    """Generate the `CREATE TABLE` statement.

    Returns:
        A SQL statement to create the table.

    """
    statements = []
    builder = ["CREATE TABLE"]

    if exists_ok:
        builder.append("IF NOT EXISTS")

    builder.append(cls.__tablename__)
    column_creations = []
    primary_keys = []
    for col in cls.columns:
        column_creations.append(col.get_column_definition())
        if col.primary_key:
            primary_keys.append(col.name)

    if primary_keys:
        column_creations.append(f'PRIMARY KEY ({", ".join(primary_keys)})')
    builder.append(f'({", ".join(column_creations)})')
    statements.append(" ".join(builder) + ";")

    for column in cls.columns:
        if column.index:
            fmt = f"CREATE INDEX IF NOT EXISTS {column.index_name} ON {cls.__tablename__} ({column.name});"
            statements.append(fmt)

    return "\n".join(statements)

all_tables classmethod

all_tables() -> list[type[Table]]

Get a list of all defined tables.

Returns:

Type	Description
list[type[Table]]	A list of defined tables in the schema.

Source code in tibiawikisql/database.py

@classmethod
def all_tables(cls) -> list[type[Table]]:
    """Get a list of all defined tables.

    Returns:
        A list of defined tables in the schema.

    """
    return cls.__subclasses__()

insert classmethod

insert(conn: Connection | Cursor, **kwargs: Any) -> None

Insert a row into this table.

Parameters:

Name	Type	Description	Default
conn	Connection | Cursor	A connection to the database.	required
**kwargs	Any	The column values.	{}

Source code in tibiawikisql/database.py

@classmethod
def insert(cls, conn: Connection | Cursor, **kwargs: Any) -> None:
    """Insert a row into this table.

    Args:
        conn: A connection to the database.
        **kwargs: The column values.

    """
    # verify column names:
    verified = {}
    for column in cls.columns:
        try:
            value = kwargs[column.name]
        except KeyError:
            continue

        check = column.column_type.python
        if value is None and not column.nullable:
            msg = "Cannot pass None to non-nullable column."
            raise InvalidColumnValueError(cls, column, msg)
        if (not check or not isinstance(value, check)) and value is not None:
            msg = f"Expected {check.__name__!r}, received {value.__class__.__name__!r}"
            raise InvalidColumnValueError(cls, column, msg)

        verified[column.name] = column.column_type.to_sql_value(value)

    sql = f"INSERT INTO {cls.__tablename__} ({', '.join(verified)}) VALUES ({', '.join('?' for _ in verified)});"
    conn.execute(sql, tuple(verified.values()))

get_drop_statement classmethod

get_drop_statement() -> str

Get the SQL query to drop this table.

Returns:

Type	Description
str	A SQL query to drop this table.

Source code in tibiawikisql/database.py

@classmethod
def get_drop_statement(cls) -> str:
    """Get the SQL query to drop this table.

    Returns:
        A SQL query to drop this table.
    """
    return f"DROP TABLE IF EXISTS {cls.__tablename__}"

get_one_by_field classmethod

get_one_by_field(
    conn: Connection | Cursor,
    column: str,
    value: Any,
    use_like: bool = False,
) -> Row | None

Get a row by a specific column's value.

Parameters:

Name	Type	Description	Default
conn	Connection | Cursor	A SQL connection.	required
column	str	The name of the column.	required
value	Any	The value to match it against.	required
use_like	bool	Whether to use LIKE as an operator instead of =.	False

Returns:

Type	Description
Row | None	The matching row, or None.

Raises:

Type	Description
ValueError	The specified column doesn't exist in the table.

Source code in tibiawikisql/database.py

@classmethod
def get_one_by_field(
        cls,
        conn: Connection | Cursor,
        column: str,
        value: Any,
        use_like: bool = False,
) -> Row | None:
    """Get a row by a specific column's value.

    Args:
        conn: A SQL connection.
        column: The name of the column.
        value: The value to match it against.
        use_like: Whether to use ``LIKE`` as an operator instead of ``=``.

    Returns:
        The matching row, or [None][].

    Raises:
        ValueError: The specified column doesn't exist in the table.
    """
    if column not in cls.column_map:
        msg = f"Column {column!r} doesn't exist"
        raise ValueError(msg)
    q = (
        Query.from_(cls.__table__)
        .select("*")
        .where(cls.__table__[column].like(value) if use_like else cls.__table__[column].eq(value))
    )
    cursor = conn.cursor() if isinstance(conn, sqlite3.Connection) else conn
    cursor.row_factory = Row
    cursor.execute(q.get_sql())
    return cursor.fetchone()

get_list_by_field classmethod

get_list_by_field(
    conn: Connection | Cursor,
    column: str,
    value: Any,
    use_like: bool = False,
    sort_by: str | None = None,
    ascending: bool = True,
    limit: int | None = None,
    *,
    base_query: SQLLiteQuery | None = None,
) -> list[Row]

Get a list of rows matching the specified field's value.

Note that this won't get values found in child tables.

Parameters:

Name	Type	Description	Default
conn	Connection | Cursor	A SQL connection.	required
column	str	The name of the column.	required
value	Any	The value to match it against.	required
use_like	bool	Whether to use LIKE as an operator instead of =.	False
sort_by	str | None	The name of the field to sort by.	None
ascending	bool	Whether to sort ascending or descending.	True
limit	int | None	Only return up to this many rows.	None

Returns:

Type	Description
list[Row]	The matching row, or None.

Raises:

Type	Description
ValueError	Tg

Source code in tibiawikisql/database.py

@classmethod
def get_list_by_field(
        cls,
        conn: Connection | Cursor,
        column: str,
        value: Any,
        use_like: bool = False,
        sort_by: str | None = None,
        ascending: bool = True,
        limit: int | None = None,
        *,
        base_query: Query | None = None,
) -> list[Row]:
    """Get a list of rows matching the specified field's value.

    Note that this won't get values found in child tables.

    Args:
        conn: A SQL connection.
        column: The name of the column.
        value: The value to match it against.
        use_like: Whether to use ``LIKE`` as an operator instead of ``=``.
        sort_by: The name of the field to sort by.
        ascending: Whether to sort ascending or descending.
        limit: Only return up to this many rows.

    Returns:
        The matching row, or ``None``.

    Raises:
        ValueError: Tg
    """
    if column not in cls.column_map:
        msg = f"Column {column!r} doesn't exist"
        raise ValueError(msg)
    if sort_by and sort_by not in cls.column_map:
        msg = f"Column {sort_by!r} doesn't exist"
        raise ValueError(msg)
    base_query = base_query or cls.get_base_select_query()
    table = PTable(cls.__tablename__)
    q = base_query.where(table[column].like(value) if use_like else table[column].eq(value))
    if sort_by is not None:
        q = q.orderby(sort_by, order=Order.asc if ascending else Order.desc)
    if limit is not None:
        q = q.limit(limit)
    cursor = conn.cursor() if isinstance(conn, sqlite3.Connection) else conn
    cursor.row_factory = sqlite3.Row
    return list(cursor.execute(q.get_sql()))

SQLType

An SQL type definition.

Methods:

Name	Description
to_sql	Get the name of the corresponding type in SQLite.
to_sql_value	Convert a value to its corresponding SQL value.

Attributes:

Name	Type	Description
python	type	The python class that represents this object.

python class-attribute

python: type = None

The python class that represents this object.

to_sql

to_sql() -> str

Get the name of the corresponding type in SQLite.

Returns:

Type	Description
str	A string containing the type's definition.

Source code in tibiawikisql/database.py

def to_sql(self) -> str:
    """Get the name of the corresponding type in SQLite.

    Returns:
        A string containing the type's definition.

    """
    raise NotImplementedError

to_sql_value

to_sql_value(value) -> Any

Convert a value to its corresponding SQL value.

Returns:

Type	Description
Any	The corresponding value as expected by SQLite.

Source code in tibiawikisql/database.py

def to_sql_value(self, value) -> Any:
    """Convert a value to its corresponding SQL value.

    Returns:
        The corresponding value as expected by SQLite.

    """
    return value

Timestamp

Bases: SQLType

A timestamp, represented as a ISO 8601 string.

Methods:

Name	Description
to_sql	Get the name of the corresponding type in SQLite.
to_sql_value	Convert a value to its corresponding SQL value.

Attributes:

Name	Type	Description
python		The python class that represents this object.

python class-attribute instance-attribute

python = datetime

The python class that represents this object.

to_sql

to_sql() -> str

Get the name of the corresponding type in SQLite.

Returns:

Type	Description
str	A string containing the type's definition.

Source code in tibiawikisql/database.py

def to_sql(self) -> str:
    return "TEXT"

to_sql_value

to_sql_value(value: datetime) -> str

Convert a value to its corresponding SQL value.

Returns:

Type	Description
Any	The corresponding value as expected by SQLite.

Source code in tibiawikisql/database.py

def to_sql_value(self, value: datetime.datetime) -> str:
    return value.isoformat()

Date

Bases: SQLType

A date, represented as a ISO 8601 string.

Methods:

Name	Description
to_sql	Get the name of the corresponding type in SQLite.
to_sql_value	Convert a value to its corresponding SQL value.

Attributes:

Name	Type	Description
python		The python class that represents this object.

python class-attribute instance-attribute

python = date

The python class that represents this object.

to_sql

to_sql() -> str

Get the name of the corresponding type in SQLite.

Returns:

Type	Description
str	A string containing the type's definition.

Source code in tibiawikisql/database.py

def to_sql(self) -> str:
    return "TEXT"

to_sql_value

to_sql_value(value: date) -> str

Convert a value to its corresponding SQL value.

Returns:

Type	Description
Any	The corresponding value as expected by SQLite.

Source code in tibiawikisql/database.py

def to_sql_value(self, value: datetime.date) -> str:
    return value.isoformat()

Integer

Bases: SQLType

Integer type.

Methods:

Name	Description
to_sql	Get the name of the corresponding type in SQLite.

Attributes:

Name	Type	Description
python		The python class that represents this object.

python class-attribute instance-attribute

python = int

The python class that represents this object.

to_sql

to_sql() -> str

Get the name of the corresponding type in SQLite.

Returns:

Type	Description
str	A string containing the type's definition.

Source code in tibiawikisql/database.py

def to_sql(self) -> str:
    return "INTEGER"

Real

Bases: SQLType

Real type.

Methods:

Name	Description
to_sql	Get the name of the corresponding type in SQLite.

Attributes:

Name	Type	Description
python		The python class that represents this object.

python class-attribute instance-attribute

python = float

The python class that represents this object.

to_sql

to_sql() -> str

Get the name of the corresponding type in SQLite.

Returns:

Type	Description
str	A string containing the type's definition.

Source code in tibiawikisql/database.py

def to_sql(self) -> str:
    return "REAL"

Text

Bases: SQLType

Text type.

Methods:

Name	Description
to_sql	Get the name of the corresponding type in SQLite.

Attributes:

Name	Type	Description
python		The python class that represents this object.

python class-attribute instance-attribute

python = str

The python class that represents this object.

to_sql

to_sql() -> str

Get the name of the corresponding type in SQLite.

Returns:

Type	Description
str	A string containing the type's definition.

Source code in tibiawikisql/database.py

def to_sql(self) -> str:
    return "TEXT"

Blob

Bases: SQLType

Blob type.

Methods:

Name	Description
to_sql	Get the name of the corresponding type in SQLite.

Attributes:

Name	Type	Description
python		The python class that represents this object.

python class-attribute instance-attribute

python = bytes

The python class that represents this object.

to_sql

to_sql() -> str

Get the name of the corresponding type in SQLite.

Returns:

Type	Description
str	A string containing the type's definition.

Source code in tibiawikisql/database.py

def to_sql(self) -> str:
    return "BLOB"

Boolean

Bases: SQLType

Boolean type.

Methods:

Name	Description
to_sql	Get the name of the corresponding type in SQLite.

Attributes:

Name	Type	Description
python		The python class that represents this object.

python class-attribute instance-attribute

python = bool

The python class that represents this object.

to_sql

to_sql() -> str

Get the name of the corresponding type in SQLite.

Returns:

Type	Description
str	A string containing the type's definition.

Source code in tibiawikisql/database.py

def to_sql(self) -> str:
    return "BOOLEAN"

ForeignKey

ForeignKey(
    sql_type: type[SQLType], table: str, column: str
)

Bases: SQLType

Defines a foreign key.

Parameters:

Name	Type	Description	Default
sql_type	type[SQLType]	The SQL type of the column.	required
table	str	The name of the table that is referenced.	required
column	str	The name of the column from the reference table.	required

Methods:

Name	Description
to_sql	Get the name of the corresponding type in SQLite.

Attributes:

Name	Type	Description
python		The python class that represents this object.

Source code in tibiawikisql/database.py

def __init__(self, sql_type: type[SQLType], table: str, column: str) -> None:
    """Create an instance of the class.

    Args:
        sql_type: The SQL type of the column.
        table: The name of the table that is referenced.
        column: The name of the column from the reference table.

    """
    if not table or not isinstance(table, str):
        msg = "Missing table to reference (must be string)"
        raise SchemaError(msg)

    self.table = table
    self.column = column

    if sql_type is None:
        sql_type = Integer

    if inspect.isclass(sql_type):
        sql_type = sql_type()

    if not isinstance(sql_type, SQLType):
        msg = "Cannot have non-SQLType derived sql_type"
        raise SchemaError(msg)

    if not sql_type.is_real_type():
        msg = "`sql_type` must be an actual type"
        raise SchemaError(msg)

    self.python = sql_type.python
    self.sql_type = sql_type.to_sql()

python instance-attribute

python = python

The python class that represents this object.

to_sql

to_sql()

Get the name of the corresponding type in SQLite.

Returns:

Type	Description
str	A string containing the type's definition.

Source code in tibiawikisql/database.py

def to_sql(self):
    fmt = "{0.sql_type} REFERENCES {0.table} ({0.column})"
    return fmt.format(self)