def create_results_table():
m_con = mysqlDB()
sql_string_create_table = """
CREATE TABLE IF NOT EXISTS results (
task_id varchar(45)
,user_id varchar(45)
,project_id int(5)
,timestamp bigint(32)
,result int(1)
,wkt varchar(256)
,task_x varchar(45)
,task_y varchar(45)
,task_z varchar(45)
,duplicates int(5)
);
"""
sql_string_alter_table = 'ALTER TABLE results ADD PRIMARY KEY (task_id, user_id, project_id)'
# create table
m_con.query(sql_string_create_table, None)
m_con.query(sql_string_alter_table, None)
del m_con
print('created results table')
def get_project_contributors(project_id):
# establish mysql connection
m_con = mysqlDB()
# sql command
sql_query = '''
SELECT
count(distinct(user_id))
FROM
results
WHERE
project_id = %s
'''
data = [project_id]
# one row with one value will be returned
contributors = m_con.retr_query(sql_query, data)[0][0]
# delete/close db connection
del m_con
print('got contributors from mysql for project: %s. contributors = %s' %
(project_id, contributors))
logging.warning(
'got contributors from mysql for project: %s. contributors = %s' %
(project_id, contributors))
return contributors
def save_results_mysql(results_filename):
### this function saves the results from firebase to the mysql database
# pre step delete table if exist
m_con = mysqlDB()
sql_insert = 'DROP TABLE IF EXISTS raw_results CASCADE;'
m_con.query(sql_insert, None)
print('dropped raw results table')
# first import to a table where we store the geom as text
sql_insert = '''
CREATE TABLE raw_results (
task_id varchar(45)
,user_id varchar(45)
,project_id int(5)
,timestamp bigint(32)
,result int(1)
,wkt varchar(256)
,task_x varchar(45)
,task_y varchar(45)
,task_z varchar(45)
,duplicates int(5)
);
'''
m_con.query(sql_insert, None)
print('Created new table for raw results')
# copy data to the new table
# we should use LOAD DATA LOCAL INFILE Syntax
sql_insert = '''
LOAD DATA LOCAL INFILE 'raw_results.txt' INTO TABLE raw_results
'''
m_con.query(sql_insert, None)
os.remove(results_filename)
print('copied results information to mysql')
# second import all entries into the task table and convert into psql geometry
sql_insert = '''
INSERT INTO
results
SELECT
*
FROM
raw_results
ON DUPLICATE KEY
UPDATE results.duplicates = results.duplicates + 1
'''
m_con.query(sql_insert, None)
print(
'inserted raw results into results table and updated duplicates count')
del m_con
return
def delete_projects_mysql():
print('Delete all projects from MySQL...')
try:
m_con = mysqlDB()
sql_insert = "DELETE FROM projects"
m_con.query(sql_insert, None)
del (m_con)
print('Done')
return True
except Exception as e:
print(e)
return False
def delete_project_mysql(project_id):
try:
m_con = mysqlDB()
sql_insert = "DELETE FROM projects WHERE project_id = %s"
data = [int(project_id)]
# insert in table
m_con.query(sql_insert, data)
del m_con
logging.warning('deleted project info in mysql for project %s' % project_id)
return True
except:
logging.warning('failed to delete project info in mysql for project %s' % project_id)
return False
def insert_project_mysql(project):
try:
m_con = mysqlDB()
sql_insert = "INSERT INTO projects Values(%s,%s,%s)"
data = [int(project['id']), project['lookFor'], project['name']]
# insert in table
m_con.query(sql_insert, data)
del m_con
logging.warning('inserted project info in mysql for project %s' % project['id'])
return True
except:
logging.warning('failed to insert project info in mysql for project %s' % project['id'])
return False
def get_project_results(project_id):
# establish mysql connection
m_con = mysqlDB()
# sql command
sql_query = '''
select
task_id as id
-- ,results.user_id
,project_id as project
-- ,results.timestamp
,task_x
,task_y
,task_z
,avg(result) as decision
,SUM(CASE
WHEN result = 1 THEN 1
ELSE 0
END) AS yes_count
,SUM(CASE
WHEN result = 2 THEN 1
ELSE 0
END) AS maybe_count
,SUM(CASE
WHEN result = 3 THEN 1
ELSE 0
END) AS bad_imagery_count
,wkt
from
results
where
project_id = %s and result > 0
group by
task_id, wkt'''
data = [project_id]
project_results = m_con.retr_query(sql_query, data)
# delete/close db connection
del m_con
print('got results information from mysql for project: %s. rows = %s' %
(project_id, len(project_results)))
logging.warning(
'got results information from mysql for project: %s. rows = %s' %
(project_id, len(project_results)))
return project_results
def create_projects_table():
m_con = mysqlDB()
sql_string_create_table = """
CREATE TABLE IF NOT EXISTS projects (
project_id int(11)
,objective varchar(20)
,name varchar(45)
);
"""
sql_string_alter_table = 'ALTER TABLE projects ADD PRIMARY KEY (project_id);'
# create table
m_con.query(sql_string_create_table, None)
m_con.query(sql_string_alter_table, None)
del m_con
print('created projects table')