CODEWITHSUNDEEP
pythonmysqlinsert
Charlie Ansell
Charlie Ansell

Reputation: 461

Python SQL insert statement not working

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.

Upvotes: 1

Views: 1929

Answers (4)

geriwald
geriwald

Reputation: 360

I just lost two hours on this : If you're trying to observe what's happening to your database with phpmyadmin, please note that all your insert commands won't be visible until you commit them

connection.commit()

Upvotes: 2

Charlie Ansell
Charlie Ansell

Reputation: 461

I have just figured out what was wrong, using Parfait's suggestion of parsing a tuple, i just changed my insert statement from cursor.execute("INSERT INTO ArduinoData (value) VALUES (%s);",(arduinoData))to cursor.execute("INSERT INTO ArduinoData (value) VALUES (%s);",(arduinoData,))thanks to everyone who answered you were all a great help! :D

Upvotes: 0

mDumple
mDumple

Reputation: 45

I may have interpreted this wrong but, shouldn't there be something like certain_value = '%s' otherwise it doesn't know what it is looking for.

Upvotes: 0

Parfait
Parfait

Reputation: 107767

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])

Upvotes: 0

Related Questions