将sql结果转换为列表python
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14835852/
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
Convert sql result to list python
提问by saidozcan
im beginner with python.I want to convert sql results to a list.Here's my code:
我是 Python 初学者。我想将 sql 结果转换为列表。这是我的代码：
cursor = connnect_db()
query = "SELECT * FROM `tbl`"
cursor.execute(query)
options = list()
for i,row in enumerate(cursor.fetchall()):
options.append(row[i])
There is 6 column in my table but this code doesn't create 6 element-list.Where am i doing wrong?
我的表中有 6 列，但此代码不会创建 6 个元素列表。我哪里做错了？
采纳答案by Gareth Latty
If you have an iterable in Python, to make a list, one can simply call the list()built-in:
如果你有一个迭代在Python，做一个列表，可以简单地调用了list()内置：
list(cursor.fetchall())
Note that an iterable is often just as useful as a list, and potentially more efficient as it can be lazy.
请注意，迭代通常与列表一样有用，并且可能更有效，因为它可以是惰性的。
Your original code fails as it doesn't make too much sense. You loop over the rows and enumerate them, so you get (0, first_row), (1, second_row), etc... - this means you are building up a list of the nth item of each nth row, which isn't what you wanted at all.
您的原始代码失败，因为它没有太大意义。你遍历行并枚举它们，所以你得到(0, first_row), (1, second_row)，等等...... - 这意味着你正在构建每个第 n 行的第 n 个项目的列表，这根本不是你想要的。
This code shows some problems - firstly, list()without any arguments is generally better replaced with an empty list literal ([]), as it's easier to read.
这段代码显示了一些问题 - 首先，list()没有任何参数通常最好用空列表文字 ( [])替换，因为它更容易阅读。
Next, you are trying to loop by index, this is a bad idea in Python. Loop over values, themselves, not indices you then use to get values.
接下来，您尝试按索引循环，这在 Python 中是个坏主意。循环值本身，而不是用于获取值的索引。
Also note that when you doneed to build a list of values like this, a list comprehensionis the best way to do it, rather than creating a list, then appending to it.
另请注意，当您确实需要构建这样的值列表时，列表推导式是最好的方法，而不是创建一个列表，然后附加到它。
回答by Sudhakar Ayyar
cursor = connnect_db()
query = "SELECT * FROM `tbl`"
cursor.execute(query)
result = cursor.fetchall() //result = (1,2,3,) or result =((1,3),(4,5),)
final_result = [list(i) for i in result]
回答by Aaron
One needs to only call list( cursor ) as demonstrated below.
一个人只需要调用 list( cursor )，如下所示。
import pymysql
# Create the database connection.
# The connection parameters are host, port, user, password, and database.
# These parameters in this code are held in the app.config().
dump_conn = pymysql.connect(
host=app.config[ 'DUMP_SQLALCHEMY_HOST' ],
port=int( app.config[ 'DUMP_SQLALCHEMY_PORT' ] ),
user=vault[ 'data' ][ 'username' ],
passwd=vault[ 'data' ][ 'password' ],
db=app.config[ 'DUMP_SQLALCHEMY_DB' ]
)
# Grab the query from a sql_queries helper file.
sql_query = query_transactions_for_csv()
# From the connection get a cursor.
dump_cursor = dump_conn.cursor()
# Handle the cursor.close() with a 'with' statement.
with dump_cursor as cursor:
# Execute the query.
cursor.execute( sql_query )
# Use list( cursor ) to get a Python list.
rows = list( cursor )
回答by NTempest
When I used the answer from Sudhakar Ayyar, the result was a list of lists, as opposed to the list of tuples created by .fetchall(). This was still not what I wanted. With a small change to his code, i was able to get a simple list with all the data from the SQL query:
当我使用 Sudhakar Ayyar 的答案时，结果是一个列表列表，而不是由 .fetchall() 创建的元组列表。这仍然不是我想要的。对他的代码稍作改动，我就能够得到一个简单的列表，其中包含来自 SQL 查询的所有数据：
cursor = connnect_db()
query = "SELECT * FROM `tbl`"
cursor.execute(query)
result = cursor.fetchall() //result = (1,2,3,) or result =((1,3),(4,5),)
final_result = [i[0] for i in result]
Additionally, the last two lines can be combined into:
此外，最后两行可以组合成：
final_result = [i[0] for i in cursor.fetchall()]
