SQLite is a lightweight, embedded database engine that stores data in a single file. It’s perfect for small to medium-sized projects, local apps, or quick development environments. Unlike other databases like MySQL or PostgreSQL, creating a database in SQLite is incredibly simple and doesn't require a server.

In this article, you'll learn how to create an SQLite database using Python step by step, with complete code examples, tips, and common pitfalls.

✅ Why Use SQLite?

• No setup required — Built into Python with the sqlite3 module

• Zero configuration — No server or external tools

• File-based — Just one .db file per database

• Great for local development and testing

Step 1: Import the SQLite Module

Python comes with SQLite support via the sqlite3 module (included in the standard library).

import sqlite3

Step 2: Create or Connect to a Database File

To create a new SQLite database, you simply connect to a file. If the file does not exist, SQLite will create it automatically.

conn = sqlite3.connect('example.db')

• 'example.db' is the name of the database file.

• If the file doesn’t exist, it will be created.

• If the file exists, it will connect to it.

You can also create an in-memory database (useful for testing) using:

conn = sqlite3.connect(':memory:')

Step 3: Create a Cursor Object

A cursor allows you to execute SQL statements.

cursor = conn.cursor()

Step 4: Create a Table

To verify your database is working, let’s create a Users table.

cursor.execute('''
CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER
)
''')

Then commit the changes:

conn.commit()

✅ Step 5: Close the Connection

Always close your connection when done to free up resources.

conn.close()

Full Working Example: Create an SQLite Database

import sqlite3

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

# Create cursor
cursor = conn.cursor()

# Create table
cursor.execute('''
CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER
)
''')

# Commit changes
conn.commit()

# Close connection
conn.close()

print("Database and table created successfully.")

After running this code, a file named example.db will appear in your project directory.

Tips for Creating SQLite Databases with Python

Example: Using a context manager

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute('CREATE TABLE IF NOT EXISTS Test (id INTEGER PRIMARY KEY)')

⚠️ Common Pitfalls

Best Practices

• Keep your schema creation in a separate setup script.

• Use try/except blocks for better error handling.

• Use consistent naming conventions (snake_case for tables and fields).

• Keep .db files out of version control (e.g., add to .gitignore).

Conclusion

Creating an SQLite database using Python is fast and easy. Just use sqlite3.connect() and you're up and running. You can immediately start building tables and inserting data — no servers, no configuration. SQLite is a perfect choice for simple apps, testing, or any situation where you want zero overhead.