The bot’s database#
Sopel database module: management and tools around Sopel’s datamodel.
This module defines a datamodel, using SQLAlchemy ORM’s mapping:
NickValues
is used to store arbitrary values for usersChannelValues
is used to store arbitrary values for channelsPluginValues
is used to store arbitrary values for plugins
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,
- identifier_factory: sopel.tools.identifiers.IdentifierFactory = Identifier,
Database object class.
- Parameters:
config (
sopel.config.Config
) – Sopel’s configuration settingsidentifier_factory – factory for
Identifier
- Type:
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:
ValueError – if the
alias
already existsSQLAlchemyError – if there is a database error
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 usesession()
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 viasession()
if it is available (Sopel 7.0+) and fall back to direct SQLite access viaconnect()
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:
The
Result
object returned is a wrapper around aCursor
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:
ValueError – if the
nick
does not exist in the databaseSQLAlchemyError – if there is a database error
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( )#
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
andcreate
is set toFalse
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 nowFalse
by default.See also
Alias/group management functions:
alias_nick()
,unalias_nick()
,merge_nick_groups()
, andforget_nick_group()
.
- get_nick_or_channel_value( ) 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( ) 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( ) 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( ) 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 firstname
which has it set, orNone
if none of thenames
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 adefault
. Try to avoid using it onkey
s which might haveNone
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:
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)#
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:
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 latervalue – the value to set for this
key
underchannel
- 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 latervalue – the value to set for this
key
undernick
- 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 latervalue – the value to set for this
key
underplugin
- 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 knownSQLAlchemyError – 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()
.