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:

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.