Some crazy code and crazy data. Insert rows in mysql db (python)

Question

I'm trying to insert some data (stored in a list of tuples) to my local database. The data is a little inconsistent - some timestamps are datetime.datetime while some can be strings. But I don't think that's where my problem is.

First of all, my db schema:

+-----------------------------+-------------+------+-----+---------+-------+
| Field                       | Type        | Null | Key | Default | Extra |
+-----------------------------+-------------+------+-----+---------+-------+
| store                       | varchar(11) | NO   |     | NULL    |       |
| order_no                    | int(11)     | NO   |     | NULL    |       |
| product_name                | text        | YES  |     | NULL    |       |
| product_id                  | int(11)     | NO   |     | NULL    |       |
| classification              | int(11)     | NO   |     | NULL    |       |
| order_date                  | datetime    | NO   |     | NULL    |       |
| power_complete_time         | datetime    | YES  |     | NULL    |       |
| stockout_date               | datetime    | YES  |     | NULL    |       |
| wfi_status                  | tinyint(4)  | YES  |     | NULL    |       |
| qc_status                   | varchar(12) | YES  |     | NULL    |       |
| scanned_for_shipment_date   | datetime    | YES  |     | NULL    |       |
| order_delivered_date        | datetime    | YES  |     | NULL    |       |
| status                      | varchar(50) | YES  |     | NULL    |       |
| stock_out_status            | varchar(50) | YES  |     | NULL    |       |
| actual_order_date           | datetime    | NO   |     | NULL    |       |
| order_for_today             | tinyint(1)  | YES  |     | NULL    |       |
| dispatched_within_same_day  | tinyint(1)  | YES  |     | NULL    |       |
| stockout_within_same_day    | tinyint(1)  | YES  |     | NULL    |       |
| order_for_yesterday         | tinyint(1)  | YES  |     | NULL    |       |
| dispatched_within_yesterday | tinyint(1)  | YES  |     | NULL    |       |
| stockout_within_yesterday   | tinyint(1)  | YES  |     | NULL    |       |
| eyeframe_less_than_1_pm     | tinyint(1)  | YES  |     | NULL    |       |

+-----------------------------+-------------+------+-----+---------+-------+

Next up, my script with the crazy weird data. Again, the data about some dates can be inconsistent that is why I'm testing with a bunch of data rows:

import MySQLdb
import datetime

