Python SQLite Create Database Guide

SQLite is a lightweight, file-based database. It integrates seamlessly with Python via the sqlite3 module. This guide explains how to create a database from scratch. Whether you're storing user data or logs, this tutorial covers the essentials.

Why Use SQLite with Python?

SQLite requires no server setup. It stores data in a single file. Perfect for small to medium projects. Python's sqlite3 module is built-in, so no extra installations are needed. For setup details, check our installation guide.

Steps to Create a Database

1. Import the sqlite3 module.
2. Use sqlite3.connect() to create or open a database.
3. Create a cursor object with cursor().
4. Execute SQL commands like CREATE TABLE.
5. Commit changes and close the connection.

1. Import the sqlite3 Module

Start by importing the module:

import sqlite3

2. Connect to the Database

Use sqlite3.connect(). If the file doesn't exist, it creates a new one:

# Creates a database file named 'example.db'
conn = sqlite3.connect('example.db')

Tip: Use :memory: for temporary in-memory databases.

3. Create a Cursor Object

The cursor executes SQL commands:

cursor = conn.cursor()

4. Execute SQL Commands

Create a table with CREATE TABLE if it doesn't exist:

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

This example creates a users table with id, name, and age columns.

5. Commit and Close

Save changes and close the connection:

conn.commit()
conn.close()

Full Example Code

Here's a complete script to create a database and table:

import sqlite3

# Connect to the database (or create it)
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER
    )
''')

# Commit and close
conn.commit()
conn.close()
print("Database and table created successfully.")

Output:
Database and table created successfully.

Best Practices

Use Python's with statement for automatic cleanup:

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

Pro Tip: Always include IF NOT EXISTS to avoid errors when tables already exist. Learn more about database configuration in our PRAGMA guide.

Common Errors and Fixes

1. Table Already Exists: Use IF NOT EXISTS in CREATE TABLE statements.

2. File Not Found: Check file paths and permissions when using connect().

3. Syntax Errors: Validate SQL syntax. For parameterized queries, see our paramstyle guide.

Querying the Database

After creating a table, insert and query data:

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

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
conn.commit()

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

Output:
(1, 'Alice', 30)

Advanced Features

Explore PRAGMA for database settings like journal mode. For backups, use iterdump() or backup(). Learn more in our backup guide.

Conclusion

Creating a Python SQLite database is straightforward. Use sqlite3.connect() to start, define tables with CREATE TABLE, and manage connections properly. Practice with examples and explore advanced features like transactions via our isolation level guide.