Hazim Hazim - 1 year ago 69
MySQL Question

Why ser.readline data not appear in mysql?

I was using a sensor that connect to arduino and using ser.readline() to print reading from sensor and success. But when I want to send the reading value using ser.readline to mysql. It only pass the device name and current time and the value of the reading that show in mysql is '0'. Here my coding.

import time
import serial
import smtplib
import datetime
import MySQLdb

db = MySQLdb.connect("", "fyp", "123456", "system")
ser = serial.Serial('/dev/ttyACM0', 9600)

while True:
api = ser.readline()
current_time = datetime.datetime.now()
print (api)

sql = "INSERT INTO reading (series_no, time, api) VALUES ('test1', current_time, api)"
data = (current_time, api)


Using %s is not work either. Thank you in advance.


You are not parameterizing the query correctly. Several things to fix:

  • add placeholders for the query parameters - %s, no quotes around
  • pass the data tuple to execute()
  • commit the transaction

Fixed version:

sql = "INSERT INTO reading (series_no, time, api) VALUES ('test1', %s, %s)"
data = (current_time, api)
cur.execute(sql, data)