The bot’s database#

Sopel database module: management and tools around Sopel’s datamodel.

This module defines a datamodel, using SQLAlchemy ORM’s mapping:

These models are made available through the SopelDB class and its convenience methods, such as get_nick_value() or get_channel_value().

class sopel.db.ChannelValues#

Channel values table SQLAlchemy class.

class sopel.db.NickIDs#

Nick IDs table SQLAlchemy class.

class sopel.db.NickValues#

Nick values table SQLAlchemy class.

class sopel.db.Nicknames#

Nicknames table SQLAlchemy class.

class sopel.db.PluginValues#

Plugin values table SQLAlchemy class.

class sopel.db.SopelDB(config: Config, identifier_factory: IdentifierFactory = Identifier)#

Database object class.

Parameters:
Type:

Callable[[str], str]

This defines a simplified interface for basic, common operations on the bot’s database. Direct access to the database is also available through its engine attribute, to serve more complex plugins’ needs.

When configured to use SQLite with a relative filename, the file is assumed to be in the directory named by the core setting homedir.

New in version 5.0.

Changed in version 7.0: Switched from direct SQLite access to SQLAlchemy, allowing users more flexibility around what type of database they use (especially on high-load Sopel instances, which may run up against SQLite’s concurrent-access limitations).

Changed in version 8.0: An Identifier factory can be provided that will be used to instantiate Identifier when dealing with Nick or Channel names.

See also

For any advanced usage of the ORM, refer to the SQLAlchemy documentation.

alias_nick(nick: str, alias: str) → None#

Create an alias for a nick.

Parameters:

  • nick – an existing nickname

  • alias – an alias by which nick should also be known

Raises:

See also

To merge two existing nick groups, use merge_nick_groups().

To remove an alias created with this function, use unalias_nick().

connect()#

Get a direct database connection.

Returns:

a proxied DBAPI connection object; see sqlalchemy.engine.Engine.raw_connection()

Important

The db_type in use can change how the raw connection object behaves. You probably want to use session() and the SQLAlchemy ORM in new plugins, and officially support only Sopel 7.0+.

Note that session() is not available in Sopel versions prior to 7.0. If your plugin needs to be compatible with older Sopel releases, your code should use SQLAlchemy via session() if it is available (Sopel 7.0+) and fall back to direct SQLite access via connect() if it is not (Sopel 6.x).

We discourage publishing plugins that don’t work with all supported databases, but you’re obviously welcome to take shortcuts and support only the engine(s) you need in private plugins.

delete_channel_value(channel: str, key: str) → None#

Delete a value from the key-value store for channel.

Parameters:

  • channel – the channel whose values to modify

  • key – the name of the value to delete

Raises:

SQLAlchemyError – if there is a database error

See also

To set a value in the first place, use set_channel_value().

To retrieve a value instead of deleting it, use get_channel_value().

delete_nick_value(nick: str, key: str) → None#

Delete a value from the key-value store for nick.

Parameters:

  • nick – the nickname whose values to modify

  • key – the name of the value to delete

Raises:

SQLAlchemyError – if there is a database error

See also

To set a value in the first place, use set_nick_value().

To retrieve a value instead of deleting it, use get_nick_value().

delete_plugin_value(plugin: str, key: str) → None#

Delete a value from the key-value store for plugin.

Parameters:

  • plugin – the plugin name whose values to modify

  • key – the name of the value to delete

Raises:

SQLAlchemyError – if there is a database error

See also

To set a value in the first place, use set_plugin_value().

To retrieve a value instead of deleting it, use get_plugin_value().

engine#

SQLAlchemy Engine used to connect to Sopel’s database.

See also

Read SQLAlchemy engine’s documentation to know how to use it.

Important

Introduced in Sopel 7, Sopel uses SQLAlchemy 1.4+. This version of SQLAlchemy deprecates various behaviors and methods, to prepare the migration to its future 2.0 version, and the new 2.x style.

Sopel doesn’t enforce the new 2.x style yet. This will be modified in Sopel 9 by using the future=True flag on the engine.

You can read more about the migration guide from 1.x to 2.x, as Sopel will ensure in a future version that it is compatible with the new style.

execute(*args, **kwargs)#

Execute an arbitrary SQL query against the database.

