0

I got an error on the following python code. I execute this code on raspberry Pi, and I want to get a input for database (MySQL) from BHV1750vi (light emittance sensor).

import smbus
import time
from mysql.connector import MySQLConnection, Error
from test_dbconfig import read_db_config

# Define some constants from the datasheet

DEVICE     = 0x23 # Default device I2C address

POWER_DOWN = 0x00 # No active state
POWER_ON   = 0x01 # Power on
RESET      = 0x07 # Reset data register value

# Start measurement at 4lx resolution. Time typically 16ms.
CONTINUOUS_LOW_RES_MODE = 0x13
# Start measurement at 1lx resolution. Time typically 120ms
CONTINUOUS_HIGH_RES_MODE_1 = 0x10
# Start measurement at 0.5lx resolution. Time typically 120ms
CONTINUOUS_HIGH_RES_MODE_2 = 0x11
# Start measurement at 1lx resolution. Time typically 120ms
# Device is automatically set to Power Down after measurement.
ONE_TIME_HIGH_RES_MODE_1 = 0x20
# Start measurement at 0.5lx resolution. Time typically 120ms
# Device is automatically set to Power Down after measurement.
ONE_TIME_HIGH_RES_MODE_2 = 0x21
# Start measurement at 1lx resolution. Time typically 120ms
# Device is automatically set to Power Down after measurement.
ONE_TIME_LOW_RES_MODE = 0x23

#bus = smbus.SMBus(0) # Rev 1 Pi uses 0
bus = smbus.SMBus(1)  # Rev 2 Pi uses 1

def convertToNumber(data):
# Simple function to convert 2 bytes of data
  # into a decimal number
  return ((data[1] + (256 * data[0])) / 1.2)

def readLight(addr=DEVICE):
  data = bus.read_i2c_block_data(addr,ONE_TIME_HIGH_RES_MODE_1)
  return convertToNumber(data)

def insert_lightlux(l):
    query = "INSERT INTO light(lux) VALUES(%s)"
    args = (l)

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.executemany(query, args)
        if cursor.lastrowid:
            print('last insert id', cursor.lastrowid)
        else:
            print('last insert id not found')

        conn.commit()
    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()

def main():
  while True:
      if readLight() is not None:
       print "luminous emmittance  " + str(readLight()) + " lx"
     time.sleep(10)
       l = [(str(readLight()))]
       insert_lightlux(l)
      else:
       print('Failed to get reading. Try again!')
       sys.exit(1)

if __name__=="__main__":
   main()

The error message is

luminous emmittance  9.16666666667 lx
Not all parameters were used in the SQL statement

Is there anyone know what is wrong in my code?

Thebrizkys
  • 3
  • 5
  • Possible duplicate of [Not all parameters were used in the SQL statement (Python, MySQL)](http://stackoverflow.com/questions/20818155/not-all-parameters-were-used-in-the-sql-statement-python-mysql) – Ani Menon Apr 30 '16 at 09:15
  • I already follow any answer from that that questions, does'nt get good result @AniMenon – Thebrizkys Apr 30 '16 at 09:33
  • It's not related to what you asked but I really need to tell you: you definitely should cache readLight() in a variable in your main loop – Francesco Apr 30 '16 at 09:46

1 Answers1

0

The problem is how you build args for executemany, which expects a list of tuples while you have a tuple of list.

In other words your arg is ([(value)]) but it should be [(value)]

Try changing args = (l) to

args = l
Francesco
  • 4,052
  • 2
  • 21
  • 29
  • Still get an "Not all parameters were used in the SQL statement" message. I really curious about this. I use the same logic for my temperature and humidity sensor, and works great. I wonder what make this code stuck on that message. – Thebrizkys Apr 30 '16 at 09:57
  • Did you see my comment about caching? Maybe your value is None when you try to write it – Francesco Apr 30 '16 at 10:07
  • Yeah, I see that...If it was None, I could'nt get any data on print "luminous emmittance " + str(readLight()) + " lx", but it get "luminous emmittance 9.16666666667 lx" – Thebrizkys Apr 30 '16 at 10:15
  • That's exactly the point. When you read it the First time you get that value, but you do not use that value to be inserted into the database, you read instead a new value. It will be the same value you read before? Who knows – Francesco Apr 30 '16 at 10:28