In this post I will demonstrate how you can create SQL databases powered by SQLite as a database engine in Python. I will be using the sqlite3 engine. The SQLite driver is installed by default through the Python installation itself.

Below code snippet is taken directly from https://www.sqlitetutorial.net/sqlite-python/create-tables/. It implements functions for creating a database and obtaining a connection to it. The second function can be used for creating a table in the database.

# import sqlite module
import sqlite3

# this function creates a database connection to a database file
# params:  database file name
# returns: connection to database
def create_connection(db_file):
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)
    return conn

# this function creates a table in a database
# params:  reference handler to database connection, sql statement for table creation
# returns: None
def create_table(conn, create_table_sql):
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)

I will use above functions to create a database, access its connection and create a table in it. I start by creating the database and accessing its connection:

# creating a database and accessing a connection to it
dbConnection = create_connection("newDatabase.db")

I will now access the connection to the database and create a new table in it:

# create table in database
materialTable = """ CREATE TABLE IF NOT EXISTS material (
                                id integer PRIMARY KEY,
                                description text NOT NULL,
                                category text NOT NULL,
                                price float); """
if dbConnection is not None:
    # create a table
    create_table(dbConnection, materialTable)

Now I can start writing entries into the database. For this I will define another function (taken from the source specifed at the beginning of this post):

# this function adds an entry to the material table in the database
# params: database connection, sql entry
# returns: None
def create_entryMaterialTable(conn, entry):
    sql = ''' INSERT INTO material(description,category,price)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, entry)
    conn.commit()

I now use above function to add a single material to the material table in the database:

materialEntry = ("rear lamp","lighting products",649.99)
create_entryMaterialTable(dbConnection,materialEntry)

In upcoming posts I will demonstrate how you can update entries in the sql database via Python. Also, I will introduce all important sql commands for conducting a successful data query.