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?
- database blueprint
- What is the purpose of identity Column in SQL database?
- to know which data is belong to
- What is the purpose of a primary key in SQL database?
- To find the data more much easily also faster
- What are the Data Types in SQL table?
- string
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 represents a unique session with a data source. I think the connection create the relationship to database.
- Same for cursor object? In this case, cursor connect to the database called sqlite3
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- Is "results" an object? How do you know? I don't think result is an object that connect database. This is the value from database
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? I think sqlite3 is more simple than SQL. In SQL, we have to define all the column, but it is more precise and more comfortable while doing a project.
- Explain purpose of SQL INSERT. Is this the same as User init? No Insert means to put the data in sqlite3
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? is to change the value in database
- Explain try/except, when would except occur? if there is and error
- What code seems to be repeated in each of these examples to point, why is it repeated?
- cursor.execute, it is the code to manage database
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?
- because it removes one of data, and it is difficult to redintegrate
- In the print statemements, what is the "f" and what does {uid} do?
- f means format, so it will print the value more beautiful
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?
- Menu has to be repeated because users sometime don't want for only one thing. They sometime want to create and update it.
- Could you refactor this menu? Make it work with a List?
- Ok I will.
def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
if operation.lower() == '1':
create()
elif operation.lower() == '2':
read()
elif operation.lower() == '3':
update()
elif operation.lower() == '4':
delete()
elif operation.lower() == '5':
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")
import requests
import json
url = "https://corona-virus-world-and-india-data.p.rapidapi.com/api"
headers = {
"X-RapidAPI-Key": "56cf0d9c39msh90ab47fd56c02e6p1d2792jsn0f4dfaa46b90",
"X-RapidAPI-Host": "corona-virus-world-and-india-data.p.rapidapi.com"
}
response = requests.request("GET", url, headers=headers)
# print(response.text['countries_stat'])
countries = response.json().get('countries_stat')
print(len(countries))
import sqlite3
import requests
def make():
url = "https://corona-virus-world-and-india-data.p.rapidapi.com/api"
headers = {
"X-RapidAPI-Key": "56cf0d9c39msh90ab47fd56c02e6p1d2792jsn0f4dfaa46b90",
"X-RapidAPI-Host": "corona-virus-world-and-india-data.p.rapidapi.com"
}
response = requests.request("GET", url, headers=headers)
countries = response.json().get('countries_stat')
# Connect to the database file
con = sqlite3.connect('instance/covid.db')
# Create a cursor object to execute SQL queries
cur = con.cursor()
cur.execute('''CREATE TABLE covid
(country_name, cases, deaths, total_recovered, serious_critical, active_cases)''')
# Save (commit) the changes
con.commit()
# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()
make()
import sqlite3
import requests
def make():
# Connect to the database file
con = sqlite3.connect('instance/covid.db')
# Create a cursor object to execute SQL queries
cur = con.cursor()
url = "https://corona-virus-world-and-india-data.p.rapidapi.com/api"
headers = {
"X-RapidAPI-Key": "56cf0d9c39msh90ab47fd56c02e6p1d2792jsn0f4dfaa46b90",
"X-RapidAPI-Host": "corona-virus-world-and-india-data.p.rapidapi.com"
}
response = requests.request("GET", url, headers=headers)
countries = response.json().get('countries_stat')
for i in range(len(countries)):
country_name = countries[i]["country_name"]
cases = countries[i]["cases"]
deaths = countries[i]["deaths"]
total_recovered = countries[i]["total_recovered"]
serious_critical = countries[i]["serious_critical"]
active_cases = countries[i]["active_cases"]
cur.execute("INSERT INTO covid (country_name, cases, deaths, total_recovered, serious_critical, active_cases) VALUES (?, ?, ?, ?, ?, ?)", (country_name, cases, deaths, total_recovered, serious_critical, active_cases))
# Save (commit) the changes
con.commit()
con.close()
make()
import sqlite3
def read():
# Connect to the database file
conn = sqlite3.connect('instance/playlist.db')
# 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 playlist').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()
import sqlite3
def create():
title = input("Enter the song title:")
author = input("Enter author:")
link = input("Enter the link")
whenMade = input("Enter your date of birth 'YYYY-MM-DD'")
time = input()
# 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 stock (_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()
import sqlite3
def rebuild():
conn = sqlite3.connect('instance/covid.db')
cursor = conn.cursor()
import sqlite3
def recreate_covid():
country_name = input("Enter the country_name: ")
cases = input("Enter whole cases: ")
deaths = input("Enter the deaths: ")
total_recovered = input("Enter total_recovered")
serious_critical = input("Enter total_recovered")
active_cases = input("enter the active cases")
conn = sqlite3.connect('instance/covid.db')
cursor = conn.cursor()
try:
# Execute an SQL command to insert data into a table
cursor.execute("DELETE INTO covid")
cursor.execute("INSERT INTO covid (_name, _uid, _password, _dob) VALUES (?, ?, ?, ?)", (country_name, cases, deaths, total_recovered, serious_critical, active_cases))
# Commit the changes to the database
conn.commit()
print(f"A new user record {country_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()
recreate_covid()
import sqlite3
from tabulate import tabulate
def read_covid():
# Connect to the database file
conn = sqlite3.connect('instance/covid.db')
# 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 covid').fetchall()
# Print the results
table = tabulate(results, headers=["appName", "_uid", "_password", "_personalUse", "_favoriteFeature"])
print(table)
# Close the cursor and connection objects
cursor.close()
conn.close()
read_covid()
import sqlite3
def update_covid():
conn = sqlite3.connect('instance/covid.db')
cursor = conn.cursor()
country_name = input("Enter conuntry name for update")
cases = input('cases')
cursor.execute("SELECT country_name FROM covid where country_name= '"+country_name+"'")
results = cursor.fetchall()
if len(results) == 0:
return "no country"
else:
pass
try:
# Execute an SQL command to update data in a table
cursor.execute("UPDATE covid SET cases = ? WHERE country_name = ?", (cases, country_name))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {country_name} was not found in the table")
else:
print(f"The row with covid id {country_name} the password has been success")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
import sqlite3
def delete_covid():
country_name = input("Enter country to delete")
# Connect to the database file
conn = sqlite3.connect('instance/covid.db')
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM covid WHERE country_name = ?", (country_name,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {country_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 uid {country_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_covid()
import sqlite3
def schema_covid():
conn = sqlite3.connect('instance/covid.db')
cursor = conn.cursor()
results = cursor.execute("PRAGMA table_info('covid')").fetchall()
for row in results:
print(row)
cursor.close()
conn.close()
def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema or E(nd)")
if operation== 'c':
recreate_covid()
if operation == 'r':
read_covid()
if operation == 'u':
update_covid()
if operation == 'd':
delete_covid()
if operation == 's':
schema_covid()
if len(operation)==0: # Escape Key
return
else:
print("Please enter c, r, u, or d")
try:
menu() # start menu
except:
print("Perform Jupyter 'Run All' prior to starting menu")
import sqlite3
conn = sqlite3.connect("baseballmarket.db", isolation_level=None)
c = conn.cursor()
productList = (('baseball','35'),('bat','100'),('glove','150'))
c.execute("CREATE TABLE IF NOT EXISTS productList(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, price INTEGER)")
c.execute("CREATE TABLE IF NOT EXISTS orderList(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, cnt INTEGER, price INTEGER, total INTEGER)")
for row in c.execute('SELECT count(*) FROM productList'):
if row[0] == 0:
c.executemany("INSERT INTO productList (name,price) values (?,?)", productList)
## 상품 목록을 표시하는 코드
while True:
print("------------------list------------------")
for row in c.execute('SELECT id,name, price FROM productList'):
print('number :',row[0],', name :', row[1], ', price :', row[2])
print("--------------------------------------------")
print('')
num = input("write the number you want to buy: ")
c.execute("SELECT name, price FROM productList WHERE id = ?",(num,))
result = c.fetchone()
print('')
count = int(input("write how much to buy: "))
total = count * int(result[1])
c.execute("INSERT INTO orderList (name, cnt, price, total) VALUES (?,?,?,?)", (result[0],count,result[1],total))
print('')
print("see the purchase history")
print("--------------------order list--------------------")
for row in c.execute('SELECT * FROM orderList'):
print('name :',row[1],', order number :', row[2], ', unit price :', row[3], ', price :', row[4])
print("------------------------------------------------")
print('')
print("Are you gonna buy more?\n\press x to stop\nif you want to continue, press enter ")
if(input() == "x"): break
print('')
print("whole purchase list", end='')
for row in c.execute('SELECT sum(total) FROM orderList'):
print(' : ',row[0],'dollars')
print('')
conn.close()
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