Session 05

SQL Databases and Python

SQL is a database language for defining data structures in relational databases as well as for editing (inserting, changing, deleting) and querying databases based on it. SQL databases can be created quite easily with Python and managed with suitable database management tools.

We will work mainly with SQLite in Python. It is an easy to use database engine and features self-contained, serverless and zero-configurational application. SQLite is very fast and lightweight, the entire database is stored in a single file and it is used in a lot of applications as internal data storage. The Python Standard Library includes a module called sqlite3 intended for working with this database.

In this session, you can work with PyCharm or in a jupyter notebook. In order to see and verify the database results, you can download the DB Browser from https://sqlitebrowser.org/dl/

A German PDF version of this tutorial can be found under GIT FH Aachen. Search for it1-unterlagen and look into the subfolder Praktikum 5.

Introduction to SQLite, Examples

SQL databases can be created quite easily with Python and administered with appropriate DBMS (database management system). In the following session a database is created which describes different machines from a production process.

Creation of databases

To use the SQLite3 module we need to import it to our python script first:

Code snippet 1:

import sqlite3
#connection = sqlite3.connect(":memory:")
connection = sqlite3.connect("Manufacture.db")

We use the function sqlite3.connect to connect to the database. We can use the argument “:memory” to create a temporary database in the RAM or we can create or open a database on the hard disk.

Create an empty Python script and name it how you like. Import the sqlite3 library. Create a database named Studenten.db. In order to make any operation with the database, you must create a cursor object. You use the cursor to modify the database by entering SQL commands, for example to create the fields for the tables (see Code snippet 3):

Code snippet 2:

# create a cursor to access the database
cursor = connection.cursor()

Code snippet 3:

# definition of fields for the data
sql_command = """CREATE TABLE student (matrikel_nr INTEGER PRIMARY KEY, name TEXT);"""

# execute command for the database
cursor.execute(sql_command)

Here, a table named student is created, which has two columns, matrikel_nr and name. Both elements have their own data type.

We go on with producing data contents. The SQL command INSERT inserts data into an existing table. Every time we insert data, we must use an execution command afterwards to execute the insert command:

Code snippet 4:

sql_command = """INSERT INTO student (matrikel_nr, name) VALUES (26120, "Fichte");"""
cursor.execute(sql_command)

Finally, the database must be updated and closed again. Otherwise the changes will not take effect. Please note that there are no BOOL variables in SQL - for example, INTEGER can be used here, where a 1 or a 0 can be selected as true or false.

Code snippet 5:

# update db
connection.commit()

# close connection to db
connection.close()

Once the database has been successfully filled, you can check or change the results using the DB Browser for SQLite. This should look like this, for example:

_images/211.png

Query of data

To query data from the SQL database, the database must be reopened.

Create a Python script “Abfrage1.py” that returns the first entry of the table MachineData.db. For this, the database is opened with Code snippet 1 and Code snippet 2. SQL commands can be executed quite short with

Code snippet 6:

cursor.execute("SELECT * FROM student")
print("\nErster Datensatz:")
result = cursor.fetchone()
print(result)

With the SQL command sequence SELECT * FROM table all entries of the table MachineData are selected.

fetchone() returns only the first data set fetchmany(n) returns the n sets data fetchall() returns all data sets as an array

To give a single view, all elements of the array must be read out:

Code snippet 7:

cursor.execute("SELECT * FROM student")
print("\nAlle Datensätze:")
result = cursor.fetchall()
for r in result:
    print(r)

The database should then be closed again. Do not forget to generate the SQL command, execute it and commit the changes after each operation.

The SQL queries can also be combined, so that e.g. all students who listen to a certain lecture are given out. Under http://www.sqlitetputorial.net/ all necessarry commands and examples to extract information from an SQLite database can be found.

Now you can extract from the all three tables the “name” of the student listening to the lecture “Grundzüge”.

Code snippet 8:

cursor.execute("SELECT name FROM listen, student, Vorlesungen WHERE "
"student.matrikel_nr = listen.matrikel_nr AND listen.Vorl_nr"
"=Vorlesungen.Vorl_nr AND Vorlesungen.Title= 'Grundzüge'")

What we are actually doing here? First, we are searching a student who is in all tables. We are selecting the “name” column from all tables where the data coincides. Same students with certain matriculate numbers in student table are also available in listen table. These students with certain lecture numbers in listen table are also available in Vorlesungen.

From Vorlesungen table we can see that a student with 5001 matriculate number is actually listening the Grundzüge lecture. All data in the tables are connected to each other.

We get:

Fichte

Excercises

The database used in this session should consist of different “tables” according to the following scheme:

_images/206.png _images/207.png _images/208.png _images/209.png _images/210.png

Create an empty Python script and give it a suitable name. Import the sqlite3 library. Create a database named Manufacture.db.

Create five tables with the corresponding structure in the database. If you want to create further scripts to enter the data into the corresponding table, you should leave the database open, generate a cursor and send the SQL commands.

Exercise 1

Generate a query which contains a list of all milling machines with their machine IDs and the type of the machine TIP: For multiple table entries, a unique assignment can be created with tablename.tableid. Otherwise SQL complains with “ambiguous column name”.

Exercise 2

Generate the total production time for the part Winkel. Then create the production time for Hülse. This does not work in this database with a pure SQL call, i.e. the manufacturing steps must first be extracted from ManuProc. Ater that ProcDescription.Laufzeit must be read accordingly and the runtimes must be added.

TIP: Arrays are accessed in Python e.g. via the index. If r=(1, 1, 0, 0, 0, 0), you can use test=r[0] to extract the first 1 from the array.

Exercise 3

You receive an order database “Bestellung.db”. It is located in it1-unterlagen in the subfolder Praktikum 5. Download it and have a first look at this database with the DB Browser. Generate the total production time for all purchase orders from the database. First extract the parts to be produced and their quantity from the purchase orders. Then you can then reuse the code from task 2 with slight modifications.

TIP: You should work with two open connections and two cursors.

Code segements

The following code snippets could be helpful for the three tasks:

Accessing arrays

fraesen = r[0]
bohren = r[1]
drehen = r[2]
sanding = r[3]
eloxy = r[4]

if sanding==1:
    cursor.execute("select ProcDescription.Laufzeit from ProcDescription where ProcDescription.Beschreibung='Sandstrahlen'")
    result = cursor.fetchone()
    r = result[0]
    time = time + r

Query a designated manufacturing process

formatStr="""SELECT ManProc.Fraesen, ManProc.Bohren, ManProc.Drehen, ManProc.Sandstrahlen, ManProc.Eloxieren from ManProc where ManProc.Piece_ID={0}"""
sql_command=formatStr.format(CurrentPiece_ID

Formatted string for in- and output of SQL commands

print(SingleOrder, "braucht", time, "min pro Teil")
TotalRunTime = TotalRunTime + NumberToProduce*time
print(SingleOrder, "braucht", TotalRunTime, "min insgesamt")
CompleteTime = CompleteTime+TotalRunTime

print("Bestellung",PiecesToOrder)
print("braucht: {0:6.2f} hrs".format(CompleteTime/60.0))