Abbot – «sql v1.0.1» by Serious Business

Code

<br /> import pandas<br /> import sqlalchemy</p> <p>def run_query(query):<br /> # Set a secret called «connstring» that contains the connection to your database.<br /> connstr = bot.secrets.read(«connstring»)</p> <p> if connstr:<br /> engine = sqlalchemy.create_engine(connstr)<br /> df = pandas.read_sql(query, engine)<br /> return ««`{}«`».format(df.to_markdown())<br /> else:<br /> return «There’s no connection string set up. Please add one before running this skill.»</p> <p># We recommend predefining some queries instead of letting people run SQL<br /> # commands directly from chat.<br /> queries = {<br /> «newusers»: «»» SELECT «Username», «CreatedAt»<br /> FROM «Users»<br /> ORDER BY «CreatedAt»<br /> DESC LIMIT 10;»»»,<br /> «usercount»: «»» SELECT DATE(date_joined) AS DAY, COUNT(id) AS NewUsers<br /> FROM users<br /> GROUP BY DATE(date_joined);»»»<br /> }</p> <p>query_keys = queries.keys()</p> <p># Check to see if the user has asked for a predefined query.<br /> if bot.arguments in query_keys:<br /> result = run_query(queries.get(bot.arguments))<br /> else:<br /> result = «Available queries are: »<br /> for key in query_keys:<br /> result += «\n * » + key</p> <p># Return the result to chat<br /> bot.reply(result)