Unit 2.4b Using Programs with Data, SQL
Using Programs with Data is focused on SQL and database actions. Part B focuses on learning SQL commands, connections, and curses using an Imperative programming style,
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 a database schema?
defines how data is organized within a database. It specifies how the data is structured and how users and applications can access and manipulate it. Define the database's structure, including the types of data that can be stored, the connections between tables, and the guidelines for adding, updating, and deleting data.
- What is the purpose of identity Column in SQL database?
a distinct value for each row that is added to the table. An identity column's function is to assign each table row a special identification number, which can be helpful for a number of reasons.
- What is the purpose of a primary key in SQL database?
A primary key is a column or group of columns that allows each row in a table to be uniquely identified. A main key's function is to offer a trustworthy and effective means to locate and access particular rows in a table.
- What are the Data Types in SQL table?
There are four basic data types in SQL table: boolean, integer, string, and blog.
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()
- Connects to a SQLite database file located at instance/sqlite.db.
- Creates a cursor object to execute SQL queries.
- Executes an SQL query to fetch information about the "dogs" table, using the PRAGMA table_info() statement.
- Prints the results of the query, which will be a list of tuples containing information about each column in the "dogs" table.
- Closes the database connection.
- Finally, the code calls the schema() function, which executes the above tasks and prints the column information for the "dogs" table.
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?
a connection between an application and a database management system represented as an object. commands to the database and receives the responses.
- Same for cursor object?
An application can navigate the records or rows returned by a SQL query using a cursor object. The cursor offers a mechanism to iteratively read each row of a query's results and execute operations on them as they are accessed.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
Executes a SQL statement using the syntax sql[, arguments]. The values to be associated to placeholders in the SQL statement are contained in a sequence or mapping that is part of the optional parameters argument. Executes a SQL query several times with various parameter sets using the executemany(sql, seq of parameters) function. A series of sequences or mappings holding the values to be associated to placeholders in the SQL query make up the seq of parameters argument. A single sequence is returned by the fetchone() function, or None if there are no more rows to fetch from the query result set. If there are additional rows in the query result set, fetchmany([size=cursor.arraysize]) fetches them, producing a collection of sequences otherwise.
- Is "results" an object? How do you know?
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()
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, dob, 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 portion of the code that has been compromised checks to see if the user-entered new password is less than two characters in length. The message variable is set to "hacked" and the password variable is set to "gothackednewpassword123" if the length is less than 2. This implies that if someone tries to set a simple or brief password, a harder password will be automatically provided to them.
- Explain try/except, when would except occur?
A control flow statement for handling errors is try/except. The try block's function is run, and if an error is made, control is instantly passed to the except block. The program is able to carry on by handling the error in the unless block and catching it. Try/except is helpful when you anticipate a piece of code might result in an error but don't want the program to crash if it does. Instead, you should gracefully manage the error and carry on with the remainder of the application.
- What code seems to be repeated in each of these examples to point, why is it repeated?
To allow the code to continue executing even in the event of a user input error, try/except is repeated.
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?
Well, if you are careless and don't have a backup, you could lose all the data in that row forever.
- In the print statemements, what is the "f" and what does {uid} do?
It is simpler to create strings with dynamic content by using f-strings, which allow expressions to be encapsulated inside string literals. The value of the uid variable is the result of the expression "uid".
import sqlite3
class Dog:
def __init__(self, db_path):
self.db_path = db_path
def delete_by_id(self, 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(self.db_path)
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()
db_path = 'instance/sqlite.db'
dog = Dog(db_path)
id = input("Select the ID of the dog you want to remove from our database: ")
dog.delete_by_id(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?
It calls on itself since it is a recursion until a condition is satisfied.
- Could you refactor this menu? Make it work with a List?
Yeah, you can use a list to reduce the number of elifs you need to employ, which will make the code more effective. Thus, you may store "c,r,u,d,s" in a list.
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
CRUD
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)