Click here to Skip to main content
14,576,808 members
Rate this:
Please Sign up or sign in to vote.
See more:
Dear All,
I have a problem while data logging in SQL server. We are not able to do it. When we are running the python code in raspberry pi we are getting the following error msg. 


Please anyone help me out. 


What I have tried:

Python program :
import serial
import time
import MySQLdb as mdb
arduino = serial.Serial("/dev/ttyACM0")
arduino.baudrate=9600
data = arduino.readline()
time.sleep(1)
data = arduino.readline()
pieces = data.split("\t")
temperature = pieces[0]
humidity = pieces[1]
con = mdb.connect('localhost', 'root', '12345678', 'testdb');
with con:
     cursor = con.cursor()
    cursor.execute("""INSERT INTO TempDB VALUES('',%s, %s)""", (temperature,humidity))
    con.commit()
    cursor.close()
  
Error message: 
 Traceback (most recent call last):
  File "./insertDB.py", line 21, in <module>
    cursor.execute("""INSERT INTO TempDB VALUES('',%s, %s)""", (temperature,humidity))
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
_mysql_exceptions.OperationalError: (1366, "Incorrect integer value: '' for column `testdb`.`TempDB`.`ID` at row 1") 
Posted
Updated 22-Jun-20 15:23pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

1) It's really helpful if you're asking a question to do with a DB or single table within a DB, that you provide a schema - else really, it's a bit of a guess (you could & should use 'Improve Question' to update your question)

2) The thing I see straight off is this statement
cursor.execute("""INSERT INTO TempDB VALUES('',%s, %s)""", (temperature,humidity))
has three values, yet I suspect your table has an autoincrement ID column as the first column, then fields for the temperature & humidity - so you don't need the empty field at the start of the values (but see point 1 why this may be a guess)

3) although it seems 'easy' to write SQL that way, it's not in the long run - it's a security risk for a start ... you really need to write a paramaterized statement ...
with con:
  cursor = con.cursor(prepared=True)
  sql_insert_query = """ INSERT INTO TempDB (temperature, humidity) VALUES (?,?)"""

  insert_tuple = (temperature, humidity)
  cursor.execute(sql_insert_query, insert_tuple)
  con.commit()


As stated, Ive had to assume your column names - see how I state the column names in the insert query, that means anyone reading your code later knows rather than guesses where things are going - sometime being explicit is better - easy enough to change the names. These links below may help as well in future

Best practice for SQL statements in Python - b.telligent[^] (See Under 'Best practice: parameterised queries')

https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/[^]
   
v5
Comments
Rithikvg 22-Jun-20 21:35pm
   
Thanks for your reply. I want to update the values in a single table within a DB.
I have tried with the youtube guide. I have seen Auto_Increment in column name " Extra " column. But I could not find out the column Name "Extra" while creating the table. So I am not filled the Auto_increment. Please help me to create the Auto_increment in the column Name Extra
Garth J Lancaster 22-Jun-20 21:41pm
   
please modify your answer to include the create statement used to create the table (that should have the details) - I dont know how you expect us to figure out which 'youtube guide' you're referring to without actually supplying it, and most of 'us' wont watch some random youtube link anyway
Garth J Lancaster 22-Jun-20 22:07pm
   
My spacing was bad AND you need to replace 'connection' with 'con' .. so I think yours should look like
with con:
  cursor = con.cursor(prepared=True)
  sql_insert_query = """ INSERT INTO TempDB (temperature, humidity) VALUES (?,?)"""
  
  insert_tuple = (temperature, humidity)
  cursor.execute(sql_insert_query, insert_tuple)
  con.commit()


You need to be prepared to experiment and test a bit
Rithikvg 22-Jun-20 22:06pm
   
Hi,
Thanks for your reply. I tried with your code. But I am getting following error msgTraceback (most recent call last):
File "./insertDB.py", line 20, in <module>
cursor = connection.cursor(prepared=True)
NameError: name 'connection' is not defined
Rithikvg 22-Jun-20 22:26pm
   
Dear Friend,
Sorry to disturb you, still I got the following error msg
Traceback (most recent call last):
File "./insertDB.py", line 19, in <module>
cursor = con.cursor(prepared=True)
TypeError: cursor() got an unexpected keyword argument 'prepared'
Garth J Lancaster 23-Jun-20 0:41am
   
it looks like your DB layer is different ? ancient .. try masking it out, and given that, you might need to change the insert statement to
with con:
  //cursor = con.cursor(prepared=True)
  sql_insert_query = """ INSERT INTO TempDB (temperature, humidity) VALUES (%s,%s)"""
  
  insert_tuple = (temperature, humidity)
  cursor.execute(sql_insert_query, insert_tuple)
  con.commit()
Rithikvg 23-Jun-20 11:50am
   
Dear Friend,
Thanks for your kind support. But still, I am getting following error msg
//cursor = con.cursor(prepared=True)
^
SyntaxError: invalid syntax
Garth J Lancaster 23-Jun-20 18:22pm
   
just delete that line

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100