HCI SQL Databases 4 - Using SQLite in Python

SQL Databases CPDD Computer Education Unit Version: Mar 2018 1 Name: __________________________ ( ) Class: _________ Date: _________ Lesson 4: Using SQLite in Python Instructional Objectives: By the end of this task, you should be able to:  Use a programming language to work with SQL databases  Use sqlite3.connect() to open or create a SQLite file  Use sqlite3.Connection.execute() to run SQL  Use sqlite3.Cursor.fetchone() and sqlite3.Cursor.fetchall() to retrieve database rows  Set sqlite3.Connection.row_factory to sqlite3.Row in order to simplify the reading of values from retrieved database rows  Use sqlite3.Connection.commit() to save changes and sqlite3.Connection.close() to close SQLite files Python and SQLite In the previous lessons, you used DB Browser for SQLite to create SQLite databases and run SQL queries. This is because DB Browser's graphical user interface makes it easy to experiment with SQL and examine the results. However, DB Browser is not an appropriate program to use if we want to customise or restrict how the contents of a database are modified or presented . Suppose we have a SQLite database that stores information about the books in a library and we want to let users search the database. We should not use DB Browser for this purpose as malicious users can also use DB Browser to run harmful SQL (e.g., DROP TABLE). The interface of DB Browser may also be confusing to users who are not familiar with databases or SQLite. Instead, developers typically write custom programs to control how users interact with a database. The program may let the user complete a form or choose from a menu to describe what he/she wants to do. Based on the user's input, the program would then generate the appropriate SQL and run it to produce the intended result. This prevents users from modifying the database in ways that are unexpected to the developer.

SQL Databases CPDD Computer Education Unit Version: Mar 2018 2 In this lesson, we will learn how to write Python programs that can interact with SQLite databases using the built-in sqlite3 module. 1 A public library uses an SQLite database to store information about its books and the year when each book was published. The library wishes to let users specify a year and query for the titles of all books published before that year. Which of the following is NOT a valid reason why DB Browser should be avoided for this purpose? A Users may use DB Browser to insert fake data into the database. B Users may not know how to perform the query using DB Browser. C Users may use DB Browser to perform a query that returns nothing. D Users may use DB Browser to drop tables from the database. Loading an SQLite Database To open or create a SQLite database, import sqlite3 and call sqlite3.connect(). This function accepts a str argument that contains the path and filename of an SQLite database file and returns a connection object. If no path is included, the SQLite file is assumed to be in the same directory as the Python file. Furthermore, if the specified file does not exist, an empty file will be created with the given filename instead. After all operations with the database are complete, the close() method of the connection object should then be called. This ensures that the database file is closed properly but does not save any modifications that have been made to the data. For example, the following Python program tries to load an SQLite database named example.db in the same directory as the program . If such a file does not exist, an empty file named example.db will be created instead: Program 1: load_example.py 1 2 3 4 import sqlite3 connection = sqlite3.connect("library.db") connection.close()

SQL Databases CPDD Computer Education Unit Version: Mar 2018 3 Executing SQL in Python After loading an SQLite file and getting a connection, we can execute SQL by calling the connection object's execute() method with a str containing the SQL we wish to run. If any data is modified, we can also save our changes by calling the connection object's commit() method. For instance, the following Python program creates a new table named Book in a new SQLite file named library.db (remove library.db from the folder first if it is present): Program 2: create_example.py 1 2 3 4 5 6 7 import sqlite3 connection = sqlite3.connect("library.db") connection.execute("CREATE TABLE Book " + "(ID INTEGER PRIMARY KEY, Title TEXT)") connection.commit() connection.close() After running the program, we can open library.db using DB Browser to check that a Book table was indeed created: However, if we try to run the program again, we will get the following error: Traceback (most recent call last): File "create_example.py", line 5, in <module> "(ID INTEGER PRIMARY KEY, Title TEXT)") sqlite3.OperationalError: table Book already exists This demonstrates that calling execute() is just like running regular SQL commands in the "Execute SQL" tab of DB Browser. Any errors caused by running the SQL (such as the error telling us the Book table already exists) are reported as Python exceptions and can be handled as usual. new table is created

SQL Databases CPDD Computer Education Unit Version: Mar 2018 4 Committing Changes and Rolling Back Now, let us try using INSERT to put data into the Book table: Program 3: insert_example_incomplete.py 1 2 3 4 5 6 import sqlite3 connection = sqlite3.connect("library.db") connection.execute("INSERT INTO Book(ID, Title) " + "VALUES(0, 'Example Book')") connection.close() This program runs with no errors. However, if we open library.db using DB Browser, we find that the inserted data is missing: What happened to the data ? It was discarded because we did not call commit() on the connection object . Using INSERT, UPDATE or DELETE with the sqlite3 module implicitly opens a transaction such that modifications to the data are not saved until the commit() method is called: Program 4: insert_example.py 1 2 3 4 5 6 7 import sqlite3 connection = sqlite3.connect("library.db") connection.execute("INSERT INTO Book(ID, Title) " + "VALUES(0, 'Example Book')") connection.commit() connection.close() inserted data is missing!

SQL Databases CPDD Computer Education Unit Version: Mar 2018 5 With a call to commit() added on line 6, the data is inserted and saved correctly: This behaviour is useful as sometimes we may wish to discard any modifications to the database's data since the last transaction was opened. For instance, in our library example we may start the process of placing a book on loan but discover partway that the borrower has already reached his/her limit of borrowed books. We can discard all the changes made since the transaction was opened by calling the connection object's rollback() method. The following example demonstrates how rollback() works. In this example, the first two INSERT statements are rolled back so they have no effect on the database. On the other hand, the last INSERT statement is committed so it does affect the database: Program 5: rollback_example.py 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 import sqlite3 connection = sqlite3.connect("library.db") connection.execute("INSERT INTO Book(ID, Title) " + "VALUES(1, 'Rollback Book')") connection.execute("INSERT INTO Book(ID, Title) " + "VALUES(2, 'Also Rollback Book')") connection.rollback() connection.execute("INSERT INTO Book(ID, Title) " + "VALUES(3, 'Committed Book')") connection.commit() connection.close() data is inserted correctly

SQL Databases CPDD Computer Education Unit Version: Mar 2018 6