Skip to content

Latest commit

 

History

History
56 lines (39 loc) · 2.64 KB

DATABASE.md

File metadata and controls

56 lines (39 loc) · 2.64 KB
Raw

BotBase - Notes on database interaction

BotBase has a built-in API to interact with an MySQL database, located in the BotBase/database/query.py module. The reason why MySQL was chosen among the lots of options is that it's solid enough for bots with standard load, has enough features, and requires little to 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 CREATE_USERS_TABLE SQL query to fit your needs, but do not alter the default users table unless you also change all the SQL queries in the raw_queries.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.

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 is 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 Sqlite/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 raw_queries.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