165. Working with sqlite3 Database

Snippet 1: Connecting to an SQLite Database

Copy

import sqlite3

# Connect to the database (creates the file if it doesn't exist)
connection = sqlite3.connect("example.db")

# Print success message
print("Database connected successfully!")

# Close the connection
connection.close()

---

Snippet 2: Creating a Table

Copy

import sqlite3

# Connect to the database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Create a table
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
)
""")
print("Table created successfully!")

# Commit changes and close the connection
connection.commit()
connection.close()

---

Snippet 3: Inserting Data into a Table

Copy

import sqlite3

# Connect to the database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))
print("Data inserted successfully!")

# Commit changes and close the connection
connection.commit()
connection.close()

---

Snippet 4: Retrieving Data from a Table

Copy

import sqlite3

# Connect to the database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Retrieve data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close the connection
connection.close()

---

Snippet 5: Updating Data in a Table

Copy

import sqlite3

# Connect to the database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Update data
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (35, "Alice"))
print("Data updated successfully!")

# Commit changes and close the connection
connection.commit()
connection.close()

---

Snippet 6: Deleting Data from a Table

Copy

import sqlite3

# Connect to the database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Delete data
cursor.execute("DELETE FROM users WHERE name = ?", ("Alice",))
print("Data deleted successfully!")

# Commit changes and close the connection
connection.commit()
connection.close()

---

Snippet 7: Using Placeholders to Prevent SQL Injection

Copy

import sqlite3

# Connect to the database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Securely insert data using placeholders
name = "Bob"
age = 25
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))
print("Secure data insertion completed!")

# Commit changes and close the connection
connection.commit()
connection.close()

---

Snippet 8: Using a Context Manager for Connections

Copy

import sqlite3

# Use context manager to manage connection
with sqlite3.connect("example.db") as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

---

Snippet 9: Using Row Factory for Named Columns

Copy

import sqlite3

# Connect to the database
connection = sqlite3.connect("example.db")

# Set row factory to access columns by name
connection.row_factory = sqlite3.Row
cursor = connection.cursor()

# Retrieve data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(f"ID: {row['id']}, Name: {row['name']}, Age: {row['age']}")

# Close the connection
connection.close()

---

Snippet 10: Dropping a Table

Copy

import sqlite3

# Connect to the database
connection = sqlite3.connect("example.db")
cursor = connection.cursor()

# Drop a table
cursor.execute("DROP TABLE IF EXISTS users")
print("Table dropped successfully!")

# Commit changes and close the connection
connection.commit()
connection.close()

---