Lecture 15 Revision: Database Programming Flashcards
What is SQLite?
SQL = Standard Query Lanuage
SQLite is a lightweight, self-contained database management system.
It’s like a digital filing cabinet that helps organize and store data efficiently. Unlike other database systems, SQLite doesn’t require a server to run—it stores data in a single file on your device, making it simple and easy to use. It’s perfect for small applications, quick prototypes, or projects that don’t need complex database features.
Compact yet powerfull
What are the steps involved in executing a statement against an SQLite database
– Import the database support module (the library that is able to interact between SQlite and Python)
– Create a connection to the database
– Create a cursor to represent each database query (allows you to programmatically navigate thro the database query results set)
– Execute the query (the answer to your query is generated and result set returned)
– Handle the results
– Close the connection (to the database asap - a bit like file io) as it may block other applications writing to the database
How so you import the Database Module?
import sqlite3
This imports the sqlite3 database module which takes care of the interaction from python and the database on your behalf
How do you open a connection to the database?
sqlite3.connect()
Need to parse in the name of the database file in the brackets with double quotation marks around it.
e.g. sqlite3.connect(“dfa.db”)
What is a curser?
The curser is the object that allows you to navigate around your database query, issue a query and process the results (it takes care of all the interactions on your behalf).
How do you create a curser?
connection.cursor()
What this means:
In Python, is used when you’re working with databases through a connection object.
Connection - represents your active link to the database. It’s created when you open a database file or connect to one using Python’s database module (like sqlite).
A cursor is like a “pointer” or a workspace that lets you execute SQL commands (such as querying or updating data) and fetch results. When you call connection.cursor(), you’re creating this cursor
Creating a cursor - example
import sqlite3
conn = sqlite3.connect(“dfa.db”)
curs = conn.cursor()
EXPLANATION: so here
curs is assigned conn.cursor()
conn.cursor will call on the cursor function of our con object.
What this gives us back is a cursor connected to that connection to our database, which in turn is connected to dfa.db.
How do we create the query and execute it?
cursor.execute()
so in our example: curs.execute()
and execute a SELECT statement.
so to use this an example could be:
results is assigned curs.execute(“SELECT * FROM Lecturer;”) the results set is now stored in the python variable called results
This method runs the SQL command provided as a string within the parentheses. In this case (“SELECT * FROM Lecturer;”)
This is an SQL query. It fetches all the columns ( * means “all columns”) and all the rows from the database table named Lecturer)
What is a SELECT statement??
The SELECT statement is a fundamental SQL command used to retrieve data from a database. Think of it as the way to ask a database to show you specific information you’re looking for
How can we handle the results after a SELECT statement has been used?
Use a FOR LOOP that deals with each row individually.
e.g.
for row in results:
print(“ID=”, row[])
print(“First_Name”, row[1])
print(“Surname”, row[2])
row element 0 is the entire row - the first attributte in our LECTURER table. row[2] will be the second attirbute in the LECTURER table etc
SO this loop propcesses thro the result set and for exvery single row is temporarily called row for the purpose of the loop and we can access each element in that row using the indexining.
The ordering is important - it will match the ordering that the data is stored in in the database
How do you close the connection?
conn.close()
when finished with the database so it is freed up for other use.
Write this immediately when connecting then put code in between.
What SQLite statement do I use to create a table using Python? e.g. a table called Lecturers.
Use SQL statement CREATE TABLE
curs.execute(“CREATE TABLE
e.g. CREATE TABLE Lecturers
Then add the attributes you want in that table e.g. ID, name, Title, Position DateAppointed
How do I add the required information into this database in python. i.e. how do I populate my Lecturers table?
Use SQL statement INSERT INTO lecturers VALUES (then comma seperated list of values)
if need a date most databases fequire YYYY-MM-DD
How do you permanently write the new table data to persistant memory?
conn.commit()
How do you retrieve information from a table / database?
Use SQL SELECT command
e.g. results = curs.execute(“SELECT * FROM lecturers WHERE Position=’Senior Lecturer’:”)
The SELECT * means select all of the attributes from the Lecturerers table where the position attribute is equal to Senior Lecturer
then use a for loop e.g. for row in results to loop thro each row and print the matching data.
How can you order / sort results?
Use SQL comand ORDER BY e.g.
ORDER BY date(DateAppointed):”). Can add desc for descending order other wise it is ascending by default.
How can you update databases?
Use SQL command UPDATE and SET where you want the specific data changed.
Then conn.commit()
conn.close()
How can you delete items in databases?
Use SQL command DELETE
e.g. CDELETE FROM Lecturers WHERE Name=’Jane Smith’:”
then conn.commit()
conn.close()
What is a try - except structure?
Deals with exceptions - typically errors, with the file. If an exception occurs when executing the code between try and except, then the try block is abandoned and the block after the except key word is executed.
A message about the exception is displayed to the user.
What does the raise statement do?
The raise statement statement informs
the calling level about the error (e.g. when using a try-except structure.)
It is used to intentionally trigger an exception. It allows you to signal that an error or an unexpected situation has occurred in your code. Think of it as a way to “raise a flag” when something goes wrong
What does rollback mean?
rollback means the changes are undone and the database is returned to it’s original state.
What is a tuple?
A tuple is an ordered series of
elements. It is delimited by parentheses ()
The main difference between a list type and tuple is that a tuple is immutable, i.e.
elements cannot be added and removed.
What method in Python is used to retrieve all the rows from the results of a database query.
The method .fetchall
e.g. result = cursor.fetchall()
What does the function executemany() do?
executemany() can be used for neatly inserting multiple records into a database.
It is used to execute a SQL command multiple times with different sets of data, all in a single call. It’s like a batch-processing tool for database operations, which makes it efficient when you need to insert, update, or delete multiple rows at once.
