1import MySQLdb as mdb
 2from fingerprinter.fingerprint_record import FingerprintRecord
 3
 4MYSQL_HOST = "localhost"
 5MYSQL_USER = "super"
 6MYSQL_PASSWORD = "password"
 7MYSQL_DB_NAME = "fingerprints"
 8INSERT_SINGLE_ROW_STATEMENT = "INSERT INTO Fingerprints (hash, episode, playhead) VALUES ({:d}, '{}', {})"
 9SELECT_SINGLE_HASH_QUERY = "SELECT hash, episode, playhead FROM Fingerprints where hash = {:d}"
10SELECT_HASH_IN_QUERY = "SELECT hash, episode, playhead FROM Fingerprints where hash in ({})"
11
12
13class FingerprintDatabase(object):
14  """Instance of the database that holds fingerprints. Assumes a schema specified in the schema.sql file in this repo.
15
16  """
17  def __init__(self, host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, db_name=MYSQL_DB_NAME):
18    self.host = host
19    self.user = user
20    self.password = password
21    self.db_name = db_name
22    self.connection = mdb.connect(host, user, password, db_name)
23
24  def insert(self, fingerprint):
25    try:
26      cur = self.connection.cursor()
27      cur.execute(INSERT_SINGLE_ROW_STATEMENT.format(fingerprint.hash_id, fingerprint.episode, fingerprint.play_head))
28      self.connection.commit()
29    except mdb.Error, e:
30      print("Error %d: %s" % (e.args[0], e.args[1]))
31
32  def lookup(self, hash_number):
33    try:
34      cur = self.connection.cursor()
35      cur.execute(SELECT_SINGLE_HASH_QUERY.format(hash_number))
36      return cur
37    except mdb.Error, e:
38      print("Error %d: %s" % (e.args[0], e.args[1]))
39
40  def find_records_by_hashes(self, hash_numbers):
41    """
42    Selects all records that exist for any hash in hash_numbers list.
43    :param hash_numbers: to select on.
44    :return: list of FingerprintRecords
45    """
46    query = SELECT_HASH_IN_QUERY.format(", ".join(str(x) for x in hash_numbers))
47    try:
48      cur = self.connection.cursor()
49      cur.execute(query)
50      return [FingerprintRecord(hash_id, episode, play_head) for (hash_id, episode, play_head) in cur]
51    except mdb.Error, e:
52      print("Error %d: %s" % (e.args[0], e.args[1]))