How to work with database files with Python and SQLite

Creating a Database

SQLite works with database files. If you don't have a database file, already you can create one from scratch.

To create a new SQLite database, you connect to it. If the database file doesn't exist, SQLite will create it for you.

import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('school.db')
# Create a cursor object
cursor = conn.cursor()
# Close the connection
conn.close()

import sqlite3
# Connect to SQLite database
conn = sqlite3.connect('school.db')
cursor = conn.cursor()
# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    grade TEXT
)
''')
# Commit changes and close the connection
conn.commit()
conn.close()

import sqlite3
# Connect to SQLite database
conn = sqlite3.connect('school.db')
cursor = conn.cursor()
# Insert data
cursor.execute('''
INSERT INTO students (name, age, grade)
VALUES ('Alice', 14, '8th')
''')
cursor.execute('''
INSERT INTO students (name, age, grade)
VALUES ('Bob', 15, '9th')
''')
# Commit changes and close the connection
conn.commit()
conn.close()

import sqlite3
# Connect to SQLite database
conn = sqlite3.connect('school.db')
cursor = conn.cursor()
# Query data
cursor.execute('''
SELECT * FROM students WHERE age >= 14
''')
# Fetch and print all rows
rows = cursor.fetchall()
for row in rows:
    print(row)
# Close the connection
conn.close()

import sqlite3
# Connect to SQLite database
conn = sqlite3.connect('school.db')
cursor = conn.cursor()
# Update data
cursor.execute('''
UPDATE students
SET grade = '10th'
WHERE name = 'Bob'
''')
# Commit changes and close the connection
conn.commit()
conn.close()

import sqlite3
# Connect to SQLite database
conn = sqlite3.connect('school.db')
cursor = conn.cursor()
# Delete data
cursor.execute('''
DELETE FROM students WHERE name = 'Alice'
''')
# Commit changes and close the connection
conn.commit()
conn.close()