Python sqlite3 – Create Table

Python – Create Table in sqlite3 Database

You can create one or more tables in sqlite3 database.

In this tutorial, we will learn how to create a table in sqlite3 database programmatically in Python.

Steps to Create Table in sqlite3 Database

To create a table using Python sqlite3, follow these steps

1. Create a connection object to the sqlite database.
2. Create a cursor to the connection.
3. Create table using the sqlite3.execute() method with the CREATE query passed to the method.

Examples

1. Create database table with sqlite3

In this example, we will create a sqlite3 database named mysqlite.db and create a table named students inside the database.

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE students
             (rollno real, name text, class real)''')
			
# Commit the changes to db			
conn.commit()

# Close the connection
conn.close()

When you run this program, a table students should be created successfully inside mysqlite.db.

If you run this program the second time, you would get the following error.

Traceback (most recent call last):
  File "example.py", line 8, in <module>
    (rollno real, name text, class real)''')
sqlite3.OperationalError: table students already exists

If you would like to not mind if the table already exists or not, you can refer the following example, where we will create the table only if it does not exist.

2. Create table only if it does not exist

In the Query, we can define to create the table only if it does not exist already. You may use IF NOT EXISTS before the table name in the query to create the table only if it does not exist.

In this example, we will try creating a sqlite3 database named mysqlite.db and create a table named students (which is already created in the previous example) inside the database.

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE IF NOT EXISTS students
             (rollno real, name text, class real)''')
			
# Commit the changes to db			
conn.commit()

# Close the connection
conn.close()

Summary

In this tutorial of Python Examples, we learned how to create a table in sqlite3 database. Also, we have seen the scenario of creating a table only when it does not exist in the database.