Schema of Users table in Sqlite.db
The schema() function in the code connects to a SQLite database and executes a query to fetch the schema of the 'users' table in the database. It then prints the schema to the console.
The purpose of an identity column in SQL database is to automatically generate a unique value for each row in a table. It is commonly used as a primary key for the table.
The purpose of a primary key in SQL database is to uniquely identify each row in a table. It is used to enforce data integrity and to create relationships between tables.
The data types in a SQL table can vary depending on the database system being used, but common data types include integers, decimals, strings, and dates. Other data types include booleans, binary data, and spatial data types.
import sqlite3
database = 'files/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? 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?
A connection object is a Python object that represents a connection to a database. It is created using a Python library for connecting to a specific database (such as SQLite, MySQL, or PostgreSQL) and allows the user to perform operations on the database.
A cursor object is a Python object that is used to interact with the database by executing SQL commands. It is created from a connection object and provides methods to execute SQL statements and retrieve the results of queries.
The conn object and cursor object have various attributes that depend on the specific database and Python library being used. In general, the conn object may have attributes such as database, user, host, port, and timeout, while the cursor object may have attributes such as rowcount, description, and arraysize. To see the attributes of these objects in the VSCode debugger, you can use the dir() function or look at the object's documentation.
There is no results object in the given code, so it cannot be determined whether it is an object or not.
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()
The if len(password) < 2: statement checks if the length of the updated password is less than 2 characters. If this condition is true, it sets the message variable to "hacked" and changes the password to a hardcoded value of "gothackednewpassword123". This code is not necessarily malicious, but it is not secure to use a hardcoded password, as it can be easily guessed or hacked.
The try/except block is used to handle exceptions that may occur when executing the SQL command to update data in the database. If an exception occurs during the execution of the try block, the code will jump to the except block to handle the exception. For example, if there is an error with the database connection or syntax error in the SQL command, the except block will be executed to print an error message.
The code that is repeated in each example is related to establishing a connection to the database and creating a cursor object to execute SQL commands. These steps are necessary for any interaction with a SQLite database, so they are repeated in each example to ensure that the necessary connection and cursor objects are created before executing SQL commands.
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()
Yes, DELETE operation is considered a dangerous operation as it can permanently remove data from a table, and if executed without proper caution, it can result in significant data loss. The "f" before a string indicates an f-string, which is a string literal that allows embedding expressions inside string literals, using curly braces {} and prefixed by an "f". {uid} inside the f-string is a variable placeholder that will be replaced with the value of the variable "uid" when the string is formatted.
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()
Why does the menu repeat?
The menu repeats because the menu() function is called recursively at the end of each operation. This means that after completing an operation, the menu is displayed again and the user is prompted to enter another operation. Could you refactor this menu? Make it work with a List?
Yes, the menu can be refactored to use a list of operations and a loop to display the options and handle the user input. Here is an example of how it can be done:
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.
Procedural Abstraction
This implementation incorporates a significant amount of procedural abstraction, with functions created to define various procedures in the code, such as create, read, update, and delete. These functions allow for a more generalized approach, enabling information to be added to specific rows of the database.
Data Abstraction
Regarding data abstraction, the code demonstrates the storage of various user attributes, such as their name, password, and date of birth, together in a user-specific uid. This uid is critical in functions like update and delete, as it enables the removal or modification of data based on the unique user identification.
import sqlite3
import os
class SQLTable:
def __init__(self, db_path, table_name):
self.db_path = db_path
self.table_name = table_name
self.conn = sqlite3.connect(self.db_path)
self.cursor = self.conn.cursor()
def create_table(self, columns):
columns_str = ', '.join(columns)
query = f'CREATE TABLE IF NOT EXISTS {self.table_name} ({columns_str})'
self.cursor.execute(query)
def close_connection(self):
self.conn.close()
# Example usage
db_path = os.path.join('files', 'sqlite.db')
table = SQLTable(db_path, 'mytable')
table.create_table(['id INTEGER PRIMARY KEY', 'name TEXT', 'age INTEGER'])
table.close_connection()
import sqlite3
class SQLTable:
def __init__(self, db_path, table_name):
self.db_path = db_path
self.table_name = table_name
self.conn = sqlite3.connect(self.db_path)
self.cursor = self.conn.cursor()
self.create_table()
def create_table(self):
query = f"CREATE TABLE IF NOT EXISTS {self.table_name} (name TEXT, age INTEGER)"
self.cursor.execute(query)
def add_user(self, name, age):
query = f"INSERT INTO {self.table_name} (name, age) VALUES (?, ?)"
self.cursor.execute(query, (name, age))
self.conn.commit()
def close_connection(self):
self.conn.close()
db_path = 'mydatabase.db'
table = SQLTable(db_path, 'mytable')
table.add_user('Alice', 25)
table.add_user('Bob', 30)
table.close_connection()