I am trying to insert Arduino data into a database through Python, however, it will not do it. Basically I am assigning data that I read in from the serial port assigned to my Arduino and storing the first value of it in the variable arduinoData
. in my insert statement I am trying to use a string literal to put the arduinoData
into the table. Here is the code:
import mysql.connector from mysql.connector import errorcode from time import sleep import serial # Obtain connection string information from the portal config = { 'host':'oursystem.mysql.database.azure.com', 'user':'project', 'password':'', 'database':'projectdb' } # Construct connection string try: conn = mysql.connector.connect(**config) print("Connection established") except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with the user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print(err) else: cursor = conn.cursor() ser = serial.Serial('/dev/ttyACM0', 9600) # Establish the connection on a specific port arduinoData=ser.read().strip() print arduinoData # Drop previous table of same name if one exists cursor.execute("DROP TABLE IF EXISTS ArduinoData;") print("Finished dropping table (if existed).") # Create table cursor.execute("CREATE TABLE ArduinoData (value VARCHAR(20));") print("Finished creating table.") # Insert some data into table cursor.execute("INSERT INTO ArduinoData (value) VALUES (%s);",(arduinoData)) print("Inserted",cursor.rowcount,"row(s) of data.") # Cleanup conn.commit() cursor.close() conn.close() print("Done.")
If I put the %s
in single quotes like '%s'
it just prints that instead of my arduinoData
. Can anyone see what is wrong here, thanks.
Advertisement
Answer
Simply pass a tuple (arduinoData,)
which means a comma within parentheses for a single value or list [arduinoData]
and not a single value (arduinoData)
in your parameterization:
cursor.execute("INSERT INTO ArduinoData (`value`) VALUES (%s);",(arduinoData,))
However if arduinoData is a list of multiple values, then use executemany
, still passing a list. Also, escape value
which is a MySQL reserved word:
cursor.executemany("INSERT INTO ArduinoData (`value`) VALUES (%s);",[arduinoData])