if __name__ == '__main__':

    rows = [
        (u'Lenskart', u'1200667194', u'Prescription Card', u'41420', u'shipping_accessories', datetime.datetime(2013, 7, 27, 4, 12, 56), datetime.datetime(2013, 7, 27, 11, 18, 4), datetime.datetime(2013, 7, 27, 11, 45, 32), u'0', None, datetime.datetime(2013, 7, 28, 3, 24, 17), u'0000-00-00 00:00:00', u'complete', None, datetime.datetime(2013, 7, 27, 9, 42, 56), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667195', u'Rs.1800 Coupon Booklet ( Rs. 500 Coupons)  - FREE', u'39799', u'shipping_accessories', datetime.datetime(2013, 7, 27, 4, 13, 17), datetime.datetime(2013, 7, 27, 10, 10, 2), datetime.datetime(2013, 7, 27, 10, 53, 32), u'1', None, datetime.datetime(2013, 7, 27, 12, 12, 2), u'0000-00-00 00:00:00', u'complete', u'complete', datetime.datetime(2013, 7, 27, 9, 43, 17), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667196', u'Vincent Chase RG 172 Gunmetal C3 Eyeglasses', u'58131', u'eyeframe', datetime.datetime(2013, 7, 27, 4, 13, 31), datetime.datetime(2013, 7, 27, 10, 10, 2), datetime.datetime(2013, 7, 27, 12, 54, 34), u'1', None, datetime.datetime(2013, 7, 28, 4, 49, 13), u'0000-00-00 00:00:00', u'complete', None, datetime.datetime(2013, 7, 27, 9, 43, 31), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667193', u'Prescription Card', u'41420', u'shipping_accessories', datetime.datetime(2013, 7, 27, 4, 11, 10), datetime.datetime(2013, 7, 27, 10, 12, 4), datetime.datetime(2013, 7, 27, 11, 37, 47), u'0', None, datetime.datetime(2013, 7, 27, 19, 51, 13), u'2013-07-29 00:00:00', u'delivered', None, datetime.datetime(2013, 7, 27, 9, 41, 10), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667193', u'Prescription-Lens PC SV Regular', u'45081', u'prescription_lens', datetime.datetime(2013, 7, 27, 4, 11, 10), datetime.datetime(2013, 7, 27, 10, 12, 4), datetime.datetime(2013, 7, 27, 17, 51, 33), u'0', None, datetime.datetime(2013, 7, 27, 19, 51, 13), u'2013-07-29 00:00:00', u'delivered', None, datetime.datetime(2013, 7, 27, 9, 41, 10), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667190', u'Rs.1800 Coupon Booklet ( Rs. 500 Coupons)  - FREE', u'39799', u'shipping_accessories', datetime.datetime(2013, 7, 27, 4, 10, 33), datetime.datetime(2013, 7, 27, 10, 7, 4), datetime.datetime(2013, 7, 27, 10, 56, 2), u'1', None, datetime.datetime(2013, 7, 27, 14, 33, 21), u'0000-00-00 00:00:00', u'complete', u'complete', datetime.datetime(2013, 7, 27, 9, 40, 33), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667187', u'Prescription-Lens CR SV Regular', u'45073', u'prescription_lens', datetime.datetime(2013, 7, 27, 4, 8, 48), datetime.datetime(2013, 7, 27, 9, 58, 2), datetime.datetime(2013, 7, 27, 12, 11, 25), u'0', None, datetime.datetime(2013, 7, 27, 17, 0, 1), u'2013-07-30 00:00:00', u'delivered', None, datetime.datetime(2013, 7, 27, 9, 38, 48), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667183', u'Rs.1800 Coupon Booklet ( Rs. 500 Coupons)  - FREE', u'39799', u'shipping_accessories', datetime.datetime(2013, 7, 27, 4, 3, 46), datetime.datetime(2013, 7, 27, 11, 44, 3), datetime.datetime(2013, 7, 27, 12, 26, 48), u'1', None, datetime.datetime(2013, 7, 27, 17, 46, 17), u'2013-07-30 00:00:00', u'delivered', None, datetime.datetime(2013, 7, 27, 9, 33, 46), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667183', u'Prescription-Lens CR SV Regular', u'45073', u'prescription_lens', datetime.datetime(2013, 7, 27, 4, 3, 46), datetime.datetime(2013, 7, 27, 11, 44, 3), datetime.datetime(2013, 7, 27, 13, 50, 35), u'0', None, datetime.datetime(2013, 7, 27, 17, 46, 17), u'2013-07-30 00:00:00', u'delivered', None, datetime.datetime(2013, 7, 27, 9, 33, 46), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667175', u'Bausch & Lomb Soflens 59 (6 Lenses/box)_S:-4.75 / C:0.00 / A:0 / BC:8.60 / AP:0.00 / CL:', u'90000443', u'contact_lens', datetime.datetime(2013, 7, 27, 3, 58, 6), datetime.datetime(2013, 7, 27, 9, 38, 2), datetime.datetime(2013, 7, 27, 10, 10, 22), u'0', None, datetime.datetime(2013, 7, 27, 12, 32, 17), u'2013-07-29 00:00:00', u'delivered', u'complete', datetime.datetime(2013, 7, 27, 9, 28, 6), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667171', u'Rs.1800 Coupon Booklet ( Rs. 500 Coupons)  - FREE', u'39799', u'shipping_accessories', datetime.datetime(2013, 7, 27, 3, 55, 56), datetime.datetime(2013, 7, 27, 9, 37, 3), datetime.datetime(2013, 7, 27, 9, 51, 47), u'1', None, datetime.datetime(2013, 7, 27, 12, 59, 50), u'0000-00-00 00:00:00', u'complete', u'complete', datetime.datetime(2013, 7, 27, 9, 25, 56), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667165', u'Rs.1800 Coupon Booklet ( Rs. 500 Coupons)  - FREE', u'39799', u'shipping_accessories', datetime.datetime(2013, 7, 27, 9, 20), datetime.datetime(2013, 7, 27, 9, 30, 4), datetime.datetime(2013, 7, 27, 10, 24, 35), u'1', None, datetime.datetime(2013, 7, 27, 16, 15, 14), u'2013-07-29 00:00:00', u'delivered', u'complete', datetime.datetime(2013, 7, 27, 14, 50), 0, None, None, 0, None, None, 0),
        (u'Bagskart', u'1200667151', u'Feelgood Backpack FG011-A Navy Blue', u'35607', u'Backpacks', datetime.datetime(2013, 7, 27, 2, 14, 52), u'', datetime.datetime(2013, 7, 27, 12, 50, 15), u'1', None, datetime.datetime(2013, 7, 27, 15, 16, 32), u'2013-07-29 00:00:00', u'delivered', u'complete', datetime.datetime(2013, 7, 27, 7, 44, 52), 0, None, None, 0, None, None, 0),
        (u'Lenskart', u'1200667148', u'Rs.1800 Coupon Booklet ( Rs. 500 Coupons)  - FREE', u'39799', u'shipping_accessories', datetime.datetime(2013, 7, 27, 2, 8, 40), datetime.datetime(2013, 7, 27, 7, 41, 3), datetime.datetime(2013, 7, 27, 7, 50, 37), u'1', None, datetime.datetime(2013, 7, 27, 12, 9, 49), u'2013-07-29 00:00:00', u'complete', u'complete', datetime.datetime(2013, 7, 27, 7, 38, 40), 0, None, None, 0, None, None, 0)
        ]
    db = MySQLdb.connect(host="localhost",
                         user="root",
                          passwd="",
                          db="inventory")

    cur = db.cursor()

    sql = """INSERT INTO daily_dispatch VALUES (%s, %s, %s, %s, %s, %s, %s,
                                                %s, %s, %s, %s, %s, %s, %s,
                                                %s, %s, %s, %s, %s, %s, %s,
                                                %s)
                                                """
    row = rows[0]

    cur.execute(sql % (row[0], row[1], row[2], row[3], row[4], row[5],
                       row[6], row[7], row[8], row[9], row[10], row[11],
                       row[12], row[13], row[14], row[15], row[16], row[17],
                       row[18], row[19], row[20], row[21]))
    db.commit()

    db.close()

The error I get:

Traceback (most recent call last):
  File "C:\Users\Karan\Desktop\Dropbox\Valyoo\Task 3\daily_dispatch phpmyadmin\local\insert.py", line 50, in <module>
    row[18], row[19], row[20], row[21]))
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 202, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Card, 41420, shipping_accessories, 2013-07-27 04:12:56, 2013-07-27 11:18:04,\n   ' at line 1")