Returns:

the query results

Return type:

sqlalchemy.engine.Result

The Result object returned is a wrapper around a Cursor object as specified by PEP 249.

Deprecated since version 8.0: This method will be removed in Sopel 9, following the deprecation of SQLAlchemy’s sqlalchemy.engine.Engine.execute().

To perform a raw SQL query, use the engine attribute as per the migration guide from SQLAlchemy:

from sqlalchemy.sql import text

def my_command(bot, trigger):
    raw_sql = ' ... '  # your raw SQL
    # get a connection as a context manager
    with bot.db.engine.connect() as conn:
        res = conn.execute(text(raw_sql))
        data = res.fetchall()

    # do something with your data here

See also

Read the migration guide from 1.x style to 2.x style by SQLAlchemy to learn more about using SQLALchemy’s engine and connection.

forget_channel(channel: str) → None#

Remove all of a channel’s stored values.

Parameters:

channel – the name of the channel for which to delete values

Raises:

SQLAlchemyError – if there is a database error

Important

This is a Nuclear Option. Be very sure that you want to do it.

forget_nick_group(nick: str) → None#

Remove a nickname, all of its aliases, and all of its stored values.

Parameters:

nick – one of the nicknames in the group to be deleted

Raises:

Important

This is otherwise known as The Nuclear Option. Be very sure that you want to do this.

forget_plugin(plugin: str) → None#

Remove all of a plugin’s stored values.

Parameters:

plugin – the name of the plugin for which to delete values

Raises:

SQLAlchemyError – if there is a database error

Important

This is a Nuclear Option. Be very sure that you want to do it.

get_channel_slug(chan: str) → str#

Return the case-normalized representation of channel.

Parameters:

channel – the channel name to normalize, with prefix (required)

Returns:

the case-normalized channel name (or “slug” representation)

This is useful to make sure that a channel name is stored consistently in both the bot’s own database and third-party plugins’ databases/files, without regard for variation in case between different clients and/or servers on the network.

get_channel_value(
channel: str,
key: str,
default: Any | None = None,
) → Any#

Get a value from the key-value store for channel.

Parameters:

  • channel – the channel whose values to access

  • key – the name by which the desired value was saved

  • default – value to return if key does not have a value set (optional)

Raises:

SQLAlchemyError – if there is a database error

New in version 7.0: The default parameter.

See also

To set a value for later retrieval with this method, use set_channel_value().

To delete a value instead of retrieving it, use delete_channel_value().

get_nick_id(nick: str, create: bool = False) → int#

Return the internal identifier for a given nick.

Parameters:

  • nick – the nickname for which to fetch an ID

  • create – whether to create an ID if one does not exist (set to False by default)

Raises:

  • ValueError – if no ID exists for the given nick and create is set to False

  • SQLAlchemyError – if there is a database error

The nick ID is shared across all of a user’s aliases, assuming their nicks have been grouped together.

Changed in version 8.0: The create parameter is now False by default.

See also

Alias/group management functions: alias_nick(), unalias_nick(), merge_nick_groups(), and forget_nick_group().

get_nick_or_channel_value(
name: str,
key: str,
default: Any | None = None,
) → Any | None#

Get a value from the key-value store for name.

Parameters:

  • name – nick or channel whose values to access

  • key – the name by which the desired value was saved

  • default – value to return if key does not have a value set (optional)

Raises:

SQLAlchemyError – if there is a database error

New in version 7.0: The default parameter.

This is useful for common logic that is shared between both users and channels, as it will fetch the appropriate value based on what type of name it is given.

See also

To get a value for a nick specifically, use get_nick_value().

To get a value for a channel specifically, use get_channel_value().

get_nick_value(
nick: str,
key: str,
default: Any | None = None,
) → Any | None#

Get a value from the key-value store for nick.

Parameters:

  • nick – the nickname whose values to access

  • key – the name by which the desired value was saved

  • default – value to return if key does not have a value set (optional)

Raises:

SQLAlchemyError – if there is a database error

New in version 7.0: The default parameter.

See also

To set a value for later retrieval with this method, use set_nick_value().

To delete a value instead of retrieving it, use delete_nick_value().

get_plugin_value(
plugin: str,
key: str,
default: Any | None = None,
) → Any | None#

