Fetchall python to list

Convert sql result to list python

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

If you have an iterable in Python, to make a list, one can simply call the list() built-in:

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.

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] 

Источник

Convert A SQL Query Result To A List In Python | MySQL

When dealing with database queries we use the for-loop to access each record and deal with it one at a time. In some cases it’s better to deal with the database results in a variable so we do not need to query again. The Python List object allows us to store a SQL query result easily as it allows for duplicates and permits changes too.

This post is a follow-up to the “Write a List to CSV file” article. In this case we are querying the database and loading the results into some Python objects like a List, Tuple and Dictionary.

Note: We’re going to skip the virtual environment.. (MySQL Ex. Here) ..but it is recommended to use a virtual environment for all your projects.

Python Environment Connect to MySQL

You should be familiar with creating and connecting to a MySQL database. Here is some quick sample code to install in a new Python environment.

1. activate the environment

.\myenv\Scripts\activate (windows) source /myenv/bin/activate (linux)

2. install the mysql driver

pip install mysql-connector-python

Use the following example to connect to a localhost MySQL database.

import mysql.connector conn = mysql.connector.connect( host="localhost", database="somedbname", user="myuser", password="goodpasswd" )

What is a Python List []

The List is similar to an Array in other languages and it is indexed starting 0. The List can contain strings, numbers and objects too, here are some simple string examples.

string = [] ## New empty List string1 = ['a', 'b', 'c', 'd', 'e', 'f', 'g'] string2 = ['Brazil', 'Panama', 'Canada', 'India', 'Decentraland'] number = [2,3.6,7,-23,99] ## can contain decimal and negative numbers together = string1[0:2] + string2[2:5] + number[2:4] ## concatenate lists print(string1[3]) ## get just 1 value print(string2[4]) print(string1[2:5]) ## Range: get values from index 2 -> 5 print(number[1:5]) print(together) Values from all 3 in 1 list string1[:] = [] ## clear the list

Append to List []

newlist = ['Houston'] newlist.append('Decentraland') ## append just 1 string newlist.append(string2[2]) ## append the 3rd value from another list newlist.append(number) ## append the entire number list print(newlist)

MySQL Table and Query

For this sample I created a table called “myusers” which includes the users first name, last name and the city they work in. In this example we are using a cursor and then querying this table for the (db tales com) first record and then all of them. Keep in mind that Python is returning a Tuple () for one record but then a List with many Tuples that contains our data, we are also querying 3 fields from the database.

cursor = conn.cursor() cursor.execute("SELECT fname, lname, city FROM myusers") onerecord = cursor.fetchone() results = cursor.fetchall() print(onerecord) ## returns as a Tuple print(results) ## returns as a List of Tuples

Lets say we are only interested in a List of the Last Names and we want it to be stored in a single List. We can create an empty List and iterate through the Tuple () that is returned by the cursor.

cursor = conn.cursor() cursor.execute("SELECT lname FROM myusers") lastnames = [] for row in cursor: for field in row: lastnames.append(field) print(lastnames)

Keep in mind that we are only SELECT’ing one field in the Query. If there is more than 1 field, such as the First and Last Names, you will end up with one List containing all of the fields.

MySQL to Python List [] & Dictionary <>

Another option to consider is using the ‘Dictionary’ cursor setting to return a Dictionary <> with the keys and then we end up with just the values from the query. Then we can iterate through them using the field values from our query.

cursor = conn.cursor(dictionary=True) ## available in v2 cursor.execute("SELECT fname, lname, city FROM myusers") for row in cursor: print(row['fname'] + ' ' + row['lname'] + ' from ' + row['city'])

Lastly, the cursor can return the records as key’d Dictionary’s all encompassed within one List. Each record is it’s own Dictionary with the key named after the database field name.

cursor = conn.cursor(dictionary=True) cursor.execute("SELECT fname, lname, city FROM myusers") print(cursor.fetchall())

Источник

Convert SQL Result to List in Python (Example)

Hi! This tutorial will show you how to turn SQL results into a list in the Python programming language.

First, though, here is an overview:

Import sqlite3 Module

In this tutorial, we will demonstrate how to use the sqlite3 Python module to connect to a SQLite database, which is a lightweight disk-based database that does not require being hosted on a separate remote server.

Please note that this tutorial will not walk you through the steps of creating a database in SQLite; it will rather only demonstrate the steps for fetching the data from the database, and transforming that data into a Python list. It is also assumed that you already have some knowledge of SQL and how to query a relational database.

The sqlite3 module is a built-in module in Python. Therefore, to import the module, run the line of code below in your preferred Python IDE as follows:

Connect to Database

The first step is to connect to an existing SQLite database. To do so, you can run your code like this:

conn = sqlite3.connect("demo.db")

Where “demo.db” is the name of your SQLite database. Once connected, you can then proceed to the next step.

Create a Cursor Object

The cursor object is what will enable us to execute SQL queries.

Execute a Statement

Having created the cursor object, we will now execute a statement that will enable us to select all the columns in the table inside our database:

cur.execute("SELECT * FROM my_table")

It is assumed that you already have a table inside your database, from which you can either select individual columns by naming them separated by commas or parsing all columns by the asterisk * just as demonstrated above.

Fetch all Rows as List of Tuples

The next step is to fetch all the rows as a list of tuples via fetchall():

Источник

Получение результатов запроса#

