# Connect to a database (creates the database if it doesn't exist)conn=sqlite3.connect('example.db')# Create a cursor objectcur=conn.cursor()
Closing the Connection
# Commit the changesconn.commit()# Close the connectionconn.close()
Creating Tables
Creating a Table
cur.execute('''CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)''')conn.commit()
Inserting Data
Inserting a Single Row
cur.execute("INSERT INTO users (name, age) VALUES (?, ?)",('Alice',30))conn.commit()
Inserting Multiple Rows
users=[('Bob',25),('Charlie',35),('David',40)]cur.executemany("INSERT INTO users (name, age) VALUES (?, ?)",users)conn.commit()
Querying Data
Selecting All Rows
cur.execute("SELECT * FROM users")rows=cur.fetchall()forrowinrows:print(row)
Selecting with Conditions
cur.execute("SELECT * FROM users WHERE age > ?",(30,))rows=cur.fetchall()forrowinrows:print(row)
Using Named Parameters
cur.execute("SELECT * FROM users WHERE name = :name",{"name":"Alice"})rows=cur.fetchall()forrowinrows:print(row)
Updating Data
Updating Rows
cur.execute("UPDATE users SET age = ? WHERE name = ?",(28,'Alice'))conn.commit()
Deleting Data
Deleting Rows
cur.execute("DELETE FROM users WHERE name = ?",('Bob',))conn.commit()
Using Transactions
Starting a Transaction
conn.execute("BEGIN TRANSACTION")
Committing a Transaction
conn.commit()
Rolling Back a Transaction
conn.rollback()
Using Context Managers
Automatically Committing Transactions
withsqlite3.connect('example.db')asconn:cur=conn.cursor()cur.execute("INSERT INTO users (name, age) VALUES (?, ?)",('Eve',22))
Advanced Features
Creating an In-Memory Database
conn=sqlite3.connect(':memory:')cur=conn.cursor()
Using Row Factory for Named Columns
conn.row_factory=sqlite3.Rowcur=conn.cursor()cur.execute("SELECT * FROM users")rows=cur.fetchall()forrowinrows:print(row['name'],row['age'])
Executing Script
cur.executescript(''' DROP TABLE IF EXISTS users; CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL); INSERT INTO users (name, age) VALUES ('Alice', 30); INSERT INTO users (name, age) VALUES ('Bob', 25);''')conn.commit()
Error Handling
Handling Exceptions
try:cur.execute("SELECT * FROM non_existing_table")exceptsqlite3.Errorase:print("An error occurred:",e.args[0])