Advanced Python: Databases (SQLite)

Goal

• Store data permanently (better than text files)

Step 1: Using SQLite

import sqlite3

# Connect to the database (creates daten.db if it doesn't exist)
conn = sqlite3.connect("daten.db")
cursor = conn.cursor()

# Create table if it does not exist
cursor.execute("CREATE TABLE IF NOT EXISTS user (name TEXT, age INTEGER)")

# Insert data
cursor.execute("INSERT INTO user VALUES ('Max', 20)")

# Save changes
conn.commit()

Explanation:

• sqlite3.connect() → establishes a connection to the database
• cursor.execute() → executes an SQL command
• CREATE TABLE IF NOT EXISTS → only creates the table if it doesn't already exist
• INSERT INTO → inserts data into the table
• conn.commit() → saves changes

Step 2: Reading Data

# Read all data
cursor.execute("SELECT * FROM user")
data = cursor.fetchall()
print(data)

# Close connection
conn.close()

Explanation:

• SELECT * FROM user → retrieves all rows from the table
• fetchall() → returns all results as a list of tuples
• conn.close() → closes the connection

Practical Task

• Store quiz results in the database:

cursor.execute("CREATE TABLE IF NOT EXISTS quiz (name TEXT, score INTEGER, level TEXT)")
cursor.execute("INSERT INTO quiz VALUES ('Anna', 3, 'easy')")
conn.commit()

• Show all scores:

cursor.execute("SELECT * FROM quiz")
scores = cursor.fetchall()
for score in scores:
    print("Name:", score[0], "Score:", score[1], "Level:", score[2])