Для получения результатов запроса в sqlite3 есть несколько способов:

• использование методов fetch — в зависимости от метода возвращаются одна, несколько или все строки
• использование курсора как итератора — возвращается итератор

Метод fetchone#

Метод fetchone возвращает одну строку данных.

Пример получения информации из базы данных sw_inventory.db:

In [16]: import sqlite3 In [17]: connection = sqlite3.connect('sw_inventory.db') In [18]: cursor = connection.cursor() In [19]: cursor.execute('select * from switch') Out[19]: sqlite3.Cursor at 0x104eda810> In [20]: cursor.fetchone() Out[20]: ('0000.AAAA.CCCC', 'sw1', 'Cisco 3750', 'London, Green Str') 

Обратите внимание, что хотя запрос SQL подразумевает, что запрашивалось всё содержимое таблицы, метод fetchone вернул только одну строку.

Если повторно вызвать метод, он вернет следующую строку:

In [21]: print(cursor.fetchone()) ('0000.BBBB.CCCC', 'sw2', 'Cisco 3780', 'London, Green Str') 

Аналогичным образом метод будет возвращать следующие строки. После обработки всех строк метод начинает возвращать None.

За счет этого метод можно использовать в цикле, например, так:

In [22]: cursor.execute('select * from switch') Out[22]: sqlite3.Cursor at 0x104eda810> In [23]: while True: . : next_row = cursor.fetchone() . : if next_row: . : print(next_row) . : else: . : break . : ('0000.AAAA.CCCC', 'sw1', 'Cisco 3750', 'London, Green Str') ('0000.BBBB.CCCC', 'sw2', 'Cisco 3780', 'London, Green Str') ('0000.AAAA.DDDD', 'sw3', 'Cisco 2960', 'London, Green Str') ('0011.AAAA.CCCC', 'sw4', 'Cisco 3750', 'London, Green Str') ('0000.1111.0001', 'sw5', 'Cisco 3750', 'London, Green Str') ('0000.1111.0002', 'sw6', 'Cisco 3750', 'London, Green Str') ('0000.1111.0003', 'sw7', 'Cisco 3750', 'London, Green Str') ('0000.1111.0004', 'sw8', 'Cisco 3750', 'London, Green Str') 

Метод fetchmany#

Метод fetchmany возвращает список строк данных.

cursor.fetchmany([size=cursor.arraysize]) 

С помощью параметра size можно указывать, какое количество строк возвращается. По умолчанию параметр size равен значению cursor.arraysize:

In [24]: print(cursor.arraysize) 1 

Например, таким образом можно возвращать по три строки из запроса:

In [25]: cursor.execute('select * from switch') Out[25]: sqlite3.Cursor at 0x104eda810> In [26]: from pprint import pprint In [27]: while True: . : three_rows = cursor.fetchmany(3) . : if three_rows: . : pprint(three_rows) . : else: . : break . : [('0000.AAAA.CCCC', 'sw1', 'Cisco 3750', 'London, Green Str'), ('0000.BBBB.CCCC', 'sw2', 'Cisco 3780', 'London, Green Str'), ('0000.AAAA.DDDD', 'sw3', 'Cisco 2960', 'London, Green Str')] [('0011.AAAA.CCCC', 'sw4', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0001', 'sw5', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0002', 'sw6', 'Cisco 3750', 'London, Green Str')] [('0000.1111.0003', 'sw7', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0004', 'sw8', 'Cisco 3750', 'London, Green Str')] 

Метод выдает нужное количество строк, а если строк осталось меньше, чем параметр size, то оставшиеся строки.

Метод fetchall#

Метод fetchall возвращает все строки в виде списка:

In [28]: cursor.execute('select * from switch') Out[28]: sqlite3.Cursor at 0x104eda810> In [29]: cursor.fetchall() Out[29]: [('0000.AAAA.CCCC', 'sw1', 'Cisco 3750', 'London, Green Str'), ('0000.BBBB.CCCC', 'sw2', 'Cisco 3780', 'London, Green Str'), ('0000.AAAA.DDDD', 'sw3', 'Cisco 2960', 'London, Green Str'), ('0011.AAAA.CCCC', 'sw4', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0001', 'sw5', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0002', 'sw6', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0003', 'sw7', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0004', 'sw8', 'Cisco 3750', 'London, Green Str')] 

Важный аспект работы метода — он возвращает все оставшиеся строки.

То есть, если до метода fetchall использовался, например, метод fetchone, то метод fetchall вернет оставшиеся строки запроса:

In [30]: cursor.execute('select * from switch') Out[30]: sqlite3.Cursor at 0x104eda810> In [31]: cursor.fetchone() Out[31]: ('0000.AAAA.CCCC', 'sw1', 'Cisco 3750', 'London, Green Str') In [32]: cursor.fetchone() Out[32]: ('0000.BBBB.CCCC', 'sw2', 'Cisco 3780', 'London, Green Str') In [33]: cursor.fetchall() Out[33]: [('0000.AAAA.DDDD', 'sw3', 'Cisco 2960', 'London, Green Str'), ('0011.AAAA.CCCC', 'sw4', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0001', 'sw5', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0002', 'sw6', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0003', 'sw7', 'Cisco 3750', 'London, Green Str'), ('0000.1111.0004', 'sw8', 'Cisco 3750', 'London, Green Str')] 

Метод fetchmany в этом аспекте работает аналогично.

Источник