W3docs
en
Log in

MySQL Select in Python

Learn how to use SELECT in Python with mysql-connector-python: fetch all rows, single rows, specific columns, and use fetchone vs fetchall.

The SELECT statement is the foundation of every database read operation. In Python you execute it through a cursor object provided by mysql-connector-python, then retrieve the results with fetchall(), fetchone(), or fetchmany(). This chapter covers all three fetch methods, how to select specific columns, how to work with column names, and the error-handling patterns you need in production code.

Prerequisites

Install the MySQL connector if you have not done so already:

pip install mysql-connector-python

All examples assume:

  • A running MySQL server (local or remote).
  • A database called mydatabase with a customers table that has at least the columns id, name, and address.

Follow Python MySQL Create Table to create the table, and Python MySQL Insert to populate it with sample rows before running the SELECT examples below.

Connecting to the database

Every example starts from a connection object. Rather than repeating the setup in every snippet, keep it in one place and reuse it:

import mysql.connector
from mysql.connector import Error

connection = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="mydatabase"
)

Replace yourusername and yourpassword with your actual credentials. See Python MySQL Get Started for how to store credentials in environment variables instead of hard-coding them.

Selecting all rows with fetchall()

fetchall() retrieves every row returned by the query and stores them in a Python list of tuples. Use it when the result set is small enough to fit comfortably in memory.

Select all records from the customers table

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM customers")

    rows = cursor.fetchall()

    for row in rows:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if cursor:
        cursor.close()
    if connection.is_connected():
        connection.close()

Each row is a tuple whose values correspond to the table columns in the order they appear in the CREATE TABLE statement. For a customers table with (id, name, address) columns you would see output similar to:

(1, 'John', '123 Main St')
(2, 'Susan', '456 Oak Ave')
(3, 'Maria', '789 Pine Rd')

Why wrap everything in try/except/finally?

If an exception is raised between opening the connection and closing it, the connection leaks — it stays open and consumes server resources until the server times it out. The finally block guarantees that cursor.close() and connection.close() are always called, even when something goes wrong.

Selecting a single row with fetchone()

When you only need the first matching row — or you know the query returns exactly one result — fetchone() is more efficient than fetchall(). It returns a single tuple or None if there are no results.

Retrieve one row from the customers table

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM customers")

    row = cursor.fetchone()

    if row:
        print("First customer:", row)
    else:
        print("No records found.")

except Error as e:
    print(f"Error: {e}")
finally:
    if cursor:
        cursor.close()
    if connection.is_connected():
        connection.close()

fetchone() advances the cursor's internal pointer. Calling it again returns the next row. This makes it suitable for iterating through results one row at a time without loading the entire result set into memory.

Selecting specific columns

Using SELECT * returns every column in the table. For large tables or when you only need a few fields, name the columns explicitly. This reduces data transferred over the network and makes your code's intent clear.

Select only the name and address columns

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    cursor = connection.cursor()
    cursor.execute("SELECT name, address FROM customers")

    rows = cursor.fetchall()

    for row in rows:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if cursor:
        cursor.close()
    if connection.is_connected():
        connection.close()

Output (example):

('John', '123 Main St')
('Susan', '456 Oak Ave')
('Maria', '789 Pine Rd')

Fetching rows in batches with fetchmany()

fetchmany(size) retrieves size rows at a time. Use it when the result set is too large for fetchall() but you still want to process rows in chunks rather than one-by-one.

Process results 10 rows at a time

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM customers")

    batch_size = 10
    while True:
        rows = cursor.fetchmany(batch_size)
        if not rows:
            break
        for row in rows:
            print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if cursor:
        cursor.close()
    if connection.is_connected():
        connection.close()

fetchmany() returns an empty list when there are no more rows, which is what the if not rows: break check detects.

Accessing columns by name with a dictionary cursor

By default, each row is a plain tuple. If you have many columns, accessing row[4] instead of row["email"] makes code hard to read and breaks silently when the column order changes. Pass dictionary=True to the cursor constructor to get each row as a dict instead.

Use a dictionary cursor for named column access

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    # dictionary=True makes each row a dict
    cursor = connection.cursor(dictionary=True)
    cursor.execute("SELECT * FROM customers")

    rows = cursor.fetchall()

    for row in rows:
        print(f"ID: {row['id']}, Name: {row['name']}, Address: {row['address']}")

