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 PRI