#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0

    Passing data to Mysql DB from serial port


    First this is first time using Python. I'm trying to pass data from a serial port directly to MySQL db. I could read the port but I get a error stating that the Name humidity is not defined. What is the possible cause of this issue?


    Code:
    import serial
    import MySQLdb
    ser = serial.Serial( '/dev/ttyUSB1', 9600, timeout=1 )
    conn = MySQLdb.connect( host='localhost', db='Weatherboard', user='rich', passwd='wordpass' )
    curs = conn.cursor()
    
    s = ser.readline().strip("\r\n")
    
    curs.execute( 'insert into weatherdata (humiditycol, sht15tempcol, scp1000tempccol, scp1000presscol, scp1000tempfcol, temt6000lightcol, batterycol) \
    values (%s, %s, %s, %s, %s, %s, %s)', (humidity, sht15temp, scp1000tempC, scp1000press, scp1000tempF, temt6000light, battery))
    conn.commit()
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by Storm017
    First this is first time using Python. I'm trying to pass data from a serial port directly to MySQL db. I could read the port but I get a error stating that the Name humidity is not defined. What is the possible cause of this issue?


    Code:
    import serial
    import MySQLdb
    ser = serial.Serial( '/dev/ttyUSB1', 9600, timeout=1 )
    conn = MySQLdb.connect( host='localhost', db='Weatherboard', user='rich', passwd='wordpass' )
    curs = conn.cursor()
    
    s = ser.readline().strip("\r\n")
    
    curs.execute( 'insert into weatherdata (humiditycol, sht15tempcol, scp1000tempccol, scp1000presscol, scp1000tempfcol, temt6000lightcol, batterycol) \
    values (%s, %s, %s, %s, %s, %s, %s)', (humidity, sht15temp, scp1000tempC, scp1000press, scp1000tempF, temt6000light, battery))
    conn.commit()
    Can you post your full code?
    How are you assigning a value to humidity?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    That is it...I was assuming %s was assigning the value. I guess a type needs to be declared.

    Originally Posted by sheffieldlad
    Can you post your full code?
    How are you assigning a value to humidity?
  6. #4
  7. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,931
    Rep Power
    481
    Code:
    s = ser.readline().strip("\r\n")
    
    # insert code here to parse the string s
    # and assign values to all variables
    # (humidity, sht15temp, scp1000tempC, scp1000press, scp1000tempF, temt6000light, battery))
    
    curs.execute(
        'insert into weatherdata '
        '(humiditycol, sht15tempcol, scp1000tempccol, scp1000presscol, scp1000tempfcol, temt6000lightcol, batterycol) '
        'values '
        '(%s, %s, %s, %s, %s, %s, %s)',
        (humidity, sht15temp, scp1000tempC, scp1000press, scp1000tempF, temt6000light, battery))
    Try to avoid backslash line continuation. I've never had to use one. Here I've used shorter lines than you posted without line continuations. It works because python reads lines through the expression closing parenthesis as part of a single statement.
    Python also concatenates successive strings.
    [code]Code tags[/code] are essential for python code and Makefiles!
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    The information you provided helped. The serial data is now being passed into MYsql db.

    Originally Posted by b49P23TIvg
    Code:
    s = ser.readline().strip("\r\n")
    
    # insert code here to parse the string s
    # and assign values to all variables
    # (humidity, sht15temp, scp1000tempC, scp1000press, scp1000tempF, temt6000light, battery))
    
    curs.execute(
        'insert into weatherdata '
        '(humiditycol, sht15tempcol, scp1000tempccol, scp1000presscol, scp1000tempfcol, temt6000lightcol, batterycol) '
        'values '
        '(%s, %s, %s, %s, %s, %s, %s)',
        (humidity, sht15temp, scp1000tempC, scp1000press, scp1000tempF, temt6000light, battery))
    Try to avoid backslash line continuation. I've never had to use one. Here I've used shorter lines than you posted without line continuations. It works because python reads lines through the expression closing parenthesis as part of a single statement.
    Python also concatenates successive strings.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Location
    Mendoza, Argentina
    Posts
    1
    Rep Power
    0
    Nice! Worked for me too.

IMN logo majestic logo threadwatch logo seochat tools logo