Unit 2.4b Using Programs with Data, SQL (Notebook FOR NOTES)
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
- Menu Interface to CRUD operations
- Recursive programing above
- Hacks
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?
- A database schema is the blueprint or structure of a database. It defines how data is organized and how tables, fields, and relationships are designed within the database. A database schema includes the tables, columns, constraints, relationships, and other elements that define the structure of the database.
- What is the purpose of identity Column in SQL database?
- An identity column in SQL database is a column that automatically generates a unique numeric value for each row that is added to a table. It is used to uniquely identify each row in the table and can be used as a primary key. Identity columns are often used in conjunction with auto-incrementing values.
- What is the purpose of a primary key in SQL database?
- The purpose of a primary key in SQL database is to uniquely identify each row in a table. A primary key is a column or set of columns that have a unique value for each row in the table. It is used to enforce data integrity and to create relationships between tables. A primary key can be used to join tables together and to ensure that each row in a table can be uniquely identified.
- What are the Data Types in SQL table?
- Numeric data types: INT, FLOAT, DECIMAL, etc.
- Character and string data types: CHAR, VARCHAR, TEXT, etc.
- Date and time data types: DATE, TIME, DATETIME, TIMESTAMP, etc.
- Binary data types: BLOB, BINARY, VARBINARY, etc.
- Other data types: BOOLEAN, ENUM, SET, JSON, XML, etc.
- The specific data types available in SQL may vary depending on the database management system being used.
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
- Schema is the Metadata for the databse
- What is a connection object? After you google it, what do you think it does?
- This conn is an object that has variables and functions.
- I think that this connection instantiates an obkect in order to connect our databse to a variable.
- We connect to the database using SQLite3.connect
- Same for cursor object?
- Attributes in the object include
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- Is "results" an object? How do you know?
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
- Compore create() in both SQL lessons. What is better or worse in the two implementations?
- The code for this create is much shorter. It requires the conn and cursor objects to be opened and closed. The cursor must execute a code which takes all of the variables and adds it to the table.
- Explain purpose of SQL INSERT. Is this the same as User init?
- SQL INSERT allows rows to be added to an SQL database table. The User init function allows a dataset which is defined to be able to added to an SQL database. This serves a similar purpose.
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 does the hacked part do?
- The hacked part of the update function outputs a message to the user if the length of the password is too short. This serves as authentication for the user to make sure that a password is long enough for security purposes.
- Explain try/except, when would except occur?
- Try and except work as things which check if certain code is running/working. Try runs if the code is working smoothly, and in this case serves to update the password. Except occurs if there is an error with the connection and cursor with the database. This serves as a good way to check errors if there is something wrong with the code.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- Code that seems to be repeated include import sqlite3, defining the conn and cursor methods, cursor execute, conn.commit(), and conn and cursor close. This code seems to be repeated because it is required to connect and use the cursor method in the database. cursor.execute() seems to run the wanted code, whether it is update, delete, read, etc. conn.commit() appears that it commits the changes to the database in order to make the changes permanent. The closing of cursor and conn appears to be present because it is important be able to close the working connections and cursors in order to be able to have different ones. This is especially important when working with servers because it makes sure to have orderly code and prevents resources from being used unnecessarily.
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?
- DELETE can be a dangerous operation because it can remove information from the database, and if placed in the wrong hands, they could delete important information by accident. However, delete is also necessary in order to maintain a clean database with proper records.
- In the print statemements, what is the "f" and what does {uid} do?
- In the print statements, the "f" and {uid} allow the coder to include the selected uid without having to add multiple strings in the print statement. It allows for easy replacement and making a statement which uses the variable which the user called for.
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.
- Why does the menu repeat?
- The menu repeats so the user has access to the functions multiple times if they want, instead of having to restart the code cell every time they want to try a new function.
- Could you refactor this menu? Make it work with a List?
- This menu could be refactored, to reduce the number of elif statements had. Refactoring allows the code to be cleaned up and become more organized. The menu could be held in a list or dictionary and based on the output, the specific function would occur.
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?
- This implementation has a lot of procedural abstraction. There are functions created in order to be able to define what procedures we want to do in the code. For example, there are procedures defined for create, read, update, and delete. This allows us to have a broad function which can be used to add information to specific rows of the database.
-
In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
- There is data abstraction in this code. There are many things which are defined when creating a user, from their name to password to date of birth. These all need to be stored together, and using the code, we create a specific uid for the user which allows the rest of the data to be called from. The uid is essential in functions like update and delete, where data is removed or changed based on the user-specific uid.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
- I will use the OOP style for data manipulation because that is what I used for my CPT project
- Do you see data abstraction? Complement this with Debugging example.
Reference... sqlite documentation