BotBase/DATABASE.md

BotBase - Notes on database interaction

BotBase has an built-in API to interact with a SQLite3 database, located in the BotBase/database/query.py module. The reason why SQLite3 was chosen among the lots of options is that it's lightweight, has less security concerns (no user and password to remember) and requires literally no setup at all.

The configuration is hassle-free, you can keep the default values and they'll work just fine. If you need a more complex database structure, just edit the DB_CREATE SQL query to fit your needs, but do not alter the default users table unless you also change all the SQL queries in the config.py file as this would otherwise break the whole internal machinery of BotBase.

Available methods

The module BotBase.database.query implements the following default methods to interact with the database. All methods either return the result of a query or True if the operation was successful, or an exception if the query errored.

Please note that the methods are NOT locked and that proper locking is needed if you think that your bot might get a sqlite3.OoerationalError: database is locked error when accessing the database.

All queries are performed within a with block and therefore rollbacked automatically if an error occurs or committed if the transaction was successful.

• get_user() -> Given a Telegram ID as input, returns a tuple containing the unique id of the user in the database, its telegram id, username, the date and time the user was inserted in the database as a string (formatted as d/m/Y H:M:S) and an integer (0 for False and 1 for True) that represents the user's status (whether it is banned or not)

• get_users() -> This method takes no parameter and returns a list of tuples. Each tuple contains a user ID as stored in the database

• set_user() -> Saves an ID/username pair (in this order) to the database. The username parameter can be None

• ban_user() -> Bans the user with the given user ID

• unban_user() -> Unbans a user with the given ID

• update_user -> Updates a user's username with the given ID

I need MySQL/other DBMS!

The API has been designed in a way that makes it easy to swap between different database managers, so if you feel in the right mood make a PR to support a new database and it'll be reviewed ASAP.

Adding more methods

If you want to add custom methods to the API, we advise to follow the bot's convention:

• Set the SQL query as a global variable whose name starts with DB_ in config.py
• Import it in the BotBase.database.query module
• Create a new function that takes the required parameters whose name reflects the query name (without DB_)
• Perform the query in a with context manager, close the cursor when you're done
• Return True or the query result if the query was successful, or an Exception subclass if an error occurs