How to SELECT data from a MySQL database using Python

A guide on how to connect and query data from a MySQL database using Python and the mysql-connector-python library.

In this article, you'll learn how to connect to a MySQL database and use Python to perform a SELECT query, retrieving data from a table within the database.

import mysql.connector

# Connect to the MySQL database
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test_db"
)

# Create a cursor object
cursor = conn.cursor()

# Execute a SELECT query
cursor.execute("SELECT * FROM students")

# Fetch all rows from the query result
rows = cursor.fetchall()

# Print the data
for row in rows:
    print(row)

# Close the connection
cursor.close()
conn.close()

Detailed explanation:

1. import mysql.connector: Imports the mysql.connector library to allow MySQL connections.
2. conn = mysql.connector.connect(...): Connects to the MySQL database using connection details such as host, user, password, and database.
3. cursor = conn.cursor(): Creates a cursor object to execute SQL queries.
4. cursor.execute("SELECT * FROM students"): Executes the SQL query to select all rows from the students table.
5. rows = cursor.fetchall(): Fetches all results from the query.
6. for row in rows: print(row): Iterates through and prints each row of data.
7. cursor.close() and conn.close(): Closes the cursor and the connection to the database.

System Requirements:

• Python 3.x
• Library: mysql-connector-python

How to install the libraries needed to run the Python code above:

Use pip to install the library:

pip install mysql-connector-python

Tips:

• Make sure your MySQL server is running before attempting to connect.
• Double-check your connection details like host, user, password, and database to avoid connection errors.