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

---