# EasyTutorGuide — MySQL Chapter 39: INSERT UPDATE DELETE
# Run: python3 ch-39.py
# These examples use only Python standard library.
# JavaScript examples in the HTML are verified using Node.js and are not part of this Python file.

# EXAMPLE 1: Execute a real SQL query on sample rows
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE lessons (course TEXT, score INTEGER)")
conn.executemany("INSERT INTO lessons VALUES (?, ?)", [('html', 75), ('python', 80), ('sql', 89), ('python', 64), ('html', 92)])
query = 'SELECT course, COUNT(*) AS records, ROUND(AVG(score),1) AS average FROM lessons GROUP BY course ORDER BY course'
print("SQL:", query)
for row in conn.execute(query):
    print(*row, sep=" | ")
conn.close()


# EXAMPLE 2: Use a bound parameter and inspect the results
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE lessons (course TEXT, score INTEGER)")
conn.executemany("INSERT INTO lessons VALUES (?, ?)", [('html', 75), ('python', 80), ('sql', 89), ('python', 64), ('html', 92)])
min_score = 81
query = "SELECT COUNT(*), MIN(score), MAX(score) FROM lessons WHERE score >= ?"
count, low, high = conn.execute(query, (min_score,)).fetchone()
print("qualifying rows:", count)
print("range:", low, high)
conn.close()


# EXAMPLE 3: Test a transaction and rollback
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE lessons (course TEXT, score INTEGER)")
conn.executemany("INSERT INTO lessons VALUES (?, ?)", [('html', 75), ('python', 80), ('sql', 89), ('python', 64), ('html', 92)])
conn.commit()
conn.execute("BEGIN")
conn.execute("UPDATE lessons SET score = score + ? WHERE course = ?", (1, "python"))
print("inside transaction:", conn.execute("SELECT MAX(score) FROM lessons WHERE course='python'").fetchone()[0])
conn.rollback()
print("after rollback:", conn.execute("SELECT MAX(score) FROM lessons WHERE course='python'").fetchone()[0])
conn.close()