SQL results to MS Teams using Python in table format

All other Source.Python topics and issues.
Ram668000
Junior Member
Posts: 6
Joined: Fri Sep 25, 2020 7:29 am

SQL results to MS Teams using Python in table format

Postby Ram668000 » Fri Sep 25, 2020 7:38 am

Hi Team,
Have wrote a small python script to send results to MS teams , when am try to sending it coming as string format.
My Code:

Syntax: Select all

import pymsteams
import pandas as pd
import numpy as np
import pyodbc
from tabulate import tabulate

server = ‘ServerName’
database = ‘DBName’
username = ‘username’
password = ‘pasword’
conn = pyodbc.connect(‘DRIVER={ODBC Driver 17 for SQL Server};SERVER=’+server+’;DATABASE=’+database+’;UID=’+username+’;PWD=’+ password)

cursor = conn.cursor()

sql_query = “select * from bookingStatusEnum”

df = pd.read_sql(sql_query, conn)

#print(tabulate(sql_query))
def sleeve(df):
df.to_html()

myTeamsMessage = pymsteams.connectorcard("my webhook url ")
myTeamsMessage.title(“List Of Booking Status”)
#myTeamsMessage.text(“Test Mail”)
myTeamsMessage.text(df.to_string())
myTeamsMessage.send()


Results are attached.

I need results in table format. Please help on ASAP

Thanks
Attachments
capture20200924184341713.png
capture20200924184341713.png (17.76 KiB) Viewed 600 times
Last edited by Ayuto on Fri Sep 25, 2020 2:14 pm, edited 1 time in total.
Reason: Added python code tags
arawra
Senior Member
Posts: 185
Joined: Fri Jun 21, 2013 6:51 am

Re: SQL results to MS Teams using Python in table format

Postby arawra » Fri Sep 25, 2020 12:53 pm

There's no "table" in Python. In generic terms, I think you'd want an associative array. Python supports dictionaries, which is a collection of key-value pairs. Keys and values can be of any data type I believe.

There may be a more efficient or pythonic way to do this, but implement first and optimize second /shrug

Syntax: Select all

df = pd.read_sql(sql_query, conn)
results = str(df).split(' ')

columns = ['Status', 'Enum', 'Booking Status']

database_table_output = {}
for i in range(0,len(results)):
database_table_output[i] = {columns[index(df, i) % 3] : results[i]}

for item,sub_dictionary in database_table_output.items():
myTeamsMessage.text("Item {0}".format(item))
for k,v in sub_dictionary.items():
myTeamsMessage.text("\t{0}:\t{1}".format(k,v))
arawra
Senior Member
Posts: 185
Joined: Fri Jun 21, 2013 6:51 am

Re: SQL results to MS Teams using Python in table format

Postby arawra » Fri Sep 25, 2020 1:19 pm

I think I like this formatting a bit better, and iterating over lists is more efficient.

Syntax: Select all

df = pd.read_sql(sql_query, conn)
results = str(df).split(' ')

columns = ['Status', 'Enum', 'Booking Status']
database_table_output = []
for i in range(0,len(results),3):
database_table_output[i] = [results[i], results[i+1], results[i+2]]

myTeamsMessage.text("{0}\t{1}\t{2}\n".format(columns[0], columns[1], columns[2]) + "-"*20)
for i in database_table_output:
myTeamsMessage.text("{0}\t{1}\t{2}".format(i[0],i[1],i[2]))
Ram668000
Junior Member
Posts: 6
Joined: Fri Sep 25, 2020 7:29 am

Re: SQL results to MS Teams using Python in table format

Postby Ram668000 » Fri Sep 25, 2020 2:01 pm

arawra wrote:There's no "table" in Python. In generic terms, I think you'd want an associative array. Python supports dictionaries, which is a collection of key-value pairs. Keys and values can be of any data type I believe.

There may be a more efficient or pythonic way to do this, but implement first and optimize second /shrug

Syntax: Select all

df = pd.read_sql(sql_query, conn)
results = str(df).split(' ')

columns = ['Status', 'Enum', 'Booking Status']

database_table_output = {}
for i in range(0,len(results)):
database_table_output[i] = {columns[index(df, i) % 3] : results[i]}

for item,sub_dictionary in database_table_output.items():
myTeamsMessage.text("Item {0}".format(item))
for k,v in sub_dictionary.items():
myTeamsMessage.text("\t{0}:\t{1}".format(k,v))


Thanks for quick response.
Let me know if any better approach to achieve this

Thanks
Ram668000
Junior Member
Posts: 6
Joined: Fri Sep 25, 2020 7:29 am

Re: SQL results to MS Teams using Python in table format

Postby Ram668000 » Fri Sep 25, 2020 2:02 pm

arawra wrote:I think I like this formatting a bit better, and iterating over lists is more efficient.

Syntax: Select all

df = pd.read_sql(sql_query, conn)
results = str(df).split(' ')

columns = ['Status', 'Enum', 'Booking Status']
database_table_output = []
for i in range(0,len(results),3):
database_table_output[i] = [results[i], results[i+1], results[i+2]]

myTeamsMessage.text("{0}\t{1}\t{2}\n".format(columns[0], columns[1], columns[2]) + "-"*20)
for i in database_table_output:
myTeamsMessage.text("{0}\t{1}\t{2}".format(i[0],i[1],i[2]))


Thanks for quick response.
User avatar
Ayuto
Project Leader
Posts: 2107
Joined: Sat Jul 07, 2012 8:17 am
Location: Germany

Re: SQL results to MS Teams using Python in table format

Postby Ayuto » Fri Sep 25, 2020 2:19 pm

You can also try one of my old snippets (Python 2 - might need to be updated for Python 3):


There might be better libraries today (I haven't searched).

You will use it like this:

Syntax: Select all

from ascii_table import AsciiTable

table = AsciiTable('Column A', 'Column B', 'Column C')
table.add_row('Value A', 'Value B', 'Value C')
table.add_row('Value A', 'Value B', 'Value C')
table.add_row('Value A', 'Value B', 'Value C')
table.add_row('Value A', 'Value B', 'Value C')
print(table.format())

Output:

Code: Select all

  Column A  |  Column B  |  Column C
======================================
  Value A   |  Value B   |  Value C
  Value A   |  Value B   |  Value C
  Value A   |  Value B   |  Value C
  Value A   |  Value B   |  Value C


Edit:
Actually, I just remebered that we have added a Python 3 version to SP:
https://github.com/Source-Python-Dev-Te ... e/table.py
Ram668000
Junior Member
Posts: 6
Joined: Fri Sep 25, 2020 7:29 am

Re: SQL results to MS Teams using Python in table format

Postby Ram668000 » Tue Nov 03, 2020 6:30 am

Thank you all. Issue was resolved

Return to “General Discussion”

Who is online

Users browsing this forum: No registered users and 2 guests