My Python Outputs is not appearing in MySQL

Question

I have a problem insersting data to my database. I want to write 'TIME'-'DATE'-'TEMPERATURE' values my 'temp-at-interrupt' table. (I'M BEGINNER). I successfuly to get value of temp with date and time. I pass the some function that not neccessary.

Here is my code that i cant find any solution.

import os
import time
import datetime
import glob
import mysql.connector
from mysql.connector import errorcode
from time import strftime

os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')

base_dir = '/sys/bus/w1/devices/'
device_folder = glob.glob(base_dir + '28-000008a43c0e')[0]
device_file = device_folder + '/w1_slave'


#---------------------------------------------------------------------------
#Connect MySQL
#---------------------------------------------------------------------------

cnx = mysql.connector.connect(user='root',password='******',
                              host='localhost',
                              database='temp-at-interrupt')


cursor= cnx.cursor()

#---------------------------------------------------------------------------
#Get Temperature Values.
#---------------------------------------------------------------------------

def read_temp_raw():
    f = open(device_file, 'r')
    lines = f.readlines()
    f.close()
    return lines
def read_temp():
    lines = read_temp_raw()
    while lines[0].strip()[-3:] != 'YES':
        time.sleep(0.2)
        lines = read_temp_raw()
    equals_pos = lines[1].find('t=')
    if equals_pos != -1:
        temp_string = lines[1][equals_pos+2:]
        temp_c = float(temp_string) / 1000.0
        return temp_c

#---------------------------------------------------------------------------
#Inset new data
#---------------------------------------------------------------------------
if True:
    temp=read_temp()
    print(temp)
    datetimeWrite = (time.strftime("%Y-%m-%d ") + time.strftime("%H:%M:%S"))
    print (datetimeWrite)
    #sql= ("""INSERT INTO `temp-at-interrupt` (Date,Time,Temperature) VALUES (%s,%s,%s )""",(datetimeWrite,temp))
    sql = ("""INSERT INTO `temp-at-interrupt` (`Date`,`Time`,`Temperature`) VALUES ('%s','%s','%s' )""",(datetimeWrite,temp))
try:
    print ("Writing to database...")
# Execute the SQL command
    cursor.execute(sql)
# Commit your changes in the database
    cnx.commit()
    print ("Write Complete")
except:
# Rollback in case there is any error
    cursor.close()
    cnx.close()
    print ("Failed writing to database")

UPDATE I solved my error. I follow the sequence '' You have to set up your connection, define your cursor, run your queries, and after you've finished querying you can close the connection.''

Now I'm very close to get solve my problem. Here what i got output while runnin.

25.35 C
2018-04-20 22:21:04
Writing to Database...
Failed writing to database

Why its give fail. There is no error notification on shell. @MaNKuR @Barmar

score 0 · Answer 1 · answered Apr 20 '18 at 16:40

0

You need backticks around a table name that contains - characters.

sql = ("""INSERT INTO `temp-at-interrupt` (Date,Time,Temperature) VALUES (%s,%s,%s )""",(datetimeWrite,temp))

See When to use single quotes, double quotes, and back ticks in MySQL

answered Apr 20 '18 at 16:40

Barmar

741,623
53
500
612

Please put back the original code. Otherwise, this answer makes no sense. – Barmar Apr 20 '18 at 19:47
If you get an error you should print the database's error message, not just "Failed writing to database". – Barmar Apr 20 '18 at 19:48
You shouldn't have quotes around `%s`. – Barmar Apr 20 '18 at 19:48
When i use '' INSERT INTO bla bla ... '' in my phpadmin its work i can add value my database. But in my script it is not reach the target. – Njord Nyström Apr 21 '18 at 14:36
You need quotes when you execute the query by hand. When you use `cur.execute()`, it uses a prepared statement and doesn't need quotes. – Barmar Apr 21 '18 at 14:45

score 0 · Answer 2 · answered Apr 20 '18 at 16:48

0

There are couple of things that need to take of.

replace infinite loop while True: to conditional one if True:
Put the try ... except block inside condition #1
Try to debug your execution steps using either pdb or print.

But even you are not able to get the expected result then add the traceback here if any.

answered Apr 20 '18 at 16:48

MaNKuR

2,578
1
19
31

You are passing two value against 3 column . (`Date`,`Time`,`Temperature`) VALUES ('%s','%s','%s' ),(datetimeWrite,temp). Break the `datetimeWrite` into `date` and `time` and pass them separately. – MaNKuR Apr 21 '18 at 04:27
Also as suggested by @Barmer print the actual error and for that put the code `cursor.execute(sql)` outside of `try` block – MaNKuR Apr 21 '18 at 04:29
Its same . Its write and rollback same time probably . Because it gives `25.35 C 2018-04-20 22:21:04 Writing to Database... Failed writing to database` – Njord Nyström Apr 21 '18 at 13:30

My Python Outputs is not appearing in MySQL

2 Answers2