Database Programming is Program with Data
Each Tri 2 Final Project should be an example of a Program with Data.
Prepare to use SQLite in common Imperative Technique
- Explore SQLite Connect object to establish database connection- Explore SQLite Cursor Object to fetch data from a table within a database
Schema of Users table in Sqlite.db
Uses PRAGMA statement to read schema.
Describe Schema, here is resource Resource
- What is the purpose of an identity column in a SQL database? An identity column provides a unique identifier for each row in a table, making it easier to access and manipulate the data.
- What is the purpose of a primary key in a SQL database? A primary key is a unique identifier for each row in a table, ensuring that no two rows have the same key and making it easier to query and join the data.
- What are the data types that can be stored in a SQL table? Common data types include integers, strings, booleans, and floats, as well as date and time values, and binary data. SQL does not support lists or dictionaries as data types.
import sqlite3
database = './instance/sqlite.db'# this is location of database
def schema():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Fetch results of Schema
results = cursor.execute("PRAGMA table_info('users')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
schema()
Reading Users table in Sqlite.db
Uses SQL SELECT statement to read data
- What is a connection object? Once connected to a database, a connection object allows you to interact with the database by executing SQL commands and retrieving results.
- What is a cursor object? A cursor object is used to execute SQL commands and retrieve results from a database, acting as a "pointer" to the current row in the result set.
- When looking at the conn object and cursor object in VSCode debugger, what attributes are available in the object? This will depend on the specific database and driver being used, but common attributes might include the connection string, the current transaction, and any pending commands or results.
- Is "results" an object? How do you know? Yes, "results" is likely an object since it contains data and may also have methods or properties associated with it, depending on how it was generated or retrieved. However, without more context it is difficult to say for sure.
import sqlite3
def read():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Execute a SELECT statement to retrieve data from a table
results = cursor.execute('SELECT * FROM users').fetchall()
# Print the results
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
# Close the cursor and connection objects
cursor.close()
conn.close()
read()
Create a new User in table in Sqlite.db
Uses SQL INSERT to add row
- How do the create() methods in the two SQL lessons compare, and what are the pros and cons of each approach? The first create() method uses object-oriented programming to organize and manage large databases, while the second approach uses a more imperative style with individual functions for each task. Object-oriented programming can be more maintainable and scalable, but may be more complex to understand, while imperative programming can be easier to comprehend but may become unwieldy with large datasets.
- What is the purpose of the SQL INSERT statement, and how does it differ from a User init method? The SQL INSERT statement is used to add new data to a specific table in a database. This is different from a User init method, which is typically used to initialize a new instance of a class or object in object-oriented programming.
import sqlite3
def create():
name = input("Enter your name:")
uid = input("Enter your user id:")
password = input("Enter your password")
dob = input("Enter your date of birth 'YYYY-MM-DD'")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to insert data into a table
cursor.execute("INSERT INTO users (_name, _uid, _password, _dob) VALUES (?, ?, ?, ?)", (name, uid, password, dob))
# Commit the changes to the database
conn.commit()
print(f"A new user record {uid} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
create()
Updating a User in table in Sqlite.db
Uses SQL UPDATE to modify password
- What is the purpose of the hacked part in the code? The hacked part checks the length of a password to ensure that it is greater than two characters. If the length is not sufficient, the message is updated to indicate that the password has been "hacked".
- What is the purpose of the try/except block in the code, and when would the except block be executed? The try/except block is used to handle errors that may occur during execution of the code, such as connection errors or database query failures. If an error occurs, the except block is executed, which may include handling the error, logging it, or displaying an error message.
- Why is the code that defines conn and cursor repeated in each example, and what would happen if it were not included? The code that defines conn and cursor is necessary for establishing a connection to the SQLite database and executing SQL commands. If it were not included, the code would not be able to interact with the database and would likely result in errors or exceptions. Therefore, it is repeated in each example to ensure that a connection to the database can be established.
import sqlite3
def update():
uid = input("Enter user id to update")
password = input("Enter updated password")
if len(password) < 2:
message = "hacked"
password = 'gothackednewpassword123'
else:
message = "successfully updated"
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to update data in a table
cursor.execute("UPDATE users SET _password = ? WHERE _uid = ?", (password, uid))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {uid} was not found in the table")
else:
print(f"The row with user id {uid} the password has been {message}")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
update()
Delete a User in table in Sqlite.db
Uses a delete function to remove a user based on a user input of the id.
- Why is DELETE considered a potentially dangerous operation? DELETE can be dangerous in a public database because it has the potential to remove large amounts of data with a single command. If someone gains unauthorized access or accidentally issues the command, it could result in significant data loss.
- What is the purpose of the "f" in the code, and what does the {uid} represent? The "f" is part of an f-string, which allows variables to be interpolated directly into the string without needing to use the .format() method. The {uid} is a placeholder in the string that will be replaced with the value of the uid variable at runtime. This allows the program to access data based on the user's input.
import sqlite3
def delete():
uid = input("Enter user id to delete")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM users WHERE _uid = ?", (uid,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {uid} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with uid {uid} was successfully deleted")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
delete()
Menu Interface to CRUD operations
CRUD and Schema interactions from one location by running menu. Observe input at the top of VSCode, observe output underneath code cell.
- What is the reason for the menu to repeat itself? The menu() function is designed to be a recursive function, which means it will keep calling itself until certain conditions are met. This causes the menu to repeat multiple times.
- Can the menu be refactored to work with a list instead? Yes, it is possible to refactor the menu to work with a list of user inputs such as 'c', 'r', 'u', 'd'. The program could then split the user input into each of the methods that must be run and continue accordingly, rather than using a recursive function.
def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
if operation.lower() == 'c':
create()
elif operation.lower() == 'r':
read()
elif operation.lower() == 'u':
update()
elif operation.lower() == 'd':
delete()
elif operation.lower() == 's':
schema()
elif len(operation)==0: # Escape Key
return
else:
print("Please enter c, r, u, or d")
menu() # recursion, repeat menu
try:
menu() # start menu
except:
print("Perform Jupyter 'Run All' prior to starting menu")
Hacks
- Add this Blog to you own Blogging site. In the Blog add notes and observations on each code cell.
- In this implementation, do you see procedural abstraction?
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
Reference... sqlite documentation