Created
February 10, 2014 02:06
-
-
Save Lwjivd/8909168 to your computer and use it in GitHub Desktop.
Pthon MySQLdb
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import MySQLdb as mysql | |
| import MySQLdb.cursors | |
| class Database: | |
| connection = None | |
| get_shows_query = "SELECT tvdb_id, show_name, popularity FROM shows ORDER BY show_name;" | |
| get_show_query = "SELECT * FROM shows WHERE tvdb_id=%s LIMIT 1;" | |
| get_show_episodes_query = ("SELECT tvdb_episode_id, season_number, episode_number, episode_name, episodes.summary, image, DATE_FORMAT(air_date, '%%Y-%%m-%%d') AS air_date" | |
| " FROM episodes" | |
| " WHERE tvdb_id=%s" | |
| " ORDER BY season_number DESC, episode_number DESC") | |
| get_popular_shows_query = "SELECT tvdb_id, show_name, views FROM shows ORDER BY views DESC LIMIT 30" | |
| increment_show_views_query = "UPDATE shows SET views=views+%s WHERE tvdb_id=%s;" | |
| put_show_query = ("INSERT INTO shows (tvdb_id, tvdb_series_id, imdb_id, show_name, air_day, air_time, summary, active)" | |
| " VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" | |
| " ON DUPLICATE KEY UPDATE" | |
| " tvdb_id=VALUES(tvdb_id), tvdb_series_id=VALUES(tvdb_series_id), imdb_id=VALUES(imdb_id), show_name=VALUES(show_name), air_day=VALUES(air_day)," | |
| " air_time=VALUES(air_time), summary=VALUES(summary), active=VALUES(active)") | |
| get_episode_query = ("SELECT * FROM episodes WHERE tvdb_episode_id=%s LIMIT 1;") | |
| get_episodes_query = ("SELECT episodes.tvdb_id, tvdb_episode_id, show_name, season_number, episode_number, episode_name, episodes.summary, image, air_date" | |
| " FROM episodes" | |
| " JOIN shows ON episodes.tvdb_id = shows.tvdb_id" | |
| " WHERE tvdb_episode_id IN (%s)") | |
| get_current_episodes_query = ("SELECT shows.tvdb_id, tvdb_episode_id, season_number, episode_number, episode_name, image, DATE_FORMAT(air_date, '%%Y-%%m-%%d') AS air_date, show_name" | |
| " FROM episodes" | |
| " JOIN shows ON episodes.tvdb_id = shows.tvdb_id" | |
| " WHERE %s" | |
| " ORDER BY air_date ASC, popularity DESC, episode_number DESC") | |
| get_hot_episodes_query = ("SELECT episodes.tvdb_id, tvdb_episode_id, show_name, season_number, episode_number, episode_name, episodes.summary, image, air_date" | |
| ", LOG10(episodes.views + 1) * %s + UNIX_TIMESTAMP(air_date) AS hotness" | |
| " FROM episodes" | |
| " JOIN shows ON episodes.tvdb_id = shows.tvdb_id" | |
| " WHERE air_date < CURDATE() AND episodes.views > 0" | |
| " ORDER BY hotness DESC" | |
| " LIMIT 5;") | |
| increment_episode_views_query = ("UPDATE episodes" | |
| " SET views=views+1" | |
| " WHERE tvdb_episode_id=%s;") | |
| put_episode_query = ("INSERT INTO episodes" | |
| " (tvdb_id, tvdb_episode_id, season_number, episode_number, episode_name, summary, image, air_date)" | |
| " VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" | |
| " ON DUPLICATE KEY UPDATE" | |
| " tvdb_id=VALUES(tvdb_id), tvdb_episode_id=VALUES(tvdb_episode_id), season_number=VALUES(season_number), episode_number=VALUES(episode_number)," | |
| " episode_name=VALUES(episode_name), summary=VALUES(summary), image=VALUES(image), air_date=VALUES(air_date);") | |
| get_sources_query = ("SELECT sources.show_name, source_name, source_id" | |
| " FROM sources" | |
| " JOIN shows on soundex(shows.show_name) LIKE soundex(sources.show_name)" | |
| " WHERE shows.tvdb_id=%s;") | |
| get_sources_for_shows_query = ("SELECT tvdb_id, sources.show_name, source_name, source_id" | |
| " FROM sources" | |
| " JOIN shows on soundex(shows.show_name) LIKE soundex(sources.show_name)" | |
| " WHERE INSTR(%s, CONVERT(shows.tvdb_id, CHAR(50)));") | |
| put_source_query = ("INSERT INTO sources" | |
| " (show_name, source_name, source_id)" | |
| " VALUES (%s, %s, %s)" | |
| " ON DUPLICATE KEY UPDATE" | |
| " show_name=show_name;") | |
| get_links_query = ("SELECT * FROM links" | |
| " JOIN domains ON links.domain = domains.domain" | |
| " WHERE tvdb_episode_id=%s AND deleted=0" | |
| " ORDER BY domains.popularity DESC, links.domain;") | |
| get_links_mobile_query = ("SELECT * FROM links" | |
| " JOIN domains ON links.domain = domains.domain" | |
| " WHERE tvdb_episode_id=%s AND deleted=0 AND mobile_url > ''" | |
| " ORDER BY domains.popularity DESC, links.domain;") | |
| put_link_query = ("INSERT INTO links" | |
| " (tvdb_episode_id, source_name, domain, url, embed_code, flv_url, mobile_url, deleted)" | |
| " VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" | |
| " ON DUPLICAE KEY UPDATE" | |
| " url=VALUES(url), embed_code=VALUES(embed_code), flv_url=VALUES(flv_url), mobile_url=VALUES(mobile_url), deleted=VALUES(deleted);" | |
| " INSERT INTO domains" | |
| " (domain, popularity)" | |
| " VALUES (domain, 0)" | |
| " ON DUPLICATE KEY" | |
| " UPDATE popularity=popularity;") | |
| put_links_query = ("INSERT INTO links" | |
| " (tvdb_episode_id, source_name, domain, url, embed_code, flv_url, mobile_url, deleted)" | |
| " VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" | |
| " ON DUPLICATE KEY UPDATE" | |
| " url=VALUES(url), embed_code=VALUES(embed_code), flv_url=VALUES(flv_url), mobile_url=VALUES(mobile_url), deleted=VALUES(deleted);") | |
| put_domains_query = (" INSERT INTO domains" | |
| " (domain, popularity)" | |
| " VALUES (%s, 0)" | |
| " ON DUPLICATE KEY" | |
| " UPDATE popularity=popularity;") | |
| def __init__(self): | |
| self.connection = mysql.connect('dilosa.com', 'dilosaco_andrew', 'Dpn0t6116', 'dilosaco_tv', | |
| cursorclass=MySQLdb.cursors.DictCursor, charset='utf8') | |
| self.connection.autocommit(True) | |
| def __del__(self): | |
| if self.connection is not None: | |
| self.connection.close() | |
| def __query__(self, query, args=None, many=None, recurse=True): | |
| cursor = self.connection.cursor() | |
| query = query.encode('utf8', 'ignore') | |
| try: | |
| if many is not None: | |
| cursor.executemany(query, args) | |
| cursor.close() | |
| return | |
| else: | |
| cursor.execute(query, args) | |
| results = cursor.fetchall() | |
| cursor.close() | |
| return results | |
| except MySQLdb.OperationalError: | |
| self.connection.close() | |
| self.__init__() | |
| if recurse: | |
| return self.__query__(query, args=args, many=many, recurse=False) | |
| else: | |
| raise | |
| def escape(self, s): | |
| return self.connection.escape_string(s) | |
| def get_shows(self): | |
| return self.__query__(self.get_shows_query) | |
| def get_show(self, tvdb_id): | |
| show = self.__query__(self.get_show_query, (tvdb_id,)) | |
| if len(show) > 0: | |
| show = show[0] | |
| show['episodes'] = self.get_show_episodes(tvdb_id) | |
| return show | |
| def put_show(self, tvdb_id, series): | |
| if series is None: | |
| return | |
| tvdb_id = tvdb_id | |
| series_id = series.find('SeriesID').text | |
| imdb_id = series.find('IMDB_ID').text | |
| show_name = series.find('SeriesName').text | |
| air_day = series.find('Airs_DayOfWeek').text | |
| air_time = series.find('Airs_Time').text | |
| summary = series.find('Overview').text | |
| active = series.find('Status').text | |
| return self.__query__(self.put_show_query, args=(int(tvdb_id), series_id, imdb_id, show_name, air_day, air_time, summary, active)) | |
| def put_shows(self, shows): | |
| args = [] | |
| for series in shows: | |
| if series is None: | |
| continue | |
| data = ( | |
| series.find('id'), | |
| series.find('SeriesID'), | |
| series.find('IMDB_ID'), | |
| series.find('SeriesName'), | |
| series.find('Airs_DayOfWeek'), | |
| series.find('Airs_Time'), | |
| series.find('Overview'), | |
| series.find('Status'), | |
| ) | |
| data = [x.text if x is not None else '' for x in data] | |
| args.append(data) | |
| return self.__query__(self.put_show_query, args=args, many=True) | |
| def get_show_episodes(self, tvdb_id): | |
| return self.__query__(self.get_show_episodes_query, args=(tvdb_id,)) | |
| def get_popular_shows(self): | |
| return self.__query__(self.get_popular_shows_query) | |
| def increment_show_views(self, tvdb_id, weight=1): | |
| weight = str(weight) | |
| return self.__query__(self.increment_show_views_query, args=(weight, tvdb_id,)) | |
| def get_episode(self, tvdb_episode_id): | |
| return self.__query__(self.get_episode_query, args=tvdb_episode_id)[0] | |
| def get_episodes(self, tvdb_episode_ids): | |
| query = self.get_episodes_query % ','.join([str(x) for x in tvdb_episode_ids]) | |
| return self.__query__(query) | |
| def get_current_episodes(self, types): | |
| interval = "" | |
| for type in types.split(','): | |
| if type == "recent": | |
| interval += "air_date BETWEEN (CURDATE() - INTERVAL 3 DAY) AND (CURDATE() - INTERVAL 1 DAY)" | |
| elif type == "today": | |
| interval += "air_date = CURDATE()" | |
| elif type == "yesterday": | |
| interval += "air_date = (CURDATE() - INTERVAL 1 DAY)" | |
| elif type == "upcoming": | |
| interval += "air_date BETWEEN (CURDATE() + INTERVAL 1 DAY) AND (CURDATE() + INTERVAL 4 DAY)" | |
| else: | |
| interval += "air_date = CURDATE()" | |
| interval += " OR " | |
| interval = interval[:-4] | |
| return self.__query__(self.get_current_episodes_query % interval) | |
| def get_hot_episodes(self): | |
| args=(287015) # A view counts as a 1 day bonus | |
| return self.__query__(self.get_hot_episodes_query, args=args) | |
| def increment_episode_views(self, tvdb_episode_id): | |
| args = (tvdb_episode_id) | |
| return self.__query__(self.increment_episode_views_query, args=args) | |
| def put_episode(self, episode_id, episode): | |
| if episode is None: | |
| return | |
| tvdb_id = episode.find('seriesid').text | |
| tvdb_episode_id = episode.find('id').text | |
| season_number = episode.find('SeasonNumber').text | |
| episode_number = episode.find('EpisodeNumber').text | |
| episode_name = episode.find('EpisodeName').text | |
| summary = episode.find('Overview').text | |
| image = episode.find('filename').text | |
| air_date = episode.find('FirstAired').text | |
| return self.__query__(self.put_episode_query, args=(tvdb_id, tvdb_episode_id, season_number, episode_number, episode_name, summary, image, air_date)) | |
| def put_episodes(self, episodes): | |
| args = [] | |
| for episode in episodes: | |
| data = ( | |
| episode.find('seriesid'), | |
| episode.find('id'), | |
| episode.find('SeasonNumber'), | |
| episode.find('EpisodeNumber'), | |
| episode.find('EpisodeName'), | |
| episode.find('Overview'), | |
| episode.find('filename'), | |
| episode.find('FirstAired'), | |
| ) | |
| data = [x.text if x is not None else '' for x in data] | |
| if not data[4] == "TBA": | |
| args.append(data) | |
| return self.__query__(self.put_episode_query, args=args, many=True) | |
| def get_sources(self, tvdb_id): | |
| return self.__query__(self.get_sources_query, args=(tvdb_id)) | |
| def get_sources_for_shows(self, tvdb_ids): | |
| return self.__query__(self.get_sources_for_shows_query, args=(','.join(tvdb_ids))) | |
| def put_sources(self, sources): | |
| arg_list = [] | |
| for show_name in sources: | |
| for source_name in sources[show_name]: | |
| for source_id in sources[show_name][source_name]: | |
| arg_list.append((show_name, source_name, source_id)) | |
| return self.__query__(self.put_source_query, args=arg_list, many=True) | |
| def get_links(self, tvdb_episode_id, mobile_only=False): | |
| query = self.get_links_mobile_query if mobile_only else self.get_links_query | |
| return self.__query__(query, args=(tvdb_episode_id)) | |
| def put_link(self, tvdb_episode_id, source_name, domain, url, embed_code, mobile_url, deleted): | |
| args = (tvdb_episode_id, source_name, domain, url, embed_code, mobile_url, deleted) | |
| return self.__query__(self.put_link_query, args=args) | |
| def put_links(self, links): | |
| self.__query__(self.put_links_query, args=links, many=True) | |
| self.__query__(self.put_domains_query, args=[x[1] for x in links], many=True) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi, I see you've found some of my old code. :)
I'd appreciate it if you would redact the strings on Line 89. All of the information is outdated, but still, I'd prefer it not be out there.
Thanks!