How to Alter an SQLite Table using Python?

Altering an SQLite table is a common task when working with databases and can be easily done using Python. SQLite is a lightweight, file-based database that doesn't require a separate server, making it perfect for small to medium applications.

In this article, we'll explore how to alter an SQLite table using Python's built-in sqlite3 module. We'll cover adding columns, modifying existing tables, and provide complete working examples.

What is SQLite?

SQLite is an open-source, serverless database engine that stores data in local files. Python's sqlite3 module comes pre-installed and provides a simple interface for working with SQLite databases directly from Python code.

Importing the Module

import sqlite3

Basic Steps to Alter SQLite Table

Here are the essential steps to alter an SQLite table using Python ?

1. Connect to Database

import sqlite3

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

2. Create Cursor Object

# Create cursor for executing SQL commands
cursor = conn.cursor()

3. Execute ALTER TABLE Statement

# Add a new column to existing table
cursor.execute("ALTER TABLE table_name ADD COLUMN column_name datatype")

4. Commit and Close

# Commit changes and close connection
conn.commit()
conn.close()

Complete Example: Adding a Column

Let's create a complete example that demonstrates altering an SQLite table ?

import sqlite3

# Connect to database
conn = sqlite3.connect('customers.db')
cursor = conn.cursor()

# Create initial table
cursor.execute('''CREATE TABLE IF NOT EXISTS customers 
                 (first_name TEXT, last_name TEXT, email TEXT)''')

# Insert sample data
cursor.execute("INSERT INTO customers VALUES ('John', 'Doe', 'john@email.com')")
cursor.execute("INSERT INTO customers VALUES ('Jane', 'Smith', 'jane@email.com')")

# Show table before alteration
print("Table before ALTER:")
cursor.execute("SELECT * FROM customers")
for row in cursor.fetchall():
    print(row)

# Add new column
cursor.execute("ALTER TABLE customers ADD COLUMN phone TEXT")

# Show table after alteration
print("\nTable after ALTER:")
cursor.execute("SELECT * FROM customers")
for row in cursor.fetchall():
    print(row)

# Commit and close
conn.commit()
conn.close()

Table before ALTER:
('John', 'Doe', 'john@email.com')
('Jane', 'Smith', 'jane@email.com')

Table after ALTER:
('John', 'Doe', 'john@email.com', None)
('Jane', 'Smith', 'jane@email.com', None)

Common ALTER TABLE Operations

Adding a Column with Default Value

import sqlite3

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

# Add column with default value
cursor.execute("ALTER TABLE customers ADD COLUMN status TEXT DEFAULT 'active'")

conn.commit()
conn.close()

Renaming a Table

import sqlite3

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

# Rename table
cursor.execute("ALTER TABLE customers RENAME TO clients")

conn.commit()
conn.close()

Important Limitations

SQLite has some limitations with ALTER TABLE ?

• Cannot drop columns directly (SQLite 3.35.0+ supports DROP COLUMN)
• Cannot modify column data types
• Cannot add constraints to existing columns

Error Handling

import sqlite3

try:
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    
    cursor.execute("ALTER TABLE customers ADD COLUMN age INTEGER")
    conn.commit()
    print("Column added successfully!")
    
except sqlite3.Error as e:
    print(f"Database error: {e}")
    
finally:
    if conn:
        conn.close()

Conclusion

Altering SQLite tables using Python is straightforward with the sqlite3 module. Use ALTER TABLE ADD COLUMN to add new columns and ALTER TABLE RENAME TO to rename tables. Always remember to commit changes and handle errors appropriately.