Get a value from the key-value store for plugin.

Parameters:

  • plugin – the plugin name whose values to access

  • key – the name by which the desired value was saved

  • default – value to return if key does not have a value set (optional)

Raises:

SQLAlchemyError – if there is a database error

New in version 7.0: The default parameter.

See also

To set a value for later retrieval with this method, use set_plugin_value().

To delete a value instead of retrieving it, use delete_plugin_value().

get_preferred_value(
names: Iterable[str],
key: str,
) → Optional[Any]#

Get a value for the first name which has it set.

Parameters:

  • names – a list of channel names and/or nicknames

  • key – the name by which the desired value was saved

Returns:

the value for key from the first name which has it set, or None if none of the names has it set

Raises:

SQLAlchemyError – if there is a database error

This is useful for logic that needs to customize its output based on settings stored in the database. For example, it can be used to fall back from the triggering user’s setting to the current channel’s setting in case the user has not configured their setting.

Note

This is the only get_*_value() method that does not support passing a default. Try to avoid using it on keys which might have None as a valid value, to avoid ambiguous logic.

get_uri() → URL#

Return a direct URL for the database.

Returns:

the database connection URI

Return type:

str

This can be used to connect from a plugin using another SQLAlchemy instance, for example, without sharing the bot’s connection.

merge_nick_groups(first_nick: str, second_nick: str) → None#

Merge two nick groups.

Parameters:

  • first_nick – one nick in the first group to merge

  • second_nick – one nick in the second group to merge

Raises:

SQLAlchemyError – if there is a database error

Takes two nicks, which may or may not be registered. Unregistered nicks will be registered. Keys which are set for only one of the given nicks will be preserved. Where both nicks have values for a given key, the value set for the first_nick will be used.

A nick group can contain one or many nicknames. Groups containing more than one nickname can be created with this function, or by using alias_nick() to add aliases.

Note that merging of data only applies to the native key-value store. Plugins which define their own tables relying on the nick table will need to handle their own merging separately.

session()#

Get a SQLAlchemy Session object.

Return type:

sqlalchemy.orm.session.Session

New in version 7.0.

Note

If your plugin needs to remain compatible with Sopel versions prior to 7.0, you can use connect() to get a raw connection. See its documentation for relevant warnings and compatibility caveats.

set_channel_value(channel: str, key: str, value: Any) → None#

Set or update a value in the key-value store for channel.

Parameters:

  • channel – the channel with which to associate the value

  • key – the name by which this value may be accessed later

  • value – the value to set for this key under channel

Raises:

SQLAlchemyError – if there is a database error

The value can be any of a range of types; it need not be a string. It will be serialized to JSON before being stored and decoded transparently upon retrieval.

See also

To retrieve a value set with this method, use get_channel_value().

To delete a value set with this method, use delete_channel_value().

set_nick_value(nick: str, key: str, value: Any) → None#

Set or update a value in the key-value store for nick.

Parameters:

  • nick – the nickname with which to associate the value

  • key – the name by which this value may be accessed later

  • value – the value to set for this key under nick

Raises:

SQLAlchemyError – if there is a database error

The value can be any of a range of types; it need not be a string. It will be serialized to JSON before being stored and decoded transparently upon retrieval.

See also

To retrieve a value set with this method, use get_nick_value().

To delete a value set with this method, use delete_nick_value().

set_plugin_value(plugin: str, key: str, value: Any) → None#

Set or update a value in the key-value store for plugin.

Parameters:

  • plugin – the plugin name with which to associate the value

  • key – the name by which this value may be accessed later

  • value – the value to set for this key under plugin

Raises:

SQLAlchemyError – if there is a database error

The value can be any of a range of types; it need not be a string. It will be serialized to JSON before being stored and decoded transparently upon retrieval.

See also

To retrieve a value set with this method, use get_plugin_value().

To delete a value set with this method, use delete_plugin_value().

unalias_nick(alias: str) → None#

Remove an alias.

Parameters:

alias – an alias with at least one other nick in its group

Raises:

  • ValueError – if there is not at least one other nick in the group, or the alias is not known

  • SQLAlchemyError – if there is a database error

See also

To delete an entire group, use forget_nick_group().

To add an alias for a nick, use alias_nick().