MySQL SELECT in Python

The SELECT statement in SQL is used to query the database and retrieve data from one or more tables. In Python, you can use the mysql-connector-python library to interact with MySQL and execute SELECT queries to fetch the data.

1. Install MySQL Connector

If you haven't installed the MySQL connector, run the following command:

pip install mysql-connector-python

2. Connect to MySQL Database

To start interacting with the MySQL database, first, establish a connection.

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase"  # Change to your database name)# Create a cursor objectcursor = conn.cursor()print("Connected to MySQL database successfully!")

3. Create Sample Table and Insert Data

For demonstration, let's create a table named employees and insert some sample data.

# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert sample data into employees tablecursor.execute("""INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR'), ('Bob', 25, 'IT'), ('Charlie', 28, 'Finance'),('David', 40, 'HR'),('Eve', 35, 'IT')""")conn.commit()

4. Basic SELECT Query

To select all the data from the employees table, you can use the SELECT * query.

# SQL query to select all data from employees tableselect_query = "SELECT * FROM employees"cursor.execute(select_query)# Fetch and print the resultsresults = cursor.fetchall()print("All employees:")for row in results:    print(row)

This query selects all columns from the employees table.

5. SELECT with Specific Columns

If you want to select only specific columns, like name and department, use:

# SQL query to select specific columnsselect_query_columns = "SELECT name, department FROM employees"cursor.execute(select_query_columns)# Fetch and print the resultsresults = cursor.fetchall()print("\nEmployee names and departments:")for row in results:    print(row)

This query retrieves only the name and department columns.

6. SELECT with WHERE Clause

To filter the results based on a condition, you can use the WHERE clause.

# SQL query to select employees with age greater than 30select_where_query = "SELECT * FROM employees WHERE age > 30"cursor.execute(select_where_query)# Fetch and print the resultsresults = cursor.fetchall()print("\nEmployees older than 30:")for row in results:    print(row)

This query retrieves only employees whose age is greater than 30.

7. SELECT with ORDER BY

You can use the ORDER BY clause to sort the results. For example, sorting by age:

# SQL query to select employees ordered by age (ascending)select_order_query = "SELECT * FROM employees ORDER BY age ASC"cursor.execute(select_order_query)# Fetch and print the resultsresults = cursor.fetchall()print("\nEmployees sorted by age (ascending):")for row in results:    print(row)

This query sorts the employees by age in ascending order.

8. SELECT with LIMIT

You can use the LIMIT clause to limit the number of rows returned.

# SQL query to select the first 3 employeesselect_limit_query = "SELECT * FROM employees LIMIT 3"cursor.execute(select_limit_query)# Fetch and print the resultsresults = cursor.fetchall()print("\nFirst 3 employees:")for row in results:    print(row)

This query retrieves only the first 3 records.

9. Closing the Connection

Once your operations are complete, make sure to close the connection.

cursor.close()conn.close()print("MySQL connection closed.")

Final Complete Code

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase")cursor = conn.cursor()# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert sample data into employees tablecursor.execute("""INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR'), ('Bob', 25, 'IT'), ('Charlie', 28, 'Finance'),('David', 40, 'HR'),('Eve', 35, 'IT')""")conn.commit()# SELECT all data from employees tableselect_query = "SELECT * FROM employees"cursor.execute(select_query)print("All employees:")results = cursor.fetchall()for row in results:    print(row)# SELECT specific columnsselect_query_columns = "SELECT name, department FROM employees"cursor.execute(select_query_columns)print("\nEmployee names and departments:")results = cursor.fetchall()for row in results:    print(row)# SELECT with WHERE clauseselect_where_query = "SELECT * FROM employees WHERE age > 30"cursor.execute(select_where_query)print("\nEmployees older than 30:")results = cursor.fetchall()for row in results:    print(row)# SELECT with ORDER BYselect_order_query = "SELECT * FROM employees ORDER BY age ASC"cursor.execute(select_order_query)print("\nEmployees sorted by age (ascending):")results = cursor.fetchall()for row in results:    print(row)# SELECT with LIMITselect_limit_query = "SELECT * FROM employees LIMIT 3"cursor.execute(select_limit_query)print("\nFirst 3 employees:")results = cursor.fetchall()for row in results:    print(row)# Close connectioncursor.close()conn.close()

Summary of SELECT Queries

• SELECT * FROM table_name — Select all columns.

• SELECT column1, column2 FROM table_name — Select specific columns.

• SELECT * FROM table_name WHERE condition — Filter rows based on a condition.

• SELECT * FROM table_name ORDER BY column_name ASC/DESC — Sort results.

• SELECT * FROM table_name LIMIT n — Limit the number of rows returned.

This is how you can use the SELECT statement in MySQL with Python! ?