def transportation_visits():
#connect to db
conn = create_connection("sqlite.db")
#set output structure
conn.row_factory = lambda cursor, row: [row[0], row[1], row[2], row[3]]
cur = conn.cursor()
#execute sql query
cur.execute(
'select sum(plane) as Plane, sum(boat) as Boat, sum(train) as Train, sum(car) as Car from Data where country is not NULL and country not like "ΓΕΝΙΚΟ ΣΥΝΟΛΟ" and country not like ""'
)
total = cur.fetchall()
# data
x_bar = ["plane", "boat", "train", "car"]
y_bar = total[0]
print(y_bar)
# sort data (sort x by its cooresponding y)
sorted_categories = sorted(x_bar,
key=lambda x: y_bar[x_bar.index(x)],
reverse=True)
# plot
bar_chart = figure(x_range=sorted_categories,
title='Transportation Statistics',
x_axis_label='x',
y_axis_label='y',
plot_height=800,
plot_width=1400,
sizing_mode='stretch_both')
bar_chart.vbar(x_bar, top=y_bar, color='purple', width=0.3)
bar_chart.y_range.start = 0
output_file("transportation.html")
show(bar_chart)
def country_visits(limit):
#connect to database
conn = create_connection("sqlite.db")
#set output structure of the select query
conn.row_factory = lambda cursor, row: row[0]
cur = conn.cursor()
#get countries
cur.execute(
'select country from Data where country is not NULL and country not like "ΓΕΝΙΚΟ ΣΥΝΟΛΟ" and country not like "" GROUP BY country order by sum(total) desc limit '
+ str(limit))
countries = cur.fetchall()
#get sum total visits from every country
cur.execute(
'select sum(total) from Data where country is not NULL and country not like "ΓΕΝΙΚΟ ΣΥΝΟΛΟ" and country not like "" GROUP BY country order by sum(total) desc limit '
+ str(limit))
total = cur.fetchall()
# data
x_bar = countries
y_bar = total
#create plot
bar_chart = figure(x_range=x_bar,
title='Total Visits By Country',
x_axis_label='x',
y_axis_label='y',
plot_height=800,
plot_width=800,
sizing_mode='stretch_both')
bar_chart.vbar(x_bar, top=y_bar, color='blue', width=0.5)
bar_chart.y_range.start = 0
#create file and open in browser
output_file("countries.html")
show(bar_chart)
def trimino_visits():
conn = create_connection("sqlite.db")
conn.row_factory = lambda cursor, row: row[0]
cur = conn.cursor()
cur.execute(
'select sum(plane) as Plane, sum(boat) as Boat, sum(train) as Train, sum(car) as Car, sum(total) as Total from Data where country is not NULL and country not like "ΓΕΝΙΚΟ ΣΥΝΟΛΟ" and country not like ""'
)
total = cur.fetchall()
month_name = [
"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT",
"NOV", "DEC"
]
#execute a query for every Trimester in the 2011-2015 period
listTotal = []
listTrimino = []
for year in range(2011, 2015):
for trimino in range(0, 4):
#print(year , month_name[trimino*3+0], month_name[trimino*3+1], month_name[trimino*3+2])
cur = conn.cursor()
cur.execute(
' select sum(total) from Data where country="ΓΕΝΙΚΟ ΣΥΝΟΛΟ" and year=? and ( month=? or month=? or month=?)',
(year, month_name[trimino * 3 + 0],
month_name[trimino * 3 + 1], month_name[trimino * 3 + 2]))
total = cur.fetchall()
#add data to list
listTrimino.append(str(year) + "-" + str(trimino + 1))
listTotal.append(total[0])
#every Trimester seperate
x_bar = listTrimino
y_bar = listTotal
#added trimester of every year
x_bar = ["Trimino 1", "Trimino 2", "Trimino 3", "Trimino 4"]
y_bar = [0, 0, 0, 0]
for i in range(0, 13):
if i % 4 == 0: y_bar[0] = y_bar[0] + listTotal[i]
elif i % 4 == 1: y_bar[1] = y_bar[1] + listTotal[i]
elif i % 4 == 2: y_bar[2] = y_bar[2] + listTotal[i]
elif i % 4 == 3: y_bar[3] = y_bar[3] + listTotal[i]
# plot
bar_chart = figure(x_range=x_bar,
title='Visits every Trimester',
x_axis_label='x',
y_axis_label='y',
plot_height=800,
plot_width=1400,
sizing_mode='stretch_both')
bar_chart.vbar(x_bar, top=y_bar, color='green', width=0.3)
bar_chart.y_range.start = 0
#create file and open in browser
output_file("trimino.html")
show(bar_chart)
def total_visits():
#connect to database
conn = create_connection("sqlite.db")
#set output structure of the select query
conn.row_factory = lambda cursor, row: row[0]
cur = conn.cursor()
cur.execute('select SUM(total) from Data where country="ΓΕΝΙΚΟ ΣΥΝΟΛΟ"')
total = cur.fetchall()
return total[0]
def create_csv():
#connect to db
conn = create_connection("sqlite.db")
print("Exporting data into CSV........")
cursor = conn.cursor()
cursor.execute("select * from Data")
with open("data.csv", "w") as csv_file:
#write rows into data.csv file
csv_writer = csv.writer(csv_file, delimiter=",")
csv_writer.writerow([i[0] for i in cursor.description])
csv_writer.writerows(cursor)
def retrieve_url(short_id):
# create sql query and fit the variables into it
query = """
SELECT url FROM url_db
WHERE short_id = ?;
"""
# create sql db connection
connection = sql.create_connection(path)
# execute query to db
url = sql.execute_read_query(connection, query, [short_id])
return url
def create_short_id(short_id, url):
query = """
INSERT INTO
url_db (short_id, url)
VALUES
(?, ?);
"""
connection = sql.create_connection(path)
# execute
error = sql.execute_insert_query(connection, query, [short_id, url])
return error
# urls = retrieve_url('Ms54Rt')
# create_url = """
# INSERT INTO
# url_db (short_id, url)
# VALUES
# ('Ms54Rt', 'https://www.google.ca/');
# """
# connection = sql.create_connection(path)
# # error = sql.execute_query(connection, create_url)
# select_url = "SELECT * FROM url_db"
# urls = sql.execute_read_query(connection, select_url)
# for url in urls:
# print(url)
driver.get(chatLink)
print("Opening: ",chatLink," ...")
chatSearch = True
messageClasses = []
matchedClasses = [] #
sqlPath= "database.sqlite"
channelName=input("enter a channel name \n")
sqlConnection = sql.create_connection(sqlPath) # create/ connect to database file, assign file to a var
create_channels_table = """
CREATE TABLE IF NOT EXISTS channels (
id TEXT PRIMARY KEY
);
"""
create_users_table = """
CREATE TABLE IF NOT EXISTS users (
user_id TEXT PRIMARY KEY
);
"""
create_comments_table = """
CREATE TABLE IF NOT EXISTS comments (
if (read_data[i] == ','):
n = n + 1
l.append(i)
i = i + 1
else:
i = i + 1
l.append(i)
#print (read_data[l[0]+1:l[1]])
header = []
for z in range(
9
): # 0 = dates, 1 = Cat, 2 = subCat, 3 = Loc, 4 = Des, 5 = Amount, 6 = Status, 7 = Remark
if z != 6:
header.append(read_data[l[z] + 1:l[z + 1]])
#print (header[6])
conn = sql.create_connection(db_dir)
with conn:
if initial:
if conn is not None: #initialize category table
sql.create_table(conn, sql_create_category_table)
sql.create_table(conn, sql_create_location_table)
sql.create_table(conn, sql_create_description_table)
sql.create_table(conn, sql_create_status_table)
sql.create_table(conn, sql_create_expense_table)
else:
print("Error loading database")
'''
Category Start
'''
index1 = sql.query_CategoryID(conn, header[1])
if index1 is None:
sql_create_image_table = """ CREATE TABLE IF NOT EXISTS image (
id INTEGER PRIMARY KEY,
name TEXT,
date TEXT NOT NULL,
source TEXT,
explicit INTEGER
); """
sql_create_tag_table = """ CREATE TABLE IF NOT EXISTS tag (
id INTEGER PRIMARY KEY,
name text NOT NULL
); """
# create a database connection
conn = sql.create_connection(database)
if conn is not None:
# create image table
sql.create_table(conn, sql_create_image_table)
# create tag table
sql.create_table(conn, sql_create_tag_table)
else:
print("Error! cannot create the database connection")
def text_based():
with conn:
# user input
com = input("Enter command: ")