sqlite3 create table

The sqlite3 module is the standard library for working with the SQLite database in Python. It provides an interface compliant with the DB-API 2.0 specification, allowing you to easily connect to, create, and interact with SQLite databases using SQL commands directly from your Python code.

It is particularly popular because SQLite is a serverless database engine, meaning it does not require a separate server process. The entire database is stored in a single file on disk, making it lightweight and ideal for local development, small-scale applications, and embedded systems.

---

Key Components of the sqlite3 Module

The interaction with an SQLite database in Python is managed primarily through two main classes: Connection and Cursor.

1. Connection Objects 🔗

The Connection object manages the connection to the SQLite database file. You get this object by calling sqlite3.connect().

Method	Description
connect(database)	The main function to establish a connection. If the database file does not exist, it is created.
commit()	Saves (persists) all changes made since the last call to commit() or rollback(). Crucial for making changes permanent.
rollback()	Undoes all changes made since the last commit().
close()	Closes the database connection. Always call this when you’re done to release the database file lock.
cursor()	Returns a new Cursor object that can be used to execute SQL statements.

Export to Sheets

---

2. Cursor Objects 📝

The Cursor object is what you use to actually execute SQL commands and fetch results. You get a Cursor object by calling the cursor() method on a Connection object.

Method	Description
execute(sql, parameters=())	Executes a single SQL statement. Use ? as placeholders in the SQL and pass values in the parameters tuple/list.
executemany(sql, seq_of_parameters)	Executes a single SQL statement against all parameter sequences found in the sequence of parameters. Used for bulk inserts or updates.
executescript(sql_script)	Executes multiple SQL statements provided as a single string (a script).
fetchone()	Fetches the next row of a query result set, returning a tuple, or None when no more data is available.
fetchall()	Fetches all remaining rows of a query result set, returning a list of tuples.
fetchmany(size=cursor.arraysize)	Fetches the next set of rows (default is 1). Returns a list of tuples.

Export to Sheets

---

Example Usage Flow:

1. Connect: conn = sqlite3.connect('example.db')
2. Cursor: c = conn.cursor()
3. Execute: c.execute("CREATE TABLE users (id, name)")
4. Commit: conn.commit()
5. Close: conn.close()

Connect to or create a database file named company.db.

Create the dept table.

Create the emp (employee) table with a Foreign Key linking it to the dept table.

Python

import sqlite3
import os

# Define the database file name
DB_FILE = 'company.db'

# --- Optional: Clean up previous file for a fresh start ---
# You can remove these lines if you want to keep data
if os.path.exists(DB_FILE):
    os.remove(DB_FILE)
# -----------------------------------------------------------

# Connect to the SQLite database (or create it if it doesn't exist)
# The connection is automatically established here.
conn = sqlite3.connect(DB_FILE)
print(f"Successfully connected to the database: {DB_FILE}")

# Create a cursor object to execute SQL commands
cursor = conn.cursor()

# 1. Create the 'dept' table
print("Creating 'dept' table...")
cursor.execute('''
    CREATE TABLE dept (
        deptno INTEGER PRIMARY KEY,
        dname TEXT NOT NULL,
        loc TEXT
    );
''')
print(" 'dept' table created.")

# 2. Create the 'emp' (employee) table
# Includes a Foreign Key (deptno) referencing the 'dept' table
print("Creating 'emp' table...")
cursor.execute('''
    CREATE TABLE emp (
        empno INTEGER PRIMARY KEY,
        ename TEXT NOT NULL,
        job TEXT,
        mgr INTEGER,
        hiredate TEXT,
        sal REAL,
        comm REAL,
        deptno INTEGER,
        FOREIGN KEY (deptno) REFERENCES dept (deptno)
    );
''')
print(" 'emp' table created.")

# Commit the changes to make them permanent
conn.commit()
print("Tables created and changes committed successfully.")

# Close the connection
conn.close()
print("Database connection closed.")