Python SQLite - Create Table

---

Using the SQLite CREATE TABLE statement you can create a table in a database.

Syntax

Following is the syntax to create a table in SQLite database −

CREATE TABLE database_name.table_name(
   column1 datatype PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

Example

Following SQLite query/statement creates a table with name CRICKETERS in SQLite database −

sqlite> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqlite>

Let us create one more table OdiStats describing the One-day cricket statistics of each player in CRICKETERS table.

sqlite> CREATE TABLE ODIStats (
   First_Name VARCHAR(255),
   Matches INT,
   Runs INT,
   AVG FLOAT,
   Centuries INT,
   HalfCenturies INT 
);
sqlite>

You can get the list of tables in a database in SQLite database using the .tables command. After creating a table, if you can verify the list of tables you can observe the newly created table in it as −

sqlite> . tables
CRICKETERS ODIStats
sqlite>

Creating a Table Using Python

The Cursor object contains all the methods to execute quires and fetch data etc. The cursor method of the connection class returns a cursor object.

Therefore, to create a table in SQLite database using python −

• Establish connection with a database using the connect() method.

• Create a cursor object by invoking the cursor() method on the above created connection object.

• Now execute the CREATE TABLE statement using the execute() method of the Cursor class.

Example

Following Python program creates a table named Employee in SQLite3 −

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
#Creating table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)
print("Table created successfully........")

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Output

Table created successfully........