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)):