+91 7387620624

Executing Queries

Introduction

After establishing a connection to a MySQL database, the next step is to execute SQL queries. Queries allow you to create tables, insert data, retrieve records, update information, and delete records from the database.

In Python, queries are executed using a cursor object.

Executing queries is a fundamental skill for:

  • Database Applications

  • Selenium Automation Frameworks

  • API Automation Frameworks

  • Test Data Management

  • Reporting Systems

  • Backend Development

In this tutorial, you will learn how to execute MySQL queries in Python, practical examples, automation testing use cases, common mistakes, and best practices.

What is a Query?

A query is an SQL statement sent to the database for execution.

Common query types:

Query Type Purpose
CREATE Create database objects
INSERT Add records
SELECT Retrieve records
UPDATE Modify records
DELETE Remove records

Creating a Database Connection

Before executing queries:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="company"
)

cursor = connection.cursor()

Using cursor.execute()

The execute() method runs a single SQL query.

Syntax

cursor.execute(query)

or

cursor.execute(query, values)

Executing CREATE TABLE Query

Example

query = """
CREATE TABLE IF NOT EXISTS employees(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    salary DECIMAL(10,2)
)
"""

cursor.execute(query)

print("Table Created")

Output

Table Created

Executing INSERT Query

Example

query = """
INSERT INTO employees
(name, salary)
VALUES (%s, %s)
"""

values = ("John", 50000)

cursor.execute(query, values)

connection.commit()

print("Record Inserted")

Output

Record Inserted

Why commit() is Required

For INSERT, UPDATE, and DELETE operations:

connection.commit()

Without commit(), changes are not permanently saved.

Executing SELECT Query

Example

query = """
SELECT *
FROM employees
"""

cursor.execute(query)

records = cursor.fetchall()

for record in records:
    print(record)

Output

(1, 'John', 50000.00)
(2, 'Alice', 60000.00)

Fetching Records

fetchone()

Returns one row.

cursor.execute(
    "SELECT * FROM employees"
)

record = cursor.fetchone()

print(record)

Output

(1, 'John', 50000.00)

fetchall()

Returns all rows.

records = cursor.fetchall()

Output

[
 (1, 'John', 50000),
 (2, 'Alice', 60000)
]

fetchmany()

Returns a specified number of rows.

records = cursor.fetchmany(3)

Executing UPDATE Query

Example

query = """
UPDATE employees
SET salary = %s
WHERE name = %s
"""

values = (75000, "John")

cursor.execute(query, values)

connection.commit()

print("Record Updated")

Output

Record Updated

Executing DELETE Query

Example

query = """
DELETE FROM employees
WHERE name = %s
"""

cursor.execute(query, ("John",))

connection.commit()

print("Record Deleted")

Output

Record Deleted

Executing Multiple Inserts

Use executemany().

Example

query = """
INSERT INTO employees
(name, salary)
VALUES (%s, %s)
"""

data = [
    ("Alice", 60000),
    ("Bob", 55000),
    ("David", 70000)
]

cursor.executemany(
    query,
    data
)

connection.commit()

Parameterized Queries

Always use placeholders.

Correct

query = """
SELECT *
FROM employees
WHERE name = %s
"""

cursor.execute(
    query,
    ("John",)
)

Incorrect

query = f"""
SELECT *
FROM employees
WHERE name='{name}'
"""

This can lead to SQL Injection attacks.

Getting Number of Affected Rows

Use rowcount.

Example

cursor.execute("""
DELETE FROM employees
WHERE salary < 30000
""")

print(
    cursor.rowcount,
    "rows affected"
)

Output

3 rows affected

Getting Last Inserted ID

Example

cursor.execute(
    query,
    ("John", 50000)
)

connection.commit()

print(
    cursor.lastrowid
)

Output

1

Executing Queries with Error Handling

Example

import mysql.connector

try:

    cursor.execute(
        "SELECT * FROM employees"
    )

    records = cursor.fetchall()

except mysql.connector.Error as error:

    print("Error:", error)

Real-World Example: Selenium Automation

Store test execution results.

query = """
INSERT INTO test_results
(test_name, status)
VALUES (%s, %s)
"""

cursor.execute(
    query,
    ("Login Test", "PASS")
)

connection.commit()

Use Cases

Store:

  • Test Results

  • Browser Names

  • Execution Times

  • Failure Reasons

Selenium Example: Retrieve Test Data

query = """
SELECT username,
       password
FROM login_data
"""

cursor.execute(query)

test_data = cursor.fetchall()

for row in test_data:
    print(row)

Useful for Data-Driven Testing.

Real-World Example: API Automation

Store API execution logs.

query = """
INSERT INTO api_logs
(endpoint, status_code)
VALUES (%s, %s)
"""

cursor.execute(
    query,
    ("/users", 200)
)

connection.commit()

Common Mistakes Beginners Make

Forgetting commit()

Incorrect

cursor.execute(query)

Changes won’t be saved.

Correct

connection.commit()

Using String Formatting

Incorrect

query = f"""
SELECT *
FROM employees
WHERE id={user_id}
"""

Correct

query = """
SELECT *
FROM employees
WHERE id=%s
"""

cursor.execute(
    query,
    (user_id,)
)

Calling fetchall() Before SELECT

Incorrect

cursor.fetchall()

Without executing a SELECT query first.

Forgetting to Close Connections

Correct

cursor.close()
connection.close()

Best Practices

Use Parameterized Queries

WHERE id=%s

Commit After Data Modification

connection.commit()

Handle Exceptions

try:
    pass

except mysql.connector.Error:
    pass

Use executemany() for Bulk Inserts

Improves performance.

Close Database Resources

cursor.close()
connection.close()

Advantages of Executing Queries Through Python

  • Dynamic database operations

  • Automation support

  • Test data management

  • Real-time updates

  • Integration with applications

Conclusion

Executing queries is the core of database programming in Python. Using methods such as execute(), executemany(), fetchone(), and fetchall(), you can interact with MySQL databases efficiently and securely.

Whether you’re retrieving Selenium test data, storing API logs, managing application records, or performing automated database validations, mastering query execution is an essential skill for Python developers and automation engineers.

Frequently Asked Questions (FAQs)

Which method executes a SQL query?

cursor.execute()

Which method executes multiple queries with different values?

cursor.executemany()

How do I retrieve all records?

cursor.fetchall()

How do I retrieve one record?

cursor.fetchone()

Why is commit() needed?

It permanently saves changes made by:

  • INSERT

  • UPDATE

  • DELETE

queries.

Key Takeaways

  • Use cursor.execute() to run SQL queries.

  • Use executemany() for bulk inserts.

  • Use fetchone(), fetchmany(), and fetchall() to retrieve records.

  • Always call commit() after data modifications.

  • Use parameterized queries to prevent SQL injection.

  • Use rowcount to check affected rows.

  • Use lastrowid to get the inserted record ID.

  • Handle database exceptions properly.

  • Useful for Selenium and API automation frameworks.

  • Close cursors and database connections after use.