58 lines
1.4 KiB
Python
58 lines
1.4 KiB
Python
def sql_get_dict(mycursor,mytable,myvar):
|
|
mycursor.execute("select * from "+str(mytable))
|
|
tout={}
|
|
tt=mycursor.fetchall()
|
|
for tv in tt:
|
|
tout[tv[myvar]]=tv['id']
|
|
return(tout)
|
|
|
|
def sql_insert(q):
|
|
mysqlmem=apsw.Connection(":memory:")
|
|
mycursor=mysqlmem.cursor()
|
|
mycursor.execute("create table data_storage(time integer,sensor_id integer,value integer);")
|
|
bsql=False
|
|
sqlinsert="insert into data_storage (time,sensor_id,value) values ({0:d},{1:d},{2:d})"
|
|
sqldata=[]
|
|
try:
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
except:
|
|
print("Could not connect to sql server! Quitting")
|
|
else:
|
|
bsql=True
|
|
mycursor=mydb.cursor(pymysql.cursors.DictCursor)
|
|
|
|
while True:
|
|
if q.empty():
|
|
if not bsql:
|
|
try:
|
|
mydb=pymysql.connect(read_default_file="~/.my.cnf",database="rasolar")
|
|
except:
|
|
bsql=False
|
|
else:
|
|
mycursor=mydb.cursor(pymysql.cursors.DictCursor)
|
|
|
|
else:
|
|
time.sleep(0.1)
|
|
else:
|
|
try:
|
|
indata=q.get()
|
|
if indata is not None:
|
|
q.task_done()
|
|
except Exception as e:
|
|
print("Error during queuing")
|
|
print(e)
|
|
else:
|
|
if indata is not None:
|
|
sqlin=sqlinsert.format(indata['time'],indata['sensorid'],indata['value'])
|
|
try:
|
|
mycursor.execute(sqlin)
|
|
except:
|
|
print("Eror in execute sql insert")
|
|
print(sqlin,file="sql_missed.txt")
|
|
else:
|
|
if bsql:
|
|
mydb.commit()
|
|
else:
|
|
mysqlmem.commit()
|
|
|