400 lines
16 KiB
Python
400 lines
16 KiB
Python
import sqlalchemy
|
|
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Sequence, text, UniqueConstraint
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.schema import MetaData
|
|
from sqlalchemy.orm import sessionmaker
|
|
from sqlalchemy.types import *
|
|
|
|
class Tables():
|
|
def __init__(self):
|
|
global tvnetworknewvals
|
|
global tvwebchannelnewvals
|
|
global characterjsonnewvals
|
|
global seriesjsonnewvals
|
|
global episodejsonnewvals
|
|
global metadata
|
|
global engine
|
|
global session
|
|
global tvnetworknewvals
|
|
global tvwebchannelnewvals
|
|
global characterjsonnewvals
|
|
global seriesjsonnewvals
|
|
global episodejsonnewvals
|
|
global tvupdates
|
|
global newseriesdata
|
|
global actorlinks
|
|
global tablelist
|
|
|
|
def __del__(self):
|
|
pass
|
|
|
|
|
|
class Createtables():
|
|
|
|
def __init__(self, session, metadata, engine):
|
|
Base = declarative_base(metadata = metadata)
|
|
self.tablelist = self.settables(metadata, engine)
|
|
try:
|
|
self.drop(session, self.tablelist)
|
|
except:
|
|
pass
|
|
self.create(Base, engine, metadata, session, self.tablelist)
|
|
session.commit()
|
|
#self.send(tablelist)
|
|
|
|
def settables(self, metadata, engine):
|
|
tablelist = []
|
|
newseriesdata = Table('seriesdata', metadata,
|
|
Column('id', BIGINT, Sequence('seriesdata_id_seq'), primary_key = True),
|
|
Column('seriesid',INTEGER, unique=True),
|
|
Column('tvdbid', String(25)),
|
|
Column('tvrageid', String(25)),
|
|
Column('imdbid', String(25)),
|
|
Column('serieslanguage', String(25)),
|
|
Column('genres', String(75)),
|
|
Column('seriesname', String(150)),
|
|
Column('seriesstatus', String(25)),
|
|
Column('premiered', String(25)),
|
|
Column('airdays', String(72)),
|
|
Column('airtime', String(25)),
|
|
Column('runtime', String(25)),
|
|
Column('rating', String(25)),
|
|
Column('showtype',TEXT),
|
|
Column('overview',TEXT),
|
|
Column('mediumimage',TEXT),
|
|
Column('originalimage',TEXT),
|
|
Column('weight',INTEGER),
|
|
Column('networkid', BIGINT),
|
|
Column('webchannel', BIGINT),
|
|
Column('officalsite',TEXT),
|
|
Column('showurl',TEXT),
|
|
Column('apilink',TEXT),
|
|
Column('previousepisode',TEXT),
|
|
Column('nextepisode',TEXT),
|
|
schema = "dbo")
|
|
tablelist.append(newseriesdata)
|
|
newactordata = Table('actordata', metadata,
|
|
Column('id', BIGINT, Sequence('actordata_id_seq'), primary_key = True),
|
|
Column('actorid', INTEGER, unique=True),
|
|
Column('actorname', VARCHAR(72)),
|
|
Column('countryname', VARCHAR(144)),
|
|
Column('countrytimezone', VARCHAR(72)),
|
|
Column('birthday', DATE),
|
|
Column('deathday', DATE),
|
|
Column('gender', VARCHAR(6)),
|
|
Column('mediumimage', TEXT),
|
|
Column('originalimage', TEXT),
|
|
Column('apilink', TEXT),
|
|
Column('url', TEXT),
|
|
Column('countrycode', VARCHAR(12)),
|
|
schema = "dbo")
|
|
tablelist.append(newactordata)
|
|
newepdata = Table('epdata', metadata,
|
|
Column('id', BIGINT, Sequence('epdata_id_seq'), primary_key = True),
|
|
Column('episodeid', INTEGER),
|
|
Column('seriesid', INTEGER),
|
|
Column('season', INTEGER),
|
|
Column('episodenumber', INTEGER),
|
|
Column('episodename', TEXT),
|
|
Column('airdate', VARCHAR(32)),
|
|
Column('airtime', VARCHAR(32)),
|
|
Column('episodesummary', TEXT),
|
|
Column('airstamp', VARCHAR(32)),
|
|
Column('runtime', VARCHAR(6)),
|
|
Column('eplink', TEXT),
|
|
Column('mediumimage', TEXT),
|
|
Column('url', TEXT),
|
|
Column('originalimage', TEXT),
|
|
schema = "dbo")
|
|
tablelist.append(newepdata)
|
|
newcastdata = Table('castdata', metadata,
|
|
Column('id', BIGINT, Sequence('castdata_id_seq'), primary_key = True),
|
|
Column('seriesid', INTEGER),
|
|
Column('characterid', INTEGER),
|
|
Column('actorid', INTEGER),
|
|
Column('charactername', INTEGER),
|
|
Column('mediumimage', TEXT),
|
|
Column('originalimage', TEXT),
|
|
Column('apilink', TEXT),
|
|
Column('tvmazeilink', TEXT),
|
|
schema = "dbo")
|
|
tablelist.append(newcastdata)
|
|
tvnetworks = Table('tvnetworkdata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('networkid', String(12)),
|
|
Column('networkname', String(255)),
|
|
Column('country', String(255)),
|
|
Column('countrycode', String(4)),
|
|
Column('countrytimezone', String(25)),
|
|
schema = "dbo")
|
|
tablelist.append(tvnetworks)
|
|
tvwebchannels = Table('tvwebchanneldata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('webchannelname', String(255)),
|
|
Column('webchannelcountrycode', String(4)),
|
|
Column('webchannelcountryname', String(255)),
|
|
Column('webchannelcountrytimezone', String(4)),
|
|
schema = "dbo")
|
|
tablelist.append(tvwebchannels)
|
|
countries = Table('countrydata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('name', String(255)),
|
|
Column('code', String(255)),
|
|
Column('timezone', String(255)),
|
|
UniqueConstraint('name', 'timezone', name='tz_name_uk'),
|
|
schema = "dbo")
|
|
tablelist.append(countries)
|
|
|
|
return(tablelist)
|
|
|
|
def create(self, Base, engine, metadata, session, tablelist):
|
|
print('Creating Data Tables')
|
|
Base.metadata.create_all(engine, tables=tablelist)
|
|
|
|
def drop(self, session, tablelist):
|
|
for table in tablelist:
|
|
try:
|
|
#print('Dropping ' + str(table))
|
|
table.drop()
|
|
except sqlalchemy.exc.ProgrammingError as se:
|
|
pass
|
|
|
|
def __del__(self):
|
|
pass
|
|
|
|
|
|
class TempTables():
|
|
|
|
def __init__(self, session, metadata, engine, tablelist):
|
|
Base = declarative_base(metadata = metadata)
|
|
self.tablelist = self.settables(metadata, engine)
|
|
try:
|
|
self.drop(session, self.tablelist)
|
|
except:
|
|
pass
|
|
self.create(Base, engine, metadata, session, self.tablelist)
|
|
#session.commit()
|
|
|
|
def settables(self, metadata, engine):
|
|
tablelist = []
|
|
tvnetworknewvals = Table('tvnetworknewvals', metadata,
|
|
Column('id', BIGINT, Sequence('network_id_seq'), primary_key = True),
|
|
Column('name', String(255)),
|
|
Column('countryid', String(255)),
|
|
schema = "updates")
|
|
tablelist.append(tvnetworknewvals)
|
|
tvwebchannelnewvals = Table('tvwebchannelnewvals', metadata,
|
|
Column('id', BIGINT, Sequence('webchannel_id_seq'), primary_key = True),
|
|
Column('name', String(255)),
|
|
Column('countryid', String(4)),
|
|
schema = "updates")
|
|
tablelist.append(tvwebchannelnewvals)
|
|
tvupdates = Table('tvupdates', metadata,
|
|
Column('seriesid', BIGINT, primary_key = True),
|
|
Column('timestamp', VARCHAR(15)),
|
|
schema = "updates")
|
|
tablelist.append(tvupdates)
|
|
tvupdatedata = Table('tvupdatedata', metadata,
|
|
Column('seriesid', BIGINT, primary_key = True),
|
|
Column('timestamp', VARCHAR(15)),
|
|
schema = "updates")
|
|
tablelist.append(tvupdatedata)
|
|
newactorlinks = Table('newactorlinks', metadata,
|
|
Column('id', BIGINT, Sequence('tempepisodejson_id_seq'), primary_key = True),
|
|
Column('actorid', INTEGER),
|
|
Column('characterid', INTEGER),
|
|
schema = "updates")
|
|
tablelist.append(newactorlinks)
|
|
return(tablelist)
|
|
|
|
def create(self, Base, engine, metadata, session, tablelist):
|
|
tablelist.remove(tablelist[6])
|
|
print('Creating Temporary JSON Tables')
|
|
try:
|
|
Base.metadata.create_all(engine, tables=tablelist)
|
|
except sqlalchemy.exc.ProgrammingError as pe:
|
|
pass
|
|
|
|
def drop(self, session, tablelist):
|
|
for table in tablelist:
|
|
if 'tvupdatedata' in table.description:
|
|
pass
|
|
else:
|
|
try:
|
|
#print('Dropping ' + str(table))
|
|
table.drop()
|
|
except sqlalchemy.exc.ProgrammingError as se:
|
|
pass
|
|
#session.commit()
|
|
|
|
class CreateTempTables():
|
|
|
|
global tvnetworknewvals
|
|
global tvwebchannelnewvals
|
|
global characterjsonnewvals
|
|
global seriesjsonnewvals
|
|
global episodejsonnewvals
|
|
global metadata
|
|
global engine
|
|
global session
|
|
|
|
global tablelist
|
|
|
|
def __init__(self, session, metadata, engine):
|
|
# Need to create schema if it does not exist
|
|
Base = declarative_base(metadata = metadata)
|
|
self.tablelist = self.settables(metadata, engine)
|
|
try:
|
|
self.drop(session, self.tablelist)
|
|
except:
|
|
pass
|
|
self.create(Base, engine, metadata, session, self.tablelist)
|
|
session.commit()
|
|
|
|
def settables(self, metadata, engine):
|
|
tablelist = []
|
|
newseriesdata = Table('newseriesdata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('seriesid',INTEGER, unique=True),
|
|
Column('thetvdb', String(25)),
|
|
Column('tvrage', String(25)),
|
|
Column('imdb', String(25)),
|
|
Column('language', String(25)),
|
|
Column('genres', String(75)),
|
|
Column('name', String(150)),
|
|
Column('status', String(25)),
|
|
Column('premiered', String(25)),
|
|
Column('days', String(72)),
|
|
Column('time', String(25)),
|
|
Column('runtime', String(25)),
|
|
Column('rating', String(25)),
|
|
Column('type',TEXT),
|
|
Column('summary',TEXT),
|
|
Column('medium',TEXT),
|
|
Column('original',TEXT),
|
|
Column('weight',INTEGER),
|
|
Column('networkid', BIGINT),
|
|
Column('webchannelid', BIGINT),
|
|
Column('officalsite',TEXT),
|
|
Column('url',TEXT),
|
|
Column('serieslink',TEXT),
|
|
Column('previousepisode',TEXT),
|
|
Column('nextepisode',TEXT),
|
|
schema="updates",
|
|
extend_existing=True)
|
|
tablelist.append(newseriesdata)
|
|
newactordata = Table('newactordata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('actorid', INTEGER),
|
|
Column('actorname', VARCHAR(72)),
|
|
Column('name', VARCHAR(72)),
|
|
Column('countryname', VARCHAR(72)),
|
|
Column('countrytimezone', VARCHAR(72)),
|
|
Column('birthday', DATE),
|
|
Column('deathday', DATE),
|
|
Column('gender', VARCHAR(6)),
|
|
Column('mediumimage', TEXT),
|
|
Column('originalimage', TEXT),
|
|
Column('apilink', TEXT),
|
|
Column('url', TEXT),
|
|
Column('countrycode', VARCHAR(12)),
|
|
schema="updates",
|
|
extend_existing=True)
|
|
tablelist.append(newactordata)
|
|
NEWEPDATA_ID = Sequence('newepdata_id_seq', start=1)
|
|
newepdata = Table('newepdata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('episodeid', INTEGER),
|
|
Column('seriesid', INTEGER),
|
|
Column('season', INTEGER),
|
|
Column('episodenumber', INTEGER),
|
|
Column('episodename', TEXT),
|
|
Column('airdate', VARCHAR(32)),
|
|
Column('airtime', VARCHAR(32)),
|
|
Column('episodesummary', TEXT),
|
|
Column('airstamp', VARCHAR(32)),
|
|
Column('runtime', VARCHAR(6)),
|
|
Column('eplink', TEXT),
|
|
Column('mediumimage', TEXT),
|
|
Column('url', TEXT),
|
|
Column('originalimage', TEXT),
|
|
schema="updates",
|
|
extend_existing=True)
|
|
tablelist.append(newepdata)
|
|
newcastdata = Table('newcastdata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('seriesid', INTEGER),
|
|
Column('characterid', INTEGER),
|
|
Column('actorid', INTEGER),
|
|
Column('url', TEXT),
|
|
Column('charactername', VARCHAR(128)),
|
|
Column('mediumimage', TEXT),
|
|
Column('originalimage', TEXT),
|
|
Column('apilink', TEXT),
|
|
Column('tvmazeilink', TEXT),
|
|
Column('playingself', CHAR),
|
|
Column('voice', CHAR),
|
|
schema="updates",
|
|
extend_existing=True)
|
|
tablelist.append(newcastdata)
|
|
newcrewdata = Table('newcrewdata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('seriesid', INTEGER),
|
|
Column('crewid', INTEGER),
|
|
Column('type', VARCHAR(128)),
|
|
Column('url', TEXT),
|
|
Column('name', VARCHAR(256)),
|
|
Column('countryid', INTEGER),
|
|
Column('birthday', DATE),
|
|
Column('deathday', DATE),
|
|
Column('gender', VARCHAR(24)),
|
|
Column('medium', TEXT),
|
|
Column('original', TEXT),
|
|
Column('apilink', TEXT),
|
|
UniqueConstraint('seriesid', 'crewid', 'type', name='crew_unique'),
|
|
schema="updates",
|
|
extend_existing=True)
|
|
tablelist.append(newcrewdata)
|
|
tvnetworks = Table('tvnetworkdata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('name', String(255)),
|
|
Column('countryid', String(255)),
|
|
schema = "updates",
|
|
extend_existing=True)
|
|
tablelist.append(tvnetworks)
|
|
tvwebchannels = Table('tvwebchanneldata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('name', String(255)),
|
|
Column('countryid', String(255)),
|
|
schema = "updates",
|
|
extend_existing=True)
|
|
tablelist.append(tvwebchannels)
|
|
countries = Table('newcountrydata', metadata,
|
|
Column('id', Integer, primary_key=True),
|
|
Column('name', String(255)),
|
|
Column('code', String(255)),
|
|
Column('timezone', String(255)),
|
|
UniqueConstraint('name', 'timezone', name='tz_name_uk'),
|
|
schema = "updates",
|
|
extend_existing=True)
|
|
tablelist.append(countries)
|
|
# tvupdates = Table('tvupdates', metadata,
|
|
# Column('seriesid', BIGINT, primary_key = True),
|
|
# Column('timestamp', VARCHAR(15)),
|
|
# schema = "updates",
|
|
# extend_existing=True)
|
|
# tablelist.append(tvupdates)
|
|
return(tablelist)
|
|
|
|
def create(self, Base, engine, metadata, session, tablelist):
|
|
print('Creating Temporary Data Tables')
|
|
Base.metadata.create_all(engine, tables=tablelist)
|
|
|
|
def drop(self, session, tablelist):
|
|
print('Dropping temporary update tables.')
|
|
for table in tablelist:
|
|
try:
|
|
print('Dropping ' + str(table))
|
|
table.drop()
|
|
except sqlalchemy.exc.ProgrammingError as se:
|
|
pass |