Mod 13 Column Store DB

# Create SQLite DB

import sqlite3
import pandas

conn = sqlite3.connect('bomojobrandindices.db')
cur = conn.cursor()

# Drops the table you are going to create if it exists
conn.execute("""DROP TABLE IF EXISTS bomojobrandindices""")

# commit the changes to db
conn.commit()

# Creates a table that contains all the attributes in your data file
cur.execute("""CREATE TABLE bomojobrandindices (Brand TEXT, Total INTEGER, Releases INTEGER, "#1 Release" INTEGER, "Lifetime Gross" INTEGER)""")

# commit the changes to db
conn.commit()

df = pandas.read_csv('bomojobrandindices.csv')
print(df.columns)

df.to_sql('bomojobrandindices', conn, if_exists='append', index = False)

cur.execute("""SELECT * FROM bomojobrandindices""")
print(cur.fetchall())

# close connection
conn.close()
print('Connection closed.')

################################

# Create Column Store DB

import monetdbe
import pandas

# Path to the database file you want to create
database = 'bomojobrandindices.mdbe'

try:
    with monetdbe.connect(database) as conn:
        # Drops the table you are going to create if it exists
        conn.set_autocommit(True)
        conn.execute("""DROP TABLE IF EXISTS bomojobrandindices""")

        # Creates a table that contains all the attributes in your data file
        # attributes are Brand (string), Total (int), Releases (int), #1 Release (int),  Lifetime Gross (int)
        conn.execute("""CREATE TABLE bomojobrandindices
        (Brand TEXT, Total INTEGER, Releases INTEGER, "#1 Release" INTEGER, "Lifetime Gross" INTEGER)""")

        # Load the table created with the data from the data file you selected
        conn.execute(f"""COPY OFFSET 2 INTO bomojobrandindices FROM R'C:\\Users\Jackson McAfee\\Documents\\Programming\\mod13\\bomojobrandindices.csv' USING DELIMITERS ',' NULL AS '' BEST EFFORT""")

    with monetdbe.connect(database) as conn:
        # Print the results
        print("bomojobrandindices Load Errors")
        results = conn.execute("""SELECT * FROM sys.rejects""")
        for r in results.fetchall():
            print(r)

    conn.close()

except Exception as inst:
    print(type(inst))
    print(inst.args)

################################

# Query Good For Row Store

import sqlite3
import monetdbe
import time

# Path to the database file you want to access
database = 'bomojobrandindices.mdbe'

monetdb_conn = monetdbe.connect(database)

# Execute query and measure execution time
start_time_monetdb = time.time()

# Query monetdb
query_monetdb = 'SELECT AVG("Lifetime Gross") AS avg_lifetime_gross FROM bomojobrandindices'
result_monetdb = monetdb_conn.execute(query_monetdb).fetchall()

end_time_monetdb = time.time()
execution_time_monetdb = end_time_monetdb - start_time_monetdb

# Display results and execution time for MonetDB
print("Results from MonetDB:")
print(result_monetdb)
print("Execution Time (MonetDB): {:.6f} seconds".format(execution_time_monetdb))

# Connect to the SQLite database
sqlite_conn = sqlite3.connect('bomojobrandindices.db')
cur = sqlite_conn.cursor()

# Execute query and measure execution time
start_time_sqlite = time.time()

# query sqlite
query_sqlite = 'SELECT AVG("Lifetime Gross") AS avg_lifetime_gross FROM bomojobrandindices'
cur.execute(query_sqlite)
result_sqlite = cur.fetchall()

end_time_sqlite = time.time()
execution_time_sqlite = end_time_sqlite - start_time_sqlite

# Display results and execution time for SQLite
print("Results from SQLite:")
print(result_sqlite)
print("Execution Time (SQLite): {:.6f} seconds".format(execution_time_sqlite))

# Close connections
monetdb_conn.close()
sqlite_conn.close()

################################

# Query Good for Column Store

import sqlite3
import monetdbe
import time

# Path to the database file you want to access
database = 'bomojobrandindices.mdbe'

monetdb_conn = monetdbe.connect(database)

# Execute query and measure execution time
start_time_monetdb = time.time()

# Query monetdb
query_monetdb = '''SELECT SUM("Lifetime Gross") AS total_lifetime_gross, Brand
               FROM bomojobrandindices
               GROUP BY Brand
               ORDER BY total_lifetime_gross DESC;'''
result_monetdb = monetdb_conn.execute(query_monetdb).fetchall()

end_time_monetdb = time.time()
execution_time_monetdb = end_time_monetdb - start_time_monetdb

# Display results and execution time for MonetDB
print("Results from MonetDB:")
for row in result_monetdb:
    print("Brand:", row[1])
    print("Total Lifetime Gross:", row[0])
    print("-----")
print("Execution Time (MonetDB): {:.6f} seconds".format(execution_time_monetdb))

# Connect to the SQLite database
sqlite_conn = sqlite3.connect('bomojobrandindices.db')
cur = sqlite_conn.cursor()

# Execute query and measure execution time
start_time_sqlite = time.time()

# query sqlite
query_sqlite = '''SELECT SUM("Lifetime Gross") AS total_lifetime_gross, Brand
               FROM bomojobrandindices
               GROUP BY Brand
               ORDER BY total_lifetime_gross DESC;'''
cur.execute(query_sqlite)
result_sqlite = cur.fetchall()

end_time_sqlite = time.time()
execution_time_sqlite = end_time_sqlite - start_time_sqlite

# Display results and execution time for SQLite
print("Results from SQLite:")
for row in result_sqlite:
    print("Brand:", row[1])
    print("Total Lifetime Gross:", row[0])
    print("-----")
print("Execution Time (SQLite): {:.6f} seconds".format(execution_time_sqlite))

# Close connections
monetdb_conn.close()
sqlite_conn.close()