How to Create Table in SQLite Using Python: Simple Guide

To create a table in SQLite using Python, use the sqlite3 module to connect to a database, then execute a CREATE TABLE SQL command with cursor.execute(). Finally, commit the changes with connection.commit() and close the connection.

Syntax

Here is the basic syntax to create a table in SQLite using Python:

• sqlite3.connect(): Opens or creates a database file.
• connection.cursor(): Creates a cursor to execute SQL commands.
• cursor.execute(): Runs the SQL CREATE TABLE command.
• connection.commit(): Saves the changes to the database.
• connection.close(): Closes the database connection.

python

import sqlite3

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

cursor.execute('''
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype
)
''')

connection.commit()
connection.close()

Example

This example creates a table named users with columns for id, name, and age. It shows how to connect, create the table, and close the connection.

python

import sqlite3

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

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

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

Common Pitfalls

Common mistakes when creating tables in SQLite with Python include:

• Not committing changes with connection.commit(), so the table is not saved.
• Forgetting to close the connection, which can lock the database.
• Using incorrect SQL syntax inside cursor.execute().
• Trying to create a table that already exists without using IF NOT EXISTS.

Example of safer table creation:

python

import sqlite3

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

# Use IF NOT EXISTS to avoid error if table exists
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
)
''')

conn.commit()
conn.close()

Quick Reference

Step	Description	Example Code
1	Connect to database	conn = sqlite3.connect('file.db')
2	Create cursor	cursor = conn.cursor()
3	Write CREATE TABLE SQL	CREATE TABLE IF NOT EXISTS table_name (id INTEGER PRIMARY KEY, ...)
4	Execute SQL command	cursor.execute(sql_command)
5	Commit changes	conn.commit()
6	Close connection	conn.close()

Key Takeaways

Use sqlite3 module to connect and execute SQL commands in Python.

Always commit changes with connection.commit() to save the new table.

Close the database connection to avoid locking issues.

Use IF NOT EXISTS in CREATE TABLE to prevent errors if table exists.

Write correct SQL syntax inside cursor.execute() for table creation.