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
- Note
- Code Ouput
- Note
- Code
- Note
- Note
- Menu Interface to CRUD operations
- Note
- Menu, to run other cells from one control point
- Debug Notes
- 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?
- What is the purpose of identity Column in SQL database?
- What is the purpose of a primary key in SQL database?
- What are the Data Types in SQL table?
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()
Note
What's Schema?
Schema is the category or column. Like a title. It's important to identify a column in a database to know what the databse is about or you will be able to access the data within the column. Makes it easily accessible.
What's a primary key?
A primary key is a parameter from classes to userid, dob. The primary key in that list is userid because id is unique to the user. Primary keys should be unique, the first thing you identify somebody by.
What are the data types you might find in a table?
Integers, strings, booleans, float, or any type of type. You can have structures, a list, or a dictionary.
What is the purpose of identity Column in SQL database?
Note I used ChatGPT to aid me in answering this question
The purpose of identity Column in SQL database is to be able to automatically generate unique numeric values for each row added to a table. identity Columns are useful because:
- Primary key: An identity column can be used as the primary key for the table, which is a unique identifier for each row that allows for efficient searching and indexing of the data.
- Relational integrity: An identity column can be used as a foreign key in other tables to establish relationships between the data in different tables, ensuring relational integrity.
- Data audit: An identity column provides a unique identifier for each row, which can be useful for auditing and tracking changes to the data over time.
- Simplify data entry: An identity column can simplify data entry by automatically generating unique values, reducing the risk of human error and improving data quality.
What are the Data Types in SQL table?
- Integer
- Float
- Boolean
- Char
- String
ChatGPT:
- Blob: Used to store binary data (e.g., images or other non-text data).
- Varchar: Used to store variable-length character strings (e.g., "hello world").
- Date/Time: Used to store date and time values (e.g., 2023-03-16 12:00:00).
- Text: Used to store large text values (e.g., blog posts or articles).
Code Ouput
The code block outputs the data of the id, name, uid, password, and uid along with the data type. The numbers at the end are the max length. It prings out the schema
Connect and Cursor
- Connection gives u a connection to the SQLite table.
- Cursor allows you to do things in the sqlite.db file.
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?
- Same for cursor object?
- 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()
Note
What is a connection object? After you google it, what do you think it does?
A connection object represents a unique session to a SQL Server data source, I think it connects the data from the databse to the ouput. It creates open communication to a data source. Connection gives u a connection to the SQLite table.
Same for cursor object?
A cursor object is a database object which is used to manipulate data in a set, do row-by-row processing. It makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. Cursor allows you to do things in the sqlite.db file.
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 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()
Code
Allows user to add their name, userid, password, and dob to databse by utilizing user input.
Object Oriented programming VS Imperative programming
My Opinion
I think this is more effective than object oriented programming and imperitive is easier because it's easier for the way we think. But object oriented programming is better for organization.
ChatGPT Reccomendation + Facts
Both object-oriented programming and imperative programming have their own advantages and disadvantages, and which one is "better" depends on the specific needs of the project and the personal preferences of the programmer.
Imperative programming is a programming paradigm that focuses on describing how a program should perform a certain task through a series of statements or instructions. This approach is typically more procedural and involves managing state and data directly. Imperative programming is often used in low-level programming, such as system programming, because it provides more control over the computer's hardware.
Object-oriented programming, on the other hand, is a programming paradigm that focuses on representing entities or objects in the real world as objects in the code. This approach is more declarative and involves defining classes and objects that interact with each other through methods and properties. Object-oriented programming is often used in higher-level programming, such as application programming, because it provides a more modular and organized approach to programming.
In general, object-oriented programming tends to be more maintainable and scalable, as it allows for the creation of reusable code and more easily understandable program structure. Imperative programming can be more efficient and low-level, making it more suitable for certain types of applications, such as those that require direct control over hardware resources.
Ultimately, the choice between object-oriented programming and imperative programming depends on the specific project requirements and the programmer's personal preference and expertise.
SQL Insert VS User Init
What is SQL Insert?
- it is used to insert new records/rows into a datase table
- INSERT statement specifies the values to be inserted into specific columns of the table
What is User Init?
- user init is short for user initialization
- initialization is the process of preparing or setting something up for use
So are they the same? No, because SQL insert is used for new records/rows adding into a database table and it specifies which data is to be added into specific colums of the table. User init is used for configuring user-specified settings or preferences for the user.
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()
Note
What does the hacked part do?
The hacked part of the code types the message telling the user they've been hacked if their password is less than 2 characters. This is to prevent users from updating their password to weak passwords.
Explain try/except, when would except occur?
Except will occur when you want to select certain rows from a table/database. It is used to take two SELECT statements and returns only the distinct rows of the first SELECT statement.
What code seems to be repeated in each of these examples to point, why is it repeated?
The code that seems to be repeated in each of these examples are:
- cursor.close()
- conn.close()
I think closing the connection is important because you make sure that the code was able to close itself gracefully and to prevent any other objects from reusing the same connection after you are done with it. I think closing the cursor is important because it resets all results, and ensures that the cursor object has no reference to its original connection object.
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()
Note
Is DELETE a dangerous operation? Why?
Yes, SQL delete can be dangerous if not used properly. The DELETE statement in SQL is used to remove rows from a table, and it can be very powerful if used incorrectly.
If you accidentally delete the wrong rows or forget to include a WHERE clause in your DELETE statement, you could potentially delete all the rows in a table, which could have serious consequences for your application or database.
It's important to always double-check your DELETE statements and make sure you're deleting the correct rows. You should also make sure you have a backup of your data before performing any significant deletions, just in case something goes wrong.
In the print statemements, what is the "f" and what does {uid} do?
"f" is used to create an f-string in Python. An f-string is a string literal that allows you to embed expressions inside string literals, using {} brackets. The expressions inside the {} brackets are evaluated at runtime, and their values are inserted into the string.
In the given statement, the {} brackets contain an expression {uid}, which will be evaluated at runtime to the value of the variable uid. The "f" at the beginning of the string tells Python to format the string using this expression.
So, when the print statement is executed, the value of the expression {uid} will be inserted into the string at the position of the {} brackets, resulting in a string that says "The row with uid {value of uid} was successfully deleted".
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")
Note
Why does the menu repeat?
The menu repeats because of the recursion call at the end of the menu() function. When a user selects an operation, the corresponding function is executed (e.g., create(), read(), etc.). After the operation is completed, the menu() function is called again, which displays the menu and prompts the user to enter another operation.
This recursion will continue until the user enters an empty string, which serves as the escape key to exit the menu. However, if an exception occurs during the execution of the menu or any of the other functions, the except block will be executed, which will print a message asking the user to perform a Jupyter 'Run All' before starting the menu.
Could you refactor this menu? Make it work with a List?
Yes, you can!
Chat GPT's refactored solution:
Menu, to run other cells from one control point
def menu(): operations = [ ('c', 'Create', create), ('r', 'Read', read), ('u', 'Update', update), ('d', 'Delete', delete), ('s', 'Schema', schema) ]
while True:
# Display menu options
print("Menu:")
for op in operations:
print(f"({op[0]}) {op[1]}")
print("(q) Quit")
# Prompt user for operation selection
operation = input("Enter an operation: ").lower()
if operation == 'q': # Exit condition
break
# Find the operation function based on user's selection
found = False
for op in operations:
if operation == op[0]:
found = True
op[2]() # Execute operation function
break
# If operation not found, print error message
if not found:
print("Invalid operation. Please try again.\n")
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
Do you see procedural/data abstraction
Yes, procedural abstraction in this code could be modeled as the delete() function because it deletes a row from Users, takes care of connecting to the database, creating a cursor object, executing the SQL command to delete the row, and handling any errors that may occur.
For data abstraction, an example from the code above is the sqlite3.connect(databse), this is data abstraction because this connection object abstracts away the details of how the code interacts with the database, such as opening and closing the database file, managing transactions, and handling low-level networking protocols. Instead, the code can simply use the connection object to execute SQL commands and retrieve data from the database.
Definitions
Procedural Abstraction:
- Procedural abstraction is a programming concept that involves breaking down a program into smaller, more manageable pieces of code. This is done by dividing the code into smaller functions or procedures that can be called from other parts of the program.
Data Abstraction:
- Data abstraction is a technique in programming that involves hiding the implementation details of data structures and operations, and presenting a simplified and higher-level view of the data to the user.