def statepiechart(state):
mydb = statistics.SQLconnect() #connects to SQL server
cursor = mydb.cursor()
datetime_1 = datetime.now()
currentdate = datetime_1.date()
sql_select_query = """SELECT * FROM landkreis WHERE Bundesland = %s AND Zuletzt_geupdatet = %s""" #SQL query
cursor.execute(sql_select_query, (
state,
currentdate,
)) #takes input from DiscordBotDebug and puts in in %s above
myresult = cursor.fetchall() #actually commit query
cursor.close()
mydb.close()
cases = []
death = []
for x in myresult: #search trough results of query
cases.append(int(x[3]))
death.append(int(x[4]))
return cases, death
def generate_dict():
dictionary = {}
mydb = statistics.SQLconnect()
cursor = mydb.cursor()
datetime_1 = datetime.now()
currentdate = datetime_1.date()
sql_select_query = """SELECT * FROM landkreis WHERE Zuletzt_geupdatet = %s""" #SQL query
cursor.execute(
sql_select_query,
(currentdate,
)) #takes input from DiscordBotDebug and puts in in %s above
myresult = cursor.fetchall() #actually commit query
cursor.close()
mydb.close()
for x in myresult: #search trough results of query
names = x[0] + " " + x[1]
cases = x[3]
deaths = x[4]
incidence = x[5]
dictionary[names] = (cases, deaths, incidence)
return dictionary
def barplot(county):
try:
os.remove('saved_figure.png') #remove old img data
except:
print("Datei nicht auffindbar")
finally:
mydb = statistics.SQLconnect() #connects to SQL server
mycursor = mydb.cursor(buffered=True)
sql_select_query = """SELECT * FROM landkreis WHERE Stadtname = %s ORDER BY Zuletzt_geupdatet DESC""" #SQL query
mycursor.execute(
sql_select_query,
(county,
)) #takes input from DiscordBotDebug and puts in in %s above
myresults = []
for x in range(0,
8): #sets how many results will be displayed/searched
myresult = mycursor.fetchone() #actually commit query
myresults.append(myresult)
mydb.close()
date = []
incidence = []
for x in myresults: #search trough results of query
if x != None:
date.append(str(x[6]))
incidence.append(float(x[5]))
df = pd.DataFrame({county: incidence}, index=date)
df = df[::-1]
ax = df.plot(kind="bar", color="g")
for p in ax.patches:
ax.annotate(str(p.get_height()),
(p.get_x() * 1.005, p.get_height() * 1.005))
plt.xticks(rotation=30, horizontalalignment="center")
plt.title('Inzidenzwert von ' + county) #name axis and title
plt.ylabel('Inzidenzfälle')
plt.xlabel('Datum')
figName = 'saved_figure.png'
fig = plt.gcf()
fig.set_size_inches((8.5, 11), forward=False)
fig.savefig(figName, dpi=500)
imgdata = 'saved_figure.png' #save and return the img path file
return imgdata
def scatterplot(county):
try:
os.remove('saved_figure.png') #remove old img data
except:
print("Datei nicht auffindbar")
finally:
mydb = statistics.SQLconnect() #connects to SQL server
mycursor = mydb.cursor()
sql_select_query = """SELECT * FROM landkreis WHERE Stadtname = %s ORDER BY Zuletzt_geupdatet DESC""" #SQL query
mycursor.execute(
sql_select_query,
(county,
)) #takes input from DiscordBotDebug and puts in in %s above
myresult = mycursor.fetchall()
mydb.close()
date = []
incidence = []
for x in myresult: #search trough results of query
date.append(str(x[6]))
incidence.append(float(x[5]))
date = date[::-2]
incidence = incidence[::-2]
df = pd.DataFrame({county: incidence}, index=date)
ax = df.plot()
plt.xticks(rotation=30, horizontalalignment="center")
plt.title('Inzidenzwert von ' + county) #name axis and title
plt.ylabel('Inzidenzfälle')
plt.xlabel('Datum')
ax.set_ylim(bottom=0)
figName = 'saved_figure.png'
fig = plt.gcf()
fig.set_size_inches((8.5, 11), forward=False)
fig.savefig(figName, dpi=500)
imgdata = 'saved_figure.png' #save and return the img path file
return imgdata
def statscompare(county):
try:
os.remove('saved_figure.png') #remove old img data
except:
print("Datei nicht auffindbar")
finally:
comparecounties = []
mydb = statistics.SQLconnect() #connects to SQL server
mycursor = mydb.cursor(buffered=True)
vsindex = county.index(" vs ")
secondcounty = vsindex + 4 #skips the " vs " trough indexing
comparecounties.append(county[:vsindex]) #takes county bevore the "vs"
comparecounties.append(county[secondcounty:]) #takes the second county
incidence = []
for county in comparecounties: #Goes trough both counties
sql_select_query = """SELECT * FROM landkreis WHERE Stadtname = %s ORDER BY Zuletzt_geupdatet DESC""" #SQL query
mycursor.execute(
sql_select_query,
(county,
)) #takes input from DiscordBotDebug and puts in in %s above
myresults = []
for x in range(
0, 8): #sets how many results will be displayed/searched
myresult = mycursor.fetchone() #actually commit query
myresults.append(myresult)
date = []
for x in myresults: #search trough results of query
if x != None:
date.append(str(x[6]))
incidence.append(float(x[5]))
county1inc = incidence[
len(incidence) //
2:] #takes the first half of the values linked to the first county
county2inc = incidence[:len(incidence) // 2]
df = pd.DataFrame(
{
comparecounties[1]: county1inc,
comparecounties[0]: county2inc
},
index=date) #create panda dataframe
df = df[::-1]
ax = df.plot(kind="bar", rot=0)
for p in ax.patches:
ax.annotate(str(p.get_height()),
(p.get_x() * 1.005, p.get_height() * 1.015),
rotation=90)
plt.xticks(rotation=30, horizontalalignment="center")
plt.title(
f"Inzidenzfälle von {comparecounties[1]} vs {comparecounties[0]}"
) #name axis and title
plt.ylabel('Inzidenzfälle')
plt.xlabel('Datum')
figName = 'saved_figure.png'
fig = plt.gcf()
fig.set_size_inches((8.5, 11), forward=False)
fig.savefig(figName, dpi=500)
mydb.close()
imgdata = 'saved_figure.png' #save and return the img path file
return imgdata
def piechart(county):
try:
os.remove('saved_figure.png') #remove old img data
finally:
states = {
"Brandenburg": "BB",
"Berlin": "BE",
"Baden-Württemberg": "BW",
"Bayern": "BY",
"Bremen": "HB",
"Hessen": "HE",
"Hamburg": "HH",
"Mecklenburg-Vorpommern": "MV",
"Niedersachsen": "NI",
"Nordrhein-Westfalen": "NW",
"Rheinland-Pfalz": "RP",
"Schleswig-Holstein": "SH",
"Saarland": "SL",
"Sachsen": "SN",
"Sachsen-Anhalt": "ST",
"Thüringen": "TH"
}
if county in states:
cases, deaths = statepiechart(county)
elif county in states.values():
key_list = list(states.keys())
val_list = list(states.values())
position = val_list.index(county)
state = key_list[position]
cases, deaths = statepiechart(state)
else:
querysearch = county
mydb = statistics.SQLconnect() #connects to SQL server
mycursor = mydb.cursor()
date_now = datetime.now()
currentdate = date_now.date()
sql_select_query = """SELECT * FROM landkreis WHERE Zuletzt_geupdatet = %s AND Stadtname = %s""" #SQL query
mycursor.execute(sql_select_query, (
currentdate,
querysearch,
)) #takes input from DiscordBotDebug and puts in in %s above
myresult = mycursor.fetchall() #actually commit query
mydb.close()
cases = []
deaths = []
for x in myresult: #search trough results of query
cases.append(int(x[3]))
deaths.append(int(x[4]))
string1 = f" Fälle: {str(sum(cases))}"
string2 = f" Tode: {str(sum(deaths))}"
d = {
'Kategorien': [string1, string2],
county: [sum(cases), sum(deaths)]
}
df = pd.DataFrame(d)
df.groupby(['Kategorien']).sum().plot(kind='pie',
y=county,
startangle=90)
plt.title('Verhältnis Fälle/Tode von ' + county)
plt.legend(loc="lower right")
figName = 'saved_figure.png'
fig = plt.gcf()
fig.savefig(figName, dpi=500)
imgdata = 'saved_figure.png' #save and return the img path file
return imgdata
def scatterplotcomp(county):
try:
os.remove('saved_figure.png') #remove old img data
except:
print("Datei nicht auffindbar")
finally:
comparecounties = []
vsindex = county.index(" vs ")
secondcounty = vsindex + 4 #skips the " vs " trough indexing
comparecounties.append(county[:vsindex]) #takes county bevore the "vs"
comparecounties.append(county[secondcounty:]) #takes the second county
mydb = statistics.SQLconnect() #connects to SQL server
mycursor = mydb.cursor()
incidence = []
for county in comparecounties:
sql_select_query = """SELECT * FROM landkreis WHERE Stadtname = %s ORDER BY Zuletzt_geupdatet DESC""" #SQL query
mycursor.execute(
sql_select_query,
(county,
)) #takes input from DiscordBotDebug and puts in in %s above
myresult = mycursor.fetchall()
date = []
for x in myresult: #search trough results of query
date.append(str(x[6]))
incidence.append(float(x[5]))
county1inc = incidence[
len(incidence) //
2:] #takes the first half of the values linked to the first county
county2inc = incidence[:len(incidence) // 2]
mydb.close()
#df = pd.DataFrame({county: incidence},
# index=date)
df = pd.DataFrame(
{
comparecounties[1]: county1inc,
comparecounties[0]: county2inc
},
index=date) #create panda dataframe
df = df[::-1]
ax = df.plot()
plt.xticks(rotation=30, horizontalalignment="center")
plt.title('Inzidenzwert von ' + county) #name axis and title
plt.ylabel('Inzidenzfälle')
plt.xlabel('Datum')
ax.set_ylim(bottom=0)
figName = 'saved_figure.png'
fig = plt.gcf()
fig.set_size_inches((8.5, 11), forward=False)
fig.savefig(figName, dpi=500)
imgdata = 'saved_figure.png' #save and return the img path file
return imgdata