309 lines
12 KiB
Python
309 lines
12 KiB
Python
import datetime
|
|
from sqlalchemy import text
|
|
from sqlalchemy import DateTime
|
|
|
|
class SQLGenerate():
|
|
def __init__(self):
|
|
pass
|
|
|
|
def merge_series(self):
|
|
seriessql = text('''
|
|
INSERT INTO dbo.seriesdata (serieslink, url, thetvdb, tvrage, imdb,
|
|
genres, seriesid, medium,original, language, name, webchannelid, networkid, premiered, rating, runtime, days, time,
|
|
status, summary, type, weight) SELECT serieslink, url, thetvdb, tvrage, imdb,
|
|
genres, seriesid, medium,original, language, name, webchannelid, networkid, premiered, rating, runtime, days, time,
|
|
status, summary, type, weight from updates.newseriesdata
|
|
ON CONFLICT(seriesid) DO UPDATE
|
|
SET serieslink = excluded.serieslink,
|
|
url = excluded.url,
|
|
thetvdb = excluded.thetvdb,
|
|
tvrage = excluded.tvrage,
|
|
genres = excluded.genres,
|
|
seriesid = excluded.seriesid,
|
|
medium = excluded.medium,
|
|
original = excluded.original,
|
|
language = excluded.language,
|
|
name = excluded.name,
|
|
webchannelid = excluded.webchannelid,
|
|
networkid = excluded.networkid,
|
|
premiered = excluded.premiered,
|
|
rating = excluded.rating,
|
|
runtime = excluded.runtime,
|
|
days = excluded.days,
|
|
time = excluded.time,
|
|
status = excluded.status,
|
|
summary = excluded.summary,
|
|
type = excluded.type,
|
|
weight = excluded.weight;
|
|
''')
|
|
return(seriessql)
|
|
|
|
def merge_episodes(self):
|
|
episodesql = text('''
|
|
INSERT INTO dbo.epdata (episodeid,
|
|
seriesid,
|
|
season,
|
|
episodenumber,
|
|
episodename,
|
|
airdate,
|
|
airtime,
|
|
episodesummary,
|
|
airstamp,
|
|
runtime,
|
|
eplink,
|
|
mediumimage,
|
|
originalimage,
|
|
"url")
|
|
SELECT episodeid,
|
|
seriesid,
|
|
season,
|
|
episodenumber,
|
|
episodename,
|
|
airdate,
|
|
airtime,
|
|
episodesummary,
|
|
airstamp,
|
|
runtime,
|
|
eplink,
|
|
mediumimage,
|
|
originalimage,
|
|
"url"
|
|
FROM updates.newepdata
|
|
ON CONFLICT (seriesid, episodeid) do UPDATE
|
|
SET episodeid = excluded.episodeid,
|
|
seriesid = excluded.seriesid,
|
|
season = excluded.season,
|
|
episodenumber = excluded.episodenumber,
|
|
episodename = excluded.episodename,
|
|
airdate = excluded.airdate,
|
|
airtime = excluded.airtime,
|
|
episodesummary = excluded.episodesummary,
|
|
airstamp = excluded.airstamp,
|
|
runtime = excluded.runtime,
|
|
eplink = excluded.eplink,
|
|
mediumimage = excluded.mediumimage,
|
|
originalimage = excluded.originalimage,
|
|
"url" = excluded."url";
|
|
''')
|
|
return(episodesql)
|
|
|
|
def merge_updates(self):
|
|
updatesql = text('''
|
|
INSERT INTO updates.tvupdatedata(seriesid, timestamp)
|
|
SELECT seriesid, timestamp from updates.tvupdates
|
|
ON CONFLICT(seriesid) DO UPDATE
|
|
SET seriesid = excluded.seriesid, timestamp = excluded.timestamp;
|
|
''')
|
|
return(updatesql)
|
|
|
|
def merge_cast(self):
|
|
datetimestamp = DateTime(datetime.datetime.utcnow)
|
|
castsql = text('''
|
|
INSERT INTO dbo.castdata (seriesid,characterid,actorid,charactername,mediumimage,originalimage,apilink,tvmazeilink)
|
|
SELECT seriesid,characterid,actorid,charactername,mediumimage,originalimage,apilink,tvmazeilink
|
|
FROM updates.newcastdata
|
|
ON CONFLICT (seriesid, characterid, actorid) DO UPDATE
|
|
SET seriesid = excluded.seriesid,
|
|
characterid = excluded.characterid,
|
|
actorid = excluded.actorid,
|
|
charactername = excluded.charactername,
|
|
mediumimage = excluded.mediumimage,
|
|
originalimage = excluded.originalimage,
|
|
apilink = excluded.apilink,
|
|
tvmazeilink = excluded.tvmazeilink
|
|
''')
|
|
return(castsql)
|
|
|
|
def merge_actors(self):
|
|
actorsql = text('''
|
|
INSERT INTO dbo.actors (actorid,actorname,countryname,countrytimezone,birthday,deathday,gender,mediumimage,originalimage,apilink,"url",countrycode)
|
|
SELECT actorid,actorname,countryname,countrytimezone,birthday,deathday,gender,mediumimage,originalimage,apilink,"url",countrycode
|
|
FROM updates.newactordata
|
|
ON CONFLICT (actorid) DO UPDATE
|
|
SET actorid = excluded.actorid,
|
|
actorname = excluded.actorname,
|
|
countryname = excluded.countryname,
|
|
countrytimezone = excluded.countrytimezone,
|
|
birthday = excluded.birthday,
|
|
deathday = excluded.deathday,
|
|
gender = excluded.gender,
|
|
mediumimage = excluded.mediumimage,
|
|
originalimage = excluded.originalimage,
|
|
apilink = excluded.apilink,
|
|
"url" = excluded."url",
|
|
countrycode = excluded.countrycode;
|
|
''')
|
|
return(actorsql)
|
|
|
|
def merge_crew(self):
|
|
crewsql = text('''
|
|
INSERT INTO dbo.crewdata
|
|
(seriesid,
|
|
crewid,
|
|
countryid,
|
|
type,
|
|
name,
|
|
medium,
|
|
original,
|
|
apilink,
|
|
gender,
|
|
birthday,
|
|
deathday)
|
|
SELECT seriesid,
|
|
crewid,
|
|
countryid,
|
|
type,
|
|
name,
|
|
medium,
|
|
original,
|
|
apilink,
|
|
gender,
|
|
birthday,
|
|
deathday
|
|
FROM updates.newcrewdata
|
|
ON CONFLICT (seriesid, crewid, type) DO UPDATE
|
|
SET seriesid = excluded.seriesid,
|
|
crewid = excluded.crewid,
|
|
countryid = excluded.countryid,
|
|
type = excluded.type,
|
|
name = excluded.name,
|
|
medium = excluded.medium,
|
|
original = excluded.original,
|
|
apilink = excluded.apilink,
|
|
gender = excluded.gender,
|
|
birthday = excluded.birthday,
|
|
deathday = excluded.deathday;
|
|
''')
|
|
return(crewsql)
|
|
|
|
def __del__(self):
|
|
pass
|
|
class Merger():
|
|
|
|
def __init__(self):
|
|
pass
|
|
|
|
def __del__(self):
|
|
pass
|
|
|
|
class Charactermerge():
|
|
|
|
def __init__(self):
|
|
pass
|
|
|
|
def merge(self, engine):
|
|
castsql = text('''MERGE dbo.castdata as N
|
|
USING updates.newcastdata as O
|
|
ON N.seriesid = O.seriesid and N.characterid = O.characterid and N.actorid = O.actorid
|
|
WHEN MATCHED THEN
|
|
UPDATE SET N.seriesid = O.seriesid, N.characterid = O.characterid,N.actorid = O.actorid,N.charactername = O.charactername,N.mediumimage = O.mediumimage,N.originalimage = O.originalimage,N.apilink = O.apilink,N.tvmazelink = O.tvmazelink, N.updated_on = GETDATE()
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (seriesid,characterid,actorid,charactername,mediumimage,originalimage,apilink,tvmazelink, created_on)
|
|
VALUES
|
|
(O.seriesid,O.characterid,O.actorid,O.charactername,O.mediumimage,O.originalimage,O.apilink,O.tvmazelink, GETDATE());
|
|
SELECT * FROM updates.newcastdata;
|
|
''')
|
|
with engine.connect() as con:
|
|
rs = con.execute(castsql)
|
|
#engine.commit()
|
|
|
|
actorsql = text('''MERGE dbo.actors as N
|
|
USING updates.newactordata as O
|
|
ON N.actorid = O.actorid
|
|
WHEN MATCHED THEN
|
|
UPDATE SET N.actorid = O.actorid, N.actorname = O.actorname, N.countryname = O.countryname, N.countrytimezone = O.countrytimezone, N.birthday = O.birthday, N.deathday = O.deathday, N.gender = O.gender, N.mediumimage = O.mediumimage, N.originalimage = O.originalimage, N.apilink = O.apilink, N.url = O.url, N.countrycode = O.countrycode, N.updated_on = GETDATE()
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (actorid,actorname,countryname,countrytimezone,birthday,deathday,gender,mediumimage,originalimage,apilink,url,countrycode,created_on)
|
|
VALUES
|
|
(O.actorid,O.actorname,O.countryname,O.countrytimezone,O.birthday,O.deathday,O.gender,O.mediumimage,O.originalimage,O.apilink,O.url,O.countrycode, GETDATE());
|
|
SELECT * FROM updates.newactordata;
|
|
''')
|
|
with engine.connect() as con:
|
|
rs = con.execute(actorsql)
|
|
#engine.commit()
|
|
|
|
def __del__(self):
|
|
pass
|
|
|
|
class Updatemerge():
|
|
|
|
def __init__(self):
|
|
pass
|
|
|
|
def merge(self, engine):
|
|
updatesql = text('''INSERT INTO updates.tvupdatedata (seriesid, timestamp)
|
|
select seriesid, timestamp from updates.tvupdates
|
|
ON CONFLICT (seriesid)
|
|
DO
|
|
UPDATE
|
|
SET seriesid = EXCLUDED.seriesid, timestamp = EXCLUDED.timestamp;''')
|
|
with engine.connect() as con:
|
|
rs = con.execute(updatesql)
|
|
#engine.commit()
|
|
|
|
def __del__(self):
|
|
pass
|
|
class Jsonmerge():
|
|
|
|
def __init__(self):
|
|
pass
|
|
|
|
def merge(self, engine):
|
|
seriessql = text('''INSERT INTO "[updates]".tvmaze_seriesjson (seriesid, jdata, created_on)
|
|
select seriesid, jdata, date_trunc('minute', current_timestamp) FROM "[updates]".seriesjsonnewvals
|
|
ON CONFLICT (seriesid)
|
|
DO
|
|
update
|
|
SET seriesid = EXCLUDED.seriesid, jdata = EXCLUDED.jdata, updated_on = date_trunc('minute', current_timestamp);
|
|
''')
|
|
with engine.connect() as con:
|
|
rs = con.execute(seriessql)
|
|
#engine.commit()
|
|
|
|
episodesql = text('''INSERT INTO "[updates]".tvmaze_episodejson (seriesid, jdata, created_on)
|
|
select seriesid, jdata, date_trunc('minute', current_timestamp) FROM "[updates]".episodejsonnewvals
|
|
ON CONFLICT (seriesid)
|
|
DO
|
|
update
|
|
SET seriesid = EXCLUDED.seriesid, jdata = EXCLUDED.jdata, updated_on = date_trunc('minute', current_timestamp);
|
|
''')
|
|
with engine.connect() as con:
|
|
rs = con.execute(episodesql)
|
|
#engine.commit()
|
|
|
|
charactersql = text('''INSERT INTO "[updates]".tvmaze_characterjson (seriesid, jdata, created_on)
|
|
select seriesid, jdata, date_trunc('minute', current_timestamp) FROM "[updates]".characterjsonnewvals
|
|
ON CONFLICT (seriesid)
|
|
DO
|
|
update
|
|
SET seriesid = EXCLUDED.seriesid, jdata = EXCLUDED.jdata, updated_on = date_trunc('minute', current_timestamp);
|
|
''')
|
|
with engine.connect() as con:
|
|
rs = con.execute(charactersql)
|
|
#engine.commit()
|
|
|
|
def __del__(self):
|
|
pass
|
|
|
|
class Episodemerge():
|
|
|
|
def __init__(self):
|
|
pass
|
|
|
|
def merge(self, engine):
|
|
episodesql = text('''INSERT INTO dbo.epdata (episodeid,seriesid,season,episodenumber,episodename,airdate,airtime,episodesummary,airstamp,runtime,eplink,mediumimage,originalimage,url, created_on)
|
|
select episodeid,seriesid,season,episodenumber,episodename,airdate,airtime,episodesummary,airstamp,runtime,eplink,mediumimage,originalimage,url, date_trunc('minute', current_timestamp) FROM "[updates]".newepdata
|
|
ON CONFLICT (episodeid)
|
|
DO
|
|
UPDATE
|
|
SET episodeid = EXCLUDED.episodeid, seriesid = EXCLUDED.seriesid, season = EXCLUDED.season, episodenumber = EXCLUDED.episodenumber, episodename = EXCLUDED.episodename,
|
|
airdate = EXCLUDED.airdate, airtime = EXCLUDED.airtime, episodesummary = EXCLUDED.episodesummary, airstamp = EXCLUDED.airstamp, runtime = EXCLUDED.runtime,
|
|
eplink = EXCLUDED.eplink, mediumimage = EXCLUDED.mediumimage, originalimage = EXCLUDED.originalimage, url = EXCLUDED.url, updated_on = date_trunc('minute', current_timestamp);''')
|
|
with engine.connect() as con:
|
|
rs = con.execute(episodesql)
|
|
#engine.commit()
|
|
|
|
def __del__(self):
|
|
pass
|
|
|
|
|