-2

Basically I am trying to upload the data collected from my raspberry pi to MySQL, I have a table called DHT_data with columns 'datetime' using data type timestamp, 'temp' using data type numeric, 'hum' using data type numeric as well. However using my Python script shown below I keep getting errors like indentation error or syntax error. I am currently new to programming so I don't really know what's the problem. Would appreciate any help in finding the solution or rectifying any errors with the script since I am not sure if it works yet

import time
import Adafruit_DHT
import MySQLdb
import sys
from time import sleep

DHTpin = 4

try:
    db = MySQLdb.connect("localhost", "root", "dmitiot", "IOTassignmentBobo")
    curs = db.cursor()
    print("Successfully connected to database!")
    except:
    print("Error connecting to mySQL database")
    update = True
    while update:
    humidity, temperature = Adafruit_DHT.read_retry(11, DHTpin)
    if humidity is not None and temperature is not None:
    hum = round(humidity)
    temp = round(temperature, 1)
    print('Temp:{:.1f}C'.format(temp))
    print('Hum:{:.1f}'.format(hum))

    curs.execute("""INSERT INTO DHT_data(datetime,temp,hum) 
    VALUES(CURRENT_TIMESTAMP,temp,hum)""")
    print("Inserted Record")
    db.commit()
    sleep(5)
    except MySQLdb.Error as e:
    update = False
    curs.close()
    db.close()
  • Welcome. When you have a code problem, you should always copy the full error message as well as the code. The error message gives you information about exactly where the error is and how to fix it; see this page for some tips. – Aurora0001 Jun 24 '18 at 11:50
  • your code is incorrectly indented. .... it is a python error that has nothing to do with MySQL .... you need to read the basics of python programming – jsotola Jun 24 '18 at 18:01
  • this question has nothing to do with RPi and should be closed – jsotola Jun 24 '18 at 18:02
  • basically, an indentation error occurs when you either don't indent (4 spaces) after a ":" or when you don't stop indenting. Indentation in Python indicates a group of commands that should all be run when the conditional is true or inside a loop. This may help docs.python.org/2.0/ref/indentation.html – Steve Robillard Jun 24 '18 at 18:20
  • 1
    @OyaMist, this isn't a punishment in any way. It's simply people suggesting that this site isn't the best one for the question (as it's primarily a programming question rather than a Pi problem; the problem would be unchanged elsewhere). That said I voted as 'unclear' rather than 'off-topic' but only the final vote affected the reason displayed here. Unfortunately the Q&A format isn't well equipped for problems like this where there are several issues to address such as indentation in Python. As Steve points out, reviewing the documentation might be more helpful to the OP. – Aurora0001 Jun 25 '18 at 13:45
1

Your SQL insert statement is not inserting the temperature and humidity values. In the VALUES clause, the temp and hum variables are not being replaced by their values. See documentation for Python string concatenation

One way to do this is:

sqlinsert="INSERT INTO DHT_data9datetime,temp,hum) "
sqlvalues="VALUES(CURRENT_TIMESTAMP," + str(temp) + "," + str(hum) + ")"
curs.execute(sqlinsert + sqlvalues)

NOTE: CURRENT_TIMESTAMP is a MySQL variable that resolves to the current time

  • Shouldn't "CURRENT_TIMESTAMP" be treated similarly to temp and hum? – NomadMaker Jun 24 '18 at 14:27
  • Updated answer with link to MySQL documentation – OyaMist Jun 25 '18 at 13:27

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