327 lines
12 KiB
Python
327 lines
12 KiB
Python
from sqlalchemy import text
|
|
|
|
class SQLGenerate():
|
|
|
|
def __init__(self):
|
|
pass
|
|
|
|
def merge_updates(self):
|
|
updatesql = text('''
|
|
MERGE updates.tvupdatedata
|
|
USING updates.tvupdates
|
|
ON updates.tvupdatedata.seriesid = updates.tvupdates.seriesid
|
|
WHEN MATCHED THEN
|
|
UPDATE SET updates.tvupdatedata.seriesid = updates.tvupdates.seriesid,updates.tvupdatedata.timestamp = updates.tvupdates.timestamp
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (seriesid, timestamp)
|
|
VALUES (updates.tvupdates.seriesid, updates.tvupdates.timestamp);
|
|
''')
|
|
return(updatesql)
|
|
|
|
def merge_series(self):
|
|
seriessql = text('''
|
|
MERGE dbo.seriesdata
|
|
USING updates.newseriesdata ON
|
|
dbo.seriesdata.seriesid = updates.newseriesdata.seriesid
|
|
WHEN MATCHED THEN UPDATE
|
|
SET
|
|
dbo.seriesdata.seriesid = updates.newseriesdata.seriesid,
|
|
dbo.seriesdata.thetvdb = updates.newseriesdata.thetvdb,
|
|
dbo.seriesdata.tvrage = updates.newseriesdata.tvrage,
|
|
dbo.seriesdata.imdb = updates.newseriesdata.imdb,
|
|
dbo.seriesdata.[language] = updates.newseriesdata.[language],
|
|
dbo.seriesdata.genres = updates.newseriesdata.genres,
|
|
dbo.seriesdata.name = updates.newseriesdata.name,
|
|
dbo.seriesdata.status = updates.newseriesdata.status,
|
|
dbo.seriesdata.premiered = updates.newseriesdata.premiered,
|
|
dbo.seriesdata.days = updates.newseriesdata.days,
|
|
dbo.seriesdata.[time] = updates.newseriesdata.[time],
|
|
dbo.seriesdata.runtime = updates.newseriesdata.runtime,
|
|
dbo.seriesdata.rating = updates.newseriesdata.rating,
|
|
dbo.seriesdata.[type] = updates.newseriesdata.[type],
|
|
dbo.seriesdata.summary = updates.newseriesdata.summary,
|
|
dbo.seriesdata.medium = updates.newseriesdata.medium,
|
|
dbo.seriesdata.original = updates.newseriesdata.original,
|
|
dbo.seriesdata.[weight] = updates.newseriesdata.[weight],
|
|
dbo.seriesdata.networkid = updates.newseriesdata.networkid,
|
|
dbo.seriesdata.webchannelid = updates.newseriesdata.webchannelid,
|
|
dbo.seriesdata.officialsite = updates.newseriesdata.officalsite,
|
|
dbo.seriesdata.url = updates.newseriesdata.url,
|
|
dbo.seriesdata.serieslink = updates.newseriesdata.serieslink,
|
|
dbo.seriesdata.updated_on = GETDATE()
|
|
WHEN NOT MATCHED THEN INSERT
|
|
(seriesid,
|
|
thetvdb,
|
|
tvrage,
|
|
imdb,
|
|
[language],
|
|
genres,
|
|
name,
|
|
status,
|
|
premiered,
|
|
days,
|
|
[time],
|
|
runtime,
|
|
rating,
|
|
[type],
|
|
summary,
|
|
medium,
|
|
original,
|
|
weight,
|
|
networkid,
|
|
webchannelid,
|
|
officialsite,
|
|
url,
|
|
serieslink,
|
|
created_on)
|
|
VALUES (updates.newseriesdata.seriesid,
|
|
updates.newseriesdata.thetvdb,
|
|
updates.newseriesdata.tvrage,
|
|
updates.newseriesdata.imdb,
|
|
updates.newseriesdata.[language],
|
|
updates.newseriesdata.genres,
|
|
updates.newseriesdata.name,
|
|
updates.newseriesdata.status,
|
|
updates.newseriesdata.premiered,
|
|
updates.newseriesdata.days,
|
|
updates.newseriesdata.[time],
|
|
updates.newseriesdata.runtime,
|
|
updates.newseriesdata.rating,
|
|
updates.newseriesdata.[type],
|
|
updates.newseriesdata.summary,
|
|
updates.newseriesdata.medium,
|
|
updates.newseriesdata.original,
|
|
updates.newseriesdata.weight,
|
|
updates.newseriesdata.networkid,
|
|
updates.newseriesdata.webchannelid,
|
|
updates.newseriesdata.officalsite,
|
|
updates.newseriesdata.url,
|
|
updates.newseriesdata.serieslink,
|
|
GETDATE());
|
|
''')
|
|
return(seriessql)
|
|
|
|
def merge_episodes(self):
|
|
episodesql = text('''
|
|
MERGE dbo.epdata
|
|
USING updates.newepdata ON
|
|
dbo.epdata.episodeid = updates.newepdata.episodeid
|
|
WHEN MATCHED THEN UPDATE
|
|
SET
|
|
dbo.epdata.episodeid = updates.newepdata.episodeid,
|
|
dbo.epdata.seriesid = updates.newepdata.seriesid,
|
|
dbo.epdata.season = updates.newepdata.season,
|
|
dbo.epdata.episodenumber = updates.newepdata.episodenumber,
|
|
dbo.epdata.episodename = updates.newepdata.episodename,
|
|
dbo.epdata.airdate = updates.newepdata.airdate,
|
|
dbo.epdata.airtime = updates.newepdata.airtime,
|
|
dbo.epdata.episodesummary = updates.newepdata.episodesummary,
|
|
dbo.epdata.airstamp = updates.newepdata.airstamp,
|
|
dbo.epdata.runtime = updates.newepdata.runtime,
|
|
dbo.epdata.eplink = updates.newepdata.eplink,
|
|
dbo.epdata.mediumimage = updates.newepdata.mediumimage,
|
|
dbo.epdata.originalimage = updates.newepdata.originalimage,
|
|
dbo.epdata.[url] = updates.newepdata.[url],
|
|
dbo.epdata.updated_on = GETDATE()
|
|
WHEN NOT MATCHED THEN INSERT
|
|
(episodeid,
|
|
seriesid,
|
|
season,
|
|
episodenumber,
|
|
episodename,
|
|
airdate,
|
|
airtime,
|
|
episodesummary,
|
|
airstamp,
|
|
runtime,
|
|
eplink,
|
|
mediumimage,
|
|
originalimage,
|
|
[url],
|
|
created_on)
|
|
VALUES (updates.newepdata.episodeid,
|
|
updates.newepdata.seriesid,
|
|
updates.newepdata.season,
|
|
updates.newepdata.episodenumber,
|
|
updates.newepdata.episodename,
|
|
updates.newepdata.airdate,
|
|
updates.newepdata.airtime,
|
|
updates.newepdata.episodesummary,
|
|
updates.newepdata.airstamp,
|
|
updates.newepdata.runtime,
|
|
updates.newepdata.eplink,
|
|
updates.newepdata.mediumimage,
|
|
updates.newepdata.originalimage,
|
|
updates.newepdata.[url],
|
|
GETDATE());
|
|
''')
|
|
return(episodesql)
|
|
|
|
def merge_cast(self):
|
|
castsql = text('''
|
|
MERGE 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());
|
|
''')
|
|
return(castsql)
|
|
|
|
def merge_actors(self):
|
|
actorsql = text('''
|
|
MERGE actors as N
|
|
USING updates.newactordata as O
|
|
ON N.actorid = O.actorid and N.actorname = O.actorname
|
|
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());
|
|
''')
|
|
return(actorsql)
|
|
|
|
def merge_crew(self):
|
|
crewsql = text('''
|
|
MERGE dbo.crewdata as N
|
|
USING updates.newcrewdata as O ON
|
|
N.[type] = O.[type]
|
|
and N.seriesid = O.seriesid
|
|
and N.crewid = O.crewid
|
|
WHEN MATCHED THEN UPDATE
|
|
SET
|
|
N.seriesid = O.seriesid,
|
|
N.crewid = O.crewid,
|
|
N.countryid = O.countryid,
|
|
N.type = O.type,
|
|
N.name = O.name,
|
|
N.medium = O.medium,
|
|
N.original = O.original,
|
|
N.apilink = O.apilink,
|
|
N.gender = O.gender,
|
|
N.birthday = O.birthday,
|
|
N.deathday = O.deathday,
|
|
N.updated_on = GETDATE()
|
|
WHEN NOT MATCHED THEN INSERT
|
|
(seriesid,
|
|
crewid,
|
|
countryid,
|
|
type,
|
|
name,
|
|
medium,
|
|
original,
|
|
apilink,
|
|
gender,
|
|
birthday,
|
|
deathday,
|
|
created_on)
|
|
VALUES (O.seriesid,
|
|
O.crewid,
|
|
O.countryid,
|
|
O.type,
|
|
O.name,
|
|
O.medium,
|
|
O.original,
|
|
O.apilink,
|
|
O.gender,
|
|
O.birthday,
|
|
O.deathday,
|
|
GETDATE());
|
|
''')
|
|
return(crewsql)
|
|
|
|
def merge_country(self):
|
|
countrysql = text('''
|
|
MERGE countrydata
|
|
USING updates.newcountrydata
|
|
ON countrydata.id = updates.newcountrydata.id
|
|
WHEN MATCHED THEN
|
|
UPDATE SET countrydata.name = updates.newcountrydata.name, countrydata.code = updates.newcountrydata.code, countrydata.timezone = updates.newcountrydata.timezone, countrydata.updated_on = GETDATE()
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (name, code, timezone, created_on)
|
|
VALUES
|
|
(updates.newcountrydata.name,updates.newcountrydata.code,updates.newcountrydata.timezone, GETDATE());
|
|
''')
|
|
return(countrysql)
|
|
|
|
def __del__(self):
|
|
pass
|
|
|
|
class Charactermerge():
|
|
|
|
def __init__(self):
|
|
pass
|
|
|
|
def __del__(self):
|
|
pass
|
|
|
|
class Episodemerge():
|
|
def __init_(self):
|
|
pass
|
|
|
|
def __del__(self):
|
|
pass
|
|
|
|
class Jsonmerge():
|
|
|
|
def __init__(self):
|
|
pass
|
|
|
|
def merge(self, engine):
|
|
seriessql = text('''BEGIN TRANSACTION;
|
|
MERGE dbo.seriesjson as N
|
|
USING updates.seriesjsonnewvals as O
|
|
ON N.seriesid = O.seriesid and N.jdata = O.jdata
|
|
WHEN MATCHED THEN
|
|
UPDATE SET N.seriesid = O.seriesid, N.jdata = O.jdata, N.updated_on = GETDATE()
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (seriesid, jdata, created_on)
|
|
VALUES
|
|
(O.seriesid, O.jdata, GETDATE());
|
|
COMMIT;
|
|
''')
|
|
with engine.connect() as con:
|
|
rs = con.execute(seriessql)
|
|
#engine.commit()
|
|
|
|
episodesql = text('''BEGIN TRANSACTION;
|
|
MERGE updates.tvmaze_episodejson as N
|
|
USING updates.episodejsonnewvals as O
|
|
ON N.seriesid = O.seriesid
|
|
WHEN MATCHED THEN
|
|
UPDATE SET N.seriesid = O.seriesid, N.jdata = O.jdata, N.updated_on = GETDATE()
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (seriesid, jdata, created_on)
|
|
VALUES
|
|
(O.seriesid, O.jdata, GETDATE());
|
|
SELECT * FROM updates.tvmaze_episodejson;COMMIT;
|
|
''')
|
|
with engine.connect() as con:
|
|
rs = con.execute(episodesql)
|
|
#engine.commit()
|
|
|
|
charactersql = text('''BEGIN TRANSACTION;
|
|
MERGE updates.tvmaze_characterjson as N
|
|
USING updates.characterjsonnewvals as O
|
|
ON N.seriesid = O.seriesid
|
|
WHEN MATCHED THEN
|
|
UPDATE SET N.seriesid = O.seriesid, N.jdata = O.jdata, N.updated_on = GETDATE()
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (seriesid, jdata, created_on)
|
|
VALUES
|
|
(O.seriesid, O.jdata, GETDATE());
|
|
SELECT * FROM updates.tvmaze_characterjson;COMMIT;
|
|
''')
|
|
with engine.connect() as con:
|
|
rs = con.execute(charactersql)
|
|
#engine.commit()
|
|
|
|
def __del__(self):
|
|
pass
|
|
|
|
|
|
|