Working with Data

Fetching Data

• SELECT statement: Retrieve rows from a table based on specific criteria.
• Result set: Collection of rows returned by a SELECT statement.
• Cursor: Object used to iterate through a result set.

Using Cursors

• Create a cursor: Establish a connection to the database and use its cursor() method to create a cursor object.
• Execute a query: Execute a SELECT statement using the cursor's execute() method.
• Fetch rows: Call the cursor's fetchone() or fetchall() methods to retrieve one or all rows in the result set.

Techniques for Retrieving and Manipulating Data using Python and MySQL

import mysql.connector

# Establish database connection
conn = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="database_name"
)

# Create cursor
cursor = conn.cursor()

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

# Fetch all rows
rows = cursor.fetchall()

# Print result set
for row in rows:
    print(row)

# Update a record
cursor.execute("UPDATE table_name SET column_name = 'new value' WHERE id = 1")

# Commit changes
conn.commit()

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