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 a blueprint that organizes a database. This specifies where the data is stored and how it will be stored in the database.
- What is the purpose of identity Column in SQL database?
- It is used to generate an id for each row in the table. This is used to index the data that is stored in the database so that it is easy to access in the future. It allows the user to simply enter the data they are searching for without needing to ask the user which row or column the data is in.
- What is the purpose of a primary key in SQL database?
- The primary key is the unique identifier of each row in a database table, which allows uniqueness in a data table. With this each row can be indexed and parsed when needed in order to extract specific data from a cell.
- What are the Data Types in SQL table?
- In SQL tables, there can be numeric values, character values, date and time values, boolean values, binary values, and spatial values. Numeric values are used for integers and decimals in order to store numbers. Character values are used to store alphabetical characters for names and texts. Date and time values are used for storing dates and times. Boolean values are used to show whether something is one or the other. Binary values are used to store images and videos. Spatial values are used to store lines and points, such as svg files.
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 used to connect an object to a database of some sort. This is created when there is a connection established between a database and code, which can be used for parsing or updating data in the database. It usually includes location and name and other information such as a username and password in order to search for the data.
- Same for cursor object?
- A cursor object is similar to a connection object, except it is used for the retrieval and manipulation of data in the database. This retrieves individual rows and data from the database for later use by the user in the frontend. It is what executes the action requested by the user while the connection object simply allows there to be a connection.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- The conn object has attributes that give information about how the program is operating, such as errors and where the user is currently looking. The cursor object has attributes that are more specific to the data that is being extracting, what it is and where it is located in the database.
- Is "results" an object? How do you know?
- Results is an object that is part of the cursor object. This object gathers the specific material from the database and displays it. I know this is an object because it gathers data specific to the database and displays it.
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
- Compare create() in both SQL lessons. What is better or worse in the two implementations?
- This use of the
create()is much better than the other implementation because it is a lot more efficient and shorter than the other implementation.- Explain purpose of SQL INSERT. Is this the same as User init?
- SQL INSERT is used to initialize the values of the table with the variables that are going to be used to store the data. This is the same as
User(__init__).
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 sets a new password to all passwords that are less than two characters. This is similar to what hackers do when they gain access to an account and immediately change the current password to a new password in order to keep the actual user from entering their accounts ever again.
- Explain try/except, when would except occur?
- The try/except tries to add in a new uid and keeps trying until the uid is either not found or until it is updated. If an exception is encountered, an error message is printed because an error in updating was found.
- What code seems to be repeated in each of these examples to point, why is it repeated?
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 because it can result in the entire database being deleted unintentionally if not used carefully.
- In the print statemements, what is the "f" and what does {uid} do?
- The f in the print statement is used to format the string so that the variables are inserted into the string correctly. The {uid} is used to print the user id in question.
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 that the user can do infinite operations until they are entirely finished with their task at hand.
- Could you refactor this menu? Make it work with a List?
- Yes, the menu could be made with boolean statements, which while the menu is run, the boolean stays true. When an operation is run, the same type of statements can be used, however it would be based on a list with all the operations that are needed.
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.
-
Yes, there is data abstraction in both examples of databases being created. There are classes and objects used to simplify how the data is stored in the database. There are also libraries used, which are imported in order to simplify the code and to allow complex operations to the used without the need for thousands of lines of code. An example of this can be seen when the code is debugged and, in my example, a Car class is made to organize the data entered.
-
-
Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
-
Reference... sqlite documentation
import sqlite3
db_n = input("What name do you want your database to be?")
tab_n = input("What name do you want your table to be?")
db = 'instance/'+db_n+'.db'
def schema():
conn = sqlite3.connect(db)
cursor = conn.cursor()
results = cursor.execute("PRAGMA table_info('car')").fetchall()
for row in results:
print(row)
conn.close()
schema()
def read():
conn = sqlite3.connect(db)
cursor = conn.cursor()
results = cursor.execute('SELECT * FROM {tab_n}').fetchall()
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
cursor.close()
conn.close()
def create():
manufacturer = input("Enter the manufacturer of the car:")
model = input("Enter the model of the car:")
price = input("Enter the price of the car:")
conn = sqlite3.connect(db)
cursor = conn.cursor()
try:
cursor.execute("INSERT INTO {tab_n} (_manufacturer, _model, _price) VALUES (?, ?, ?)", (manufacturer, model, price))
conn.commit()
print(f"A new car record {model} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
cursor.close()
conn.close()
def update():
model = input("Enter the model to update")
price = input("Enter updated price")
conn = sqlite3.connect(db)
cursor = conn.cursor()
try:
cursor.execute("UPDATE {tab_n} SET _price = ? WHERE _model = ?", (price, model))
if cursor.rowcount == 0:
print(f"No model {model} was not found in the table")
else:
print(f"The row with model {model} the password has been successfully updated")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
cursor.close()
conn.close()
def delete():
model = input("Enter car model to delete")
conn = sqlite3.connect(db)
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM {tab_n} WHERE _model = ?", (model,))
if cursor.rowcount == 0:
print(f"No model {model} was not found in the table")
else:
print(f"The row with model {model} was successfully deleted")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
cursor.close()
conn.close()
def menu():
operations = ['c', 'r', 'u', 'd', 's']
while True:
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
if operation.lower() in operations:
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:
return
else:
print("Please enter c, r, u, or d")
try:
menu()
except:
print("Perform Jupyter 'Run All' prior to starting menu")