-1

This question is an exact duplicate of:

Hello I want to insert sensor data in mysql to python. My sensor is connected to arduino and it sends sensor data to raspberry pi via bluetooth. Raspberry pi receives sensor data to python code. This is my python code to receive sensor data.

#! /usr/bin/python
import bluetooth
bd_addr="(MAC ADDRESS)"
port=1
sock=bluetooth.BluetoothSocket(bluetooth.RFCOMM)
sock.connect((bd_addr,port))
data=""
while 1
  try:
    data +=sock.recv(1024)
    data_end = data.find('\n')
    if data_end!=-1:
      rec=data[:data_end]
      print data
      data = data[data_end+1:]
  except KeyboardInterrupt:
    break
sock.close()

If I run this python code I can check sensor data. And I want to insert sensor data in mysql. So I change python code like this and it's file name is mysql.py

#! /usr/bin/python
import bluetooth
import MySQLdb

bd_addr="(MAC ADDRESS)"
port=1
sock=bluetooth.BluetoothSocket(bluetooth.RFCOMM)
sock.connect((bd_addr,port))
data=""
db=MySQLdb.connect("localhost", "root", "1234", "testdb")
curs=db.cursor()
print 'Start Monitor'

while 1:
  try:
    data +=sock.recv(1024)
    data_end=data.find('\n')
    if data_end!=-1:
      rec=data[:data_end+1:]
      print data
      data=data[data_end+1:]
      curs.execute("INSERT INTO weight (kg) VALUES('data')")
      db.commit()
  except KeyboardInterrupt:
    break
sock.close()
db.close

I construct database and table like this.

create database testdb;
use testdb;
create table weight (kg float);

And I run python code terminal displays like this.

Start monitor
1.50 //sensor data
mysql.py:24 Warning: INcorrect double value: 'data'for column 'kg' at row 1
  curs.execute("INSERT INTO weight (kg) VALUES('data')")
1.50//sensor data
1.50
1.50

And in localhost/phpmyadmin(url) '0' is inserted. (not 1.50) How can I solve it?

marked as duplicate by goldilocks Mar 18 '18 at 13:49

This question was marked as an exact duplicate of an existing question.

  • It was not explicitly explained why your first question was closed as off-topic, so for future reference: General python programming questions belong on our larger parent site, Stack Overflow. This site is only for things that are specific to the Raspberry Pi, e.g., stuff about working with the GPIOs. – goldilocks Mar 18 '18 at 13:52
1

You are sending the string data to MySQL where you should be sending the string 1.5. You need to interpolate the value of your variable with that name into the string before you send it.

Try something like this instead:

 curs.execute("INSERT INTO weight (kg) VALUES(%f)" % data)
  • I change code but result is like this. Start Monitor 1.50 Traceback(most recent call last); File "mysql.py", line 24, in <module> curs.execute("INSERT INTO weight (kg) VALUES($f)" % data) Type Error : float argument required, not str – 김진수 Mar 18 '18 at 14:00
  • I see. You made the data variable as a string. So either make data a floating point number in python and use what I gave you, or use %s instead of %f in what I gave you. – Brick Mar 18 '18 at 15:54
  • If I use %s it says operationalError "Column count doesn't match value count at row 1. And how can I make data a floating point number ? – 김진수 Mar 19 '18 at 8:44
  • You may have to debug it from here on your own using the MySQL docs and more print statements. Your basic problem was that you were not including the data that you want into the string sent to MyQSL (rather you were sending the literal string 'data' instead). I gave you a common way to interpolate a variable value into a string - It's not clear why it's not working now, but the error suggests that you got an empty string this time where you should have had the value. Quick experimenting on your side should fix that, I would think. – Brick Mar 19 '18 at 12:30
0

You are trying to insert the literal string value data into the table.

I've not used mysqldb for ages but you would need to do something like this:

curs.execute("INSERT INTO weight (kg) VALUES (?);", data)

Not the answer you're looking for? Browse other questions tagged or ask your own question.