Database Programming is Program with Data
The Tri 2 Final Project is 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 a database schema?
describes the organization of data within a database. It defines how the data is organized and how it can be accessed and manipulated by users or applications. define the structure of the database, including the types of data that can be stored, the relationships between different tables, and the rules that govern how data can be added, updated, and deleted.
- What is the purpose of identity Column in SQL database?
unique value for each row inserted into the table. The purpose of an identity column is to provide a unique identifier for each row in the table, which can be useful for a variety of reasons
- What is the purpose of a primary key in SQL database?
a primary key is a column or set of columns that uniquely identifies each row in a table. The purpose of a primary key is to provide a reliable and efficient way to identify and access specific rows in a table.
- What are the Data Types in SQL table? booleans, integers, strings, blog
imperative programming- series of functions you make
OOP- you have a class and working with objects and methods in that same class
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('dogs')").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? After you google it, what do you think it does?
object that represents a connection between an application and a database management system. Sends commands to database and receives results.
- Same for cursor object?
a cursor object is an object that allows an application to traverse the records or rows returned by a SQL query. The cursor provides a way to access the results of a query one row at a time, and to perform operations on each row as it is accessed.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
execute(sql[, parameters]):Executes an SQL statement. The optional parameters argument is a sequence or mapping containing the values to be bound to placeholders in the SQL statement.executemany(sql, seq_of_parameters): Executes an SQL statement multiple times with different parameter sets. The seq_of_parameters argument is a sequence of sequences or mappings containing the values to be bound to placeholders in the SQL statement. fetchone(): Fetches the next row of a query result set, returning a single sequence or None if there are no more rows. fetchmany([size=cursor.arraysize]): Fetches the next set of rows of a query result set, returning a list of sequences or an empty list if there are no more rows. fetchall(): Fetches all remaining rows of a query result set, returning a list of sequences or an empty list if there are no rows. close(): Closes the cursor object.
- Is "results" an object? How do you know?
yes because it is set equal to cursor.execute...
import sqlite3
def read():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor() # open connection object: contains methods and attributes you need to alter data
# Execute a SELECT statement to retrieve data from a table
results = cursor.execute('SELECT * FROM dogs').fetchall() #results is an object
# 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
- Compare create() in both SQL lessons. What is better or worse in the two implementations?
4a lesson create function uses the SQLite database and takes user inputs to create a new record in the "dogs" table. It prompts the user to enter their name, user id, password, and date of birth, and then inserts the data into the database using a SQL query. this create function uses SQLAlchemy, a Python SQL toolkit, and an ORM (Object-Relational Mapping) to create a new record in a dog table. The first code block might be more suitable for simple projects where there are no complex relationships between tables, and the data to be inserted is minimal. In contrast, the second code block, which uses ORM, can help simplify complex database operations by mapping database tables to Python classes, making it more intuitive and easy to work with. However, it may be more challenging to set up, particularly for beginners.
- Explain purpose of SQL INSERT. Is this the same as User init?
The SQL INSERT statement is used to insert new rows or records into a database table. The User init method is used to create a new instance of the User class, which represents a user record in the database. It sets the initial values for the instance attributes of the User class. They are different.
import sqlite3
def create():
image = input("Enter image link:")
link = input("Enter link to dog info:")
name = input("Enter dog name:")
uid = input("Enter UID:")
breed = input("Enter dog breed:")
sex = input("Enter dog sex:")
dob = input("Enter dog date of birth:")
price = input("Enter dog price:")
# 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 dogs (_image, _link, _name, _uid, _breed, _sex, _dob, _price) VALUES (?, ?, ?, ?, ?, ?, ?)", (image, link, name, uid, breed, sex, price))
# 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 does the hacked part do?
The hacked part in the code is checking if the length of the new password entered by the user is less than 2 characters. If the length is less than 2, it sets the message variable to "hacked" and sets the password variable to "gothackednewpassword123". This means that if someone tries to set a weak or short password, they will be assigned a stronger password automatically.
- Explain try/except, when would except occur?
try/except is a control flow statement used for error handling. The code within the try block is executed, and if an error occurs, the execution is immediately transferred to the except block. The except block catches the error and handles it appropriately, allowing the program to continue executing. try/except is useful when you know that a certain piece of code may cause an error, but you don't want the program to crash when that error occurs. Instead, you want to handle the error gracefully and continue executing the rest of the program.
- What code seems to be repeated in each of these examples to point, why is it repeated?
try/except is repeated so that the code can continue running even if there is a user input error.
import sqlite3
def update():
uid = input("Enter UID to update")
price = input("Enter updated price")
if len(price) > 500:
message = "price is too expensive"
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 dogs SET _price = ? WHERE _uid = ?", (price, uid))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {uid} was found in the table")
else:
print(f"The row with user id {uid} the price 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()
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.
- Is DELETE a dangerous operation? Why?
yes, if you are not careful you can permanently all the data in that row if you don't have a backup.
- What is the "f" and {uid} do?
An f-string is a way to embed expressions inside string literals, making it easier to build strings with dynamic content. {uid} is an expression that evaluates to the value of the uid variable.
import sqlite3
def delete(id):
# Validate the input
try:
id = int(id)
except ValueError:
print("Invalid input: ID must be an integer.")
return
# Connect to the database
conn = sqlite3.connect('instance/sqlite.db')
try:
# Use a parameterized query to prevent SQL injection
c = conn.cursor()
c.execute("DELETE FROM dogs WHERE id=?", (id,))
conn.commit()
print(f"Row with ID {id} has been deleted.")
except sqlite3.Error as e:
print(f"Error deleting row with ID {id}: {e}")
finally:
# Close the connection
conn.close()
id = input("Select the ID of the dog you want to remove from our database: ")
delete(id)
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.
- Why does the menu repeat? Because it is a recursion, so it calls upon itself until a condition is met
- Could you refactor this menu? Make it work with a List?
Yes,you can use a list so that you don't have to use so many elifs, which would make the code more efficient. therefore, you can use a list to store "c,r,u,d,s"
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")
def menu():
operations = [ # cruds placed into a dictionary, keys
('c', create),
('r', read),
('u', update),
('d', delete),
('s', schema)
]
while True:
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
if len(operation) == 0: # Escape Key
return #
for op in operations:
if operation.lower() == op[0]: # checks whether the user's input matches the current option being considered in the loop.
op[1]()
break
else:
print("Please enter c, r, u, d, or s") # if no match, print prompt
menu()
Hacks
- Add this Blog to you own Blogging site. In the Blog add notes and observations on each code cell.
- Create a new Table or do something new, sqlite documentation
- In implementation in previous bullet, do you see procedural abstraction?
You can create functions like create, update, read, and delete which can be reused whenever you need to create a new table in the database.
import sqlite3
from tabulate import tabulate
# Connect to the SQLite database
conn = sqlite3.connect('instance/sqlite.db')
cursor = conn.cursor()
# Select all rows from the "dogs" table
cursor.execute("SELECT id, _name, _uid, _breed, _sex, _dob, _price FROM dogs")
rows = cursor.fetchall()
# Use tabulate to format the rows as a table
table = tabulate(rows, headers=["ID", "Name", "UID", "Breed", "Sex", "DOB", "Price"])
# Print the table
print(table)