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