Unit 2.4b Hacks
My Hacks for Unit 2.4b
Schema of Users table in Sqlite.db
- What is a database schema?
- A database schema is the structure of a database system, which describes the objects (tables, views, procedures, functions, etc.) and relationships between them.
- What is the purpose of identity Column in SQL database?
- The purpose of an identity column in a SQL database is to provide an auto-incremental, unique number for each row in a table. This allows the database to generate a unique identifier for each row.
- What is the purpose of a primary key in SQL database?
- The purpose of a primary key in a SQL database is to uniquely identify each record in a table. A primary key is composed of one or more columns, which must contain unique values.
- What are the Data Types in SQL table?
- The data types in a SQL table can vary depending on the type of database being used, but the most common data types are integer, decimal, string, date, and boolean.
import sqlite3
database = 'instance/cars.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('cars')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
schema()
Create
Create a new User in table in Sqlite.db
-
Compare create() in both SQL lessons. What is better or worse in the two implementations? In the OOP, the create() function is called on an object. On the other hand, in imperative programming, several variables need to be created within the function instead. Both of these approaches have their own advantages and disadvantages. The imperative approach is less prone to errors but also less efficient, whereas the create() in the OOP version does the opposite. Furthermore, the imperative version does not censor passwords whereas OOP is better for working with large amounts of data. If one is dealing with a small amount of data, then the imperative approach may be more suitable.
-
Explain purpose of SQL INSERT. Is this the same as User init? The SQL INSERT statement used in the code is used to add a new row of data to the "users" table of a SQLite database. The INSERT INTO statement defines the table name and columns for the data to be added, and the VALUES clause provides the values associated with each column. The ? placeholders help prevent SQL injection attacks by cleaning user input. The INSERT INTO statement can be likened to the init method of a User class, as they both serve to create a record or object within the database. The distinction is that the INSERT INTO statement only adds data to a table in the database, while the init method of a User class creates a new instance of a class in memory.
import sqlite3
database = 'instance/cars.db'
def create():
make = input("Enter the make of the car:")
model = input("Enter the model:")
price = input("Enter the price:")
year = input("Enter the year:")
desc = input("Enter a description of the car:")
body_style = input("Enter the body style of the car:")
engine = input("Enter the engine type:")
owner = input("Who's the owner of the car?")
# 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 cars (_make, _model, _price, _year, _desc, _body_style, _engine, _owner) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (make, model, price, year, desc, body_style, engine, owner))
# Commit the changes to the database
conn.commit()
print(f"A new car has been added")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
create()
Read
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 object is an object used to establish a connection between a database and an application. It allows the application to interact with the database by executing queries, retrieving data, and performing other database operations.
- Same for cursor object?
A cursor object is an object used to traverse the records in a database result set. It provides methods to retrieve and manipulate the results of a query.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
The conn object contains attributes such as hostname, port, database, user, and password, while the cursor object contains attributes such as description and rowcount.
- Is "results" an object? How do you know?
Yes, "results" can be classified as an object as it is not only identified as a local variable, but it also stores data which is required for the program to work. This data includes the name, user id and date of birth of each user."
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 cars').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()
Update
Updating a User in table in Sqlite.db
Uses SQL UPDATE to modify password
-
What does the hacked part do? The section of code that includes the term "hacked" is used to create a password that meets the minimum length requirement for safety reasons (2 characters). The message variable has been set to "hacked" to make it clear that the password has been auto-generated.
-
Explain try/except, when would except occur?
If any errors occur while trying to execute an SQL command, the try/except block can be used to handle them. In this situation, the try block holds the SQL command to change the user's password. If any sqlite3.Error exceptions take place, the code in the except block is implemented.
- What code seems to be repeated in each of these examples to point, why is it repeated?
In order to execute SQL commands, it is necessary to create a connection to the database. This connection is represented by a cursor object, which is used to interact with the database. This connection and cursor object must be established for each example, as it is a crucial component of processing SQL commands. At the end, the cursor and connection objects must be closed in order to free up resources and avoid any memory issues.
import sqlite3
database = 'instance/cars.db'
# Take the inputs for each attribute
def update():
id = input("Enter the id of the car:")
make = input("Enter the make of the car:")
model = input("Enter the model:")
price = input("Enter the price:")
year = input("Enter the year:")
desc = input("Enter a description of the car:")
body_style = input("Enter the body style of the car:")
engine = input("Enter the engine type:")
owner = input("Who's the owner of the car?")
# 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
# Trying to add dob right now...
cursor.execute("UPDATE cars SET _make = ?, _model = ?, _price = ?, _year = ?, _desc = ?, _body_style = ?, _engine = ?, _owner = ? WHERE id = ?", (make, model, price, year, desc, body_style, engine, owner, id))
if cursor.rowcount == 0:
# Car ID not found
print(f"No id was found in the table")
# Commit new data
else:
print(f"Car with the id {id} has been updated")
conn.commit()
except sqlite3.Error as error:
print("Error while updating the record:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
update()
Delete
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, delete is a dangerous operation because it permanently removes a record from a database, so it should be handled with care.
- In the print statemements, what is the "f" and what does {uid} do?
The "f" in the print statement is a format specifier, which defines the type of data being printed. The {uid} refers to the user id of the user being deleted, which is passed in as a parameter.
import sqlite3
def delete():
id = input("Select the ID of the car you wish to remove: ")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
# Validate the input
try:
id = int(id)
except ValueError:
print("Invalid input: ID must be an integer.")
return
try:
# Use a parameterized query to prevent SQL injection
c = conn.cursor()
c.execute("DELETE FROM cars 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
cursor.close
conn.close()
delete()
CRUD Menu
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?
the user wishes to conduct multiple tasks in a single session (such as creating and deleting), they can do so by accessing the same menu repeatedly. Any of the CRUD operations can be repeated until the user decides to exit the program via the escape button.
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")
- Could you refactor this menu? Make it work with a List?
Yes, you can add the options for each operation into a list.
def menu():
options = ['c', 'r', 'u', 'd', 's']
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
if operation.lower() in options:
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")