VJC Chapter 21 SQLite with Python

Chapter 21 SQLite with Python Contents 1 Connecting to SQLite database with sqlite3 2 Execute CRUD operations 3 Commit changes 4 Revert changes 5 Enclosing user input 6 Retrieving data Syllabus Learning Outcomes 3.3 Databases and Data Management Understand, create and use SQL and NoSQL databases, as well as understand techniques to protect the privacy and integrity of data. 3.3.1 Determine the attributes of a database: table, record and field. 3.3.2 Explain the purpose of and use primary, secondary, composite and foreign keys in tables. 3.3.3 Explain with examples, the concept of data redundancy and data dependency. 3.3.4 Reduce data redundancy to third normal form (3NF). 3.3.5 Draw entity-relationship (ER) diagrams to show the relationship between tables. 3.3.6* Understand how NoSQL database management system addresses the shortcomings of relational database management system (SQL). 3.3.7* Explain the applications of SQL and NoSQL. 3.3.8*Use a programming language to work with both SQL and NoSQL databases. *Note: NoSQL will be addressed in later chapter

1 1 Connecting to SQLite database with sqlite3 Python comes with sqlite3 module which allows working with SQLite databases using Python. Generally, to work with the database, 1. We first establish connection to the database with connect() method 2. Execute some SQL statements, with execute () method 3. Save the changes we made to the database with the commit () method 4. Close the database using the close () method. This is similar to how we handle file I/O earlier. Connect to database 1 2 3 4 import sqlite3 connection = sqlite3.connect("school.db") connection.close() To connect to a database: Step 1: import sqlite3 module Step 2: make a connection with database using sqlite3.connect. Note: If the database of interest, for e.g. school.db, does not exist, an empty school.db database will be created. Step 3: To ensure the database file is closed properly, use connection.close().

2 2 Execute CRUD operations After loading an SQLite file and getting a connection, we can execute SQL statements by calling the connection object's execute() method with a str containing the SQL statement we wish to run. For instance, the following Python program creates a new table named student in a new SQLite database file named school.db : Execute SQL statements 1 2 3 4 5 6 7 import sqlite3 connection = sqlite3.connect("school.db") connection.execute("CREATE TABLE student " + "(ExamNo INTEGER PRIMARY KEY," + " Name TEXT, Class TEXT)") connection.close() After running the program, we can open school.db using DB Browser to check that a student table was indeed created. We can also make the statement into a string and parse the string as an argument to be executed. Parse statement as a string

1 2 3 4 5 6 7 8 9 10 11 12 13 import sqlite3 connection = sqlite3.connect("school.db") statement = """CREATE TABLE student ( ExamNo INTEGER PRIMARY KEY, Name TEXT NOT NULL, Class TEXT NOT NULL ); """ connection.execute(statement) connection.close() If we try to run the program again, we will get the following error: OperationalError: table student already exists 3 Any errors caused by running the SQL (such as the error telling us the student table already exists) are reported as Python exceptions and can be handled as usual. To avoid this error, we can use “IF NOT EXISTS” when creating tables. IF NOT EXISTS 1 2 3 4 5 6 7 8 9 10 11 12 13 import sqlite3 connection = sqlite3.connect("school.db") statement = """CREATE TABLE IF NOT EXISTS student ( ExamNo INTEGER PRIMARY KEY, Name TEXT NOT NULL, Class TEXT NOT NULL ); """ connection.execute(statement) connection.close() Now, let us try using INSERT to put data into the student table:

Insert record 1 2 3 4 5 6 7 8 9 10 11 import sqlite3 connection = sqlite3.connect("school.db") statement = "INSERT INTO student(ExamNo, Name, Class)" + "VALUES(2150601, 'Jack Neo', '21S56')" connection.execute(statement) connection.close() This program runs with no errors. However, if we open student.db using DB Browser, we find that the inserted data is missing. What happened to the data? It was discarded because we did not save the changes we made to the database. We can include “OR IGNORE” or “OR REPLACE” when inserting records to avoid errors associated with inserting duplicate records. For example, INSERT OR IGNORE INTO student(ExamNo, Name, Class)VALUES(2150601, 'Jack Neo', '21S56') 4 3 Commit changes It is important to run the commit() method to save the changes to the database. This is equivalent to the action Write Changes in DB Browser. Commit method 1 2 3 4 5 6 7 8 9 10 11 import sqlite3 connection = sqlite3.connect("school.db") statement = """INSERT INTO student(ExamNo, Name, Class) VALUES(2150601, 'Jack Neo', '21S56')""" connection.execute(statement) connection.commit() connection.close()

Adding the commit() method in line 10, the data is inserted and saved correctly. 4 Revert changes We can discard all the changes made since the last commit was executed by calling the connection object's rollback() method. Using rollback to revert changes 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 import sqlite3 connection = sqlite3.connect("school.db") connection.execute("INSERT INTO student(ExamNo, Name, Class)" + "VALUES(2150602, 'Peter Rabbit', '21S56')") connection.execute("INSERT INTO student(ExamNo, Name, Class)" + "VALUES(2150603, 'Bugs Bunny, '21S56')") connection.rollback() connection.execute("INSERT INTO student(ExamNo, Name, Class)" + "VALUES(2150602, 'Mark Lee', '21S56')") connection.commit() connection.close() 5 In the above 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. This process is illustrated by the following diagram: Transaction (Rolled Back) 1 Row Jack Neo 2 Rows INSERT INSERT Jack Neo Peter Rabbit rollback( ) Transaction (Committed) 3 Rows Example Book Peter Rabbit Bugs Bunny

1 Row Jack Neo 2 Rows Jack Neo Mark Lee INSERT commit() 2 Rows Jack Neo Mark Lee Comparing the contents of the student table before and after running the program shows that the first two INSERT statements are indeed ignored. Before After only committed transaction is saved 6 5 Enclosing user input When generating SQL commands, we often need to include some data that is provided by the user. We may be tempted to use str concatenation in order to generate the required SQL command. Unfortunately, this is insecure as special characters or k