The bot’s database

class sopel.db.ChannelValues(**kwargs)

Channel values table SQLAlchemy class.

class sopel.db.NickIDs(**kwargs)

Nick IDs table SQLAlchemy class.

class sopel.db.NickValues(**kwargs)

Nick values table SQLAlchemy class.

class sopel.db.Nicknames(**kwargs)

Nicknames table SQLAlchemy class.

class sopel.db.PluginValues(**kwargs)

Plugin values table SQLAlchemy class.

class sopel.db.SopelDB(config)

Database object class.

Parameters

config (sopel.config.Config) – Sopel’s configuration settings

This defines a simplified interface for basic, common operations on the bot’s database. Direct access to the database is also available, 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).

alias_nick(nick, alias)

Create an alias for a nick.

Parameters

  • nick (str) – an existing nickname

  • alias (str) – 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, key)

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

Parameters

  • channel (str) – the channel whose values to modify

  • key (str) – 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_group(nick)

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

Parameters

nick (str) – one of the nicknames in the group to be deleted

Raises

SQLAlchemyError – if there is a database error

Important

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

delete_nick_value(nick, key)

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

Parameters

  • nick (str) – the nickname whose values to modify

  • key (str) – 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, key)

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

Parameters

  • plugin (str) – the plugin name whose values to modify

  • key (str) – 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().

execute(*args, **kwargs)

Execute an arbitrary SQL query against the database.

Returns

the query results

Return type

sqlalchemy.engine.ResultProxy

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

get_channel_slug(chan)

Return the case-normalized representation of channel.

Parameters

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

Return str

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, key, default=None)

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

Parameters

  • channel (str) – the channel whose values to access

  • key (str) – the name by which the desired value was saved

  • default (mixed) – 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, create=True)

Return the internal identifier for a given nick.

Parameters

  • nick (Identifier) – the nickname for which to fetch an ID

  • create (bool) – whether to create an ID if one does not exist

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.

See also

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

get_nick_or_channel_value(name, key, default=None)

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

Parameters

  • name (str) – nick or channel whose values to access

  • key (str) – the name by which the desired value was saved

  • default (mixed) – 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, key, default=None)

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

Parameters

  • nick (str) – the nickname whose values to access

  • key (str) – the name by which the desired value was saved

  • default (mixed) – 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, key, default=None)

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

Parameters

  • plugin (str) – the plugin name whose values to access

  • key (str) – the name by which the desired value was saved

  • default (mixed) – 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, key)

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

Parameters

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

  • key (str) – 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()

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, second_nick)

Merge two nick groups.

Parameters

  • first_nick (str) – one nick in the first group to merge

  • second_nick (str) – 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, key, value)

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

Parameters

  • channel (str) – the channel with which to associate the value

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

  • value (mixed) – 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, key, value)

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

Parameters

  • nick (str) – the nickname with which to associate the value

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

  • value (mixed) – 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, key, value)

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

Parameters

  • plugin (str) – the plugin name with which to associate the value

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

  • value (mixed) – 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)

Remove an alias.

Parameters

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

Raises

See also

To delete an entire group, use delete_nick_group().

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