im beginner with python.I want to convert sql results to a list.Here's my code:
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?
If you have an iterable in Python, to make a list, one can simply call the list() built-in:
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.
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.
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.
Also note that when you do need to build a list of values like this, a list comprehension is the best way to do it, rather than creating a list, then appending to it.
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:
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()]
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]
One needs to only call list( cursor ) as demonstrated below.
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 )
if you are using stored procedure you can convert results to list like this.
cursor = connection.cursor()
parameters = []
cursor.callproc('spProcedureName', parameters)
resultList =[r.fetchall() for r in cursor.stored_results()][0]
cursor.close()
