Python SQLite - Create Table

In this article, we will discuss how can we create tables in the SQLite database from the Python program using the sqlite3 module. 

Syntax

CREATE TABLE table_name (

column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype,
...
columnN datatype

);

• table_name: name of the table you want to create.
• column1, column2, ..., columnN: columns you want to include in your table.
• datatype: type of data that will be stored in each column (e.g., INTEGER, TEXT, REAL, etc.).
• PRIMARY KEY: column (or set of columns) that uniquely identifies each row in the table.

Steps to Create a Table in SQLite using Python

1. Import the SQLite3 Module: Use import sqlite3 to access SQLite functionality in Python.
2. Establish Connection: Use the connect() method to establish a connection to your SQLite database.
3. Create a Cursor Object: The cursor() method creates a cursor object that allows you to execute SQL commands.
4. Execute SQL Query: The execute() method of the cursor object is used to run the SQL CREATE TABLE command.
5. Close the Connection: After executing the required commands, it is essential to close the connection to the database.

Implementation:

import sqlite3

# Connect to the SQLite database (or create it if it doesn't exist)
connection_obj = sqlite3.connect('geek.db')

# Create a cursor object to interact with the database
cursor_obj = connection_obj.cursor()

# Drop the GEEK table if it already exists (for clean setup)
cursor_obj.execute("DROP TABLE IF EXISTS GEEK")

# SQL query to create the table
table_creation_query = """
    CREATE TABLE GEEK (
        Email VARCHAR(255) NOT NULL,
        First_Name CHAR(25) NOT NULL,
        Last_Name CHAR(25),
        Score INT
    );
"""

# Execute the table creation query
cursor_obj.execute(table_creation_query)

# Confirm that the table has been created
print("Table is Ready")

# Close the connection to the database
connection_obj.close()

Output:

Explanation:

• sqlite3.connect('geek.db') connects to the geek.db database. If it doesn't exist, it's created.
• connection_obj.cursor() creates a cursor object to interact with the database.
• cursor_obj.execute("DROP TABLE IF EXISTS GEEK") removes the GEEK table if it already exists, ensuring a clean setup.
• The SQL query CREATE TABLE GEEK defines the structure of the table with columns Email, First_Name, Last_Name, and Score and their respective data types.
• cursor_obj.execute(table_creation_query) runs the SQL command to create the table.
• connection_obj.close() closes the connection to the database after the operation is complete.