except Error as e:
    print(f"Error: {e}")
finally:
    if cursor:
        cursor.close()
    if connection.is_connected():
        connection.close()

Output:

ID: 1, Name: John, Address: 123 Main St
ID: 2, Name: Susan, Address: 456 Oak Ave

Dictionary cursors make code self-documenting and resilient to column reordering.

Filtering rows with WHERE

To retrieve only the rows that match a condition, add a WHERE clause. Always use parameterized queries — never format user-supplied values directly into the SQL string, because that opens the door to SQL injection attacks.

Retrieve customers who live on a specific street

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    cursor = connection.cursor()

    sql = "SELECT * FROM customers WHERE address = %s"
    val = ("123 Main St",)  # Always pass a tuple, even for a single value
    cursor.execute(sql, val)

    rows = cursor.fetchall()

    for row in rows:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if cursor:
        cursor.close()
    if connection.is_connected():
        connection.close()

The %s placeholder is filled in by the connector driver, which escapes the value safely. See Python MySQL Where for a full treatment of filtering, including multiple conditions with AND/OR.

Using SELECT with ORDER BY and LIMIT

Combine SELECT with ORDER BY to sort results and LIMIT to cap the number of rows returned. This pattern is essential for displaying the most recent records or implementing pagination.

Get the 3 customers with the highest IDs, sorted newest first

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM customers ORDER BY id DESC LIMIT 3")

    rows = cursor.fetchall()

    for row in rows:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if cursor:
        cursor.close()
    if connection.is_connected():
        connection.close()

For a dedicated guide to sorting, see Python MySQL Order By. For pagination with OFFSET, see Python MySQL Limit.

Checking the column names with cursor.description

After calling execute(), the cursor's description attribute holds metadata about each column in the result — including the column name. This is useful when you need the column names dynamically (for example, when building a CSV export) without hard-coding them.

Print column names alongside the data

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM customers")

    # cursor.description is a list of 7-item sequences; index 0 is the column name
    column_names = [desc[0] for desc in cursor.description]
    print("Columns:", column_names)

    rows = cursor.fetchall()
    for row in rows:
        print(dict(zip(column_names, row)))

except Error as e:
    print(f"Error: {e}")
finally:
    if cursor:
        cursor.close()
    if connection.is_connected():
        connection.close()

Output (example):

Columns: ['id', 'name', 'address']
{'id': 1, 'name': 'John', 'address': '123 Main St'}
{'id': 2, 'name': 'Susan', 'address': '456 Oak Ave'}

Choosing between fetchall(), fetchone(), and fetchmany()

MethodReturnsBest for
fetchall()List of all tuplesSmall to medium result sets where all rows are needed at once
fetchone()Single tuple or NoneLookup by primary key; iterating row-by-row
fetchmany(n)List of up to n tuplesLarge result sets processed in streaming chunks

If you call fetchall() on a million-row result set, Python stores all one million tuples in memory at the same time. For large tables, use fetchmany() or add a LIMIT clause to the query.

Common errors and how to fix them

ErrorLikely causeFix
mysql.connector.errors.ProgrammingError: Table doesn't existThe table name is wrong or the database was not selectedCheck the table name; ensure the database parameter is set on connect()
mysql.connector.errors.InterfaceError: No result setCalling fetchall() after a non-SELECT statementOnly call fetch methods after SELECT, SHOW, or other result-producing statements
mysql.connector.errors.DatabaseError: Lost connectionNetwork timeout or server restartRe-establish the connection; for long-running apps, use a connection pool
InternalError: Unread result foundStarting a new execute() before consuming previous resultsCall fetchall() or cursor.reset() to drain the previous result set first

Summary

  • Execute a SELECT statement with cursor.execute("SELECT ...").
  • Use fetchall() for small result sets, fetchone() for a single row, and fetchmany(n) for large datasets processed in chunks.
  • Pass dictionary=True to the cursor to access columns by name instead of index.
  • Always use %s parameterized placeholders when filtering with user-supplied values.
  • Wrap every database operation in try/except/finally to guarantee the connection is closed on error.
Was this page helpful?