How to Fetch data from MySQL database by using Python?

How to Fetch data from MySQL database by using Python with example?

Let's say, 
we have a database named is eagleye, the table name is employees, the username is root and the password is 1234.
or, The Records in employees Table are: (learn how)

To get data from employee table we have different queries based on our requirements.

For Example:

Method	Use for
cursor.fetchall()	Fetches all records from the table.
cursor.fetchone()	Fetche only one record.

 

Let's use these one by one:

1) fetchall() : Fetch All Data Fetch all rows from a table to display them.

Syntax:

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

# Fetch data
rows = cursor.fetchall()

# Process the results
for row in rows:
    print(row)

Example:

cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
for row in rows:
    print(row)

Complete code:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",  
  user="root",     
  password="1234", 
  database="eagleye"
)

cursor = db.cursor()

cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
for row in rows:
    print(row)

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

print("Data Fetched.")

Output:

(1, 'Alice', 25, 40000.0)
(2, 'Bob', 35, 55000.0)        
(3, 'Charlie', 29, 48000.0)    
(4, 'Kriss moris', 20, 10000.0)
(5, 'dishu', 22, 155000.0)     
(6, 'seya', 24, 88000.0)       
Data Fetched.

---

Other example: Fetch Conditional Data Fetch rows based on a condition, like fetching employees with a salary greater than 50000.

code:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",  
  user="root",     
  password="1234", 
  database="eagleye"
)

cursor = db.cursor()

cursor.execute("SELECT * FROM employees WHERE salary > 50000")
rows = cursor.fetchall()
for row in rows:
    print(row)

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

print("Data Fetched.")

Output:

(2, 'Bob', 35, 55000.0)
(5, 'dishu', 22, 155000.0)
(6, 'seya', 24, 88000.0)  
Data Fetched.

---

 

2) Fetchone(): Fetch One Row Fetch the first row that matches a condition.

Syntax:

# Execute a query
cursor.execute("SELECT * FROM table_name WHERE condition")

# Fetch data
rows = cursor.fetchone()

Example:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",  
  user="root",     
  password="1234", 
  database="eagleye"
)

cursor = db.cursor()

cursor.execute("SELECT * FROM employees WHERE id = 2")
rows = cursor.fetchall()
for row in rows:
    print(row)

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

print("Data Fetched.")

Output:

(2, 'Bob', 35, 55000.0)
Data Fetched.

---

 

Interview questions related to fetching data using Python and SQL queries.

Question 1. How do you connect to a MySQL database in Python?

Answer: Use a library like mysql-connector-python to establish a connection with database credentials (host, username, password, database).

Example:

import mysql.connector
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test_db"
)
print("Connected!")

Output:

Connected!

 

Question 2. How do you fetch all rows from a table?

Answer: Use the SELECT * FROM table_name query and the fetchall() method.

Example:

cursor = conn.cursor()
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

(1, 'Alice', 'HR', 60000)
(2, 'Bob', 'IT', 70000)

 

Question 3. How do you fetch only one row?

Answer: Use the fetchone() method to retrieve the next row in the result set.

Example:

cursor.execute("SELECT * FROM employees")
row = cursor.fetchone()
print(row)

Output:

(1, 'Alice', 'HR', 60000)

 

Question 4. How do you fetch rows based on a condition?

Answer: Use the WHERE clause in the SQL query.

Example:

cursor.execute("SELECT * FROM employees WHERE department = 'IT'")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

(2, 'Bob', 'IT', 70000)

 

Question 5. How do you fetch specific columns from a table?

Answer: Mention the column names in the SELECT query.

Example:

cursor.execute("SELECT name, department FROM employees")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

('Alice', 'HR')
('Bob', 'IT')

 

Question 6. How do you fetch a limited number of rows?

Answer: Use the LIMIT keyword in the SQL query.

Example:

cursor.execute("SELECT * FROM employees LIMIT 2")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

(1, 'Alice', 'HR', 60000)
(2, 'Bob', 'IT', 70000)

 

Question 7. How do you order the results of a query?

Answer: Use the ORDER BY clause.

Example:

cursor.execute("SELECT * FROM employees ORDER BY salary DESC")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

(2, 'Bob', 'IT', 70000)
(1, 'Alice', 'HR', 60000)

 

Question 8. How do you find rows that match multiple conditions?

Answer: Use the AND or OR operator in the WHERE clause.

Example:

cursor.execute("SELECT * FROM employees WHERE department = 'IT' AND salary > 60000")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

(2, 'Bob', 'IT', 70000)

 

Question 9. How do you count the number of rows in a table?

Answer: Use the COUNT() function in SQL.

Example:

cursor.execute("SELECT COUNT(*) FROM employees")
row_count = cursor.fetchone()
print(row_count[0])

Output:

4

 

Question 10. How do you handle SQL injection while fetching data?

Answer: Use parameterized queries to prevent SQL injection.

Example:

cursor.execute("SELECT * FROM employees WHERE department = %s", ('IT',))
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

(2, 'Bob', 'IT', 70000)

 

Question 11. How do you join two tables in a query?

Answer: Use the JOIN clause in SQL.

Example:

cursor.execute("""
SELECT employees.name, departments.department_name 
FROM employees 
JOIN departments ON employees.department = departments.id
""")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

('Alice', 'Human Resources')
('Bob', 'Information Technology')

 

Question 12. How do you filter rows containing NULL values?

Answer: Use the IS NULL or IS NOT NULL condition.

Example:

cursor.execute("SELECT * FROM employees WHERE salary IS NOT NULL")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

(1, 'Alice', 'HR', 60000)

 

Question 13. How do you group data?

Answer: Use the GROUP BY clause.

Example:

cursor.execute("SELECT department, COUNT(*) FROM employees GROUP BY department")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

('HR', 2)
('IT', 1)

 

Question 14. How do you fetch data with a LIKE pattern?

Answer: Use the LIKE keyword for pattern matching.

Example:

cursor.execute("SELECT * FROM employees WHERE name LIKE 'A%'")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

(1, 'Alice', 'HR', 60000)

 

Question 15. How do you handle duplicate values in results?

Answer: Use the DISTINCT keyword.

Example:

cursor.execute("SELECT DISTINCT department FROM employees")
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

('HR',)
('IT',)

 

Question 16. How do you update data and fetch updated results?

Answer: Use the UPDATE query followed by a SELECT.

Example:

cursor.execute("UPDATE employees SET salary = 80000 WHERE name = 'Bob'")
conn.commit()
cursor.execute("SELECT * FROM employees WHERE name = 'Bob'")
print(cursor.fetchone())

Output:

(2, 'Bob', 'IT', 80000)

 

Question 17. How do you delete data and verify the result?

Answer: Use the DELETE query followed by a SELECT.

Example:

cursor.execute("DELETE FROM employees WHERE name = 'Alice'")
conn.commit()
cursor.execute("SELECT * FROM employees")
print(cursor.fetchall())

Output:

(2, 'Bob', 'IT', 70000)

 

Question 18. How do you fetch the current database name?

Answer: Use the query SELECT DATABASE().

Example:

cursor.execute("SELECT DATABASE()")
print(cursor.fetchone()[0])

Output:

test_db

 

Question 19. How do you fetch column names of a table?

Answer: Use cursor.description after executing the query.

Example:

cursor.execute("SELECT * FROM employees")
columns = [desc[0] for desc in cursor.description]
print(columns)

Output:

['ID', 'Name', 'Department', 'Salary']

 

 Question 20. How do you close the connection after fetching data?

Answer: Use conn.close() to close the database connection.

Example:

conn.close()
print("Connection closed.")

Output:

Connection closed.