Adding entries into related SQLite 3 database tables in Python

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
        conn = sqlite3.connect(db_file)
        return conn
    except Error as 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):
        c = conn.cursor()
    except Error as 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

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):
    valuesStr ="VALUES("
    for i in range(0,len(headers)):
        if i<len(headers)-1:
    sql = """ INSERT INTO """+str(table)+headerStr+"""
    cur = conn.cursor()
    cur.execute(sql, entry)

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
# add entries to student table
entries = [(1,"Linnart","ETH","MSc"),

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.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *