Python:在for循环中将命名元组添加到MySQL [英] Python: adding named tuples to MySQL in a for loop

问题描述

所以我有以下namedtuple，其中包含多个项目:

So I have the following namedtuple, containing multiple items:

[item(company='MARINE AND GENERAL MUTUAL LIFE ASSURANCE SOCIETY', google_name='no results', place_id='no results', formatted_address='no results'),
 item(company='KENTSTONE PROPERTIES LIMITED', google_name='no results', place_id='no results', formatted_address='no results'),
 item(company='ASHFORD CATTLE MARKET COMPANY LIMITED(THE)', google_name=u'The Ashford Cattle Market Co Ltd', place_id=u'ChIJRSxF4gbb3kcRCjmXJcSWOrI', formatted_address=u'The New Ashford Market Monument Way, Orbital Park, Ashford TN24 0HB, United Kingdom'),
 item(company='ORIENTAL GAS COMPANY, LIMITED(THE)', google_name=u'Orient Express Hotels', place_id=u'ChIJffRJYVVYwokReF_qwmzMgh0', formatted_address=u'1155 Ave of the Americas, New York, NY 10036, United States'),
 item(company='BRITISH INDIA STEAM NAVIGATION COMPANY LIMITED', google_name=u'British-India Steam-Navigation Co Ltd', place_id=u'ChIJe6yzIVN2AjoRZdGKagFvkvs', formatted_address=u'54/7, Bisha    Lakshmitala Road, Parnashree, Kolkata, West Bengal 700060, India')]

我想将这些项目添加到MySQL数据库中，添加到名为place_id的表中，这是我到目前为止所获得的:

I would like to add those items to MySQL database, to the table called place_id, this is what I've got so far:

cursor.execute("INSERT INTO place_id (company, google_name, place_id, formatted_address) VALUES (%(company)s, %(google_name)s, %(place_id), %(formatted_address)s" ....

我不知道从那里去哪里.

And I don't know where to go from there.

我想通过for循环添加这些项(或executemany，但是我并不熟悉).我已经通过MySQLdb模块连接到数据库.

I would like to add the items via for loop (or executemany, but I am not that familiar with it). I am already connected to the database via MySQLdb module.

谢谢您的帮助.

推荐答案

假设项目确实是 namedtuple 并从参数声明的顺序中推断出它的声明像

Assuming item really is a namedtuple and deducing from the order of arguments that it's declared like

item = namedtuple('item', 'company google_name place_id formatted_address')

除非确实需要，否则无需在查询中使用命名占位符.只需使用适用于元组的常规序列格式样式即可:

there's no need to use named placeholders in your query, unless you really want to. Just use the normal sequence formatting style that works with tuples:

# assuming items is a reference to the list of item instances you gave
# in the example
cursor.executemany("INSERT INTO place_id "
                   "(company, google_name, place_id, formatted_address) "
                   "VALUES (%s, %s, %s, %s)", items)

要使用命名的占位符版本，您可以使用

To use the named placeholder version you could convert the list of items to a sequence of OrderedDicts (a sequence of mappings) with namedtuple._asdict, but there's really no need for this:

cursor.executemany("INSERT INTO place_id "
                   "(company, google_name, place_id, formatted_address) "
                   "VALUES (%(company)s, %(google_name)s, %(place_id)s, %(formatted_address)s",
                   (i._asdict() for i in items))

这篇关于Python:在for循环中将命名元组添加到MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！