def start():
x = DbType.SYBASE
logger = JLogger("/workspace/outme.log", "test")
db_temp = DbConn(x, "sa", "myPassword", "dbsybase", "5000", "tempdb",
logger)
db_master = DbConn(x, "sa", "myPassword", "dbsybase", "5000", "master",
logger)
pipeline(db_master, db_temp)
def load_user(userid):
try:
return User(
DbConn.DbConn(app.config['MYSQL_USERNAME'],
app.config['MYSQL_PASSWORD'],
app.config['MYSQL_DB']), int(userid))
except InvalidUserError, e:
return None
def movie_detail(year):
db = DbConn()
sql = "select moviecd from boxoffice2 where dailydate between TO_DATE('" + str(
year) + "/01/01', 'YYYY/MM/DD') and TO_DATE('" + str(
year) + "/12/31', 'YYYY/MM/DD') group by moviecd"
print(sql)
cd_list = db.execute(sql)
db.disconnect()
for movie_cd in cd_list:
insert_moviedetail(movie_cd[0])
print(len(cd_list), "개 인서트 끝~~~~")
def insert_boxoffice(showrange):
db = DbConn()
url = BOX_OFFICE_URL + "?key=" + API_KEY + "&targetDt=" + str(showrange)
data = requests.get(url).json()
# showRange = data['boxOfficeResult']['showRange']
dailyBoxOfficeList = data['boxOfficeResult']['dailyBoxOfficeList']
dailydate = datetime.strptime(showrange, '%Y%m%d').strftime('%Y-%m-%d')
insert_query = """
INSERT INTO boxOffice2
(dailydate, rnum, mrank, rankinten, rankoldandnew, moviecd, movienm, opendt, salesamt, salesshare, salesinten, saleschange, salesacc, audicnt, audiinten, audichange, audiacc, scrncnt, showcnt)
VALUES (TO_DATE(:dailydate, 'YYYY-MM-DD'), :rnum, :mrank, :rankinten, :rankoldandnew, :moviecd, :movienm, TO_DATE(:opendt, 'YYYY-MM-DD'), :salesamt, :salesshare, :salesinten, :saleschange, :salesacc, :audicnt, :audiinten, :audichange, :audiacc, :scrncnt, :showcnt)
"""
select_query = "SELECT * FROM BOXOFFICE2 WHERE dailydate = TO_DATE(:dailydate, 'YYYY-MM-DD') AND MOVIECD = :MOVIECD"
for i in dailyBoxOfficeList: # 하루에 1위~10위 데이터 들어있음
p = re.compile('[0-9]')
movieCd_re = p.findall(i['movieCd'])
print(i['movieCd'], movieCd_re)
if len(movieCd_re) != 8:
continue
openDt = i['openDt']
if len(openDt) < 3:
continue
query_params = {
'dailydate': dailydate,
'rnum': i['rnum'],
'mrank': i['rank'],
'rankinten': i['rankInten'],
'rankoldandnew': i['rankOldAndNew'],
'moviecd': i['movieCd'],
'movienm': i['movieNm'],
'opendt': openDt,
'salesamt': i['salesAmt'],
'salesshare': i['salesShare'],
'salesinten': i['salesInten'],
'saleschange': i['salesChange'],
'salesacc': i['salesAcc'],
'audicnt': i['audiCnt'],
'audiinten': i['audiInten'],
'audichange': i['audiChange'],
'audiacc': i['audiAcc'],
'scrncnt': i['scrnCnt'],
'showcnt': i['showCnt']
}
# print(query_params)
select_result = db.execute(select_query, {
"dailydate": dailydate,
"MOVIECD": i['movieCd']
})
if len(select_result) == 0:
db.execute(insert_query, query_params)
# print(showRange, movieNm,'성공적으로 DB에 입력했슴')
# else :
# print(showRange, movieNm,'는 안대요 안대~!')
print(showrange, "작업 완료. -----")
db.disconnect()
import requests import time from pathlib import Path import sys from DbConn import * db = DbConn() sql = """ SELECT * FROM BOXOFFICE """ result = db.execute(sql) print(type(result), result)
def dict_dot(a, b):
return sum(a[key] * b[key] for key in a if key in b)
def dict_corr(a, b):
keys = list(a.viewkeys() | b.viewkeys())
return numpy.corrcoef([a.get(x, 0) for x in keys],
[b.get(x, 0) for x in keys])[0, 1]
config = configobj.ConfigObj(
infile=open('/home/shaldengeki/llAnimuBot/config.txt', 'r'))
db = DbConn.DbConn(username=config['DB']['llBackup']['username'],
password=config['DB']['llBackup']['password'],
database=config['DB']['llBackup']['name'])
# assemble a list of topics.
sat_db = DbConn.DbConn(username=config['DB']['llAnimu']['username'],
password=config['DB']['llAnimu']['password'],
database=config['DB']['llAnimu']['name'])
sats = [
eti.Topic(db, topic_id).load()
for topic_id in sat_db.table('sats').fields('ll_topicid').where(
completed=1).order('ll_topicid ASC').list(valField='ll_topicid')
]
# assemble a dict of all users and their post counts in each SAT.
users = {}
for sat in sats:
def insert_moviedetail(moviecd):
db = DbConn()
url = MOVIE_DETAIL_URL + '?key=' + API_KEY + '&movieCd=' + str(moviecd)
data = requests.get(url).json()
movie_info_list = data['movieInfoResult']['movieInfo']
insert_query = '''
INSERT INTO movie (movieCd,movieNm,movieNmEn,prdtYear,showTm,openDt,prdtStatNm,typeNm,nations,genre,genreSub,director,actors1,actors2,actors3,actors4,audits,prodCd,prodNm,distCd,distNm,staffs)
VALUES(:movieCd,:movieNm,:movieNmEn,:prdtYear,:showTm,:openDt,:prdtStatNm,:typeNm,:nations,:genre,:genreSub,:director,:actors1,:actors2,:actors3,:actors4,:audits,:prodCd,:prodNm,:distCd,:distNm,:staffs)
'''
select_query = "SELECT * FROM movie WHERE MOVIECD = :MOVIECD"
#변수 조건
actor1 = movie_info_list['actors'][0]['peopleNm'] if len(
movie_info_list['actors']) > 0 else ''
actor2 = movie_info_list['actors'][1]['peopleNm'] if len(
movie_info_list['actors']) > 1 else ''
actor3 = movie_info_list['actors'][2]['peopleNm'] if len(
movie_info_list['actors']) > 2 else ''
actor4 = movie_info_list['actors'][3]['peopleNm'] if len(
movie_info_list['actors']) > 3 else ''
nations = movie_info_list['nations'][0]['nationNm'] if len(
movie_info_list['nations']) > 0 else ''
genre = movie_info_list['genres'][0]['genreNm'] if len(
movie_info_list['genres']) > 0 else ''
genreSub = movie_info_list['genres'][1]['genreNm'] if len(
movie_info_list['genres']) > 1 else ''
director = movie_info_list['directors'][0]['peopleNm'] if len(
movie_info_list['directors']) > 0 else ''
audits = movie_info_list['audits'][0]['watchGradeNm'] if len(
movie_info_list['audits']) > 0 else ''
openDt = datetime.strptime(movie_info_list['openDt'], '%Y%m%d')
prodCd = ""
prodNm = ''
distCd = ''
distNm = ''
if len(movie_info_list['companys']) > 0:
for n in movie_info_list['companys']:
if n['companyPartNm'] == '제작사':
prodCd = n['companyCd']
prodNm = n['companyNm']
break
for n in movie_info_list['companys']:
if n['companyPartNm'] == '배급사':
distCd = n['companyCd']
distNm = n['companyNm']
break
staffs = movie_info_list['staffs'][0]['peopleNm'] if len(
movie_info_list['staffs']) > 0 else ''
query_params = {
'movieCd': movie_info_list['movieCd'],
'movieNm': movie_info_list['movieNm'],
'movieNmEn': movie_info_list['movieNmEn'],
'prdtYear': movie_info_list['prdtYear'],
'showTm': movie_info_list['showTm'],
'openDt': openDt,
'prdtStatNm': movie_info_list['prdtStatNm'],
'typeNm': movie_info_list['typeNm'],
'nations': nations,
'genre': genre,
'genreSub': genreSub,
'director': director,
'actors1': actor1,
'actors2': actor2,
'actors3': actor3,
'actors4': actor4,
'audits': audits,
'prodCd': prodCd,
'prodNm': prodNm,
'distCd': distCd,
'distNm': distNm,
'staffs': staffs
}
select_result = db.execute(select_query, {"MOVIECD": moviecd})
if len(select_result) == 0:
# print(insert_query, query_params)
db.execute(insert_query, query_params)
print(movie_info_list['movieNm'], "작업 완료. -----")
db.disconnect()
def before_request():
g.db = DbConn.DbConn(app.config['MYSQL_USERNAME'],
app.config['MYSQL_PASSWORD'], app.config['MYSQL_DB'])
# try:
# if sproc:
# if sproc[0] in name_proc.keys():
# name_proc[sproc[0]] = name_proc[sproc[0]].append(sproc[1])
# else:
# name_proc[sproc[0]] = [sproc[1]]
# except AttributeError:
# print("Not found: {0}".format(sproc[0]))
# print(len(name_proc.keys()))
# print("".join(txt))
from Nums import DbType
from Utils import JLogger
x = DbType.SYBASE
logger = JLogger("/workspace/outme.log", "test")
db = DbConn(x, "sa", "myPassword", "dbsybase", "5000", "tempdb", logger)
# statement = """
# CREATE TABLE mockData(
# pid int not null,
# img image
# )
# """
# DataUtils.uploadImage(db.conn, "/workspace/samples/img.png",
# statement = """
# SELECT * FROM blobtest
# """
# queryList = db.queryToList(statement)
# for y in queryList:
# print(y)
# statement = """
def insert_boxoffice(showrange):
db = DbConn()
url = BOX_OFFICE_URL+"?key="+API_KEY+"&targetDt="+showrange
data = requests.get(url).json()
boxOfficeResult = data['boxOfficeResult']
showRange = boxOfficeResult['showRange']
dailyBoxOfficeList = boxOfficeResult['dailyBoxOfficeList']
insert_query = """
INSERT INTO boxOffice
(showrange, rnum, rank, rankinten, rankoldandnew, moviecd, movienm, opendt, salesamt, salesshare, salesinten, saleschange, salesacc, audicnt, audiinten, audichange, audiacc, scrncnt, showcnt)
VALUES (:showrange, :rnum, :rank, :rankinten, :rankoldandnew, :moviecd, :movienm, :opendt, :salesamt, :salesshare, :salesinten, :saleschange, :salesacc, :audicnt, :audiinten, :audichange, :audiacc, :scrncnt, :showcnt)
"""
select_query = "SELECT * FROM BOXOFFICE WHERE SHOWRANGE = :SHOWRANGE AND MOVIECD = :MOVIECD"
for i in dailyBoxOfficeList: # 하루에 1위~10위 데이터 들어있음
salesInten = i['salesInten']
openDt = i['openDt']
if len(openDt) < 3:
continue
audiInten = i['audiInten']
rnum = i['rnum']
movieCd = i['movieCd']
audiAcc = i['audiAcc']
showCnt = i['showCnt']
audiCnt = i['audiCnt']
salesAmt = i['salesAmt']
movieNm = i['movieNm']
rank = i['rank']
audiChange = i['audiChange']
rankInten = i['rankInten']
scrnCnt = i['scrnCnt']
rankOldAndNew = i['rankOldAndNew']
salesChange = i['salesChange']
salesAcc = i['salesAcc']
salesShare = i['salesShare']
query_params = {
'showrange' : showRange,
'rnum' : rnum,
'rank' : rank,
'rankinten' : rankInten,
'rankoldandnew' : rankOldAndNew,
'moviecd' : movieCd,
'movienm' : movieNm,
'opendt' : openDt,
'salesamt' : salesAmt,
'salesshare' : salesShare,
'salesinten' : salesInten,
'saleschange' : salesChange,
'salesacc' : salesAcc,
'audicnt' : audiCnt,
'audiinten' : audiInten,
'audichange' : audiChange,
'audiacc' : audiAcc,
'scrncnt' : scrnCnt,
'showcnt' : showCnt
}
select_result = db.execute(select_query, {"SHOWRANGE": showRange, "MOVIECD": movieCd})
if len(select_result) == 0:
db.execute(insert_query, query_params)
# print(showRange, movieNm,'성공적으로 DB에 입력했슴')
# else :
# print(showRange, movieNm,'는 안대요 안대~!')
print(showrange, "작업 완료. -----")
db.disconnect()
