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: stackoverflow.com/questions/775296/python-mysql-with-variables. — jpmc26
– jpmc26, Commented Mar 18, 2014 at 4:01

alecxe · Accepted Answer · 2014-03-18 04:05:01Z

1

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.

edited Mar 18, 2014 at 4:05

answered Mar 18, 2014 at 4:00

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mohit mohan · Accepted Answer · 2015-02-09 10:47:10Z

0

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))

answered Feb 9, 2015 at 10:47

mohit mohan

592 bronze badges

Collectives™ on Stack Overflow

Insert python variables into rows of mysql table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related