Styjun

I would like some help with a sql query, i'm using SQLAlchemy but I don't even understand how i can express the query in raw sql.

Im phashing every frames of all videos in a season and adding them to the db.
My goal is to find intros the videos checking for the same reaccuring frames in the videos.

My table looks like:

|id|tvdbid|hash |season|episode|offset
---+------+-----+------+-------+------
|1 |1337 |a1a1a|1 |1 |42
---+------+-----+------+-------+-------
|2 |1337 |a1a1a|1 |1 |68
---+------+-----+------+-------+-------
|3 |1337 |a1a1b|1 |2 |92
---+------+-----+------+-------+-------
|4 |1337 |a1a1a|1 |2 |116
---+------+-----+------+-------+-------
|5 |1337 |a1a1a|1 |3 |42
---+------+-----+------+-------+-------
|6 |1337 |a1a1a|1 |3 |42

The result im looking for a is a list of rows where the hash matches in n number of episodes(it can only match on episode at the time) and has the same tvdbid and season number.

At the moment i'm doing:

import sqlalchemy as sa 
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Hashes(Base):
 __tablename__ = 'hashes'

 id = sa.Column(sa.Integer, primary_key=True)
 season = sa.Column(sa.Integer)
 episode = sa.Column(sa.Integer)
 tvdbid = sa.Column(sa.Text(length=100))
 hash = sa.Column(sa.Text(length=16))
 offset = sa.Column(sa.Integer)

h = Hashes.__table__ 

async def some_web_request(request):

 # I need to use raw sql or core as the db library requires it.
 # my cli tool uses a sync method to insert the rows in the db.
 query = h.select().where(sa.and_(h.c.tvdbid == 
 request.path_params['tvdbid'], 
 h.c.season == request.path_params['season'])).group_by('hash', 'episode')
 result = await DB.fetch_all(query)
 return result

This seems to work just fine, but it isn't exactly what I want so I have to clean up up with python and it will not be viable in the long run. The table will have have between 5 - 500 million rows.

My current "work around":

from collections import defaultdict

def clean_up(result):
 d = defaultdict(set)
 for row in result:
 d[row.hash].add(row.episode)

 final_result = []
 for k, v in d.items():
 if (l) > 4: # 4 is the number of episodes.
 final_result.append(k)

 return final_result

The desired output should have been:

|id|tvdbid|hash |season|episode|offset
---+------+-----+------+-------+------
|1 |1337 |a1a1a|1 |1 |42

as the hash needs to present in atleast 50% of the episodes.
or it could simply be a1a1a i dont really need to entire rows now. (this will needed laster to check for recaps etc.)