This is an example of a Python based Mastodon bot which reads and writes to an SQLite DB

sqlite_db_bot_example.md

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding('utf8')
from mastodon import Mastodon,StreamListener
import sqlite3 as lite
from bs4 import BeautifulSoup

basedir = "/home/you/yourbot/"

mastodon = Mastodon(
        api_base_url='https://botsin.space',
        client_id = basedir + 'clientcred.txt',
        access_token = basedir + 'usercred.txt'
)

def htmlText(html):
        soup = BeautifulSoup(html, 'html.parser')
        lines = []
        for p in soup('p'):
                lines.append(p.text)
        return '\n'.join(lines)

def response(username,vis,text,mentionid):
        if vis=="public": # respond to plublic as unlisted to try to prevent public timeline flooding 

                status = mastodon.status_post("@" + str(username) + " " + text, in_reply_to_id=mentionid,visibility="unlisted")
        else: # otherwise respond with the same privacy level 
                status = mastodon.status_post("@" + str(username) + " " + text, in_reply_to_id=mentionid,visibility=vis)

class BotListener( StreamListener ):
        def on_update( self, _ ):
                pass

        def on_delete( self, _ ):
                pass

        def on_notification( self, notification ):
                if notification['type'] == 'mention':
                        mention_html = notification['status']['content'] 		# get the mention
                        mention_text = BeautifulSoup(mention_html, 'html.parser')
                        mention_text = mention_text.get_text() 				# convert it to text
                        mention_text = mention_text.replace("yourbot ","")		# strip the bot name from the mention
                        mentionid = str(notification['status']['id'])	
                        username = notification['status']['account']['acct']
                        mention = mention_text.lower()					# set mention to lower case to make it easier to deal with
                        vis = notification['status']['visibility']
                        display_name = notification['status']['account']['display_name'] # get user display name in case you want to use it for something

			if ("give" in mention):
                        	give_arr = mention.split()	# split mention into array
                                total= len(give_arr)		# count the words as total
                                glyph = give_arr[1]		# assume glyph to be the second term
                                to = (total-2)			# assume "to" to be the second to last term 
                                if give_arr[0] == "give":	# confirm that give is the first term
                                	if ((give_arr[to] == "to") and ("@" in give_arr[total-1])):	# confirm that "to" is where we think it is
                                        	recipient = give_arr[total-1]	# set the last term to "recipient"
                                                if ("@" in recipient) and (recipient[:1] != "@"):	# make sure there is an @ but they don't start recipient name with an @ so it doesnt link to the user when they make the requests
							try: # search for the glyph and get the glyph_id
   								con = lite.connect(basedir + 'glyphs.db')
                                                		con.row_factory = lite.Row
                                                		with con:
                                                        		cur = con.cursor()
									cur.execute("select glyph_id, glyph_description from GLYPHS where username=? and glyph=? limit 1;",(username,glyph))
									query_result = cur.fetchall()
                                                		con.close()
								glyph_id = query_result[0][0]
								glyph_description = query_result[0][1]
							except:
								print "search query failed"

							try: # update the glyph username
   								con = lite.connect(basedir + 'glyphs.db')
                                                		con.row_factory = lite.Row
                                                		with con:
                                                        		cur = con.cursor()
                                                        		cur.execute("update GLYPHS set username=? where glyph_id=? limit 1;",(recipient,glyph_id))
									query_result = cur.fetchall()
                                                		con.close()
								text = username + " has presented you with the " + glyph_description + " - " + glyph  
								response(username,vis,text,mentionid)
								
							except:
								print "update query failed"
							
                                                                                                                          
stream = mastodon.stream_user( listener=BotListener() )
stream.stream_user()

Install Prereqs

sudo apt install sqlite3
pip install Mastodon.py bs4

DB STUFF

Create the empty DB and open it

sqlite3 glyphs.db

Create the table

CREATE TABLE GLYPHS (GLYPH_ID INTEGER PRIMARY KEY, USERNAME varchar(30), GLYPH varchar(10),GLYPH_DESCRIPTION varchar(100));

Inserting a row with NULL value tells it to generate a unique GLYPH_ID as primary key

INSERT INTO GLYPHS VALUES(NULL,'vgan@mastodon.social','🍍','pineapple');

Look at the DB structure

.schema

Look for your pineapple record

select * from glyphs;

Quit the database and try your script!

.quit