一些疯狂的代码和疯狂的数据.在mysql数据库中插入行(python)

2022-01-15 07:21:15

我正在尝试将一些数据(存储在元组列表中)插入本地数据库.数据有点不一致-一些时间戳是datetime.datetime,而有些可以是字符串.但是我不认为这是我的问题所在.

首先,我的数据库模式：

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

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

接下来,我的脚本带​​有疯狂的怪异数据.同样,有关某些日期的数据可能会不一致,这就是为什么我要使用大量数据行进行测试的原因：

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

我得到的错误：

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

>我不知道怎么了.有人帮我调试吗？
>是否有插入元组而不使用索引的更好的方式(在视觉上和性能方面)？

解决方法:

您需要正确引用参数.但是,您不必手动执行此操作.相反,将参数作为第二个参数传递给cursor.execute,MySQLdb将为您引用它们：

要仅插入第一行：

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

而不是cur.execute(sql％row [0]).它看起来像是一个微妙的变化,但却带来了与众不同的世界.

要插入所有行,请使用：

cur.executemany(sql, rows)
上一篇：sqlite之聚合函数的使用


下一篇：在Python中这样的代码：a_list [i,j] = 3