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()
