def seed_towns(): print "Seeding Towns tables..." towns_file = 'current/data/uk-towns-list/uk-towns.csv' print towns_file Town.drop_table(fail_silently=True) Town.create_table(fail_silently=True) rq = RawQuery(Town, "COPY uk_towns FROM '{0}' DELIMITERS ',' CSV HEADER".format(towns_file)) try: rq.execute() except TypeError, e: # Wierdness here assert Town.select().count() == 43143
def seed_towns(): print "Seeding Towns tables..." towns_file = 'current/data/uk-towns-list/uk-towns.csv' print towns_file Town.drop_table(fail_silently=True) Town.create_table(fail_silently=True) rq = RawQuery( Town, "COPY uk_towns FROM '{0}' DELIMITERS ',' CSV HEADER".format( towns_file)) try: rq.execute() except TypeError, e: # Wierdness here assert Town.select().count() == 43143
def get_next_queue_item(cls, accounts, types, action_model, order_by): """Get the next order available to like, follow, retweet, etc... Returns an item that the accounts have not previously encountered, and that wasn't created by one of the accounts, and is one of the supported types. """ # TODO: convert to peewee when peewee supports joins on # sub-selects # see: https://groups.google.com/forum/#!topic/peewee-orm/Lm2qYpYo88k # TODO: also use service params = { 'action_table': action_model._meta.db_table, 'order_table': cls._meta.db_table, 'account_ids': ','.join(str(account.id) for account in accounts), 'type_ids': ','.join(str(type) for type in types), 'order_by': order_by, } # get latest order not created by account or seen by account sql = """ SELECT `%(order_table)s`.* FROM `%(order_table)s` LEFT JOIN (SELECT * FROM `%(action_table)s` WHERE `%(action_table)s`.account_id IN (%(account_ids)s)) AS b ON b.order_id = `%(order_table)s`.id WHERE b.id IS NULL AND `%(order_table)s`.account_id NOT IN (%(account_ids)s) AND `%(order_table)s`.status = 0 AND `%(order_table)s`.type IN (%(type_ids)s) ORDER BY %(order_by)s LIMIT 1 """ % params logging.debug(sql) rq = RawQuery(cls, sql) orders = [order for order in rq.execute()] if not orders: raise cls.DoesNotExist() return orders[0]
def get_user_timesheet(cls, *, user=None, week_ending_date=None): """ Retrievs timesheet entries for a user a week ending on week_ending_date. """ if user is None: user = current_user if week_ending_date is None: week_ending_date = current_week_ending_date() rq = RawQuery(cls, """ WITH daynums(num) AS (VALUES (6),(5),(4),(3),(2),(1),(0)), week(day) AS (SELECT date(?, '-'||num||' day') FROM daynums) SELECT id, day as date, finished_at, started_at, user_id, modified_at, break_for_id, is_approved, approver_id, approved_at, comment FROM week LEFT JOIN entry ON "date" = day AND user_id = ? ORDER BY "date" ASC""", week_ending_date.isoformat(), user.id) return rq.execute()
def get_latest_change_date(playlist_number): rq = RawQuery(Tracksinplaylist, "SELECT GREATEST(IFNULL(t.c1,0),IFNULL(t.c2,0)) dateAdded " "FROM (SELECT MAX(dateAdded) c1, MAX(dateRemoved) c2 " "FROM TracksInPlaylist WHERE playlistNumber=%s) AS t", playlist_number) for obj in rq.execute(): return obj.dateAdded
def fetch_graph_full(genre_ids, color='red', max_position=100): """Return full graph w/o single nodes""" in_ = ','.join([str(i) for i in genre_ids]) rq = RawQuery( Similar, 'SELECT from_id, a1.name as from_label, to_id, a2.name as to_label, ' 'a1.degree_input as from_degree, a2.degree_input as to_degree ' 'FROM "similar" ' 'JOIN "artist" a1 ON (from_id = a1.id) ' 'JOIN "artist" a2 ON (to_id = a2.id) ' 'WHERE position < %d AND ' 'from_id IN (SELECT DISTINCT artist_id FROM artist_genre WHERE genre_id IN (%s)) ' 'AND to_id IN (SELECT DISTINCT artist_id FROM artist_genre WHERE genre_id IN (%s)) ' 'LIMIT %d' % (max_position, in_, in_, EXPORT_LIMIT)) nodes = {} edges = [] for obj in rq.execute(): nodes[obj.from_id] = { 'label': obj.from_label, 'color': color, 'size': obj.from_degree } nodes[obj.to_id] = { 'label': obj.to_label, 'color': color, 'size': obj.to_degree } edges.append((obj.from_id, obj.to_id)) return nodes, edges
def available_hash_list(cls): """ Need to figure out more about what this does... """ keys = cls.get_primary_keys() # pylint: disable=no-member table = cls._meta.db_table # pylint: enable=no-member db_keys = {} for key in keys: # pylint: disable=no-member if len(cls._meta.rel) > 0: rel_mod = cls._meta.rel.get(key) db_keys[key] = rel_mod.db_column else: db_keys[key] = key # pylint: enable=no-member hash_list = [] hash_dict = {} sql = "SELECT {0} FROM {1}".format(",".join(db_keys.values()), table) all_keys_query = RawQuery(cls, sql, None).dicts() for obj in all_keys_query.execute(): inst_key = index_hash(*obj.values()) hash_list.append(inst_key) entry = { 'key_list': {db_keys.get(key): obj[key] for key in db_keys.keys()}, 'index_hash': inst_key } hash_dict[inst_key] = entry return hash_list, hash_dict
def fetch_graph_custom(rock_ids, metal_ids, max_position=100, primary=True): rock_in = ','.join([str(i) for i in rock_ids]) metal_in = ','.join([str(i) for i in metal_ids]) all_in_ = ','.join([rock_in, metal_in]) primary_condition = 'a1.is_primary = True AND a2.is_primary = True AND' if primary else '' rq = RawQuery( Similar, 'SELECT from_id, a1.name as from_label, to_id, a2.name as to_label, ' 'a1.degree_input as from_degree, a2.degree_input as to_degree, ' 'CASE WHEN (SELECT COUNT(*) FROM artist_genre ' 'WHERE genre_id IN (%s) AND artist_id = from_id) > 0 ' 'THEN 1 ELSE 0 END AS from_is_rock, ' 'CASE WHEN (SELECT COUNT(*) FROM artist_genre ' 'WHERE genre_id IN (%s) AND artist_id = from_id) > 0 ' 'THEN 1 ELSE 0 END AS from_is_metal, ' 'CASE WHEN (SELECT COUNT(*) FROM artist_genre ' 'WHERE genre_id IN (%s) AND artist_id = to_id) > 0 ' 'THEN 1 ELSE 0 END AS to_is_rock, ' 'CASE WHEN (SELECT COUNT(*) FROM artist_genre ' 'WHERE genre_id IN (%s) AND artist_id = to_id) > 0 ' 'THEN 1 ELSE 0 END AS to_is_metal ' 'FROM "similar" ' 'JOIN "artist" a1 ON (from_id = a1.id) ' 'JOIN "artist" a2 ON (to_id = a2.id) ' 'WHERE %s position < %d' 'AND from_id IN (SELECT DISTINCT artist_id FROM artist_genre WHERE genre_id IN (%s)) ' 'AND to_id IN (SELECT DISTINCT artist_id FROM artist_genre WHERE genre_id IN (%s)) ' 'LIMIT %d' % (rock_in, metal_in, rock_in, metal_in, primary_condition, max_position, all_in_, all_in_, EXPORT_LIMIT)) def select_color(is_rock, is_metal): if is_rock and is_metal: return COLOR_BOTH elif is_rock: return COLOR_ROCK elif is_metal: return COLOR_METAL else: return 'grey' nodes = {} edges = [] for obj in rq.execute(): nodes[obj.from_id] = { 'label': obj.from_label, 'color': select_color(obj.from_is_rock, obj.from_is_metal), 'size': obj.from_degree } nodes[obj.to_id] = { 'label': obj.to_label, 'color': select_color(obj.to_is_rock, obj.to_is_metal), 'size': obj.to_degree } edges.append((obj.from_id, obj.to_id)) return nodes, edges
def filter(self, user_input_string): condition = collect_all_filters(user_input_string) q = RawQuery(RecordRevision, """ SELECT DISTINCT "last_revs".* FROM last_revision as last_revs LEFT OUTER JOIN record_entity ON "last_revs".id = record_entity.revision_id {condition}""" .format(condition=condition.replace('last_revision', '"last_revs"'))) return q.execute()
def fetch_top_by_genre(genre_ids=None, reverse=False, limit=10): where = '' if genre_ids: in_ = 'NOT IN' if reverse else 'IN' where = 'WHERE id %s (SELECT DISTINCT artist_id FROM artist_genre WHERE genre_id IN (%s)) ' \ % (in_, ','.join([str(i) for i in genre_ids])) rq = RawQuery( Similar, 'SELECT name, degree_input ' 'FROM "artist" %s ' 'ORDER BY degree_input DESC ' 'LIMIT %d' % (where, limit)) return [(i.name, i.degree_input) for i in rq.execute()]
def get_by_words(cls, words): words = "%" + words + "%" rq = RawQuery(cls, "select * from words where words like ?", words) return rq.execute()
def update_degree(): rq = RawQuery( Similar, 'UPDATE "artist" SET ' 'degree_output = (SELECT COUNT(*) FROM "similar" WHERE from_id = id), ' 'degree_input = (SELECT COUNT(*) FROM "similar" WHERE to_id = id)') rq.execute()