def get_route(lat_origine, lon_origine, lat_destinazione, lon_destinazione):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM public.\"Itinerari\" WHERE lat_origine={0} and lon_origine={1} "
"and lat_destinazione={2} and lon_destinazione={3}".format(lat_origine, lon_origine,
lat_destinazione, lon_destinazione))
route = cursor.fetchone()
if route is not None:
result = {
"id_route": route[0],
"departure": route[1],
"lat_dep": route[2],
"lon_dep": route[3],
"arrival": route[4],
"lat_arr": route[5],
"lon_arr": route[6],
"distance": route[7],
"duration": route[8]
}
else:
result = None
connection.close()
return result
def get_requests(previous_day):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
requests = []
if previous_day:
yesterday = str(date.today() - timedelta(days=1))
query = "SELECT * FROM public.\"Richieste\" WHERE ora_arrivo::date = '{0}'".format(yesterday)
else:
query = 'SELECT * FROM public."Richieste"'
cursor.execute(query)
for row in cursor:
requests.append(
Request(
id_request=row[0],
departure=row[1],
lat_dep=row[2],
lon_dep=row[3],
arrival=row[4],
lat_arr=row[5],
lon_arr=row[6],
time_dep=row[7],
time_arr=row[8],
user=row[9]
)
)
connection.close()
return requests
def generateAndVote():
db = DBConnection()
generate = db.query_and_return_array("""SELECT * FROM assignments, slow_snapshots, study_videos
WHERE
assignments.assignmentid = slow_snapshots.assignmentid
AND assignments.videoid = study_videos.videoid
AND workerid NOT LIKE 'photographer'
ORDER BY assignments.videoid, assignments.submit""")
generate_by_video = groupByKey(generate, lambda x: x['videoid'])
lags = []
for video in generate_by_video.keys():
print(video)
generates = list(generate_by_video[video])
# get third dude
third = generates[2]
generate_lag = third['submit'] - third['slow_available_time']
#print generate_lag
votes = db.query_and_return_array("""SELECT * FROM assignments,
slow_votes, study_videos WHERE assignments.videoid = %s AND assignments.assignmentid = slow_votes.assignmentid AND assignments.videoid = study_videos.videoid ORDER BY assignments.submit""", (video, ))
fifth = votes[4]
vote_lag = fifth['submit'] - fifth['slow_voting_available_time']
#print vote_lag
lags.append(float(generate_lag + vote_lag))
print(lags[-1])
print lags
print("\n\n\nRESULTS")
print("Median: %s" % numpy.median(lags))
print("Average: %s" % numpy.mean(lags))
print("Std. Dev: %s" % numpy.std(lags))
def uploadVideo(name, width, height):
db = DBConnection()
try:
sql = """INSERT INTO videos (filename, width, height, creationtime, enabled) VALUES (%s, %s, %s, %s, FALSE)"""
db.query_and_return_array(sql, (name, width, height, unixtime(datetime.now())))
except Exception, e:
print("Error writing video to database:")
print(e)
def test_delete_planet_not_found(self):
planet_id = 'xxxxxxxxxxxx'
table = self.create_table()
db = DBConnection()
response = db.delete_planet(planet_id)
assert bool(response) == False
def reset():
results = []
error = None
dbconnection = DBConnection()
conn = dbconnection.mongodb_conn()
if conn is not None:
dbconnection.delete_database()
return render_template('index.html', results=results, error=error)
def post(self):
planet = json.loads(self.request.body)
if 'planet_name' and 'climate' and 'terrain' not in planet:
raise Exception('Missing body arguments')
db = DBConnection()
db.insert_planet(planet)
# returnar uma mensagem de sucesso
self.write({'planet': planet})
def test_delete_slide(self):
db = DBConnection("../data_base/test_base.db")
self.assertTrue(db.get_slide_count() == 0)
db.insert_slide(1, "1")
self.assertTrue(db.get_slide_count() == 1)
self.assertTrue(db.get_slide_name(1) == "1")
db.delete_slide(1)
self.assertTrue(db.get_slide_count() == 0)
def get(self):
try:
db = DBConnection()
planets = db.retrieve_all_planets()
for planet in planets:
planet['films'] = fetch_films(planet['planet_name'])
self.write({'planets': planets})
except Exception as e:
logger.exception("Planets::get")
raise Exception('Error to get all planets')
def test_retrieve_planet_not_found(self):
planet_id = 'yyyyyyyyyyy'
planet = {
"planet_id": planet_id,
"planet_name": "Marcela",
"terrain": "Arenoso",
"climate": "Deserto"
}
self.create_table()
db = DBConnection()
response = db.retrieve_planet('planet_id', planet_id)
self.assertEquals(response, {})
def portPhotographer():
db = DBConnection()
snapshots = db.query_and_return_array(
"""SELECT slow_snapshots.pk, slow_snapshots.location, assignments.videoid
FROM slow_snapshots, assignments, study_videos WHERE slow_snapshots.assignmentid = assignments.assignmentid AND workerid LIKE 'photographer' AND study_videos.videoid = assignments.videoid ORDER BY assignments.videoid"""
)
for snapshot in snapshots:
print(snapshot)
db.query_and_return_array(
"""INSERT INTO algorithms (algorithm, videoid, location, detail) VALUES (%s, %s, %s, %s)""",
(PHOTOGRAPHER, snapshot['videoid'], snapshot['location'],
json.dumps(dict(snapshot_pk=snapshot['pk']))))
print("Done!")
def get_bus_id(license_plate):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("SELECT id FROM public.\"Mezzi\" WHERE targa = '{0}'".format(license_plate))
row = cursor.fetchone()
if row is not None:
id_bus = row[0]
else:
id_bus = None
connection.close()
return id_bus
def portGenerateAndVote():
"""ports information into the algorithms table for the generate and vote condition"""
db = DBConnection()
sql = """SELECT vote_assignments.pk, study_videos.videoid, vote FROM study_videos
LEFT JOIN (SELECT slow_votes.pk, slow_votes.assignmentid, videoid, vote FROM slow_votes, assignments WHERE slow_votes.assignmentid = assignments.assignmentid) AS vote_assignments
ON vote_assignments.videoid = study_videos.videoid
WHERE slow_voting_available = TRUE ORDER BY vote_assignments.pk"""
votes = db.query_and_return_array(sql)
# we need to take just the first five votes for each video
grouped_votes = groupAssignmentsByKey(votes, lambda x: x['videoid'])
videoids = sorted(grouped_votes.keys())
votes = []
for videoid in videoids:
video_votes = sorted(list(grouped_votes[videoid]),
key=lambda x: x['pk'])
num_votes = 0
winner = None
while winner is None:
if len(video_votes) < num_votes:
print("Not enough votes to decide this case: %s", video_votes)
return
num_votes += 1
(winner, counts) = checkForWinner(video_votes[:num_votes])
vote = dict()
vote['videoid'] = videoid
vote['slow_vote_pks'] = [
vote['pk'] for vote in video_votes[:num_votes]
]
vote['count'] = counts
vote['winner'] = (Decimal(str(winner)) / 100) - Decimal(
'.01'
) # off by one frame error from jpeg filename to slider position
votes.append(vote)
print vote
# num_voters = sorted([len(vote['slow_vote_pks']) for vote in votes])
for vote in votes:
db.query_and_return_array(
"""INSERT INTO algorithms (algorithm, videoid, location, detail) VALUES (%s, %s, %s, %s)""",
(GENERATE_AND_VOTE, vote['videoid'], vote['winner'],
json.dumps(vote, cls=StudyDecimalEncoder)))
print("Done")
def test_retrieve_planet(self):
planet_id = 'xxxxxxxxxxxx'
planet = {
"planet_id": planet_id,
"planet_name": "Renata",
"terrain": "Arenoso",
"climate": "Deserto"
}
table = self.create_table()
table.put_item(Item=planet)
db = DBConnection()
response = db.retrieve_planet('planet_id', planet_id)
self.assertEquals(response, planet)
def sign_in(email, user_password):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
username = None
cursor.execute("SELECT username FROM public.\"Utenti\" WHERE email = '{0}' and password = '******'"
"".format(email, user_password))
row = cursor.fetchone()
if row is not None:
username = row[0]
connection.close()
return username
def delete(self, planet_id):
try:
db = DBConnection()
result = db.delete_planet(planet_id)
if not result:
self.set_status(404)
self.finish("Planet not found")
return
self.write(
{'message': 'Planet with id={} was deleted'.format(planet_id)})
except Exception as e:
logger.exception("DeletePlanet::delete")
raise Exception('Error to delete planet')
def test_delete_planet(self):
planet_id = 'yyyyyyyyyyy'
planet = {
"planet_id": planet_id,
"planet_name": "Marcela",
"terrain": "Arenoso",
"climate": "Deserto"
}
table = self.create_table()
table.put_item(Item=planet)
db = DBConnection()
response = db.delete_planet(planet_id)
assert bool(response) == True
def getPhotos(filename):
db = DBConnection()
locations = db.query_and_return_array(
"""SELECT DISTINCT location FROM pictures, videos WHERE pictures.videoid = videos.pk AND videos.filename = %s""",
(filename, ))
# don't forget off-by-one: slider starts at 0, but first photo is photo1.jpg
filenumbers = [
'%03d' % min(int(row['location'] * 100 + 1), 100) for row in locations
]
return [
VIDEO_HOST_DIRECTORY + filename + filenumber + '.jpg'
for filenumber in filenumbers
]
def get_bus_stops():
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
result = []
cursor.execute("SELECT luogo, lat, lon FROM public.\"Fermate\"")
bus_stops = cursor.fetchall()
if bus_stops is not None:
for row in bus_stops:
result.append({"place": row[0], "lat": row[1], "lon": row[2]})
connection.close()
return result
def verify_user(cls, email, password):
db_conn = DBConnection.get_connection()
db_cursor = db_conn.cursor()
try:
query = """SELECT password, first_name, last_name, email,id FROM users
WHERE is_active = True and email = %s"""
db_cursor.execute(query, (email, ))
record = db_cursor.fetchone()
result = db_cursor.rowcount
if result > 0:
# return True
if check_password_hash(record[0], password):
return record
else:
return False
else:
return False
except (Exception, psycopg2.Error) as error:
raise UserNotFoundError(error)
finally:
db_conn.close()
def validationAgreed(chosen_image):
db = DBConnection()
good = getImages(db, True)
print(good)
print(chosen_image)
return chosen_image in good
def get_website_urls(cls, user_id):
db_conn = DBConnection.get_connection()
db_cursor = db_conn.cursor()
try:
query = """
SELECT DISTINCT ta.id, website_category,website_link, (CASE WHEN news_account_id IS NOT NULL THEN True ELSE False END) "news_account_id"
FROM website_configuration ta LEFT JOIN user_newssite_account_mapping tam ON ta.id = tam.news_account_id
WHERE ta.is_active = True AND tam.user_id=%s
UNION
SELECT ta.id, website_category, website_link, False "news_account_id"
FROM website_configuration ta WHERE ta.id NOT IN(SELECT DISTINCT ta.id
FROM website_configuration ta LEFT JOIN user_newssite_account_mapping tam ON ta.id = tam.news_account_id
WHERE ta.is_active = True AND tam.user_id=%s) ORDER BY news_account_id DESC"""
db_cursor.execute(query, (user_id, user_id))
news_records = db_cursor.fetchall()
return news_records
except (Exception, psycopg2.Error) as error:
raise ErrorFound(error)
finally:
db_conn.close()
def get_twitter_account(cls, user_id):
db_conn = DBConnection.get_connection()
db_cursor = db_conn.cursor()
try:
query = """
SELECT ta.id, screen_id, screen_name, country,(CASE WHEN twitter_account_id IS NOT NULL THEN True ELSE False END) "twitter_account_id"
FROM twitter_account ta LEFT JOIN user_twitter_account_mapping tam ON ta.id = tam.twitter_account_id
WHERE ta.is_active = True AND tam.user_id=%s
UNION
SELECT ta.id, screen_id, screen_name, country,False "twitter_account_id"
FROM twitter_account ta WHERE ta.id NOT IN(SELECT DISTINCT ta.id
FROM twitter_account ta LEFT JOIN user_twitter_account_mapping tam ON ta.id = tam.twitter_account_id
WHERE ta.is_active = True AND tam.user_id=%s) ORDER BY twitter_account_id DESC"""
db_cursor.execute(query, (user_id, user_id))
twiter_records = db_cursor.fetchall()
return twiter_records
except (Exception, psycopg2.Error) as error:
raise ErrorFound(error)
finally:
db_conn.close()
def add_website_url(cls, website_url, user_id):
db_conn = DBConnection.get_connection()
db_cursor = db_conn.cursor()
try:
# check if url already exists
query = """SELECT id FROM user_monitor_newssite
WHERE website_url = %s and user_id=%s"""
db_cursor.execute(query, (website_url, user_id))
record = db_cursor.fetchone()
result = db_cursor.rowcount
if result > 0:
query = """UPDATE user_monitor_newssite SET is_active = True WHERE website_url = %s and user_id=%s"""
db_cursor.execute(query, (website_url, user_id))
result = db_cursor.rowcount
else:
query = """ INSERT INTO user_monitor_newssite (user_id,website_url)
VALUES (%s,%s) """
db_cursor.execute(query, (user_id, website_url))
result = db_cursor.rowcount
return result
except (Exception, psycopg2.Error) as error:
raise ErrorFound(error)
finally:
db_conn.commit()
db_conn.close()
def map_user_website(cls, selected_website_url, user_id):
db_conn = DBConnection.get_connection()
db_cursor = db_conn.cursor()
try:
# delete existing mapping records
query = """DELETE FROM user_newssite_account_mapping
WHERE user_id = %s"""
db_cursor.execute(query, (user_id, ))
result = db_cursor.rowcount
if len(selected_website_url) > 0:
# insert map twitter account
query = """ INSERT INTO user_newssite_account_mapping (user_id,news_account_id)
VALUES (%s,%s) """
for website_id in selected_website_url:
db_cursor.execute(query, (user_id, website_id))
result = db_cursor.rowcount
return result
except (Exception, psycopg2.Error) as error:
raise ErrorFound(error)
finally:
db_conn.commit()
db_conn.close()
def get_indices_data(cls):
db_conn = DBConnection.get_connection()
db_cursor = db_conn.cursor()
query = """ SELECT DISTINCT ind.index_symbol FROM indices ind JOIN indices_data idata
ON ind.id = idata.index_id WHERE ind.id in(1,2)
ORDER BY index_symbol DESC """
db_cursor.execute(query)
unique_indices_list = db_cursor.fetchall()
columns = ['country','index_symbol','entry_date','lat','lon','open','adj_close','adj_close_change','percent_change']
indices_data_list = []
for row in unique_indices_list:
query = """ CREATE TEMPORARY TABLE indices_data_temp
(
index_id INT,
index_name VARCHAR(100) NOT NULL,
index_symbol VARCHAR(50) NOT NULL,
entry_date DATE,
lat VARCHAR(30) NOT NULL,
lon VARCHAR(30) NOT NULL,
open DECIMAL,
adj_close DECIMAL
);
INSERT INTO indices_data_temp
SELECT DISTINCT idata.index_id,ind.country,ind.index_symbol,idata.entry_date,ind.lat,ind.lon,ROUND(idata.open,2) open,ROUND(idata.adj_close,2) adj_close
FROM indices ind JOIN indices_data idata ON ind.id = idata.index_id;
select ind.country,ind.index_symbol,idata.entry_date,ind.lat,ind.lon,ROUND(idata.open,2) open,
ROUND(idata.adj_close,2) adj_close,
ROUND(idata.adj_close,2) - lag(ROUND(idata.adj_close,2)) over (order by idata.entry_date, ind.index_symbol) as adj_close_change,
round(((ROUND(idata.adj_close,2) - lag(ROUND(idata.adj_close,2)) over (order by idata.entry_date, ind.index_symbol))/lag(ROUND(idata.adj_close,2))
over (order by idata.entry_date, ind.index_symbol))*100,2) as percent_change
FROM indices ind JOIN indices_data_temp idata ON ind.id = idata.index_id WHERE idata.index_symbol = %s
ORDER BY ind.index_symbol,idata.entry_date DESC;"""
db_cursor.execute(query,(row[0],))
indices_data = db_cursor.fetchall()
row_count = db_cursor.rowcount
if row_count > 0:
indices_data_list.append(indices_data[0])
indices_map_data = pd.DataFrame(indices_data_list,columns=columns)
indices_map_data['adj_close'] = indices_map_data['adj_close'].astype(float)
indices_map_data['adj_close_change'] = indices_map_data['adj_close_change'].astype(float)
indices_map_data['percent_change'] = indices_map_data['percent_change'].astype(float)
indices_map_data.sort_values(by=['entry_date','country'], inplace=True, ascending=False)
query = "drop table indices_data_temp;"
db_cursor.execute(query)
return list(indices_map_data.itertuples(index=False))
def set_user_session_data(self, user_id, session_key, session_data):
db_conn = DBConnection.get_connection()
db_cursor = db_conn.cursor()
try:
# delete existing session
query = """DELETE FROM user_session_data WHERE user_id = %s and session_key = %s"""
db_cursor.execute(query, (user_id, session_key))
result = db_cursor.rowcount
# insert sessin data
if session_data != "":
query = """ INSERT INTO user_session_data (user_id,session_key,session_data) VALUES (%s,%s,%s)"""
record_to_insert = (user_id, session_key, session_data)
db_cursor.execute(query, record_to_insert)
result = db_cursor.rowcount
return result
except (Exception, psycopg2.Error) as error:
raise UserNotFoundError(error)
finally:
db_conn.commit()
db_conn.close()
def loadObjects():
sql = "select obj_name, obj_description, til_path, obj_til_x, obj_til_y "
sql += "from object "
sql += "inner join tileset on obj_til_xid = til_id ";
rows = DBConnection.getResult(sql)
dic = {}
text = {}
for obj in rows:
name = obj[0]
decription = obj[1]
region = obj[3] * TILE_SIZE[0], obj[4] * TILE_SIZE[0], TILE_SIZE[0], TILE_SIZE[1]
path = obj[2]
img = ServerConnection.getImage(path,region)
text[name]= decription
dic[name] = img
global OBJECTS
global TEXTS
OBJECTS.update(dic)
TEXTS['object'].update(text)
def loadEnemies():
sql = "select ene_name, ene_description, til_path, ene_til_x, ene_til_y "
sql += "from enemy "
sql += "inner join tileset on ene_til_xid = til_id ";
rows = DBConnection.getResult(sql)
dic = {}
text = {}
for ene in rows:
name = ene[0]
decription = ene[1]
region = ene[3] * TILE_SIZE[0], ene[4] * TILE_SIZE[0], TILE_SIZE[0], TILE_SIZE[1]
path = ene[2]
img = ServerConnection.getImage(path,region)
dic[name] = img
text[name]= decription
global ENEMIES
global TEXTS
ENEMIES.update(dic)
TEXTS['enemy'].update(text)
def __downloadFile(self,path):
dwl = False
if not os.path.exists(self.__main_directory + path):
dwl = True
else:
t = time.gmtime(os.path.getmtime(self.__main_directory + path))
t = datetime.datetime.fromtimestamp(time.mktime(t))
sql = "select upf_update_date "
sql += "from update_file "
sql += "where upf_path = '" + str(path) + "' "
data = DBConnection.getResult(sql)
if len(data) > 0:
if t <= data[0][0]:
dwl = True
if dwl:
u = urllib2.urlopen(SERVER + path)
try:
f = open(self.__main_directory + path,'w')
try:
f.write(u.read())
finally:
f.close()
finally:
u.close()
return self.__main_directory + path
def getAssignments(experiments):
""" Queries the database for all the assignments completed in this experiment, and populates the array with all relevant timestamps """
db = DBConnection()
experimentString = ', '.join(
[str(experiment) for experiment in experiments])
results = db.query_and_return_array(
"""SELECT * from assignments a, workers w, hits h WHERE experiment IN ("""
+ experimentString +
""") AND a.workerid = w.workerid AND a.hitid = h.hitid """)
assignments = []
""" For each assignment, get its completion information """
for row in results:
assignment = Assignment()
assignment.workerid = row['workerid']
assignment.assignmentid = row['assignmentid']
assignment.wait_bucket = row['waitbucket']
assignment.precision = row['precision']
assignment.recall = row['recall']
assignment.condition = condition.getConditionName(
bool(row['is_alert']), bool(row['is_reward']),
bool(row['is_tetris']))
if row['first'] is not None:
assignment.answer_time = datetime.fromtimestamp(row['first'])
# when did the worker accept that task?
if row['accept'] is not None:
assignment.accept_time = datetime.fromtimestamp(row['accept'])
# when did the task or 'go' button appear to the user?
if row['show'] is not None:
assignment.show_time = datetime.fromtimestamp(row['show'])
# when did the worker click the "go" button?
if row['go'] is not None:
assignment.go_time = datetime.fromtimestamp(row['go'])
if row['submit'] is not None:
assignment.submit_time = datetime.fromtimestamp(row['submit'])
assignments.append(assignment)
print("Total number of assignments: %s" % (len(assignments)))
return assignments
def parseVideos(study_videos_only=True):
"""
Grabs each video from the DB and tells us information
about its lag
"""
db = DBConnection()
if study_videos_only:
all_videos = db.query_and_return_array(
"""SELECT pk, filename FROM videos, study_videos WHERE videos.pk = study_videos.videoid"""
)
else:
all_videos = db.query_and_return_array(
"""SELECT pk, filename FROM videos""")
printPhaseTimes(db, study_videos_only, all_videos)
print('\n')
printVideoTimes(db, study_videos_only)
def randomizeConditions(do_you_mean_it="NO"):
if do_you_mean_it != "YES_I_MEAN_IT":
print """
This will assign all workerids to a new random condition. Do not do this lightly!\n
Call with randomizeConditions('YES_I_MEAN_IT')
"""
return 0
else:
print("Assigning all workers to a new random condition")
db = DBConnection()
# get all workers, randomize them one by one
result = db.query_and_return_array("""SELECT workerid FROM workers""")
workers = [row['workerid'] for row in result]
for worker in workers:
setRandomCondition(worker)
def getSlowPhotos(videoid, limit):
db = DBConnection()
locations = db.query_and_return_array(
"""SELECT filename, location FROM slow_snapshots, assignments, videos WHERE slow_snapshots.assignmentid = assignments.assignmentid AND videos.pk = assignments.videoid AND assignments.videoid = %s ORDER BY slow_snapshots.pk ASC LIMIT %s""",
(videoid, limit))
filename = locations[0]['filename']
# don't forget off-by-one: slider starts at 0, but first photo is photo1.jpg
filenumbers = set([
'%03d' % min(int(row['location'] * 100 + 1), 100) for row in locations
]) # use set() so we don't get copies
return [
VIDEO_HOST_DIRECTORY + filename + filenumber + '.jpg'
for filenumber in filenumbers
]
def portTakeFirst():
db = DBConnection()
sql = """SELECT slow_snapshots.pk, slow_snapshots.location, assignments.videoid FROM slow_snapshots, assignments WHERE slow_snapshots.pk IN
(SELECT MIN(slow_snapshots.pk) FROM slow_snapshots, assignments, study_videos WHERE assignments.assignmentid = slow_snapshots.assignmentid AND assignments.videoid = study_videos.videoid AND workerid NOT LIKE 'photographer' GROUP BY study_videos.videoid)
AND assignments.assignmentid = slow_snapshots.assignmentid ORDER BY assignments.videoid ASC
"""
snapshots = db.query_and_return_array(sql)
for snapshot in snapshots:
print(snapshot)
db.query_and_return_array(
"""INSERT INTO algorithms (algorithm, videoid, location, detail) VALUES (%s, %s, %s, %s)""",
(GENERATE_FIRST, snapshot['videoid'], snapshot['location'],
json.dumps(dict(snapshot_pk=snapshot['pk']))))
print("Done!")
def printCurrentlyActiveCount():
ping_floor = datetime.now() - timedelta(seconds=15)
ping_types = ["ping-waiting", "ping-showing", "ping-working"]
db = DBConnection()
results = dict()
for ping_type in ping_types:
row = db.query_and_return_array(
"""SELECT COUNT(DISTINCT assignmentid) FROM logging WHERE event='%s' AND servertime >= %s"""
% (ping_type, unixtime(ping_floor)))[0]
results[ping_type] = row['COUNT(DISTINCT assignmentid)']
print(ping_type + ": unique assignmentIds pings in last 15 seconds: " +
str(results[ping_type]))
return results
def getCondition(worker_id):
""" Sees if the worker has already been assigned a condition, and if not, assigns them """
db = DBConnection()
rows = db.query_and_return_array(
"""SELECT is_alert, is_reward, is_tetris FROM workers WHERE workerid = %s """,
(worker_id, ))
if len(rows) == 0: # not in database yet
result = setRandomCondition(worker_id)
else:
result = rows[0]
is_alert = bool(result['is_alert'])
is_reward = bool(result['is_reward'])
is_tetris = bool(result['is_tetris'])
return {'isAlert': is_alert, 'isReward': is_reward, 'isTetris': is_tetris}
def setRandomCondition(worker_id):
""" Chooses a random group to assign the worker to, and sets it in the database """
db = DBConnection()
count = db.query_and_return_array(
"""SELECT COUNT(*) FROM workers""")[0]['COUNT(*)']
index = count % len(CONDITIONS)
random_condition = CONDITIONS[index]
db.query_and_return_array(
"""INSERT INTO workers (workerid, is_alert, is_reward, is_tetris, read_instructions) VALUES (%s, %s, %s, %s, FALSE) ON DUPLICATE KEY UPDATE is_alert=%s, is_reward=%s, is_tetris=%s, read_instructions=FALSE""",
(worker_id, random_condition['is_alert'],
random_condition['is_reward'], random_condition['is_tetris'],
random_condition['is_alert'], random_condition['is_reward'],
random_condition['is_tetris']))
return random_condition
def test_insert_planet(self):
planet = {
"planet_name": "Marino",
"terrain": "blah",
"climate": "Seco"
}
table = self.create_table()
db = DBConnection()
db.insert_planet(planet)
response = table.scan(FilterExpression=Attr('planet_name').eq('Marino'))
if 'Items' in response:
item = response['Items'][0]
self.assertTrue('planet_id' in item)
self.assertEquals(item['planet_name'], 'Marino')
self.assertEquals(item['terrain'], 'blah')
self.assertEquals(item['climate'], 'Seco')
def test_get_slide_name(self):
db = DBConnection("../data_base/test_base.db")
db.insert_slide("1","1.jpg")
db.insert_object("1","LOL")
disp = InfoDisplayer(db, "TestImages/")
name = disp.get_slide_name(1)
self.assertTrue(name == "1.jpg")
db.clear()
def check_user(username, email):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
result = {"username": None, "email": None}
cursor.execute("SELECT username FROM public.\"Utenti\" WHERE username = '******'".format(username))
row = cursor.fetchone()
if row is not None:
result["username"] = row[0]
cursor.execute("SELECT email FROM public.\"Utenti\" WHERE email = '{0}'".format(email))
row = cursor.fetchone()
if row is not None:
result["email"] = row[0]
connection.close()
return result
def get_buses():
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
buses = []
cursor.execute('SELECT * FROM public."Mezzi"')
for row in cursor:
buses.append(
Bus(
id_bus=row[0],
license_plate=row[1],
seats=row[2],
place=row[3],
lat=row[4],
lon=row[5]
)
)
connection.close()
return buses
def get_movements(id_fitness, id_mezzo):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("SELECT * FROM public.\"Spostamenti\" WHERE id_fitness='{0}' and id_mezzo='{1}'"
"ORDER BY id".format(id_fitness, id_mezzo))
spostamenti = cursor.fetchall()
result = []
for spostamento in spostamenti:
result.append({
"id_movement": spostamento[0],
"id_fitness": spostamento[1],
"id_bus": spostamento[2],
"place": spostamento[3],
"lat": spostamento[4],
"lon": spostamento[5]
})
connection.close()
return result
def loadTileset():
dic = {}
sql = "select til_name, til_path, til_type "
sql += "from tileset "
rows = DBConnection.getResult(sql)
tiles = { 'wall-nw': (0,3),
'wall-n': (1,3),
'wall-ne': (2,3),
'wall-w': (0,2),
'wall-e': (2,2),
'wall-sw': (0,1),
'wall-s': (1,1),
'wall-se': (2,1),
'door-n': (3,3),
'door-s': (3,2),
'ground': (1,2),
'ground-alt1': (0,0),
'ground-alt2': (1,0),
'ground-alt3': (2,0),
'empty': (3,0)
}
for row in rows:
name = row[0]
path = row[1]
type_tileset = row[2]
if type_tileset == 'room':
img = ServerConnection.getImage(path)
tileset = cocos.tiles.TileSet(name, None)
for n, p in tiles.items():
prop = { 'passable': True,
'transition' : False
}
if n == 'door-n':
prop['transition'] = True
tileset.add( prop, getTile(img,p), n)
dic[name] = tileset
else:
ServerConnection.getClientPath(path)
global TILESETS
TILESETS.update(dic)
def test_display(self):
db = DBConnection("../data_base/test_base.db")
db.insert_slide("1","1.jpg")
db.insert_object("1","LOL")
disp = InfoDisplayer(db, "TestImages/")
im = cv2.imread("TestImages/1.jpg")
im2 = cv2.imread("TestImages/2.jpg")
im2 = disp.display(1,im2)
db.clear()
self.assertTrue(len(im) == len(im2))
for x in range(im.shape[0]):
for y in range(im.shape[1]):
self.assertTrue(im[x][y][0]==im2[x][y][0])
def insert_coordinates(license_plate, travel_date, lat, lon):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO public.\"Posizioni_Mezzi\"(mezzo, data_viaggio, lat, lon) "
"VALUES ('{0}', '{1}', {2}, {3})".format(license_plate, travel_date, lat, lon))
connection.commit()
connection.close()
def insert_movement(id_fitness, id_mezzo, lat, lon, luogo=''):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO public.\"Spostamenti\"(id_fitness, id_mezzo, luogo, lat, lon) "
"VALUES ('{0}', {1}, '{2}', {3}, {4})".format(id_fitness, id_mezzo, luogo, lat, lon))
connection.commit()
connection.close()
def sign_up(name, surname, email, username, user_password):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO public.\"Utenti\"(username, email, nome, cognome, password) VALUES ("
"'{0}','{1}','{2}','{3}','{4}')".format(username, email, name, surname, user_password))
connection.commit()
connection.close()
def test():
game = cocos.director.director.init(width=SCREEN_SIZE[0], height=SCREEN_SIZE[1], caption=TITLE)
logo = ServerConnection.getImage('img/gui/logo.png')
game.set_icon(logo)
try:
user = DBConnection.getUser(getpass.getuser())
user = User(user)
hero = user.getHero()
dungeon = Dungeon(hero)
game.push_handlers(dungeon)
credits_scene = creditsScene()
#main scene
main_command = [
('Play',play,[dungeon[0]]),
('Credits',play,[credits_scene]),
('Quit',game.close,[])
]
main_scene = cocos.scene.Scene()
menu = MainMenu(main_command)
#Title
label = cocos.text.Label(TITLE,position = (400,500), font_name = 'Drakoheart Leiend', font_size = 45, anchor_x = 'center')
main_scene.add(label)
main_scene.add(menu)
#music
bgm = ServerConnection.getMusic('bgm/main_screen.ogg')
bgm_player = pyglet.media.Player()
bgm_player.queue(bgm)
bgm_player.eos_action = bgm_player.EOS_LOOP
bgm_player.play()
cocos.director.director.run(main_scene)
except UDungeonException as ude:
print ude.message
def insert_route(lat_ori, lon_ori, lat_dest, lon_dest, distanza, durata, origine='', destinazione=''):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO public.\"Itinerari\"(origine, lat_origine, lon_origine, destinazione,"
" lat_destinazione, lon_destinazione, distanza, durata) VALUES ('{0}',{1},{2},'{3}',{4},{5},{6},{7})"
"".format(origine, lat_ori, lon_ori, destinazione, lat_dest, lon_dest, distanza, durata))
connection.commit()
connection.close()
def test_get_first(self):
db = DBConnection("../data_base/test_base.db")
db.insert_slide("1","1.jpg")
db.insert_slide("2","2.jpg")
db.insert_object("1","LOL")
db.insert_object("2","LOL")
ob1 = (1,"LOL")
ob2 = (2,"LOL")
ob3 = (3,"LOL")
disp = InfoDisplayer(db, "TestImages/")
out = disp.get_first(ob1)
self.assertTrue(out == "1.jpg")
out = disp.get_first(ob2)
self.assertTrue(out == "2.jpg")
out = disp.get_first(ob3)
self.assertTrue(out == "")
db.clear()
def insert_request(lat_departure, lon_departure, lat_arrival, lon_arrival, time_departure, time_arrival,
request_user, departure='', arrival=''):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO public.\"Richieste\"(origine, lat_origine, lon_origine, destinazione, "
"lat_destinazione, lon_destinazione, ora_partenza, ora_arrivo, utente) "
"VALUES('{0}', {1}, {2}, '{3}', {4}, {5}, '{6}', '{7}', '{8}') RETURNING id".format(departure,
lat_departure, lon_departure, arrival, lat_arrival, lon_arrival, time_departure, time_arrival,
request_user))
request_inserted_id = cursor.fetchone()[0]
connection.commit()
connection.close()
return request_inserted_id
def __init__(self, master=None):
self.db = DBConnection("data_base/base.db")
tk.Frame.__init__(self, master)
self.pack()
self.run_detector = tk.Button(self, text="Run detector", command=self.run)
self.object_listbox = tk.Listbox(self)
self.slide_listbox = tk.Listbox(self)
self.object_id_label = tk.Label(self, text="Id:")
self.object_name_label = tk.Label(self, text="Name:")
self.object_id_entry = tk.Entry(self)
self.object_name_entry = tk.Entry(self)
self.file_name_label = tk.Label(self, text="File name:")
self.object_name_label2 = tk.Label(self, text="Object name:")
self.file_name_entry = tk.Entry(self)
self.variable = tk.StringVar(self)
self.object_name_optionmenu = tk.OptionMenu(self, self.variable, '')
self.add_object_button = tk.Button(self, text="Add object", command=self.add_object)
self.delete_object_button = tk.Button(self, text="Delete object", command=self.delete_object)
self.delete_slide_button = tk.Button(self, text="Delete slide", command=self.delete_slide)
self.add_slide_button = tk.Button(self, text="Add slide", command=self.add_slide)
self.quit = tk.Button(self, text="Quit", fg="red", command=root.destroy)
self.dict = None
self.place_widgets()
self.set_widgets()
def get_coordiantes(license_plate, travel_date):
connection = DBConnection(host, port, database, user, password).connect()
cursor = connection.cursor()
date_from = "{0} 00:00:00".format(travel_date)
date_to = "{0} 23:59:59".format(travel_date)
cursor.execute("SELECT lat, lon FROM public.\"Posizioni_Mezzi\" WHERE mezzo = '{0}' AND "
"(data_viaggio, data_viaggio) OVERLAPS ('{1}', '{2}')".format(license_plate, date_from, date_to))
data = cursor.fetchall()
coordinates = []
for row in data:
coordinates.append([row[0], row[1]])
connection.commit()
connection.close()
return coordinates
def test_get_slide_list(self):
db = DBConnection("../data_base/test_base.db")
self.assertTrue(db.get_slide_count() == 0)
db.insert_slide(1, "1")
db.insert_slide(2, "2")
db.insert_slide(3, "3")
db.insert_slide(4, "4")
db.insert_slide(5, "5")
db.insert_slide(6, "6")
self.assertTrue(db.get_slide_count() == 6)
test_list = db.get_slide_list()
self.assertTrue(test_list[0][2] == "1")
self.assertTrue(test_list[1][2] == "2")
self.assertTrue(test_list[2][2] == "3")
self.assertTrue(test_list[3][2] == "4")
self.assertTrue(test_list[4][2] == "5")
self.assertTrue(test_list[5][2] == "6")
db.clear()
self.assertTrue(db.get_object_count() == 0)
def test_insert_object(self):
db = DBConnection("../data_base/test_base.db")
db.clear()
self.assertRaises(DBConnection("RANDOM_BASE"))
self.assertTrue(db.get_object_count() == 0)
db.insert_object(1, "1")
self.assertTrue(db.get_object_count() == 1)
self.assertTrue(db.get_object_name(1) == "1")
db.clear()
self.assertTrue(db.get_object_count() == 0)
class InteractiveDisplay(tk.Frame):
def __init__(self, master=None):
self.db = DBConnection("data_base/base.db")
tk.Frame.__init__(self, master)
self.pack()
self.run_detector = tk.Button(self, text="Run detector", command=self.run)
self.object_listbox = tk.Listbox(self)
self.slide_listbox = tk.Listbox(self)
self.object_id_label = tk.Label(self, text="Id:")
self.object_name_label = tk.Label(self, text="Name:")
self.object_id_entry = tk.Entry(self)
self.object_name_entry = tk.Entry(self)
self.file_name_label = tk.Label(self, text="File name:")
self.object_name_label2 = tk.Label(self, text="Object name:")
self.file_name_entry = tk.Entry(self)
self.variable = tk.StringVar(self)
self.object_name_optionmenu = tk.OptionMenu(self, self.variable, '')
self.add_object_button = tk.Button(self, text="Add object", command=self.add_object)
self.delete_object_button = tk.Button(self, text="Delete object", command=self.delete_object)
self.delete_slide_button = tk.Button(self, text="Delete slide", command=self.delete_slide)
self.add_slide_button = tk.Button(self, text="Add slide", command=self.add_slide)
self.quit = tk.Button(self, text="Quit", fg="red", command=root.destroy)
self.dict = None
self.place_widgets()
self.set_widgets()
def set_widgets(self):
"""
Function set widgets parametrs.
"""
self.set_optionmenu()
self.set_object_listbox()
self.set_slide_listbox()
self.object_listbox.bind('<<ListboxSelect>>', self.onselect_object)
self.slide_listbox.bind('<<ListboxSelect>>', self.onselect_slide)
def place_widgets(self):
"""
Function place widgets on window.
"""
self.run_detector.grid(row=0, columnspan=8)
self.object_listbox.grid(row=1, column=0, columnspan=4)
self.slide_listbox.grid(row=1, column=4, columnspan=4)
self.object_id_label.grid(row=3, column=0)
self.object_name_label.grid(row=3, column=2)
self.object_id_entry.grid(row=3, column=1)
self.object_name_entry.grid(row=3, column=3)
self.file_name_label.grid(row=3, column=4)
self.object_name_label2.grid(row=3, column=6)
self.file_name_entry.grid(row=3, column=5)
self.object_name_optionmenu.grid(row=3, column=7)
self.add_object_button.grid(row=4, column=0, columnspan=2)
self.delete_object_button.grid(row=4, column=2, columnspan=2)
self.delete_slide_button.grid(row=4, column=6, columnspan=2)
self.add_slide_button.grid(row=4, column=4, columnspan=2)
self.quit.grid(row=5, columnspan=8)
def onselect_object(self, evt):
"""
Function handle oncelect event.
"""
w = evt.widget
index = int(w.curselection()[0])
item = w.get(index)
element = item.split(' - ')
self.object_id_entry.delete(0, tk.END)
self.object_id_entry.insert(0, element[0])
self.object_name_entry.delete(0, tk.END)
self.object_name_entry.insert(0, element[1])
def onselect_slide(self, evt):
"""
Function handle oncelect event.
"""
w = evt.widget
index = int(w.curselection()[0])
item = w.get(index)
slide = item.split(' - ')
self.file_name_entry.delete(0, tk.END)
self.file_name_entry.insert(0, slide[1])
# self.variable.set(next((object for object, id in self.dict.items() if id == slide[0]), None))
def add_object(self):
"""
Function handle add_object_button and insert data to data base.
"""
self.db.insert_object(self.object_id_entry.get(), self.object_name_entry.get())
DataMatrixCreator.create_data_matrix(600, self.object_id_entry.get(), 6)
self.object_id_entry.delete(0, tk.END)
self.object_name_entry.delete(0, tk.END)
self.set_object_listbox()
self.set_optionmenu()
def delete_object(self):
"""
Function handle delete_object_button and delete data from data base.
"""
self.db.delete_object(self.object_id_entry.get())
DataMatrixCreator.remove_data_matrix(self.object_id_entry.get())
self.set_object_listbox()
self.set_optionmenu()
def add_slide(self):
"""
Function handle add_slide_button and insert data to data base.
"""
self.db.insert_slide(self.dict[self.variable.get()], self.file_name_entry.get())
self.file_name_entry.delete(0, tk.END)
self.set_slide_listbox()
def delete_slide(self):
"""
Function handle delete_slide_button and delete data from data base.
"""
self.db.delete_slide(self.file_name_entry.get())
self.set_slide_listbox()
def set_optionmenu(self):
"""
Function set optionmenu.
"""
temp_list = self.db.get_object_list()
self.dict = {row[1]: row[0] for row in temp_list}
self.variable.set(list(self.dict.keys())[0])
self.object_name_optionmenu = tk.OptionMenu(self, self.variable, *self.dict.keys())
self.object_name_optionmenu.grid(row=3, column=7)
def set_object_listbox(self):
"""
Function set object_listbox.
"""
self.object_listbox.delete(0, tk.END)
temp_list = self.db.get_object_list()
element = ["" + str(record[0]) + " - " + record[1] for record in temp_list]
self.object_listbox.insert(tk.END, *element)
self.object_name_optionmenu.grid(row=3, column=7)
def set_slide_listbox(self):
"""
Function set slide_listbox.
"""
self.slide_listbox.delete(0, tk.END)
temp_list = self.db.get_slide_list()
slide = ["" + str(record[1]) + " - " + record[2] for record in temp_list]
self.slide_listbox.insert(tk.END, *slide)
def run(self):
"""
Function handle run_detector_button.
"""
detector = DataMatrixDetector(self.db)
detector.set_template("data_matrixes/template.jpg")
camera = Camera()
camera.OnCapture += detector.detect_matrix
camera.run()
from sqlalchemy import *
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from db_connection import DBConnection
import sqlalchemy
from db_mapping import DBMapping
engine = DBConnection()._createConn()
engine.echo = True
DBMapping().Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add_all(
[
DBMapping().ErrorCodes(code="FPCT", error_name="Fail Pre Check Test"),
DBMapping().ErrorCodes(code="FKP", error_name="Fail to create key pair"),
DBMapping().ErrorCodes(code="FSG", error_name="Fail to create security group"),
DBMapping().ErrorCodes(code="FRI", error_name="Fail to run instances"),
DBMapping().ErrorCodes(code="FRB", error_name="Fail to reboot instances"),
DBMapping().ErrorCodes(code="FRTS", error_name="Fail to reboot process"),
DBMapping().ErrorCodes(code="FFT", error_name="Fail to file test"),
DBMapping().ErrorCodes(code="FIPN", error_name="Fail instances port Not ready"),
DBMapping().ErrorCodes(code="FISB", error_name="Fail instances stuck in build process"),
DBMapping().ErrorCodes(code="FURS", error_name="Fail unable to remove snapshot"),
]
)
session.commit()
def test_delete_object(self):
db = DBConnection("../data_base/test_base.db")
self.assertTrue(db.get_object_count() == 0)
db.insert_object(1, "1")
self.assertTrue(db.get_object_count() == 1)
self.assertTrue(db.get_object_name(1) == "1")
db.delete_object(1)
self.assertTrue(db.get_object_count() == 0)
db.clear()
