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?
- A database schema is the structure or blueprint that defines how data is organized in a database. It defines the tables, fields, relationships, and constraints of a database, and provides a clear and comprehensive view of the data model that an application or system uses to store and manage data.
- What is the purpose of identity Column in SQL database?
- In SQL databases, an identity column is a special type of column that automatically generates unique numeric values for each row that is inserted into a table. The purpose of an identity column is to provide a unique identifier for each row in the table, without requiring the user to manually enter a value for that column.
- What is the purpose of a primary key in SQL database?
- In SQL databases, 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 ensure that each row in the table can be uniquely identified and retrieved, and that the data in the table is organized and structured in a consistent and efficient manner.
- What are the Data Types in SQL table?
- Some example types are booleans, blob, string, integer, and binary.
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? After you google it, what do you think it does?
- A connection object is an object that represents a connection to a database or other data source. It is used to establish a connection between an application and a database, and to manage that connection throughout the lifetime of the application.
- Same for cursor object?
- After reading what it is on Google, I believe that it process each row that are queued in the database, and thats how it reads it in order to display the results.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- Attributes in conn are create, menu, read, schema, database, file, ipykernel, os, sqlite3 and sys. Attributes in cursor are conn, create, meny, read, schema, database, file, ipykernel, os, sqlite3, sys.
- Is "results" an object? How do you know?
- Results is an object because an object has functions and data, which is what is shown when we debug it and look at the results, there are special variables, function variables, etc.
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 2.4a lesson does create by has try and excepts for each one individually, while this one lists all the things being created then has the try and except functions after. Im not to sure what is better or worse, but I think that off of looks that this cell below seems better when trying to create a lot of things at once.
- Explain purpose of SQL INSERT. Is this the same as User init?
- The purpose fo SQL insert is to add new data into the database, I think it is because user init initializes the data meaning it assigns an initial value for a variable or data object. However I think init should be used to make the dataset then insert should be just strictly for adding new data in.
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?
- I think that hacked part helps towards encrypting the password so it is not as easily accessible.
- Explain try/except, when would except occur?
- Try would happen when a user wants to reset or make new password, I think except would occur if the problem was not that no user id has been found or row for password was changed, bur not entirely sure exactly what the problem would be for this to happen.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- It seems to be close cursor or connection, I think it is done so that it finishes processing, therefore results can be displayed.
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?
- It is not dangerous because deleting is part of the process of data if things are put incorrectly, and you can always input new data anyways.
- In the print statemements, what is the "f" and what does {uid} do?
- I think f find the userid, the bracket uid displays that certain uid found
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?
- Menu repeats because it is the main thing that connects you to CRUD or S.
- Could you refactor this menu? Make it work with a List?
- I am not entirely sure, but it does sound possible.
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