*********** 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:* .. code-block:: python 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:* .. code-block:: python # create a cursor to access the database cursor = connection.cursor() *Code snippet 3:* .. code-block:: python # 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:* .. code-block:: python 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:* .. code-block:: python # 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: .. image:: images/211.png :width: 400 -------------------------------------------------------------------------------------------------------------------- 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:* .. code-block:: python 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:* .. code-block:: python 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:* .. code-block:: python 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: .. code-block:: pycon Fichte --------------------------------------------------------------------------------------------------------------------------- Excercises ========== The database used in this session should consist of different "tables" according to the following scheme: .. image:: images/206.png :width: 400 .. image:: images/207.png :width: 300 .. image:: images/208.png :width: 250 .. image:: images/209.png :width: 250 .. image:: images/210.png :width: 500 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** .. code-block:: python fraesen = r[0] bohren = r[1] drehen = r[2] sanding = r[3] eloxy = r[4] .. code-block:: python 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** .. code-block:: python 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** .. code-block:: python print(SingleOrder, "braucht", time, "min pro Teil") TotalRunTime = TotalRunTime + NumberToProduce*time print(SingleOrder, "braucht", TotalRunTime, "min insgesamt") CompleteTime = CompleteTime+TotalRunTime .. code-block:: python print("Bestellung",PiecesToOrder) print("braucht: {0:6.2f} hrs".format(CompleteTime/60.0))