def load_data():
connection = dbs.init_db()
cur = connection.cursor()
query = "SELECT * FROM magicians_all;"
magicians_all_df = pd.read_sql_query(query, connection)
query = "SELECT * FROM locations;"
locations_df = pd.read_sql_query(query, connection)
query = "SELECT * FROM magicians_rounds;"
magicians_rounds_df = pd.read_sql_query(query, connection)
query = "SELECT * FROM shows;"
shows_df = pd.read_sql_query(query, connection)
connection.close()
return magicians_all_df, locations_df, magicians_rounds_df, shows_df
def load_data():
connection = dbs.init_db()
query = "SELECT * FROM users_rounds;"
users_rounds_df = pd.read_sql_query(query, connection)
query = "SELECT * FROM locations;"
locations_df = pd.read_sql_query(query, connection)
query = "SELECT * FROM rounds;"
rounds_df = pd.read_sql_query(query, connection)
# Merge users_rounds and locations tables to get actual location names
users_rounds_loc_df = pd.merge(users_rounds_df, locations_df,
left_on="fk_location_id", right_on="id")
# Merge users_rounds and rounds tables to get actual dates each user has enrolled for
users_rounds_loc_df = pd.merge(users_rounds_loc_df, rounds_df,
left_on="fk_round_id", right_on="id")
return users_rounds_loc_df
def list_shows_from_db():
st.header("Find magic shows using DB tables:")
magician = st.text_input("Magician name:", "")
if magician == "":
return
connection = dbs.init_db()
cur = connection.cursor()
cur.execute("SELECT * FROM magicians WHERE name = '%s'" % magician)
magician_id = cur.fetchone()[0]
query = """SELECT * FROM shows WHERE fk_magician_1_id = %s
OR fk_magician_2_id = %s;""" % (str(magician_id),
str(magician_id))
df = pd.read_sql_query(query, connection)
if len(df) > 0:
st.write(df)
else:
st.info("Sorry, no shows found 😥.")
import app_db_setup as dbs
connection = dbs.init_db()
cur = connection.cursor()
query = """INSERT INTO shows (year, month, fk_magician_1_id, fk_magician_2_id) VALUES
(2025, 3, (SELECT id FROM magicians WHERE name = 'Harry Potter'),
(SELECT id FROM magicians WHERE name = 'Jackie O'))"""
cur.execute(query)
query = """INSERT INTO shows (year, month, fk_magician_1_id, fk_magician_2_id) VALUES
(2089, 10, (SELECT id FROM magicians WHERE name = 'Harry Potter'),
(SELECT id FROM magicians WHERE name = 'Thor the Norse God'))"""
cur.execute(query)
query = """INSERT INTO shows (year, month, fk_magician_1_id, fk_magician_2_id) VALUES
(2091, 12, (SELECT id FROM magicians WHERE name = 'Thor the Norse God'),
(SELECT id FROM magicians WHERE name = 'Rumpelstilschen'))"""
cur.execute(query)
connection.close()
def save(response):
# Initialize sqlite database
connection = dbs.init_db()
#save inputs to db
dbs.save(connection, response)