In a previous post on SQLite 3 and Python I added two related tables to a SQLite 3 database, usign Pyhton. The tables are called universitiesTBxx and studentsTBxx. Both tables are stored in a database called “newDatabase.db”. Students are enlisted at a university. Information related to the respective university is contained by the universitiesTBxx table.

In this post I will implement a basic SQL query for retrieving data from the SQLite 3 database. First, lets repeat the implementation of a relevant function for creating a connection to a database:

# 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

Since we know the database name we can now connect to it using above function:

# obtaining a reference handler for connecting with the database
dbConnection = create_connection("newDatabase.db")

We now have a reference to the connection to the relevant database. Let us implement a SELECT * command for the university table, using Python and the sqlite3 module:

cursorObj = dbConnection.cursor()
cursorObj.execute("SELECT * FROM universitiesTBxx")
rowList = cursorObj.fetchall()
for row in rowList:
    print(row)

('MIT', 'USA')
('ETH', 'CH')
('DTU', 'DK')

We could e.g. also try a SELECT * FROM tableName WHERE condition statement. E.g. I only want students to be returned that studied at ETH Zürich (Swiss Federal Institute of Technology):

cursorObj.execute("SELECT * FROM studentsTBxx WHERE university = 'ETH'")
rowList = cursorObj.fetchall()
for row in rowList:
    print(row)

(1, 'Linnart', 'ETH', 'MSc')

We could also implement a function that generically allows for execution of a select statement from a parametrized table name, using parametric filters.

Linnart Felkl

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