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

  1. I have no clue what's wrong. Anyone help me debug this?

  2. Is there a better (visually, and performance-wise) way of inserting the tuple rather than using indexes?


Answer: 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.

