Insert python variables into rows of mysql table

Question

I have the following code that parses data from an XML file.

import MySQLdb
from xml.dom import minidom

xmldoc = minidom.parse("report.xml")

parking_report = xmldoc.getElementsByTagName("parking_report")[0]

sensors = parking_report.getElementsByTagName("sensor")

for sensor in sensors:
    id = sensor.getElementsByTagName("id")[0].firstChild.data
    date = sensor.getElementsByTagName("date")[0].firstChild.data
    time = sensor.getElementsByTagName("time")[0].firstChild.data
    status = sensor.getElementsByTagName("status")[0].firstChild.data

    db = MySQLdb.connect(host="localhost",user="root",passwd="pass",db="parking_report")
    cur = db.cursor()
    cur.execute('INSERT INTO report_table (id, date, time, status) VALUES (id, date, time, status)')

    print(id, date, time, status)

Right now, it runs without errors and returns the id, date, time and status of each parking sensor. However, my mySQL table (parking_report) also has the columns ID, Date, Time and Status. I want to insert the data of those variables into my table under those columns. (Note there are three separate sensors so I will need three rows of data in the end.)

When I run this it does not insert into my table. Please Help! Thank you.

Take note of the fact that you never actually pass your values into the `execute` function. What you want is a parametrized query. See this, which is a duplicate, actually: http://stackoverflow.com/questions/775296/python-mysql-with-variables. — jpmc26, Mar 18 '14 at 04:01
Possible duplicate of [Python MySQL Parameterized Queries](https://stackoverflow.com/questions/775296/python-mysql-parameterized-queries) — jpmc26, Jun 22 '17 at 22:46

alecxe · Answer 1 · 2014-03-18T04:05:01.363

You are not passing any parameters to the query.

Instead of:

cur.execute('INSERT INTO report_table (id, date, time, status) VALUES (id, date, time, status)')

create a parameterized query:

cur.execute("""INSERT INTO 
                   report_table 
                   (id, date, time, status) 
               VALUES
                   (%(id)s, %(date)s, %(time)s, %(status)s)""", 
            {'id': id, 
             'date': date, 
             'time': time, 
             'status': status})

There are other fixes you should apply to the code:

define db and cursor variables before the loop so that you don't need to connect to the database and open a cursor on every iteration
you should properly close the cursor and connection objects
instead of calling INSERT database query on each iteration step, consider gathering the data into a list and then insert once after the loop

Hope that helps.

score 0 · Answer 2 · answered Feb 09 '15 at 10:47

Well easiest way to insert python variables into rows of mysql table is:

cursor.execute(
   """INSERT INTO menu(col1, col2, col3) VALUES(%s, %s, %s)"""
   %(item1, item2, item3))

In your case:

cursor.execute(
   """INSERT INTO report_table(id, date, time, status) VALUES(%s, %s, %s, %s)"""
   %(id, date, time, status))

Insert python variables into rows of mysql table

2 Answers2