Writing to SQL Databases
Learn to insert, update and delete data in SQL databases
Writing to SQL Databases
Inserting Single Row
Add one row of data at a time.
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
cursor.execute("""
INSERT INTO products (name, price, quantity)
VALUES ("Laptop", 999.99, 5)
""")
connection.commit()
print("Product added")
connection.close()What this does: Adds one product to the products table.
Important: Always call commit() to save changes.
Using Parameters
Never put values directly in SQL. Use placeholders.
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
name = "Phone"
price = 599.99
quantity = 10
cursor.execute("""
INSERT INTO products (name, price, quantity)
VALUES (?, ?, ?)
""", (name, price, quantity))
connection.commit()
connection.close()Why use placeholders:
- Prevents SQL injection attacks
- Handles special characters safely
- Cleaner code
Inserting Multiple Rows
Use executemany() for multiple rows at once.
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
products = [
("Tablet", 399.99, 7),
("Mouse", 25.99, 20),
("Keyboard", 75.99, 15)
]
cursor.executemany("""
INSERT INTO products (name, price, quantity)
VALUES (?, ?, ?)
""", products)
connection.commit()
print(cursor.rowcount, "products added")
connection.close()What this does: Inserts 3 products in one operation. Much faster than looping.
Getting Last Inserted ID
When you insert a row with auto-incrementing ID, get the new ID.
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
cursor.execute("""
INSERT INTO products (name, price, quantity)
VALUES ("Monitor", 299.99, 8)
""")
new_id = cursor.lastrowid
print("New product ID:", new_id)
connection.commit()
connection.close()What lastrowid gives: The ID of the row you just inserted.
Updating Data
Change existing rows.
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
cursor.execute("""
UPDATE products
SET price = 899.99
WHERE name = "Laptop"
""")
connection.commit()
print("Rows updated:", cursor.rowcount)
connection.close()What this does: Changes Laptop price to 899.99. rowcount tells how many rows changed.
Updating Multiple Columns
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
cursor.execute("""
UPDATE products
SET price = 549.99, quantity = 12
WHERE name = "Phone"
""")
connection.commit()
connection.close()What this does: Updates both price and quantity for Phone.
Updating with Calculations
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
cursor.execute("""
UPDATE products
SET quantity = quantity - 1
WHERE name = "Laptop"
""")
connection.commit()
print("Stock decreased")
connection.close()What this does: Reduces quantity by 1 (useful for sales).
Deleting Data
Remove rows from table.
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
cursor.execute("""
DELETE FROM products
WHERE quantity = 0
""")
connection.commit()
print("Rows deleted:", cursor.rowcount)
connection.close()What this does: Removes all products with zero quantity.
Warning: DELETE without WHERE removes ALL rows!
Conditional Insert
Insert only if row doesn't exist.
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
cursor.execute("""
INSERT OR IGNORE INTO products (name, price, quantity)
VALUES ("Laptop", 999.99, 5)
""")
connection.commit()
connection.close()What INSERT OR IGNORE does: If row with same name exists, skips insert (no error).
Replace Existing Row
Update if exists, insert if not.
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
cursor.execute("""
REPLACE INTO products (id, name, price, quantity)
VALUES (1, "Laptop", 899.99, 10)
""")
connection.commit()
connection.close()What REPLACE does: If id 1 exists, updates it. If not, inserts new row.
Transactions
Group multiple operations together. All succeed or all fail.
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
try:
cursor.execute("""
UPDATE products SET quantity = quantity - 1 WHERE name = "Laptop"
""")
cursor.execute("""
INSERT INTO sales (product, amount)
VALUES ("Laptop", 999.99)
""")
connection.commit()
print("Sale completed")
except Exception as e:
connection.rollback()
print("Sale failed, rolled back:", e)
connection.close()What rollback() does: Undoes all changes if any operation fails. Keeps data consistent.
Practice Example
The scenario: Build inventory management system with stock tracking.
import sqlite3
def init_database():
connection = sqlite3.connect("inventory.db")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS inventory (
id INTEGER PRIMARY KEY AUTOINCREMENT,
product TEXT UNIQUE NOT NULL,
quantity INTEGER DEFAULT 0,
price REAL
)
""")
connection.commit()
connection.close()
def add_product(product, quantity, price):
connection = sqlite3.connect("inventory.db")
cursor = connection.cursor()
try:
cursor.execute("""
INSERT INTO inventory (product, quantity, price)
VALUES (?, ?, ?)
""", (product, quantity, price))
connection.commit()
print("Product added:", product)
except sqlite3.IntegrityError:
print("Error: Product already exists")
connection.close()
def update_stock(product, quantity_change):
connection = sqlite3.connect("inventory.db")
cursor = connection.cursor()
cursor.execute("""
UPDATE inventory
SET quantity = quantity + ?
WHERE product = ?
""", (quantity_change, product))
if cursor.rowcount > 0:
connection.commit()
print("Stock updated for:", product)
else:
print("Product not found:", product)
connection.close()
def remove_product(product):
connection = sqlite3.connect("inventory.db")
cursor = connection.cursor()
cursor.execute("""
DELETE FROM inventory WHERE product = ?
""", (product,))
if cursor.rowcount > 0:
connection.commit()
print("Product removed:", product)
else:
print("Product not found:", product)
connection.close()
init_database()
add_product("Laptop", 10, 999.99)
add_product("Mouse", 50, 25.99)
update_stock("Laptop", -2)
update_stock("Mouse", 10)
remove_product("Laptop")What this system does:
- Creates database with inventory table
- add_product() inserts new products
- update_stock() increases or decreases quantity
- remove_product() deletes products
- Handles errors gracefully
Bulk Updates
Update many rows at once.
import sqlite3
connection = sqlite3.connect("store.db")
cursor = connection.cursor()
updates = [
(899.99, "Laptop"),
(549.99, "Phone"),
(349.99, "Tablet")
]
cursor.executemany("""
UPDATE products
SET price = ?
WHERE name = ?
""", updates)
connection.commit()
print("Bulk update complete")
connection.close()What this does: Updates prices for multiple products efficiently.
Key Points to Remember
Always use commit() to save changes. Changes are lost without it.
Use placeholders (question marks) for values. Never put user input directly in SQL.
executemany() is much faster for multiple rows than looping with execute().
UPDATE and DELETE without WHERE affect ALL rows. Always be careful with these commands.
Use transactions with try-except for operations that must complete together. rollback() undoes changes if anything fails.
Common Mistakes
Mistake 1: Forgetting commit
cursor.execute("INSERT INTO products VALUES (?, ?, ?)", data)
connection.close() # Data lost!Mistake 2: SQL injection
product = input("Product: ")
cursor.execute("INSERT INTO products (name) VALUES (" + product + ")") # Dangerous!Mistake 3: UPDATE without WHERE
cursor.execute("UPDATE products SET price = 0") # Sets ALL prices to 0!Mistake 4: Not handling errors
cursor.execute("INSERT INTO products VALUES (?, ?, ?)", data) # May fail!Use try-except.
What's Next?
You now know how to write to databases. Next, you'll learn about working with JSON data - reading and writing JSON format commonly used by APIs.