def run():
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
today = date.today()
dta = timedelta(days=1)
now = today - dta
ans = now.strftime("%m%d")
name = "morning_" + ans
new_column = """ALTER TABLE body_temperture Change morning `%s` FLOAT"""
gcpsql.execute(new_column, (name, ))
lssh1.commit()
name = "evening_" + ans
new_column = """ALTER TABLE body_temperture Change evening `%s` FLOAT"""
gcpsql.execute(new_column, (name, ))
lssh1.commit()
new_column = "ALTER TABLE body_temperture ADD morning FLOAT after nickname"
gcpsql.execute(new_column)
lssh1.commit()
new_column = "ALTER TABLE body_temperture ADD evening FLOAT after morning"
gcpsql.execute(new_column)
lssh1.commit()
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
def bulletboard():
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
sql_select_Query = "select * from bulletboard"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
for row in records:
dateString = row[3]
#if(dateString==""): break
dateFormatter = "%Y/%m/%d"
a = datetime.strptime(dateString, dateFormatter)
#a=a+timedelta(days=1)
if (datetime.now() > a):
sql_Delete_query = """DELETE FROM bulletboard WHERE ID= %s"""
test = row[0]
gcpsql.execute(sql_Delete_query, (test, ))
lssh1.commit()
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
def test():
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
pr = "--------------------\n考試：\n\n"
sql_select_Query = "select * from test ORDER BY expiretime ASC"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
i = 0
for row in records:
i = i + 1
pr += str(i) + "." + row[2]
date = row[3]
datecor = date[5] + date[6] + "/" + date[8] + date[9]
pr += " (" + datecor + ")\n"
pr += "\n"
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return pr
def homework():
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
timeotp = time.strftime("%Y/%m/%d", time.localtime())
pr = timeotp + "放學推播：\n--------------------\n作業：\n\n"
sql_select_Query = "select * from homework ORDER BY expiretime ASC"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
i = 0
for row in records:
i = i + 1
pr += str(i) + "." + row[2]
date = row[3]
datecor = date[5] + date[6] + "/" + date[8] + date[9]
pr += " (" + datecor + ")\n"
pr += "\n"
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return pr
def userpick():
users.clear()
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
gcpsql = lssh1.cursor()
sql_select_Query = "select line_id,notify from user_id WHERE line_id IS NOT NULL"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
for row in records:
if (row[1] == 1):
users.append(row[0])
def teacher():
lssh1 = mysql.connector.connect(host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database())
gcpsql = lssh1.cursor()
sqlselect = """select ID,object from bulletboard ORDER BY expiretime ASC"""
gcpsql.execute(sqlselect)
records = gcpsql.fetchall()
output = ""
i = 0
for row in records:
i = i + 1
output += str(i) + "." + row[1] + "\n\n"
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return output