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 ResourceA database schema is a logical framework or blueprint for how a database is organized. It defines the structure of the database, including the tables, fields, relationships, constraints, and indexes. The schema provides a roadmap for how data is stored, organized, and accessed in the database.
An identity column in a SQL database is a column that is automatically generated and incremented by the database system for each new 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 specify the value explicitly.
The purpose of a primary key in a SQL database is to uniquely identify each record in a table. A primary key is a column or set of columns that has a unique value for each row in the table. The primary key is used to enforce data integrity and ensure that each record in the table can be uniquely identified.
The data types in a SQL table define the type of data that can be stored in each column of the table. Some common data types in SQL include:
INT: used to store integer values. VARCHAR: used to store variable-length character strings. DATE: used to store date values. FLOAT: used to store floating-point numbers. BOOLEAN: used to store boolean (true/false) values. BLOB: used to store binary large object data (e.g. images or documents).
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 SQLite is a lightweight, embedded database that supports a subset of SQL commands. A connection object is an instance of the sqlite3.Connection class, which represents a connection to a SQLite database. The connection object allows you to execute SQL commands and manage transactions. A cursor object is an instance of the sqlite3.Cursor class, which is used to execute SQL commands and fetch results from the database. The cursor object is created by the connection object and provides a way to iterate over the results of a SELECT statement. The conn object and cursor object have various attributes, depending on the specific implementation and library used. In general, the conn object might have attributes such as the database name, host, port, and connection status. The cursor object might have attributes such as the last executed SQL command, the current row, and the number of rows affected by the last command. "results" is likely a variable or object that holds the results of the SQL SELECT statement. Whether it is an object or not depends on how it was implemented in the code. It might be a list, tuple, dictionary, or other data structure that holds the rows returned by the SELECT statement.
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 countries').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
The SQL CREATE statement is used to create a new database object, such as a table, view, index, or trigger. It specifies the name of the object, the columns or fields in the object, and any constraints or rules for the object. The syntax and features of the CREATE statement may vary between different SQL database systems.
The SQL INSERT statement is used to add new rows of data to an existing table in a database. It specifies the name of the table, the columns or fields to insert data into, and the values to insert. The purpose of the INSERT statement is to populate a table with data, either manually or programmatically. It is similar to the init method in Python classes, which is used to initialize the object with data.
import sqlite3
def create():
name = input("Enter country name:")
language = input("enter the language:")
population = int(input("Enter your the population:"))
area = int(input("Enter the area:"))
# 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 countries (_name, _language, _population, _area) VALUES (?, ?, ?, ?)", (name, language, population, area))
# Commit the changes to the database
conn.commit()
print(f"A new country record {name} 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
This is a Python function that prompts the user for a user id and an updated password, and then updates the password for that user in a SQLite database table called "users". If the length of the updated password is less than 2 characters, it will be set to a default password of "gothackednewpassword123".
To use this function, you need to have a SQLite database file containing a "users" table with columns for user id and password. The database file should be specified in the "conn" variable.
import sqlite3
def update():
name = input("Enter user name to update")
population = int(input("enter population to update:"))
# 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 _population = ? WHERE _name = ?", (population, name))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No name {name} was not found in the table")
else:
print(f"The row with name {name} the population has been updated")
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 dangerous because the information cannot be recovered
- In the print statemements, what is the "f" and what does {uid} do?
- both of these embed the variables in the string
import sqlite3
def delete():
name = input("Enter name 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 countries WHERE _name = ?", (name,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No name {name} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with name {name} 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?
- so we can keep doing it over and over again
- Could you refactor this menu? Make it work with a List?
- yes, by adding each operation into a list
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?
Reference... sqlite documentation