In a previous post I already demonstarted how one can connect to a SQLite 3 database in Python and how one can create tables in the connected database. I also demonstrate how one can write data into a table created in a SQLite 3 database.

In this post I will create two tables in a database called newDatabase.db and I will create two tables in it. One table is for students, another one is for universities.

I will populate the tables with data from my Python script, adding both students and universities. I will first populat the university table and then, afterwards, I will populate the students table.

Below is the code creating a connection to the existing database and adding the two tables to it.

# import sqlite3 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)
        
# creating a database and accessing a connection to it
dbConnection = create_connection("newDatabase.db")

# prepare table creation SQL command in database, for UNIVERSITIES
universitiesTable = """ CREATE TABLE IF NOT EXISTS universitiesTBxx (
                                    name text NOT NULL PRIMARY KEY,
                                    country text NOT NULL
                                );"""

# prepare table creation SQL command in database, for STUDENTS
studentsTable = """ CREATE TABLE IF NOT EXISTS studentsTBxx (
                                        id integer PRIMARY KEY,
                                        name text NOT NULL,
                                        university text,
                                        degree text,
                                        FOREIGN KEY (university) REFERENCES universitiesTBx (name)
                                    ); """

# calling create_table to create the tables in the sqlite3 database
create_table(dbConnection,universitiesTable)
create_table(dbConnection,studentsTable)

I now specify a function which can be generically used for adding data:

# this function adds an entry into a specified table, within the database
# params: name of table, database connection, sql entry, list of column header names
# returns: None
def create_entry(table,conn,entry,headers):
    headerStr="("
    valuesStr ="VALUES("
    for i in range(0,len(headers)):
        if i<len(headers)-1:
            headerStr=headerStr+headers[i]+","
            valuesStr=valuesStr+"?,"
        else:
            headerStr=headerStr+headers[i]+")"
            valuesStr=valuesStr+"?"+")"
    sql = """ INSERT INTO """+str(table)+headerStr+"""
              """+valuesStr
    cur = conn.cursor()
    cur.execute(sql, entry)
    conn.commit()

Having created the tables already, I will now populate them with data. I start by adding universities. Afterwards, I add some students.

# add entries to university in table database
entries = [("MIT","USA"), # MIT is first entry
           ("ETH","CH"),# ETH in Switzerland is second entry
           ("DTU","DK")]  # DTU in Denmark is final entry
# add entries to university table
create_entry("universitiesTBxx",dbConnection,(entries[0][0],entries[0][1]),headers=["name","country"])
create_entry("universitiesTBxx",dbConnection,(entries[1][0],entries[1][1]),headers=["name","country"])
create_entry("universitiesTBxx",dbConnection,(entries[2][0],entries[2][1]),headers=["name","country"])
# add entries to student table
entries = [(1,"Linnart","ETH","MSc"),
          (2,"RandomGuy","MIT","BSc"),
          (3,"RandomGirl","DTU","PhD")]
create_entry("studentsTBxx",dbConnection,(entries[0][0],entries[0][1],entries[0][2],entries[0][3]),headers=["id","name","university","degree"])
create_entry("studentsTBxx",dbConnection,(entries[1][0],entries[1][1],entries[1][2],entries[1][3]),headers=["id","name","university","degree"])
create_entry("studentsTBxx",dbConnection,(entries[2][0],entries[2][1],entries[2][2],entries[2][3]),headers=["id","name","university","degree"])

In upcoming posts I will demonstrate how you can update entries in tables and how you can implement SQL commands for implementing a data query in a SQLite 3 database – using the Python sqlite3 module.

Linnart Felkl

Data scientist focusing on simulation, optimization and modeling in R, SQL, VBA and Python