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?
- *The database schema is the structure of a database described in a language supported by the database management system. The term schema refers to the organization of data as of how the database is constructed.*
- What is the purpose of identity Column in SQL database?
- The Identity columns can be used for generating key values. The identity property on a column guarantees the following: Each new value is generated based on the current seed value increment. Each new value for a particular transaction is different from other concurrent transactions on the table.
- What is the purpose of a primary key in SQL database?
- A primary key is the column or columns that contain values that uniquely identify each row in a table. A primary key is needed: To extract or archive data from a table that is visited more than once during a process
- What are the Data Types in SQL table?
- Data types are hte diffrent types the data stored can be represented. For example, Numeric data types such as: INT , TINYINT , BIGINT , FLOAT , REAL , etc. Date and Time data types such as: DATE , TIME , DATETIME , etc. Character and String data types such as: CHAR , VARCHAR, TEXT
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?
- Connection Object is used to create a connection between the SQL database and Python, the connect() method of sqlite3 module is used for establishing the DB connection wit Pyhton
- Same for cursor object?
- The sqlite3 Cursor class is an instance using which we can invoke methods that execute SQLite statements, fetch data from the result sets of the queries.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- cursor has arraysize, lastrowid, rowcount, description, rowcount, connection
- connection has commit, rollback, execute, in_transaction, total_changes attributes
- Is "results" an object? How do you know?
- results is a list. From the documentation of cursor.fetchall() would return all (remaining) rows of a query result as a list
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?
- create here is done with connect to db and get cursor and execute an SQL statement with commit. This would assume the user to know SQL query language. Debug and error handling may be much precise
- create in earlier section was done with Python classes. I feel this is much cleaner since this hides all the SQL calls to the DB. Also this leads to cleaner code
- Explain purpose of SQL INSERT. Is this the same as User init?
- The INSERT INTO statement is used to insert new records in a table. This is not the same as 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?
- If the password length is less than 2 it prints gothackednewpassword123 message
- Explain try/except, when would except occur?
- Except will execute when there is an Error while executing the UPDATE. The try block handle the UPDATE
- What code seems to be repeated in each of these examples to point, why is it repeated?
- The connection connect, cursor, and close are repeated. Since maybe dont want to keep the connection open
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 is not a dangerous operation if correctly used. But if input params are not checked correctly it may inadverently delete unwanted data and this may cause to lose data permanantly.
- In the print statemements, what is the "f" and what does {uid} do?
- string formatting mechanism known as Literal String Interpolation or more commonly as F-strings. The idea behind f-strings is to make string interpolation simpler. The {uid} will print the literal uid value
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?
- Its recursively called when CRUDS choice is not entered or after the correct CRUDS operation was done
- Could you refactor this menu? Make it work with a List?
- List can have only the values but an hashtable can have value and function to call. Hashtable would make better sense
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