I have no clue what's wrong. Anyone help me debug this?
Is there a better (visually, and performance-wise) way of inserting the tuple rather than using indexes?

You need to put quotes around %s for all the varchar and text columns, so that SQL will see them as string values. — Barmar
– Barmar, Commented Jul 30, 2013 at 11:33
There is problem in SQL syntax, it should be '%s' for text/varchar columns, nothing else as stated by @Barmar. — vedarthk
– vedarthk, Commented Jul 30, 2013 at 11:37
@Barmar: Bad idea; use SQL parameters instead. See answers below. — Martijn Pieters
– Martijn Pieters, Commented Jul 30, 2013 at 12:02
@vedarthk: The SQL syntax is just fine; but instead of string interpolation, the OP should use SQL parameters, which applies correct escaping based on the value type, prevents SQL injection attacks and lets the database reuse the query plan for subsequent insertions. — Martijn Pieters
– Martijn Pieters, Commented Jul 30, 2013 at 12:03
@MartijnPieters I completely agree to your point. I was stating the error in his existing script, right answers are listed below. — vedarthk
– vedarthk, Commented Jul 30, 2013 at 12:11

Martijn Pieters · Accepted Answer · 2013-07-30 11:33:56Z

2

You are interpolating your data, leave that to the database adapter, which will handle escaping the various values as appropriate. There is also no need to expand the whole list of row values:

cur.execute(sql, row)

You don't really have to type out all those %s placeholders; have Python generate them for you:

sql = "INSERT INTO daily_dispatch VALUES ({})".format(', '.join(['%s' for _ in range(len(row[0]))]))

Since you have more than one row, have the database loop through them and execute the same query for each row in turn:

cur.executemany(sql, rows)

No need to loop yourself.

answered Jul 30, 2013 at 11:33

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

1 Comment

Sylvain Leroux Over a year ago

Just to put emphasis on Martijn's answer, you should always use the DB adapter to properly escape values. If you perform the same query over and over, I would push for a prepared statement. But both are good. OTOH, using bare % operator of string.format() function is a very bad habit, opening the door to SQL injection.

unutbu · Accepted Answer · 2013-07-30 11:40:02Z

1

You need to properly quote the arguments. However, you don't have to do that manually. Instead, pass the arguments as the second argument to cursor.execute and MySQLdb will quote them for you:

To insert just the first row:

sql = """INSERT INTO daily_dispatch VALUES ({})""".format(
         ', '.join(['%s']*22))
cur.execute(sql, row[0])

instead of cur.execute(sql % row[0]). It looks like a subtle change, but it makes a world of difference.

To insert all the rows use:

cur.executemany(sql, rows)

edited Jul 30, 2013 at 11:40

answered Jul 30, 2013 at 11:34

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

4 Comments

KGo Over a year ago

cur.executemany(sql, row[0]) gives an error that not enough arguments are passed. cur.executemany(sql, rows) work perfectly, though!

unutbu Over a year ago

Oops, I wrote cur.executemany when I meant cur.execute. It's now fixed.

KGo Over a year ago

One question. Why is executemany faster than execute considerably?

unutbu Over a year ago

executemany sends one command to the MySQL server with all the values to insert. That's much faster than using a Python loop and many calls to execute. The server is written in C and looping over the values in a C-loop is generally faster than doing the equivalent with a Python loop.

Collectives™ on Stack Overflow

Some crazy code and crazy data. Insert rows in mysql db (python)

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related