def query3(conn: MySQLConnection):
def preload_data():
return # no need to preload data, use the sample data from the database
preload_data()
cursor = conn.cursor()
sql = "SELECT (SELECT COUNT(DISTINCT cplate) FROM rent_records " \
"WHERE (DATE(date_from) BETWEEN DATE(%s) AND DATE(%s)) AND " \
"(TIME(date_from) BETWEEN TIME(%s) AND TIME(%s)))/(SELECT COUNT(*) FROM cars) AS Morning, " \
"(SELECT COUNT(DISTINCT cplate) FROM rent_records " \
"WHERE (DATE(date_from) BETWEEN DATE(%s) AND DATE(%s)) AND " \
"(TIME(date_from) BETWEEN TIME(%s) AND TIME(%s)))/(SELECT COUNT(*) FROM cars) AS Afternoon, " \
"(SELECT COUNT(DISTINCT cplate) FROM rent_records " \
"WHERE (DATE(date_from) BETWEEN DATE(%s) AND DATE(%s)) AND " \
"(TIME(date_from) BETWEEN TIME(%s) AND TIME(%s)))/(SELECT COUNT(*) FROM cars) AS Evening"
d1 = getstr(datetime(2018, 9, 1))
d2 = getstr(datetime(2018, 9, 7))
mor1 = time(7, 0)
mor2 = time(10, 0)
aft1 = time(12, 0)
aft2 = time(14, 0)
eve1 = time(17, 0)
eve2 = time(19, 0)
value = (d1, d2, mor1, mor2, d1, d2, aft1, aft2, d1, d2, eve1, eve2)
cursor.execute(sql, value)
return cursor.fetchall(), [i[0] for i in cursor.description]
def query2(conn: MySQLConnection):
cursor = conn.cursor()
date = datetime(2018, 10, 5, 0, 0, 0)
def preload_data():
# get five charging stations to be used for statistics
sql = "SELECT * FROM charging_stations LIMIT 5"
cursor.execute(sql)
# get ids and # of sockets of five stations
data = [station for station in cursor.fetchall()]
rand_stations_id = [x[0] for x in data]
no_of_socket = [x[3] for x in data]
sql = "INSERT INTO charging_station_sockets (station_id, no_of_available_sockets, date_time) " \
"VALUES (%s, %s, %s)"
date_times = [date + timedelta(hours=x) for x in range(24)]
for i in range(len(rand_stations_id)):
for date_time in date_times:
value = (rand_stations_id[i], randint(0, no_of_socket[i]),
getstr(date_time))
cursor.execute(sql, value)
conn.commit()
preload_data()
query = "SELECT CONCAT(HOUR(css.date_time), 'h-', HOUR(css.date_time) + 1, 'h') AS Period, " \
"SUM(cs.total_no_of_sockets - css.no_of_available_sockets) AS OccupiedSockets " \
"FROM charging_station_sockets AS css " \
"INNER JOIN charging_stations AS cs ON css.station_id = cs.id " \
"WHERE DATE(css.date_time) = DATE(%s) GROUP BY TIME(css.date_time)"
val = (getstr(date), )
cursor.execute(query, val)
return cursor.fetchall(), [i[0] for i in cursor.description]
def preload_data():
# get one deposit
sql = "SELECT id FROM deposits LIMIT 1"
cursor.execute(sql)
deposit_id = cursor.fetchone()[0]
# get the customer id
sql = "SELECT id FROM customers WHERE username = %s"
val = ("Liza", )
cursor.execute(sql, val)
customer_id = cursor.fetchone()[0]
# get one car plate
sql = "SELECT plate FROM cars LIMIT 1"
cursor.execute(sql)
cplate = cursor.fetchone()[0]
# delete previous payment records with trans_no between 1 and 31 to avoid insertion errors
sql = "DELETE FROM payment_records WHERE no_of_transaction BETWEEN 1 AND 30"
cursor.execute(sql)
conn.commit()
date = d1 + timedelta(hours=0)
# insert payment and rent records
for i in range(1, 31):
date_from = get_fake_date_time(start=date,
end=date + timedelta(days=1))
date_to = get_fake_date_time(start=date_from,
end=date_from + timedelta(hours=3))
sql = "INSERT INTO rent_records (date_from, date_to, cid, cplate, distance) " \
"VALUES (%s, %s, %s, %s, %s)"
val = (getstr(date_from), getstr(date_to), customer_id, cplate,
randint(10, 100))
cursor.execute(sql, val)
pay_time = get_fake_date_time(start=date_from, end=date_to)
sql = "INSERT INTO payment_records (no_of_transaction, date_time, cid, did, price) " \
"VALUES (%s, %s, %s, %s, %s)"
val = (i, getstr(pay_time), customer_id, deposit_id,
randint(10, 100))
cursor.execute(sql, val)
date = date_to
conn.commit()
def preload_data():
# get one customer_id to demostrate how our query works
sql = "SELECT id FROM customers WHERE username = %s"
val = ("Liza", )
cursor.execute(sql, val)
customer_id = cursor.fetchone()[0]
# get 10 car plates to add them to renting and charge records
sql = "SELECT plate FROM cars ORDER BY RAND() LIMIT 10"
cursor.execute(sql)
cplates = [car[0] for car in cursor.fetchall()]
# get one random charging station id to add it to charge records
sql = "SELECT id FROM charging_stations ORDER BY RAND() LIMIT 1"
cursor.execute(sql)
station_id = cursor.fetchone()[0]
rent_sql = "INSERT INTO rent_records (date_from, date_to, cid, cplate, distance) " \
"VALUES (%s, %s, %s, %s, %s)"
charge_sql = "INSERT INTO charge_records (date_time, sid, cplate, price) " \
"VALUES (%s, %s, %s, %s)"
for cplate in cplates:
# insert rent record
date_from = get_fake_date_time(start=start_date,
end=end_date -
timedelta(days=1, hours=3))
date_to = get_fake_date_time(start=date_from,
end=date_from +
timedelta(hours=2, minutes=59))
val = (getstr(date_from), getstr(date_to), customer_id, cplate,
randint(10, 100))
cursor.execute(rent_sql, val)
# insert charge record the same day of rent record
date_time = get_fake_date_time(start=date_from.date(),
end=date_from.date() +
timedelta(hours=12))
val = (getstr(date_time), station_id, cplate, randint(10, 100))
cursor.execute(charge_sql, val)
conn.commit()
def preload_data():
# get five charging stations to be used for statistics
sql = "SELECT * FROM charging_stations LIMIT 5"
cursor.execute(sql)
# get ids and # of sockets of five stations
data = [station for station in cursor.fetchall()]
rand_stations_id = [x[0] for x in data]
no_of_socket = [x[3] for x in data]
sql = "INSERT INTO charging_station_sockets (station_id, no_of_available_sockets, date_time) " \
"VALUES (%s, %s, %s)"
date_times = [date + timedelta(hours=x) for x in range(24)]
for i in range(len(rand_stations_id)):
for date_time in date_times:
value = (rand_stations_id[i], randint(0, no_of_socket[i]),
getstr(date_time))
cursor.execute(sql, value)
conn.commit()
def preload_data():
# get one charging station to be used for nearest_station attribute of customers
sql = "SELECT * FROM charging_stations LIMIT 1"
cursor.execute(sql)
station = cursor.fetchone()
sql = "DELETE FROM customers WHERE username = %s OR full_name = %s OR " \
"email = %s OR phone_number = %s OR bank_account = %s "
value = ("Liza", "Elizabeth Test", "*****@*****.**", "123456789",
"12341234")
cursor.execute(sql, value)
conn.commit()
# insert a customer into table
sql = "INSERT INTO customers (username, full_name, email, phone_number, " \
"bank_account, gps_location, address, nearest_station) " \
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
value = value + ("gps-location", "address", station[0])
cursor.execute(sql, value)
conn.commit()
customer_id = cursor.lastrowid
# get 5 car plates to add rent records between the customer and cars
sql = "SELECT * FROM cars LIMIT 5"
cursor.execute(sql)
rand_cplates = [car[0] for car in cursor.fetchall()]
# create 5 rent records between the customer and chosen cars on a specific day
date_time = date + timedelta(hours=0)
sql = "INSERT INTO rent_records (date_from, date_to, cid, cplate, distance) " \
"VALUES (%s, %s, %s, %s, %s)"
for i in range(5):
date_from = get_fake_date_time(start=date_time,
end=date_time + timedelta(hours=3))
date_to = get_fake_date_time(start=date_from,
end=date_time + timedelta(hours=3))
value = (getstr(date_from), getstr(date_to), customer_id,
rand_cplates[i], randint(1, 100))
cursor.execute(sql, value)
date_time = date_to
conn.commit()
# create a red car with using some car model and plate starting with 'AN'
sql = "SELECT * FROM car_models LIMIT 1"
cursor.execute(sql)
model = cursor.fetchone()
sql = "INSERT INTO cars (plate, cmodel, color) " \
"VALUES(%s, %s, %s)"
value = ("ANINAS123", model[0], "red")
cursor.execute(sql, value)
conn.commit()
# create a rent record between the customer and this car
sql = "INSERT INTO rent_records (date_from, date_to, cid, cplate, distance) " \
"VALUES (%s, %s, %s, %s, %s)"
date_from = get_fake_date_time(start=date_time,
end=date_time + timedelta(hours=3))
date_to = get_fake_date_time(start=date_from,
end=date_time + timedelta(hours=3))
value = (getstr(date_from), getstr(date_to), customer_id, "ANINAS123",
randint(1, 100))
cursor.execute(sql, value)
conn.commit()
def query4(conn: MySQLConnection):
cursor = conn.cursor()
d1 = datetime(2018, 9, 1)
d2 = datetime(2018, 9, 30)
def preload_data():
# get one deposit
sql = "SELECT id FROM deposits LIMIT 1"
cursor.execute(sql)
deposit_id = cursor.fetchone()[0]
# get the customer id
sql = "SELECT id FROM customers WHERE username = %s"
val = ("Liza", )
cursor.execute(sql, val)
customer_id = cursor.fetchone()[0]
# get one car plate
sql = "SELECT plate FROM cars LIMIT 1"
cursor.execute(sql)
cplate = cursor.fetchone()[0]
# delete previous payment records with trans_no between 1 and 31 to avoid insertion errors
sql = "DELETE FROM payment_records WHERE no_of_transaction BETWEEN 1 AND 30"
cursor.execute(sql)
conn.commit()
date = d1 + timedelta(hours=0)
# insert payment and rent records
for i in range(1, 31):
date_from = get_fake_date_time(start=date,
end=date + timedelta(days=1))
date_to = get_fake_date_time(start=date_from,
end=date_from + timedelta(hours=3))
sql = "INSERT INTO rent_records (date_from, date_to, cid, cplate, distance) " \
"VALUES (%s, %s, %s, %s, %s)"
val = (getstr(date_from), getstr(date_to), customer_id, cplate,
randint(10, 100))
cursor.execute(sql, val)
pay_time = get_fake_date_time(start=date_from, end=date_to)
sql = "INSERT INTO payment_records (no_of_transaction, date_time, cid, did, price) " \
"VALUES (%s, %s, %s, %s, %s)"
val = (i, getstr(pay_time), customer_id, deposit_id,
randint(10, 100))
cursor.execute(sql, val)
date = date_to
conn.commit()
preload_data()
query = "SELECT rr.id AS RentId, rr.date_to AS RentFromDateTime, rr.date_to AS RentToDateTime, " \
"pr.no_of_transaction AS NoOfTransaction, pr.date_time AS PaymentDateTime " \
"FROM rent_records AS rr " \
"INNER JOIN payment_records AS pr ON (pr.date_time BETWEEN rr.date_from AND rr.date_to) " \
"AND rr.cid = pr.cid " \
"INNER JOIN customers AS c ON rr.cid = c.id WHERE c.username = %s " \
"AND DATE(pr.date_time) BETWEEN DATE(%s) AND DATE(%s) " \
"GROUP BY rr.id HAVING COUNT(pr.no_of_transaction) > 1"
value = ("Liza", getstr(d1), getstr(d2))
cursor.execute(query, value)
return cursor.fetchall(), [i[0] for i in cursor.description]