← Back to Python Course | Chapter 15: Python & MySQL | Lesson 5 of 12

Python MySQL Select From Table

Rows are read back out of a table with a SELECT SQL statement -- run through cursor.execute() as usual, then retrieved into Python with fetchall(), fetchone(), or fetchmany(), each suited to a different situation.

In this page:

  1. Selecting All Columns
  2. Using fetchone() for a Single Row
  3. Iterating the Cursor Directly
  4. Accessing Results as Dictionaries
  5. Counting Result Rows

Selecting All Columns

cursor.execute("SELECT * FROM customers") followed by cursor.fetchall() retrieves every column of every row in the customers table as a list of tuples, one tuple per row, giving you the complete contents of the table in Python.

Note: Prefer naming specific columns over SELECT * in production code -- it is more explicit about what your code depends on and avoids pulling unnecessary data over the network.

Warning: fetchall() must be called after execute() to actually retrieve the results -- execute() alone only runs the query on the server without returning data to Python.

Example: Selecting All Columns

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers")
cursor.fetchall.return_value = [(1, "John", "Highway 21")]
print(cursor.fetchall())

Using fetchone() for a Single Row

cursor.fetchone() retrieves just the next single row from the result set (or None if there are no more rows), a good choice when you know or expect exactly one matching row, such as looking up a user by a unique ID.

Note: Use fetchone() instead of fetchall()[0] when you only need the first or only result -- it avoids the overhead of transferring and holding the entire result set in memory.

Warning: fetchone() returns None, not an error, when there are no more rows -- always check for None before trying to unpack or index into the result.

Example: Using fetchone() for a Single Row

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers WHERE id = %s", (1,))
cursor.fetchone.return_value = (1, "John", "Highway 21")
print(cursor.fetchone())

Iterating the Cursor Directly

A cursor object is itself iterable after execute() runs -- looping over it directly with a for statement fetches rows one at a time under the hood, which is memory-efficient for large result sets since it avoids loading everything into a list at once.

Note: Iterate the cursor directly (for row in cursor:) instead of fetchall() when processing very large result sets, to avoid holding the entire result in memory simultaneously.

Warning: Once a cursor has been fully iterated (or fetchall'd), trying to read from it again returns nothing -- you must re-run execute() to query again.

Example: Iterating the Cursor Directly

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers")
cursor.__iter__.return_value = iter([(1, "John"), (2, "Amy")])
for row in cursor:
    print(row)

Accessing Results as Dictionaries

mysql.connector.connect(..., dictionary=True) as a cursor argument (or conn.cursor(dictionary=True)) makes each fetched row come back as a dict keyed by column name instead of a plain tuple, which is often more readable than remembering positional tuple indexes.

Note: Use conn.cursor(dictionary=True) whenever a query touches several columns, since row["column_name"] is far more self-documenting at the call site than row[3].

Warning: Dictionary cursors carry a small extra performance overhead compared to plain tuple cursors -- for extremely hot, high-throughput query paths, plain tuples can still be preferable.

Example: Accessing Results as Dictionaries

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM customers")
cursor.fetchall.return_value = [{"id": 1, "name": "John"}]
for row in cursor.fetchall():
    print(row["name"])

Counting Result Rows

cursor.rowcount, read after fetching results from a SELECT, reports how many rows the query returned -- useful for quickly checking whether any matching rows exist at all without inspecting the full fetched data.

Note: Check cursor.rowcount (or simply len() on a fetchall() list) rather than assuming a query always returns at least one row.

Warning: For SELECT queries, some MySQL drivers only populate rowcount accurately after the results have actually been fetched, not immediately after execute() runs -- always fetch first.

Example: Counting Result Rows

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers")
cursor.fetchall.return_value = [(1,), (2,), (3,)]
rows = cursor.fetchall()
cursor.rowcount = len(rows)
print(cursor.rowcount)
Common Mistakes
  1. Calling cursor.execute() for a SELECT but never actually calling a fetch method afterward, leaving the results unread.
  2. Using fetchall() on a table with millions of rows, loading the entire result set into memory at once instead of fetching in smaller batches or iterating the cursor directly.
  3. Forgetting that column order in the result tuples matches the order columns were listed in the SELECT statement (or the table's column order for SELECT *), not alphabetical order.
Chapter Summary
  • cursor.execute("SELECT * FROM table") runs a query that selects every column from every row in a table.
  • cursor.fetchall() retrieves every row from the last executed query as a list of tuples.
  • cursor.fetchone() retrieves just the single next row, useful when you expect (or only need) one result.
Browser Support

SELECT syntax and the fetch methods described here work identically across all supported mysql-connector-python versions.

🔒

Chapter Quiz — Complete all 12 topics to unlock

0/12 topics done

Complete these topics first:

← Previous Lesson Python MySQL Insert Into Table Next Lesson → Python MySQL Select With a Filter

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.