def put_sensor_data():
json = request.get_json()
data = {
'sensor': json['sensor'],
'time': time.strftime('%Y-%m-%d %H:%M:%S'),
'temp': int(json['temp']),
'humidity': int(json['humidity']),
}
sql = 'insert into sensor_data value (%(sensor)s, %(time)s, %(temp)s, %(humidity)s);'
tele_util.executeSQL(sql, data)
return "OK"
def dnbot_hook():
m = request.get_json()
if m.get('message', {}).get('photo', None):
sql = """
insert into photo values
select '%(f)s' from DUAL where not exists (
select file_id from photo where file_id = '%(f)s'
) limit 1""" % {
'f': m['message']['photo'][0]['file_id']
}
tele_util.executeSQL(sql)
return "OK"
def addList(chat_id, name, entry):
sql = '''
insert into ls_entry (chat_id, name, entry, cts)
select %(chat_id)s, %(name)s, %(entry)s, %(ts)s from DUAL where not exists (
select name from ls_entry where chat_id=%(chat_id)s and name=%(name)s and entry=%(entry)s
) limit 1
'''
data = {
'chat_id': chat_id,
'name': name,
'entry': entry,
'ts': time.strftime('%Y-%m-%d %H:%M:%S')
}
tele_util.executeSQL(sql, data)
def getDailyPost(tags):
sql = "select id, file_id, type from daylie_post where tags like '%s' and date_day='%s' limit 1" \
% (tags, time.strftime('%Y-%m-%d'))
rows = tele_util.readSQL(sql)
if rows:
return rows[0][2], rows[0][1]
else:
sql = """ select id, file_id, type from daylie_post where tags like '%s' and date_day=''
order by rand() limit 1""" % (tags)
rows = tele_util.readSQL(sql)
if len(rows) == 0:
return 'der tag ' + tags + ' ist aufgebraucht', 'm'
sql = "update daylie_post set date_day = '%s' where id=%s" % (
time.strftime('%Y-%m-%d'), rows[0][0])
tele_util.executeSQL(sql)
return rows[0][2], rows[0][1]
def importfile(filename, typ3):
print('import %s\t%s' % (typ3, filename))
errors = []
with open(filename) as f:
s = f.read()
s = escape(s)
i = 0
for l in s.split('\n'):
sql = "INSERT INTO tod (type, text)value (%s,%s);"
try:
tele_util.executeSQL(sql, data=(typ3, l.encode('utf-8')))
i += 1
except:
errors.append(l)
print('finish import ' + str(i))
print(errors)
return len(errors) == 0
def props(msg):
m = re.search(REX_PROP, msg.txt)
if m == None:
return
data = {
'chat_id': str(msg.getChatId()),
'name': m.group(1),
'value': m.group(2)
}
sql = "select value from props where chat_id=%(chat_id)s and name=%(name)s"
value = tele_util.getOneSQL(sql, data=data)
if value:
sql = "update props set value=%(value)s where chat_id=%(chat_id)s and name=%(name)s"
tele_util.executeSQL(sql, data=data)
else:
value = 'None'
sql = "insert into props (chat_id, name, value) values (%(chat_id)s, %(name)s, %(value)s)"
tele_util.executeSQL(sql, data=data)
tele_util.clearGetProp()
newval = str(tele_util.getProp(msg.getChatId(), m.group(1)))
msg.send('Property *' + m.group(1) + '* von *' + value + '* nach *' +
newval + '* geändert',
parse_mode='Markdown')
def zitat(msg):
m = re.search(REX_ZTT_ADD, msg.upd["message"]["text"])
if m.group(1) != None:
sql = '''
INSERT INTO ztt (chat_id, autor, text, cts)
value (%(chat_id)s, %(at)s, %(tx)s, %(cts)s)
'''
data = {
'chat_id': msg.getChatId(),
'at': m.group(3),
'tx': m.group(2),
'cts': time.strftime('%Y-%m-%d %H:%M:%S')
}
tele_util.executeSQL(sql, data)
msg.send(('%s ~|~ %s' % (m.group(2), m.group(3))))
return "OK"
m = re.search(REX_ZTT, msg.upd["message"]["text"])
if m != None:
sql = """
select concat(text, ' ~|~ ', autor)
from ztt
where chat_id=%(id)s
and lower(text) like '%%%(t)s%%'
or lower(autor) like '%%%(t)s%%'
order by rand() limit 1
""" % {
'id': msg.getChatId(),
't': m.group(1).lower()
}
print(sql)
msg.send(tele_util.getOneSQL(sql))
else:
sql = """
select concat(text, ' ~|~ ', autor)
from ztt
where chat_id=%s order by rand() limit 1""" % (msg.getChatId())
msg.send(tele_util.getOneSQL(sql))
def delList(chat_id, name, entry):
sql = 'delete from ls_entry where chat_id=%(chat_id)s and name=%(name)s and entry=%(entry)s'
data = {'chat_id': chat_id, 'name': name, 'entry': entry}
tele_util.executeSQL(sql, data)