Abbot – sql for Microsoft Teams 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)