Python MySQL – Select Data

To read data from a MySQL table we run a SELECT query and then fetch the rows from the cursor. We use the users table with the three rows added in the insert chapter.

---

Fetch all rows

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="student"
)
cursor = conn.cursor()

cursor.execute("SELECT id, name, email FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

cursor.close()
conn.close()

fetchall() returns a list of tuples, one tuple for each row.

---

Fetch one row

fetchone() gives the next single row, or None if there are no more rows.

cursor.execute("SELECT name, email FROM users")
print(cursor.fetchone())
print(cursor.fetchone())

---

Filter rows with WHERE

Use a placeholder in the WHERE condition too, and pass the value in a tuple. Note the comma in ("Maria",), which makes a one item tuple.

cursor.execute("SELECT name, mobile FROM users WHERE name = %s", ("Maria",))
print(cursor.fetchone())

---

Get rows as dictionaries

If you pass dictionary=True to the cursor, every row becomes a dictionary, and you can use the column names.

cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT name, email FROM users")

for row in cursor.fetchall():
    print(row["name"], "-", row["email"])

---

Order and limit

cursor.execute("SELECT name FROM users ORDER BY name DESC LIMIT 2")
print(cursor.fetchall())

The names are sorted from Z to A, and only the first two are returned.

---

Summary

• Run SELECT with cursor.execute().
• fetchall() gives all rows, fetchone() gives one row.
• Pass values for WHERE with %s placeholders.
• Use conn.cursor(dictionary=True) to read columns by name.
• Next: update and delete data.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.