Unit 2.4b Hacks
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,
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# Create the database engine
engine = create_engine('sqlite:///overwatch.db', echo=True)
# Create a session factory
Session = sessionmaker(bind=engine)
# Define a base class for declarative models
Base = declarative_base()
# Define the Hero model
class Hero(Base):
__tablename__ = 'heroes'
id = Column(Integer, primary_key=True)
name = Column(String)
role = Column(String)
ultimate = Column(String)
def __repr__(self):
return f'<Hero(id={self.id}, name="{self.name}", role="{self.role}", ultimate="{self.ultimate}")>'
# Create the table in the database
Base.metadata.create_all(engine)
Add Heroes to the Table
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# Create the database engine
engine = create_engine('sqlite:///overwatch.db', echo=True)
# Create a session factory
Session = sessionmaker(bind=engine)
# Define a base class for declarative models
Base = declarative_base()
# Define the Hero model
class Hero(Base):
__tablename__ = 'heroes'
id = Column(Integer, primary_key=True)
name = Column(String)
role = Column(String)
ultimate = Column(String)
def __repr__(self):
return f'<Hero(id={self.id}, name="{self.name}", role="{self.role}", ultimate="{self.ultimate}")>'
# Create a session to interact with the database
session = Session()
# Add some heroes to the database
mario = Hero(name='Mario', role='DPS', ultimate='Mushroom')
session.add_all([mario])
session.commit()
# Query the database for all heroes
heroes = session.query(Hero).all()
print(heroes)
DELETE
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# Create the database engine
engine = create_engine('sqlite:///overwatch.db', echo=True)
# Create a session factory
Session = sessionmaker(bind=engine)
# Define a base class for declarative models
Base = declarative_base()
# Define the Hero model
class Hero(Base):
__tablename__ = 'heroes'
id = Column(Integer, primary_key=True)
name = Column(String)
role = Column(String)
ultimate = Column(String)
def __repr__(self):
return f'<Hero(id={self.id}, name="{self.name}", role="{self.role}", ultimate="{self.ultimate}")>'
# Create a session to interact with the database
session = Session()
# Query the database for the hero to delete
hero_to_delete = session.query(Hero).filter_by(name='Mercy').first()
# Delete the hero object from the session and commit the changes to the database
session.delete(hero_to_delete)
session.commit()
# Query the database for all heroes
heroes = session.query(Hero).all()
print(heroes)
Clear Database
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# Create the database engine
engine = create_engine('sqlite:///overwatch.db', echo=True)
# Create a session factory
Session = sessionmaker(bind=engine)
# Define a base class for declarative models
Base = declarative_base()
# Define the Hero model
class Hero(Base):
__tablename__ = 'heroes'
id = Column(Integer, primary_key=True)
name = Column(String)
role = Column(String)
ultimate = Column(String)
def __repr__(self):
return f'<Hero(id={self.id}, name="{self.name}", role="{self.role}", ultimate="{self.ultimate}")>'
# Create a session to interact with the database
session = Session()
# Delete all heroes from the database
session.query(Hero).delete()
session.commit()
# Query the database for all heroes (should be empty)
heroes = session.query(Hero).all()
print(heroes)
Update
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# Create the database engine
engine = create_engine('sqlite:///overwatch.db', echo=True)
# Create a session factory
Session = sessionmaker(bind=engine)
# Define a base class for declarative models
Base = declarative_base()
# Define the Hero model
class Hero(Base):
__tablename__ = 'heroes'
id = Column(Integer, primary_key=True)
name = Column(String)
role = Column(String)
ultimate = Column(String)
def __repr__(self):
return f'<Hero(id={self.id}, name="{self.name}", role="{self.role}", ultimate="{self.ultimate}")>'
# Create a session to interact with the database
session = Session()
# Query for a hero to update
hero = session.query(Hero).filter_by(name='Tracer').first()
# Update the hero's ultimate ability
hero.ultimate = 'Pulse Bomb Plus'
# Commit the changes to the database
session.commit()
# Query the database for the updated hero
hero = session.query(Hero).filter_by(name='Tracer').first()
print(hero)
import sqlite3
# create a connection to the database file
conn = sqlite3.connect('overwatch2.db')
# create a new table called 'heroes'
conn.execute('''CREATE TABLE IF NOT EXISTS heroes
(id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
role TEXT,
ultimate_ability TEXT);''')
# insert a new hero into the table
conn.execute("INSERT INTO heroes (name, role, ultimate_ability) VALUES ('Tracer', 'Damage', 'Pulse Bomb')")
# save the changes and close the connection
conn.commit()
conn.close()
conn = sqlite3.connect('overwatch2.db')
# insert a new hero into the table
conn.execute("INSERT INTO heroes (name, role, ultimate_ability) VALUES ('Tracer', 'Damage', 'Pulse Bomb')")
# save the changes and close the connection
conn.commit()
conn.close()
import sqlite3
def create_hero():
hero_name = input("Enter new hero name:")
role = input("Enter new hero role:")
ultimate_ability = input("Enter ultimate ability name:")
# Connect to the database file
conn = sqlite3.connect("overwatch2.db")
# 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 heroes (name, role, ultimate_ability) VALUES (?, ?, ?)", (hero_name, role, ultimate_ability))
# Commit the changes to the database
conn.commit()
print(f"A new hero {hero_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_hero()
import sqlite3
def read():
# Connect to the database file
conn = sqlite3.connect('overwatch2.db')
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to select all data from the "heroes" table
cursor.execute("SELECT * FROM heroes")
# Fetch all the rows returned by the SQL command
rows = cursor.fetchall()
# Print the data
for row in rows:
print(row)
except sqlite3.Error as error:
print("Error while executing the SELECT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
import sqlite3
def update_hero(tracer):
# Prompt the user to enter the hero ID and new information
hero_id = input("Enter the ID of the hero you want to update: ")
hero_name = input("Enter the new hero name (leave blank to keep current name): ")
role = input("Enter the new role (leave blank to keep current role): ")
ultimate_ability = input("Enter the new ultimate ability (leave blank to keep current ability): ")
# Connect to the database
conn = sqlite3.connect('overwatch2.db')
# Create a cursor object
cursor = conn.cursor()
# Build the SQL query
sql = "UPDATE heroes SET"
params = []
# If the user entered a new hero name, add it to the query and parameters
if hero_name:
sql += " hero_name = ?,"
params.append(hero_name)
# If the user entered a new role, add it to the query and parameters
if role:
sql += " role = ?,"
params.append(role)
# If the user entered a new ultimate ability, add it to the query and parameters
if ultimate_ability:
sql += " ultimate_ability = ?,"
params.append(ultimate_ability)
# Remove the trailing comma from the query
sql = sql.rstrip(",")
# Add the WHERE clause to specify the hero to update
sql += " WHERE id = ?"
params.append(hero_id)
# Execute the SQL query with the provided parameters
cursor.execute(sql, params)
# Commit the changes and close the connection
conn.commit()
conn.close()
# Display a message indicating success
print("Hero updated successfully!")
update_hero('Tracer', 'Damage', 'Pulse Bomb Plus')
import sqlite3
def delete_hero():
# Prompt the user for the hero name to delete
name = input("tracer")
# Connect to the database file
conn = sqlite3.connect("overwatch2.db")
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to delete the hero with the given name
cursor.execute("DELETE FROM heroes WHERE hero_name = ?", (name))
# Check if any rows were affected by the delete command
if cursor.rowcount == 0:
print(f"No hero with name {name} found.")
else:
print(f"{cursor.rowcount} hero(s) with name {name} deleted.")
# Commit the changes to the database
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()