Python Creating SQLite Database and Table

In this article, we’re going to learn how to create a new SQLite 3 database in Python program. And then execute a SQL script to create a new data table on that new database.

Step 1 - Creating new SQLite database

The Python code below to create a new SQLite 3 database file and saved it to D:\ToriCode\db folder named contacts.db

import sqlite3 
from sqlite3 import Error

connection = None
try:
    connection = sqlite3.connect('D:\ToriCode\db\contacts.db')
except Error as e:
    print(e)
finally:
    if connection:
        print('Connect to database successfully.')

Step 2 - Creating new table in SQLite database

The Python code snippet below to continue using the connection object above to execute creating table SQL script.

if connection:
    sql_create_table = """
        CREATE TABLE IF NOT EXISTS contacts ( 
            id integer PRIMARY KEY,
            first_name text NOT NULL,
            last_name text NOT NULL
        ); 
    """
    try:
        cursor = connection.cursor()
        cursor.execute(sql_create_table)
    except Error as e:
        print(e)
    finally: 
        print('Execute SQL script successfully.')
    connection.close()

The complete Python program code:

import sqlite3 
from sqlite3 import Error

connection = None
try:
    connection = sqlite3.connect('D:\ToriCode\db\contacts.db')
except Error as e:
    print(e)
finally:
    if connection:
        print('Connect to database successfully.')

if connection:
    sql_create_table = """
        CREATE TABLE IF NOT EXISTS contacts ( 
            id integer PRIMARY KEY,
            first_name text NOT NULL,
            last_name text NOT NULL
        ); 
    """
    try:
        cursor = connection.cursor()
        cursor.execute(sql_create_table)
    except Error as e:
        print(e)
    finally: 
        print('Execute SQL script successfully.')
    connection.close()

Output:

Connect to database successfully.
Execute SQL script successfully.

Open the database on the DbVisualizer application and we will get the result as below.

Python Creating SQLite Database and Table

Python Creating SQLite Database and Table

Step 1 - Creating new SQLite database

Step 2 - Creating new table in SQLite database

Tags: