3

I'm using python which version is 3.x.x . In python 3.x.x as you know there is no library for MySQL. For this reason i'm trying to use pymysql for connect my database. While using pymysql , I can not insert data my database.(I'm BEGINNER). When i run this script i got :

Traceback (most recent call last):
File "home/pi ......
cur.execute(sql)
Name:Error: cur is not defined.

How can i fix this ? How can i define cursor.execute? I called library for this why i have to define this again ?

Here is my code. I deleted the part which is not neccessarry for this problem.

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='mehmetali',
                              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

#---------------------------------------------------------------------------
#Insert 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 : When 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.'' I fixed my errors. But im still stuck here. My output like this.

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

Where i'm wrong here?

  • 2
    While you may be programming on Ubuntu, StackOverflow may be better for programming questions, which this primarily is. – vidarlo Apr 20 '18 at 20:00
2

You've misunderstood how to use the python interface.

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

Here you connect, and then close the connection. After this, without ever defining your cursor cur you attempt to execute a query with it. This won't work.

You have to set up your connection, define your cursor, run your queries, and after you've finished querying you can close the connection.

The mysql documentation provides this example, which shows the flow of it:

import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

tomorrow = datetime.now().date() + timedelta(days=1)

add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %s)")
add_salary = ("INSERT INTO salaries "
              "(emp_no, salary, from_date, to_date) "
              "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

# Insert new employee
cursor.execute(add_employee, data_employee)

emp_no = cursor.lastrowid

# Insert salary information
data_salary = {
  'emp_no': emp_no,
  'salary': 50000,
  'from_date': tomorrow,
  'to_date': date(9999, 1, 1),
}
cursor.execute(add_salary, data_salary)

# Make sure data is committed to the database
cnx.commit()

cursor.close()
cnx.close()

Here you clearly see the flow of connecting to the database, creating a cursor, executing queries, committing the result to the DB, and finally cleaning up and closing the connection.

| improve this answer | |
  • i updated my post can you check ? – Njord Nyström Apr 20 '18 at 19:47
  • Print your SQL statement, and check that it's valid. Look into showing errors. A quick glance indicates that your number of columns and number of parameters does not match. – vidarlo Apr 20 '18 at 19:54
  • hmmm i think i got it . I create table from my phpadmin (localhost) not created from with code. Is this make a difference ? – Njord Nyström Apr 20 '18 at 21:32
  • No, that does not make a difference, as long as data types are correct. But print the sql statement, and try it in mysql directly. This will hilight any mistakes. – vidarlo Apr 20 '18 at 21:41
  • I can insert data in phpmyadmin manually. Probably , i can not execute the ''INSERT into 'TAB_TEMP' code in my script. This code is not reach my phpadmin or my script write and rollback sametime. – Njord Nyström Apr 21 '18 at 14:35
0

Your insert statement tries to insert 3 values (Date, Time, Temperature) but you supply only 2 (datetimeWrite,temp). It cannot work. Besides, as indicated by vidarlo, you really should look into errors. Simply putting the print statement indicated in reference can help you a lot:

...
except mysql.connector.Error as err:
  print("Something went wrong: {}".format(err))
...
| improve this answer | |

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