def get_winnings_dictionary_for_each_year(database_name, table_name):
mydb = connect_to_database(database_name)
connector = mydb.cursor()
team_names = OrderedDict([('Sunrisers Hyderabad', []),
('Rising Pune Supergiants', []),
('Kolkata Knight Riders', []),
('Kings XI Punjab', []),
('Royal Challengers Bangalore', []),
('Mumbai Indians', []), ('Delhi Daredevils', []),
('Gujarat Lions', []),
('Chennai Super Kings', []),
('Rajasthan Royals', []),
('Deccan Chargers', []),
('Kochi Tuskers Kerala', []),
('Pune Warriors', [])])
unique_years = get_unique_years(database_name, table_name)
for team in team_names:
number_of_winnings_per_year = []
for years in unique_years:
str_year = str(years)
query_to_get_winnings_per_year = "SELECT COUNT(winner) FROM" + "`" + table_name + "`" + "WHERE season = " + str_year + " AND winner ='" + team + "';"
connector.execute(query_to_get_winnings_per_year)
values = connector.fetchone()
number_of_winnings_per_year.append(values[0])
team_names[team] = number_of_winnings_per_year
return team_names
def user_login():
response.content_type = "application/json"
if conn.is_connected:
data = {}
email = request.json['email']
password = request.json['password']
connector = conn.cursor(buffered = True)
query = ("Select * from staff where staff_email ='{}' ").format(email)
connector.execute(query)
count = connector.rowcount
if count > 0:
count = 0
for (staff )in connector:
data[count] = {}
data[count]['staff_id'] =staff[0]
data[count]['dept_no'] =staff[1]
data[count]['staff_name'] = staff[2]
data[count]['staff_password'] = staff[3]
data[count]['staff_email'] = staff[4]
data[count]['staff_role'] =staff[5]
data[count]['staff_avatar'] = staff[6]
count +=1
return (data)
connector.close()
conn.close()
return ({'status':False,'Message':'Login failed' })
def main_insert():
kata = randomString(50)
nilai = random.randint(0, 100)
randoma = uuid.uuid4().hex
ket = randoma.lower()[0:stringLength]
sql = "INSERT INTO t_test_threading(kata,nilai,ket) VALUES (%s,%s,%s)"
data = (kata, nilai, ket)
conn.execute(sql, data)
def exec_update(id, nilai):
# a = nilai + 3
# b = pow(a,3)
# c = a * b
# nilai_baru = (pow(b, 2) - 4 * a * c)
nilai_baru = random.randint(10, 99)
sql_upt = "UPDATE t_test_threading SET nilai = %s WHERE id = %s" % (
nilai_baru, id)
conn.execute(sql_upt)
def get_extra_runs_per_team(database_name, matches, deliveries):
extra_runs = dict()
mydb = connect_to_database(database_name)
connector = mydb.cursor()
query_to_get_distinct_years = "select bowling_team, sum(extra_runs) from " + deliveries + " where match_id IN (select id from " + matches + " where season = 2016) group by(bowling_team);"
connector.execute(query_to_get_distinct_years)
distinct_years = connector.fetchall()
for year in range(0, len(distinct_years)):
extra_runs[distinct_years[year][0]] = distinct_years[year][1]
return (extra_runs)
def get_unique_years(database_name, table_name):
mydb = connect_to_database(database_name)
connector = mydb.cursor()
query_to_get_distinct_years = "SELECT `season` FROM" + "`" + table_name + "`" + "GROUP BY `season`;"
connector.execute(query_to_get_distinct_years)
distinct_tuples = connector.fetchall()
distinct_years = []
for years in distinct_tuples:
distinct_years.append(years[0])
return distinct_years
def update():
sql = "SELECT id,kata,nilai,ket FROM t_test_threading"
conn.execute(sql)
hasil = conn.fetchall()
for data in hasil:
id = data[0]
nilai = data[2]
t = threading.Thread(target=exec_update, args=(id, nilai))
t.start()
db.commit()
exit()
def analysis_max():
pwd = 'rlaehgus1'
engine = create_engine('mysql+mysqlconnector://root:' + pwd +
'@localhost/lotto',
echo=False)
connector = engine.connect()
#각 자리수별 뽑힌 숫자들 전체를 조회
for i in range(1, 8):
sql = "select `"
sql += str(i)
sql += "` from winlotto"
try:
nums = connector.execute(sql)
results = nums.fetchall()
#해당 숫자의 뽑힌 횟수를 하나씩증가
lottoarray = [0 for i in range(46)]
for row in results:
k = row[0]
count = lottoarray[k]
lottoarray[k] = count + 1
print(i, "자리 max count 숫자 =", lottoarray.index(max(lottoarray)))
except Exception as err:
print(str(err))
connector.close()
def analysis(time, band=3):
pwd = 'rlaehgus1'
engine = create_engine('mysql+mysqlconnector://root:' + pwd +
'@localhost/lotto',
echo=False)
connector = engine.connect()
#1부터 45까지의 배열을 생성하고 0으로 초기화
lottoarray = [0 for i in range(0, 46)]
#각 자리수별 뽑힌 숫자들 전체를 조회
for i in range(1, 7):
sql = "select `"
sql += str(i)
sql += "` from winlotto where count > {} and band = {}".format(
time, band)
try:
nums = connector.execute(sql)
results = nums.fetchall()
#해당 숫자의 뽑힌 횟수를 하나씩증가
for row in results:
k = row[0]
count = lottoarray[k]
lottoarray[k] = count + 1
except Exception as err:
print(str(err))
print("전체 숫자 당첨 카운수")
for i in range(1, len(lottoarray)):
if (i % 10) == 0:
print("") # 10개 마다 줄 바꾸기
print("[" + str(i) + ":" + str(lottoarray[i]) + "]", end=" ")
print("")
connector.close()
return (lottoarray)
def checkLast():
pwd = 'rlaehgus1'
engine = create_engine('mysql+mysqlconnector://root:' + pwd +
'@localhost/lotto',
echo=False)
connector = engine.connect()
sql = "SELECT MAX(count) FROM winlotto"
try:
count = connector.execute(sql)
result = count.fetchone()
if result[0] is None:
result = [
1,
]
except Exception as err:
print(str(err))
connector.close()
return result[0]
if __name__ == "__main__":
pwd = 'rlaehgus1'
engine = create_engine('mysql+mysqlconnector://root:' + pwd +
'@localhost/findb',
echo=False)
connector = engine.connect()
start = datetime(1996, 1, 3) # 주식시장 첫 거래일 지정
end = datetime.today() - timedelta(days=1) # yearterday
dates = pd.date_range(start=start, end=end)
# db에 저장된 가장 최근 날짜 or
begin_date = str(datetime(1969, 1, 3))
sql = 'SELECT date FROM krx_stock_price WHERE 종목코드=005930 ORDER BY date DESC LIMIT 1' #가장 최신date, 쿼리 정확도를 위해 삼성전자 사용
result = connector.execute(sql)
imsi_day = result.fetchone()
if imsi_day is not None:
latest_date = imsi_day[0].strftime('%Y%m%d')
elif imsi_day is None:
latest_date = datetime(1969, 1, 3).strftime('%Y%m%d')
# start date 찾기
for date in dates:
if date.strftime('%Y%m%d') <= latest_date:
continue
date_str = date.strftime('%Y%m%d')
df = stock_master_krx(date_str)
# In[ ]:
if __name__ == "__main__":
pwd = 'rlaehgus1'
engine = create_engine('mysql+mysqlconnector://root:'+pwd+'@localhost/findb', echo=False)
connector = engine.connect()
start = datetime(1996, 1, 3) # 주식시장 첫 거래일 지정
end = datetime.today() - timedelta(days=1) # yearterday
dates = pd.date_range(start=start, end=end)
# db에 저장된 가장 최근 날짜 or
begin_date = str(datetime(1969, 1, 3))
sql = 'SELECT date FROM krx_stock_price WHERE 종목코드=005930 ORDER BY date DESC LIMIT 1' #가장 최신date, 쿼리 정확도를 위해 삼성전자 사용
result = connector.execute(sql)
imsi_day = result.fetchone()
if imsi_day is not None:
latest_date = imsi_day[0].strftime('%Y%m%d')
elif imsi_day is None:
latest_date = datetime(1969, 1, 3).strftime('%Y%m%d')
# start date 찾기
for date in dates:
if date.strftime('%Y%m%d') <= latest_date:
continue
date_str = date.strftime('%Y%m%d')
df = stock_master_krx(date_str)
def insert():
pwd = 'rlaehgus1'
engine = create_engine('mysql+mysqlconnector://root:' + pwd +
'@localhost/lotto',
echo=False)
connector = engine.connect()
for dic in lotto_list:
count = dic["회차"]
numbers = dic["번호"]
persons = dic["당첨자"]
amounts = dic["금액"]
odd = 0 # 홀수
even = 0 # 짝수
yellow = 0 # 1~10
blue = 0 # 11~20
red = 0 # 21~30
green = 0 # 31~40
gray = 0 # 41 ~ 45
band = 0 #숫자 밴드 카운트
winNumbers = []
lotto_continue = 0
lotto_2continue = 0
lotto_3continue = 0
lotto_4continue = 0
print("insert to database at " + str(count))
numberlist = str(numbers).split(",")
winNumbers.append(int(numberlist[0]))
winNumbers.append(int(numberlist[1]))
winNumbers.append(int(numberlist[2]))
winNumbers.append(int(numberlist[3]))
winNumbers.append(int(numberlist[4]))
winNumbers.append(int(numberlist[5].split("+")[0]))
winNumbers.append(int(numberlist[5].split("+")[1]))
persons = int(persons)
total = sum(winNumbers[0:6])
# 홀수갯수 구하기
for i in range(0, 6):
if (winNumbers[i] % 2 != 0):
odd = odd + 1
even = 6 - odd # 짝수갯수는 6 - 홀수갯수
# bamd 구분하기
for i in range(0, 6):
if (winNumbers[i] <= 10):
yellow += 1
elif (winNumbers[i] >= 11 and winNumbers[i] <= 20):
blue += 1
elif (winNumbers[i] >= 21 and winNumbers[i] <= 30):
red += 1
elif (winNumbers[i] >= 31 and winNumbers[i] <= 40):
green += 1
elif (winNumbers[i] >= 41 and winNumbers[i] <= 45):
gray += 1
if (yellow > 0):
band += 1
if (blue > 0):
band += 1
if (red > 0):
band += 1
if (green > 0):
band += 1
if (gray > 0):
band += 1
#continure number 구하기
#1 연번
if (winNumbers[1] - winNumbers[0] == 1):
lotto_continue += 1
elif (winNumbers[2] - winNumbers[1] == 1):
lotto_continue += 1
elif (winNumbers[3] - winNumbers[2] == 1):
lotto_continue += 1
elif (winNumbers[4] - winNumbers[3] == 1):
lotto_continue += 1
elif (winNumbers[5] - winNumbers[4] == 1):
lotto_continue += 1
#2 연번
if (winNumbers[2] - winNumbers[0] == 2):
lotto_2continue += 1
lotto_continue -= 1
elif (winNumbers[3] - winNumbers[1] == 2):
lotto_2continue += 1
lotto_continue -= 1
elif (winNumbers[4] - winNumbers[2] == 2):
lotto_2continue += 1
lotto_continue -= 1
elif (winNumbers[5] - winNumbers[3] == 2):
lotto_2continue += 1
lotto_continue -= 1
#3 연번
if (winNumbers[3] - winNumbers[0] == 3):
lotto_3continue += 1
lotto_2continue -= 1
elif (winNumbers[4] - winNumbers[1] == 3):
lotto_3continue += 1
lotto_2continue -= 1
elif (winNumbers[5] - winNumbers[2] == 3):
lotto_3continue += 1
lotto_2continue -= 1
#4 연번
if (winNumbers[4] - winNumbers[0] == 4):
lotto_4continue += 1
lotto_3continue += 1
elif (winNumbers[5] - winNumbers[1] == 4):
lotto_4continue += 1
lotto_3continue += 1
#끝자리수 횟수 확인
ending_digit = []
for i in range(0, 6):
if (winNumbers[i] <= 9):
ending_digit.append(winNumbers[i])
elif (winNumbers[i] >= 10 and winNumbers[i] <= 19):
ending_digit.append(winNumbers[i] - 10)
elif (winNumbers[i] >= 20 and winNumbers[i] <= 29):
ending_digit.append(winNumbers[i] - 20)
elif (winNumbers[i] >= 30 and winNumbers[i] <= 39):
ending_digit.append(winNumbers[i] - 30)
elif (winNumbers[i] >= 40 and winNumbers[i] <= 45):
ending_digit.append(winNumbers[i] - 40)
unique_elements, counts_elements = np.unique(ending_digit,
return_counts=True)
max_ending_digit_count = int(max(counts_elements)) # max count
# 아래 코드를 사용하면 sql문 에러 발생으로 시행되지 않음
# sql = "INSERT INTO winlotto (count, 1, 2, 3, 4, 5, 6, 7, persons, amounts)\
# VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
# connector.execute(sql, count, i1, i2, i3, i4, i5, i6, 7, persons, amounts)
# sql문 생성시 table name 으로 표기함.
sql = "INSERT INTO winlotto VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
try:
connector.execute(sql, count,winNumbers[0], winNumbers[1],winNumbers[2],winNumbers[3],winNumbers[4],\
winNumbers[5],winNumbers[6],persons, amounts, total, odd, even,yellow, blue, red, green, gray, band,\
lotto_continue, lotto_2continue, lotto_3continue, lotto_4continue, max_ending_digit_count)
except Exception as err:
print(str(err))
break
connector.close()
def get_required_rate_of_return():
sql = 'SELECT * FROM bond ORDER BY date DESC LIMIT 1'
result = connector.execute(sql)
required = result.fetchone()
return (required[1])
def insert_srim(icode, ref_year, value_per_share10, value_per_share5,
value_per_share, type):
# ref_year = ref_year + 1 #RIM은 현재 값이 아닌 과거값이여서 현재는 현재-1 임.
if type == 'quarter':
'''
ref_year3month = datetime.strptime(ref_year,'%Y%m')
ref_year3month = ref_year3month + relativedelta(months=+2)
ref_year3month = ref_year3month.strftime('%Y%m')
'''
ref_year3month = datetime.strptime(ref_year, '%Y%m')
ref_year3month = ref_year3month + relativedelta(months=-2)
ref_year3month = ref_year3month.strftime('%Y%m')
if type == 'year':
# WHERE .. AND NOT ... 조건 사용. 두개 table 사용
sql = 'INSERT INTO srim_year (code, date, close, year) SELECT code, date, close, YEAR(date) FROM krx_stock_price WHERE code = %s AND YEAR(date) = %s AND NOT EXISTS (SELECT * FROM srim_year WHERE code = %s AND YEAR(date) = %s)'
connector.execute(sql, icode, ref_year, icode, ref_year)
# 위 sql에서 WHERE ... AND NOT ... 조건 이면 update는 시행됨.
sql = 'UPDATE srim_year SET value_per_share10 = %s, value_per_share5 = %s, value_per_share = %s WHERE year = %s AND code = %s'
connector.execute(sql, value_per_share10, value_per_share5,
value_per_share, ref_year, icode)
sql = 'UPDATE srim_year SET roe = (SELECT `ROE(%)` FROM naverfs_y WHERE code = %s AND YEAR(date) = %s) WHERE year = %s AND code = %s'
connector.execute(sql, icode, ref_year, ref_year, icode)
elif type == 'quarter':
# 분기별로 구하것으로 year포맷을 YYYYmm으로 쿼리
# 3개월 단위로 데이터 사용하기...
sql = 'INSERT INTO srim_quarter (code, date, close, year) SELECT code, date, close, date_format(date,"%Y%m") FROM krx_stock_price WHERE code = %s AND date_format(date,"%Y%m") BETWEEN %s AND %s AND NOT EXISTS (SELECT * FROM srim_quarter WHERE code = %s AND date_format(date,"%Y%m") BETWEEN %s AND %s)'
connector.execute(sql, icode, ref_year3month, ref_year, icode,
ref_year3month, ref_year)
# 위 sql에서 WHERE ... AND NOT ... 조건 이면 update는 시행됨.
sql = 'UPDATE srim_quarter SET value_per_share10 = %s, value_per_share5 = %s, value_per_share = %s WHERE year BETWEEN %s AND %s AND code = %s'
connector.execute(sql, value_per_share10, value_per_share5,
value_per_share, ref_year3month, ref_year, icode)
sql = 'UPDATE srim_quarter SET roe = (SELECT `ROE(%)` FROM naverfs_q WHERE code = %s AND date_format(date,"%Y%m") = %s) WHERE year BETWEEN %s AND %s AND code = %s'
connector.execute(sql, icode, ref_year, ref_year3month, ref_year,
icode)
return None
value_per_share = 0
ref_year = 0
pwd = 'rlaehgus1'
engine = create_engine('mysql+mysqlconnector://root:' + pwd +
'@localhost/findb',
echo=False)
connector = engine.connect()
#요구수익률 가져오기
required_rate_of_return = get_required_rate_of_return()
required_rate_of_return = required_rate_of_return / 100 # 단위 % 환산
#국채 금리구하기
sql = 'SELECT * FROM naverfs_y ORDER BY date LIMIT 1'
result = connector.execute(sql)
s_year = result.fetchone()
s_year = s_year[0]
df_bond_kospi = get_bond_kospi(s_year)
df_code_name = get_code_name()
li_code = df_code_name.loc[:, 'code']
li_name = df_code_name.loc[:, 'name']
'''
li_code = ['139480']
li_name = ['국순당']
'''
for i in range(0, len(li_code)):