pythoncode to Mysql

[vaarasp]

Hello
I'm a not too experienced Raspberryuser, managed to get temp sensors working and putting dada in database etc. Mainly by copy-paste but starting to understand a bit how it works.
To get some sensors for air-quality measuring, I found some code to to this and after some fumbling around it works. MQ-7 sensor, MCP3008 etc.
I get "exact" data for LPG, CO, Smoke.
The necessary input for MQ measurement I got from:
https://tutorials-raspberrypi.com/confi ... nsor-mq-x/
my problem is: I want to get these data from python script via MySQL into a table and that's where I don't seem to advance a lot.
I created a table:
[img]Schermafbeelding 2020-02-02 om 17.15.30.jpg[img]
and did many attempts to create lines to put the data in table "GasData2"

Here's my script till now...the first part works (displaying data) the second part shows me that the data are not put in Dbase.
Thx if someone can give me the code that accesses the table!.

Code: Select all

GasS2SQL.py
#hersteld van GasS1SQL.py.save 26-01
#ombouw example.py naar Gassensor.py 17012020 voor SQL
from mq import *
from MCP3008 import MCP3008
import logging
import os
import glob
import MySQLdb
from time import strftime
#logging.basicConfig(filename="logexample1.log" , level=logging.DEBUG)

#adc = MCP3008()
#value = adc.read( channel = 0 )
#print("Anliegende Spannung: %.2f" % (value / 1023 * 3.3) )

#oude deel van example.py
from mq import *
import sys, time
# Variables for MySQL
db = MySQLdb.connect(host="localhost", user="root",passwd="raspeter50", db="temp_database")
cur = db.cursor()

try:
    print("Press CTRL+C to abort.")
    
    mq = MQ();
    #logging.debug("MQ?: {}".format.MQ())
    while True:
        perc = mq.MQPercentage()
        sys.stdout.write("\r")
        sys.stdout.write("\033[K")
        sys.stdout.write("LPG: %g ppm, CO: %g ppm, Smoke: %g ppm" % (perc["GAS_LPG"], perc["CO"], perc["SMOKE"]))
        datetimeWrite = (time.strftime("%Y-%m-%d ") + time.strftime("%H:%M"))
	#print ("\r")
	print "CO"
	#%g = percLPG
	#%g = percCO
	#%g = percSmoke
	print datetimeWrite
	sql = ("""INSERT INTO GasData2 (datetimeCO, percLPG, percCO, percSMOKE) VALUES (%s,%s,%s,%s)""",(datetimeWrite,perc))

	sys.stdout.flush()
        time.sleep(1)

	try:
		print "Writing to DB..."
		cur.execute(*sql)
		db.commit()
		print "weggeschreven"

	except:
		db.rollback()
		print "lukt niet naar DB te schrijven"

except:
    print("\nAbort by user")
   
code]

[topguy]

Code: Select all

sql = ("""INSERT INTO GasData2 (datetimeCO, percLPG, percCO, percSMOKE) VALUES (%s,%s,%s,%s)""",(datetimeWrite,perc))

I'm not an expert of Python nor SQL but it looks like this expects 4 values, but you are only providing 2. ( also I'm assuming "%s" expects strings ? )

You should probably create a simple test script where you just test this insert command with dummy data until you get it right.

[vaarasp]

me neither, but as far as I know "datetimeCO, percLPG, percCO, percSMOKE" are the column names of the Table I created (GasData2) and (%s,%s,%s,%s) should be the string values the program gives each time it makes a loop.
Maybe using sys.stdout.write("LPG: %g ppm, CO: %g ppm, Smoke: %g ppm" % (perc["GAS_LPG"], perc["CO"], perc["SMOKE"]))
is the problem? Tried xxxx possibilities and do't find a clear explanation for this problem

[DougieLawson]

me neither, but as far as I know "datetimeCO, percLPG, percCO, percSMOKE" are the column names of the Table I created (GasData2) and (%s,%s,%s,%s) should be the string values the program gives each time it makes a loop.
Maybe using

Code: Select all

sys.stdout.write("LPG: %g ppm, CO: %g ppm, Smoke: %g ppm" % (perc["GAS_LPG"], perc["CO"], perc["SMOKE"]))

is the problem? Tried xxxx possibilities and do't find a clear explanation for this problem

Try

Code: Select all

sql = ("""INSERT INTO GasData2 (datetimeCO, percLPG, percCO, percSMOKE) VALUES (%s,%s,%s,%s);""", % (perc["GAS_LPG"], perc["CO"], perc["SMOKE"],))

Note that trailing comma on the tuple of values you're passing to the string formatter.

[vaarasp]

This didn't work out well neither. Tried several variation of your solution. But no result. Thanks anyway. Maybe the sysdout is not compatible to this kind of syntax