Create Table - Python sqlite3

Python - Create Table in sqlite3 Database

The CREATE TABLE statement in SQLite allows you to define a table to store data in a structured format. Using Python's sqlite3 library, you can programmatically create tables in a database with ease.

In this tutorial, we will learn how to create tables using Python sqlite3, including scenarios for handling table creation errors and additional use cases.

Steps to Create Table in sqlite3 Database

Follow these steps to create a table in an SQLite database:

  1. Import sqlite3: Import the sqlite3 library in your Python script to work with SQLite databases.
  2. Establish a connection: Use sqlite3.connect() to create or connect to a database. This returns a connection object.
  3. Create a cursor object: Call the cursor() method on the connection object to create a cursor for executing SQL queries.
  4. Execute the CREATE TABLE query: Use the execute() method of the cursor object to run the CREATE TABLE SQL statement.
  5. Commit changes: Save changes to the database using commit() on the connection object.
  6. Close the connection: Always close the connection using close() to release resources.

Examples

1. Create a Table in sqlite3 Database

In this example, we will create a database named mysqlite.db and a table named students with columns rollno, name, and class.

Python Program

import sqlite3

# Step 1: Establish a connection to the database
conn = sqlite3.connect('mysqlite.db')

# Step 2: Create a cursor object
c = conn.cursor()

# Step 3: Execute the CREATE TABLE query
c.execute('''CREATE TABLE students
             (rollno INTEGER PRIMARY KEY, name TEXT, class TEXT)''')

# Step 4: Commit the changes
conn.commit()

# Step 5: Close the connection
conn.close()

print("Table 'students' created successfully!")

Explanation:

  1. sqlite3.connect('mysqlite.db'): Opens or creates the database file mysqlite.db.
  2. cursor(): Creates a cursor object to execute SQL statements.
  3. CREATE TABLE students: Defines a table with columns: rollno (INTEGER), name (TEXT), and class (TEXT).
  4. conn.commit(): Saves the table structure to the database.
  5. conn.close(): Closes the connection to release resources.

2. Create a Table Only if It Does Not Exist

Use the IF NOT EXISTS clause to prevent errors when attempting to create a table that already exists.

Python Program

import sqlite3

# Connect to the database
conn = sqlite3.connect('mysqlite.db')

# Create a cursor object
c = conn.cursor()

# Execute CREATE TABLE query with IF NOT EXISTS
c.execute('''CREATE TABLE IF NOT EXISTS students
             (rollno INTEGER PRIMARY KEY, name TEXT, class TEXT)''')

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

print("Table 'students' created or already exists!")

Explanation:

  1. The IF NOT EXISTS clause ensures that the table is only created if it doesn't already exist.

3. Create a Table with Unique Constraints

You can enforce unique constraints on columns to prevent duplicate entries.

Python Program

import sqlite3

# Connect to the database
conn = sqlite3.connect('mysqlite.db')

# Create a cursor object
c = conn.cursor()

# Create a table with a UNIQUE constraint
c.execute('''CREATE TABLE IF NOT EXISTS courses
             (course_id INTEGER PRIMARY KEY, 
              course_name TEXT UNIQUE, 
              instructor TEXT)''')

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

print("Table 'courses' created successfully!")

Explanation:

  1. UNIQUE constraint on course_name ensures no two courses have the same name.

4. Create Multiple Tables in One Script

Create multiple tables in the same database using separate CREATE TABLE statements.

Python Program

import sqlite3

# Connect to the database
conn = sqlite3.connect('school.db')

# Create a cursor object
c = conn.cursor()

# Create multiple tables
c.execute('''CREATE TABLE IF NOT EXISTS teachers
             (teacher_id INTEGER PRIMARY KEY, name TEXT, subject TEXT)''')
c.execute('''CREATE TABLE IF NOT EXISTS classes
             (class_id INTEGER PRIMARY KEY, grade TEXT, section TEXT)''')

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

print("Tables 'teachers' and 'classes' created successfully!")

Explanation:

  1. You can execute multiple CREATE TABLE statements to define more than one table in a single script.

Summary

In this tutorial, we covered how to create tables in an SQLite database using Python. We explored creating basic tables, using the IF NOT EXISTS clause, adding constraints, and creating multiple tables. These techniques help you design and implement structured database schemas efficiently in your Python applications.

Python Libraries