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.
Data scientist focusing on simulation, optimization and modeling in R, SQL, VBA and Python