Python MySQL Select From

To select data from a MySQL table using Python, you can use the following steps.

1. Install MySQL Connector

If you haven’t installed the MySQL connector yet, use this command:

pip install mysql-connector-python

2. Connect to the MySQL Database

You need to connect to the database where the table from which you want to select data resides.

Example Python Script to Select Data from a Table

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",         # Change this to your MySQL host if needed
  user="yourusername",       # Change this to your MySQL username
  password="yourpassword",   # Change this to your MySQL password
  database="mynewdatabase"   # Change this to your database name
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to select data from the table
mycursor.execute("SELECT * FROM customers")

# Fetch all the rows
result = mycursor.fetchall()

# Print the result
for row in result:
    print(row)

Explanation:

• mycursor.execute(): Executes the SQL query to select data from the table.
• mycursor.fetchall(): Fetches all the rows returned by the executed query. The result is a list of tuples, where each tuple represents a row in the table.

Example Output:

If the customers table has the following data:

The output will look like this:

(1, 'John Doe', '123 Elm Street')
(2, 'Jane Doe', '456 Maple Avenue')
(3, 'Steve Smith', '789 Oak Drive')

3. Selecting Specific Columns

You can modify the SQL query to select only specific columns from the table.

mycursor.execute("SELECT name, address FROM customers")

# Fetch all the rows
result = mycursor.fetchall()

for row in result:
    print(row)

This query selects only the name and address columns from the customers table.

4. Using fetchone() for Single Rows

You can also use the fetchone() method to retrieve one row at a time:

mycursor.execute("SELECT * FROM customers")

# Fetch one row
row = mycursor.fetchone()
while row:
    print(row)
    row = mycursor.fetchone()

5. Filtering Results with WHERE Clause

To filter the data returned by the query, use the WHERE clause.

sql = "SELECT * FROM customers WHERE address = %s"
val = ("123 Elm Street",)

mycursor.execute(sql, val)

# Fetch all matching rows
result = mycursor.fetchall()

for row in result:
    print(row)

6. Sorting Results with ORDER BY

You can sort the results using the ORDER BY clause.

mycursor.execute("SELECT * FROM customers ORDER BY name")

# Fetch all sorted rows
result = mycursor.fetchall()

for row in result:
    print(row)

Full Example: Select and Filter Data

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mynewdatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# Select specific columns and filter by address
sql = "SELECT name, address FROM customers WHERE address = %s"
val = ("123 Elm Street",)

# Execute the query
mycursor.execute(sql, val)

# Fetch all the rows
result = mycursor.fetchall()

# Print the result
for row in result:
    print(row)

# Close the connection
mydb.close()

7. Closing the Connection

Always close the connection after you’re done:

mydb.close()

This script allows you to retrieve data from a MySQL table, apply filtering and sorting, and handle the data in various ways.