#导入新高新低数据 import pymysql def toTransNewRecordData(db_conn_a,dbcursor_a,db_conn_b,dbcursor_b,trade_date): # #数据库A的连接信息；NAS数据库 # db_a_config = { # 'host': '192.168.0.222', # 'user': 'root', # 'password': '1qazse42W3', # 'db': 'ry', # 'charset': 'utf8mb4', # } # # 数据库B的连接信息；NAS数据库 # db_b_config = { # 'host': '192.168.0.222', # 'user': 'root', # 'password': '1qazse42W3', # 'db': 'mojin', # 'charset': 'utf8mb4', # } # # 数据库A的连接信息；腾讯云数据库 # db_a_config = { # 'host': '124.223.98.178', # 'user': 'root', # 'password': '1qazse42W3', # 'db': 'ry', # 'charset': 'utf8mb4', # } # # 数据库B的连接信息；腾讯云数据库 # db_b_config = { # 'host': '124.223.98.178', # 'user': 'root', # 'password': '1qazse42W3', # 'db': 'mojin', # 'charset': 'utf8mb4', # } # try: # # 连接数据库A # db_a_conn = pymysql.connect(**db_a_config) # db_a_cursor = db_a_conn.cursor() # # 连接数据库B # db_b_conn = pymysql.connect(**db_b_config) # db_b_cursor = db_b_conn.cursor() # 连接数据库A db_a_conn = db_conn_a db_a_cursor = dbcursor_a # 连接数据库B db_b_conn = db_conn_b db_b_cursor = dbcursor_b trans_date = trade_date stock_basic_map = {} #查询交易日数据 更新数据到 8月9日 # sql_trade_qurey = 'select date,week,trade from trade_dates where trade = %s and date > %s' sql_trade_qurey = 'select date,week,trade from trade_dates where trade = %s and date = %s' sql_trade = "trading" db_a_cursor.execute(sql_trade_qurey,(sql_trade,trade_date)) trading_dates = db_a_cursor.fetchall() #查询东财基础数据 industries_map = {} sql_stockindustry = "select code,name from stock_index where trade_day = '2022-07-01'" db_a_cursor.execute(sql_stockindustry) industries = db_a_cursor.fetchall() for industry in industries: code,name = industry industries_map[name] = (code,name) #查询基础数据 sql_stockbasic = "select code,name,blemind2,blemind3,listdate from stock_basis" db_a_cursor.execute(sql_stockbasic) stock_basic_datas = db_a_cursor.fetchall() for row in stock_basic_datas: code,name,blemind2,blemind3,listdate = row if code == '数据来源：东方财富Choice数据' : continue # print('stock_basic_datas: ',code,name,blemind2) stock_basic_map[code] = (code,name,blemind2,blemind3,listdate,industries_map[blemind2][0]) for row in trading_dates: trading_date,week,trade = row print('start transfer newrecord data ',trading_date , ' data.') # if trading_date == '2023-08-11': # print('break then finished transfer newrecord ',trading_date , ' data.') # break sql_stock_query = 'select code,trade_day, open,close,high,low,islimit,isdrop,differrange,trade_days,volumn,amount,differrange10,differrange20,differrange60,avg_volume20,freefloat_market_value,total_market_value,agencies_hold from stocks where trade_day = %s' db_a_cursor.execute(sql_stock_query,trading_date) datas = db_a_cursor.fetchall() stocks_map = {} #遍历数据，并组装数据，转存到目标库中 for stock in datas: code,trade_day, open,close,high,low,islimit,isdrop,differrange,trade_days,volumn,amount,differrange10,differrange20,differrange60,avg_volume20,freefloat_market_value,total_market_value,agencies_hold = stock stocks_map[code] = (code,trade_day, open,close,high,low,islimit,isdrop,differrange,trade_days,volumn,amount,differrange10,differrange20,differrange60,avg_volume20,freefloat_market_value,total_market_value,agencies_hold) #查询日交易数据 sql_stock_query = 'select code,trade_day, isHigh,isLow from stocks_new_record where trade_day = %s' db_a_cursor.execute(sql_stock_query,trading_date) datas = db_a_cursor.fetchall() #遍历数据，并组装数据，转存到目标库中 for stock_new_record in datas: code,trade_day, isHigh,isLow = stock_new_record business_date = trading_date security_code = code security_name = stock_basic_map[code][1] security_type = '' if code[0:2] == '30': security_type = '30' elif code[0:2] == '00': security_type = 'SZ' elif code[0:2] == '60': security_type = 'SH' elif code[0:3] == '688': security_type = '688' # print(security_type,security_name,security_code) opening_price = stocks_map[code][2] closing_price = stocks_map[code][3] highest_price = stocks_map[code][4] highest_price_of_range = trading_date change_percentage = stocks_map[code][8] volume = stocks_map[code][10] recently_high = isHigh dong_cai_industry_index_level2 = stock_basic_map[code][2] day_on_market = stocks_map[code][9] is_st_stock='' hit_low_recently = isLow lowest_price_range = '' is_star_st_stock = '' # create_time = sql_insert = f"INSERT INTO high_low_of_three_hundred (business_date,security_code,security_name,security_type,opening_price,closing_price,highest_price,highest_price_of_range,change_percentage,volume,recently_high,dong_cai_industry_index_level2,day_on_market,is_st_stock,hit_low_recently,lowest_price_range,is_star_st_stock) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" db_b_cursor.execute(sql_insert, (business_date,security_code,security_name,security_type,opening_price,closing_price,highest_price,highest_price_of_range,change_percentage,volume,recently_high,dong_cai_industry_index_level2,day_on_market,is_st_stock,hit_low_recently,lowest_price_range,is_star_st_stock)) #提交数据到数据库B中 db_b_conn.commit() print('successed transfer ',trading_date , ' data.' ) print('\r\n') ####high_low_of_three_hundred # `id` bigint(0) NOT NULL AUTO_INCREMENT, # `business_date` date NOT NULL COMMENT '业务日期', # `security_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '证券代码', # `security_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '证券名称', # `security_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '证券类型', # `opening_price` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '开盘价', # `closing_price` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, # `highest_price` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, # `highest_price_of_range` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, # `change_percentage` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '涨跌幅(%)', # `volume` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '成交量(股)', # `recently_high` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '近期创阶段新高最新[近N日内]', # `dong_cai_industry_index_level2` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '东财行业指数2级', # `day_on_market` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '上市天数', # `is_st_stock` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '是否为ST股票 1-是 0-否', # `hit_low_recently` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, # `lowest_price_range` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, # `is_star_st_stock` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '是否为*ST股票 1-是 0-否', # `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间', ##### # print(datas) # print(stock_basic_map) # print(trading_dates) # 查询数据库A中的数据表a1和a2的数据，并进行关联 #db_a_cursor.execute('SELECT a1.id, a1.date, a1.user, a2.userName, a2.count FROM a1 JOIN a2 ON a1.id = a2.id') #data_to_transfer = db_a_cursor.fetchall() # 删除数据库B中表c的数据（可选，如果需要覆盖表c的数据） # db_b_cursor.execute('DELETE FROM c') # 插入数据到数据库B中的表c中 # for row in data_to_transfer: # id, date, user, userName, count = row # sql_insert = f"INSERT INTO c (id, date, user, userName, count) VALUES (%s, %s, %s, %s, %s)" # db_b_cursor.execute(sql_insert, (id, date, user, userName, count)) # 提交数据到数据库B中 # db_b_conn.commit() print("数据传输完成！") # except Exception as e: # print(f"出现错误：{e}") # finally: # # 关闭连接 # if db_a_cursor: # db_a_cursor.close() # if db_a_conn: # db_a_conn.close() # if db_b_cursor: # db_b_cursor.close() # if db_b_conn: # db_b_conn.close() def main(): # #数据库A的连接信息；NAS数据库 # db_a_config = { # 'host': '192.168.0.222', # 'user': 'root', # 'password': '1qazse42W3', # 'db': 'ry', # 'charset': 'utf8mb4', # } # # 数据库B的连接信息；NAS数据库 # db_b_config = { # 'host': '192.168.0.222', # 'user': 'root', # 'password': '1qazse42W3', # 'db': 'mojin', # 'charset': 'utf8mb4', # } # 数据库A的连接信息；腾讯云数据库 db_a_config = { 'host': '124.223.98.178', 'user': 'root', 'password': '1qazse42W3', 'db': 'ry', 'charset': 'utf8mb4', } # 数据库B的连接信息；腾讯云数据库 db_b_config = { 'host': '124.223.98.178', 'user': 'root', 'password': '1qazse42W3', 'db': 'mojin', 'charset': 'utf8mb4', } try: # 连接数据库A db_a_conn = pymysql.connect(**db_a_config) db_a_cursor = db_a_conn.cursor() # 连接数据库B db_b_conn = pymysql.connect(**db_b_config) db_b_cursor = db_b_conn.cursor() trade_day = '2023-08-09' toTransNewRecordData(db_a_conn,db_a_cursor,db_b_conn,db_b_cursor,trade_day) except Exception as e: print(f"出现错误：{e}") finally: # 关闭连接 if db_a_cursor: db_a_cursor.close() if db_a_conn: db_a_conn.close() if db_b_cursor: db_b_cursor.close() if db_b_conn: db_b_conn.close() if __name__ == "__main__": main()