Python SQLite Create Table

In  this article like we will use the SQLite CREATE TABLE statement you can create a table in a database.

Syntax

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

Creating a Table

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

So to the steps to create a table in SQLite database using python are as follows −

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

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

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

Example

In this first example we create a database with a simple table to store country data

Basic example

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

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

#Creating table
sql ='''CREATE TABLE COUNTRY(
   NAME CHAR(30) NOT NULL,
   CAPITAL CHAR(30),
   DOMAIN CHAR(10),
   POPULATION BIGINT
)'''
cursor.execute(sql)
print("Table created successfully........")

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

#Closing the connection
conn.close()

When run you should see this

>>> %Run sqlitecreatedb.py
Table created successfully……..

Now there is a problem with this, if you were to run this example again you would see the following

>>> %Run sqlitecreatedb.py
Traceback (most recent call last):
File “C:\Python38-32\sqlitecreatedb.py”, line 15, in
cursor.execute(sql)
sqlite3.OperationalError: table COUNTRY already exists

Updated example

In this example we will create the table only if it does not exist.

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

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

#Creating table
sql ='''CREATE TABLE IF NOT EXISTS COUNTRY(
   NAME CHAR(30) NOT NULL,
   CAPITAL CHAR(30),
   DOMAIN CHAR(10),
   POPULATION BIGINT
)'''
cursor.execute(sql)
print("Table created successfully........")

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

#Closing the connection
conn.close()