Support required to data log in SQL server via raspbeery pi

Question

1.00/5 (1 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 22-Jun-20 15:00pm

Rithikvg

Updated 22-Jun-20 15:23pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Garth J Lancaster · Answer 1 · 2020-06-22T15:24:00

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/[^]

Posted 22-Jun-20 15:24pm

Garth J Lancaster

Updated 22-Jun-20 16:19pm

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