Python MySQLDB：在列表中获取fetchall的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12867140/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python MySQLDB: Get the result of fetchall in a list
提问by Raunak Agarwal
I would like to get the result of the fetchall operation in a list instead of tuple of tuple or tuple of dictionaries. For example,
我想在列表中获取 fetchall 操作的结果，而不是元组的元组或字典的元组。例如，
cursor = connection.cursor() #Cursor could be a normal cursor or dict cursor
query = "Select id from bs"
cursor.execute(query)
row = cursor.fetchall()
Now, the problem is the resultant row is either ((123,),(234,)) or ({'id':123}, {'id':234}) What I am looking for is (123,234) or [123,234]. Be best if I can save on parsing the resulset. Thanks in advance
现在，问题是结果行是 ((123,),(234,)) 或 ({'id':123}, {'id':234}) 我要找的是 (123,234) 或 [ 123,234]。如果我可以节省解析结果集的时间，那就最好了。提前致谢
回答by César
And what about list comprehensions? If result is ((123,), (234,), (345,)):
那么列表理解呢？如果结果是((123,), (234,), (345,))：
>>> row = [item[0] for item in cursor.fetchall()]
>>> row
[123, 234, 345]
If result is ({'id': 123}, {'id': 234}, {'id': 345}):
如果结果是({'id': 123}, {'id': 234}, {'id': 345})：
>>> row = [item['id'] for item in cursor.fetchall()]
>>> row
[123, 234, 345]
回答by pupil
If there is only one field, i can use this to make a list from database:
如果只有一个字段，我可以使用它从数据库中创建一个列表：
def getFieldAsList():
kursor.execute("Select id from bs")
id_data = kursor.fetchall()
id_list = []
for index in range(len(id_data)):
id_list.append(id_data[index][0])
return id_list
回答by user2395682
cursor.execute("""Select * From bs WHERE (id = %s)""",(id))
cursor.fetchall()
回答by onetwopunch
I'm sure that after all this time, you've solved this problem, however, for some people who may not know how to get the values of a cursor as a dictionary using MySQLdb, you can use this method found here:
我敢肯定，这一切的时候，你却解决了这个问题，对一些人谁可能不知道如何使用MySQLdb的获得光标作为字典的值之后，就可以用这个方法找到这里：
import MySQLdb as mdb
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
with con:
cur = con.cursor(mdb.cursors.DictCursor)
cur.execute("SELECT * FROM Writers LIMIT 4")
rows = cur.fetchall()
for row in rows:
print row["Id"], row["Name"]
回答by billspat
This old Q comes up on Google while searching for flattening db queries, so here are more suggestions...
这个旧 Q 在搜索扁平化数据库查询时出现在谷歌上，所以这里有更多建议......
Consider a fast list-flattening iterator.
考虑一个快速列表扁平化迭代器。
Others answers use fetchall()which first loads all rows in memory, then iterates over that to make a new list. Could be inefficient. Could combine with MySQL so-called server side cursor:
其他答案使用fetchall()which 首先加载内存中的所有行，然后迭代它以创建一个新列表。可能效率低下。可以结合 MySQL 所谓的服务器端游标：
# assume mysql on localhost with db test and table bs
import itertools
import MySQLdb
import MySQLdb.cursors
conn = MySQLdb.connect(host='localhost',db='test',
cursorclass=MySQLdb.cursors.SSCursor )
cursor = conn.cursor()
# insert a bunch of rows
cursor.executemany('INSERT INTO bs (id) VALUES (%s)',zip(range(1,10000)) )
conn.commit()
# retrieve and listify
cursor.execute("select id from bs")
list_of_ids = list(itertools.chain.from_iterable(cursor))
len(list_of_ids)
#9999
conn.close()
But the question is also tagged Django, which has a nice single field query flattener
但是这个问题也被标记为 Django，它有一个很好的单字段查询扁平化器
class Bs(models.Model):
id_field = models.IntegerField()
list_of_ids = Bs.objects.values_list('id_field', flat=True)
回答by Sahana Joshi
Make your cursor object in this manner:
以这种方式制作光标对象：
db = MySQLdb.connect("IP", "user", "password", "dbname")
cursor = db.cursor(MySQLdb.cursors.DictCursor)
Then when you perform cursor.fetchall() on a query, a tuple of dictionaries will be obtained, which you can later convert to a list.
然后，当您对查询执行 cursor.fetchall() 时，将获得一个字典元组，您可以稍后将其转换为列表。
data = cursor.fetchall()
data = list(data)
回答by Iceman
list= [list[0] for list in cursor.fetchall()]
this will render results in one list like - list = [122,45,55,44...]
这会将结果呈现在一个列表中，例如 - list = [122,45,55,44...]
