Пример #1
0
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
Пример #2
0
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
Пример #3
0
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))    
Пример #4
0
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
Пример #6
0
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)
Пример #7
0
    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)
Пример #9
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')
Пример #10
0
    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, {})
Пример #11
0
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!")
Пример #12
0
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
Пример #13
0
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")
Пример #14
0
    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)
Пример #15
0
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
Пример #16
0
    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')
Пример #17
0
    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
Пример #18
0
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
    ]
Пример #19
0
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()
Пример #21
0
def validationAgreed(chosen_image):
    db = DBConnection()
    good = getImages(db, True)
    print(good)
    print(chosen_image)

    return chosen_image in good
Пример #22
0
    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()
Пример #23
0
    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()
Пример #24
0
    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()
Пример #25
0
    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()
Пример #26
0
    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()
Пример #28
0
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)
Пример #29
0
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
Пример #31
0
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
Пример #32
0
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)
Пример #33
0
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)
Пример #34
0
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
    ]
Пример #35
0
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!")
Пример #36
0
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
Пример #37
0
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}
Пример #38
0
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
Пример #39
0
    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()
Пример #41
0
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
Пример #42
0
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
Пример #43
0
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
Пример #44
0
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])
Пример #46
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()
Пример #47
0
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()
Пример #48
0
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()
Пример #49
0
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
Пример #50
0
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()
Пример #52
0
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()
Пример #54
0
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()
Пример #58
0
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()