Lecture 17 SQL Application Persistence Design Patterns SQLite

Lecture 17 SQL – Application Persistence Design Patterns

SQLite • A relational database management system • Not a client–server database engine. • It is embedded into the program itself! • The database is stored in a file • SQLite is widely used as embedded database software for local storage in application software such as web browsers. • It is arguably the most widely deployed database engine, • as it is used today by several widespread browsers! • For Python we will see sqlite 3 library

SQLite: connecting to database 1. import sqlite 3 2. 3. # connect to an existing database 4. # if the file does not exist, 5. # it will be created! 6. connection = sqlite 3. connect('mydb. db') 7. # object received is used to execute 8. # both commands and queries on the database

SQLite: creating a table - command 1. connection. execute(""" 2. CREATE TABLE students ( 3. id INT PRIMARY KEY, 4. name TEXT NOT NULL 5. ) """

SQLite: inserting data - command 1. id = 0 2. name = ‘mark’ 3. connection. execute(""" 4. INSERT INTO students (id, name) VALUES (? , ? ) 5. """, [id, name])

SQLite: retrieving data - query 1. # executing a statement that returns a value 2. cursor = connection. cursor() 3. id = 0 4. cursor. execute(""" 5. SELECT * FROM students 6. WHERE id = ? 7. """, id) 8. 9. #the result is stored inside the curser we can retrieve it 10. # as a list of tuples using: 11. all = cursor. fetchall() 12. 13. #or if we know that there is a single value 14. val = cursor. fetchone() 15. # connections must be closed when you done with them 16. connection. commit() # commit any changes not yet written to the database 17. connection. close() # close the connection

Application Persistence Patterns • There are many ways to construct an application that needs to store and retrieve data. • We will create a simple application that uses SQLite. • We will then examine it and show different design patterns and best practices.

'Vicious' - The Assignment Tester • We want to create an assignment checker for python assignments in the SPL course. • This application will include a method that checks assignments and store their grades in the database. • We will assume: • Students has a unique id • Assignments are numbered (e. g. , assignment 1, 2, . . ) • Each assignment contains a single. py file with a method run_assignment() that accept no arguments and returns a string. • The submitted file name will be of the form <submitter student id>. py

Our data base structure • Our program will use an sqlite database with the following tables: • students which includes the id of the student and its name • assignments which include the assignment number and a string representing the expected output of the run_assignment method of the corresponding assignment. • grades which includes the grade of each student on a specific assignment. • We will now examine a simple python script that provides the behavior defined above.

• The atexit module defines a single function to register cleanup functions. Functions thus registered are automatically executed upon normal interpreter termination.

• we have 3 students in our class: Alice, Bob and Chris, with the corresponding ids: 1111, 2222 and 3333. • assume that they submitted the following files: • We run vicious. py:

Remarks • The code works. • But the logic of the application is coupled with the logic of the database: • If we will change our database (e. g. , some table name) we will have to modify our application logic in multiple functions (and maybe multiple files). • If we will change our database type (e. g. , from sqlite 3 to sqlite 4) we will have to go over all our code and adapt it • If we add more modules that want to use our database, they will have to import the 'vicious' module, even if they do not need to 'grade' assignments • Database queries are a relatively long operation. Many times, we want to cache some of our queried so that we will not have to re-query the database if we need it again. • We want to decouple the persistence related code from our application.

Data Persistence Layer • We will divide our code into: • Persistence Layer: a group of files which is used to communicate between the application and DB. (persistence. py) • The rest of the application logic - which will use the persistence layer to store and query data. (vicious. py)

Problems • In vicious. py we can spot two "problems“: • In the first line of the grade method we get the expected_output of an assignment from the persistence layer. • What if we add more fields to the assignment, should we create a new method to get each field? what if we want all the fields? • Inside print_grades() we can see that we assume that get_all_grades returns a list of tuples: (student name, assignment number, grade). If we add fields to the grades table this may break our assumption. • What if we need all the grades but need the student id and not name? - should we create another method get_all_grades 2?

Data Transfer Object (DTO) Data Access Object (DAO) • DTOs: objects that are passed to and from the persistence layer. • When passed from the persistence layer to the application logic, they contains the data retrieved from the database. • When passed from the application logic to the persistence layer, they contains the data that should be written to the database. • In most cases, these objects represent a single table (Student, Grade, Assignment). • DAOs: contain methods for retrieving and storing DTOs. • Usually, each DAO represents a DTO. • Not sufficient (only DTO-DAO relation). Where is create_tables()? Join tables? Repository – handles multiple DTOs.

Automating things: ORMs and generic DAOs • Some code repetition in DAOs: • Insert() methods generally look the same (on different tables). So do find() methods. • DAOs – convert database records to objects and vice versa. • This conversion is called Object Rational Mapping (ORM). • We wish to a generic ORM that converts data to any DTO. • Then we can use it to create a generic DAO class.

Generic ORM • We need to make some assumptions about our DTO classes: • Each DTO class represents a single table • The different DTO classes are obeying a common naming conventions: A DTO class named Foo will represents a table named foos. • The different DTO classes have a constructor that accepts all their fields. The name of the constructor arguments is the same as the name of the fields. • The name of the fields of each DTO class is the same as the column names in the database.

Generic ORM • If we examine the code of a DTO class that follows these restrictions we can easily infer how the corresponding database table looks like. • Python contains modules to query the structure of a class. • inspect. getargsspec(): returns the names of the arguments of a function or a constructor. • Following our naming restrictions, our generic ORM can now query the arguments’ names and convert database data to DTO.

Generic ORM • Our ORM also needs the structure of the database data. • This data comes from a cursor object. • The cursor object has a function named description which returns the column names of the last query. • returns a list which contains a 7 -tuple for each column. The first item in this tuple is the column name.

Generic ORM • Our generic ORM method will receive a cursor and a DTO type. • It will examine the constructor arguments of the DTO class and the column names inside the cursor. • It will then create a mapping array col_mapping: • For each constructor argument in position i, col_mapping[i] is the index of the corresponding column inside the database. • If C’tor accepts (id, name) and table columns is (id, name) then col_mapping is [1, 2]. You can however call Student(name = “Eran”, id = 5). • Finally it will loop over the data inside the cursor and use the col_mapping to construct one DTO object per data row.

Remarks • Join() of a String: • Our code contains a generic orm() function and DAO class. • Our DAO class is not completed. Doesn’t implement find()

Old Find • Our generic find() will receive a dictionary with column/values entries. • We can then construct a select statement that includes a WHERE clause which represents the keys in the dictionary.

• Python has a special syntax:

Find() in our DAO

Same code…

Previous Repository

Repository – generic DAO instead of specific c’tors

Our assignment checker

Our old assignment checker