sql 1.0.1

an Abbot package for Microsoft Teams by Serious Business

Run SQL queries from chat

Abbot is a programmable bot that turns your team chat into a shared command center. We handle all the boilerplate of building and running these conmmands so that you can focus on making tools that help you ship faster.

We built Abbot because we saw the power of this style of work (called ChatOps), when we worked at GitHub. ChatOps made it possible for GitHub to work productively without meetings, while globally distributed. We think it's a pretty great way to work, so we made it easy to use in Slack, Discord, and Microsoft Teams.

You can read more about Abbot here, check out our blog, or take a look at some of the other cool packages available as a one-click install from Abbot's Package Directory.

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

Version	Created	Author	Release Notes	Installs
1.0.1	7/1/2024	Paul	Updated usage text.	1
1.0.0	7/1/2024	Paul	First release!	0

Sign in to add this to Microsoft Teams You need an account to install this package with Abbot. Signing in with Microsoft Teams will create an account if you don't have one already.

Not using Microsoft Teams? No worries! Abbot works in Slack, Discord, and Microsoft Teams.

It's free to get started!

Details

Last updated: Thursday, 01 July 2024 01:00
Installs: 1 total install
Language: Python
License: MIT License

?	Display shortcuts
Cmd+K	Display shortcuts when a form input has focus.
t	Jump to skill
ctrl+t	Jump to skill when a form input has focus.
Esc	Dismiss modal. Also used to remove focus from a text input to enable keyboard shortcut.
g m	Go to the My Bot page
g s	Go to the skills list
g p	Go to the package directory
g a	Go to the About page
g d	Go to the Docs site
g l	Go to Activity Log

About Abbot

README

Usage

Code

Version History

Details

Keyboard Shortcuts

Jump to Skill