#!/usr/bin/env python # -*- coding: utf-8 -*- """ @Time : 2020-03-23 18:31 @Author : Zdanjuan @Site : @File : xd_clienttrader.py @Software: PyCharm """ import pywinauto from pywinauto import clipboard from pywinauto import keyboard import pandas as pd import win32api import re import requests from skimage import io import time import io as mio from PIL import Image import win32com.client import getpass import pytesseract import warnings import pyautogui import datetime import os from sqlalchemy import create_engine warnings.filterwarnings('ignore') from connect_wifi import connect_wifi from iptest import get_proxy_ip,set_global_proxy,get_ip_data pc_name111 = getpass.getuser() #返回用户名 shell = win32com.client.Dispatch("WScript.Shell") #使用启动独立的进程 shortcut = shell.CreateShortCut(f"C:\\Users\\{pc_name111}\\Desktop\\信达证券.lnk") path1 = shortcut.Targetpath path = path1 class XDClientTrader: """ 基于同花顺委托下单程序的"信达客户端"自动交易程序 """ def __init__(self, account_name, exe_path=path): self.path = exe_path self.BALANCE_CONTROL_ID_GROUP = { # "资金余额": 0x3F4, "可用金额": 0x3f8, # "可取金额": 0x3f9, "股票市值": 0x3f6, "总资产": 0x3f7, } self.trade_control_id_group = { 's1': 0x3FD, 's2': 0x3FE, 's3': 0x3FF, 's4': 0x409, 's5': 0x408, 'b1': 0x3FA, 'b2': 0x401, 'b3': 0x402, 'b4': 0x40B, 'b5': 0x40C, } self.BALANCE_MENU_PATH = ["查询[F4]", "资金股票"] self.today = datetime.datetime.today().strftime('%Y-%m-%d') self.account_name = account_name self.table_df = [] # self.is_bond = is_bond # 0:股票 1：债券 self.is_buy = 1 self.verify_code_wrong = 0 self.position = {'当日成交':(103,345),'中心':(333,333)} # 获取债券列表 engine_iddb = create_engine( 'mysql+pymysql://cn_ainvest_db:cn_ainvest_sd3a1@rm-2zewagytttzk6f24xno.mysql.rds.aliyuncs.com:3306/iddb') df_bond = pd.read_sql(f'select * from conbond_basic_info', engine_iddb) self.df_list = df_bond.drop_duplicates('Bond_Ticker')['Bond_Ticker'].values.tolist() # 用户券商信息 sql = f"select * from ainvest_usercount where username='{account_name}'" df_count = self.download_data_from_db(sql, 'ai_strategy_update_iddb') self.securities_name = str(list(df_count['securities_username'])[0]) self.securities_password = str(list(df_count['securities_password'])[0]) self.communication_password = str(list(df_count['communication_password'])[0]) # 转换股票代码 def tranTicker(self, x): x = str(x) if len(x) == 8: pass else: if len(x) == 6: if x.startswith('6'): x = 'SH' + x else: x = 'SZ' + x else: num = 6 - len(x) x = 'SZ' + num * '0' + x return x # 从数据库获取数据 def download_data_from_db(self, sql, schema_name): """ create connection with DB 阿里云数据库连接 :param sql: 查询语句 :param schema_name: 数据库名称 :return: 查询结果数据 """ cnx = create_engine( 'mysql+pymysql://cn_ainvest_db:cn_ainvest_sd3a1@rm-2zewagytttzk6f24xno.' 'mysql.rds.aliyuncs.com:3306/%s?charset=utf8' % schema_name, encoding="utf-8", echo=False) df = pd.read_sql(sql, con=cnx) return df def get_open_price(self, tick): str_ticker = tick.lower() price_all = requests.get('http://hq.sinajs.cn/list=' + str_ticker).text price_ticker = str(re.findall(r'var hq_str_(.*?);', price_all)) # 开盘价 open_price = float(price_ticker.split(',')[1]) # 昨日收盘价 price_pre_close = float(price_ticker.split(',')[2]) # 实时股价 price_real_time = float(price_ticker.split(',')[3]) return open_price def exit(self): self._app.kill() def login(self): try: self._app = pywinauto.Application().connect( path=self.path, timeout=1 ) # print('连接成功') a.exit() except Exception: pass start_time = time.time() reset_proxy_to_default() # 初始化proxy信息 # 重置mac set_mac = SetMac() set_mac.run() if not connect_wifi('ZTE_A5DB9D', '1234567890'): # 'Redmi K40','123456789' self.log.error("无法建立网络连接，请检查配置") else: self.log.info("网络已就绪") get_ip_times = 0 writer = ExcelDataWriter() # 初始化ip_records 表格 used_ip = writer.get_unavailable_ips(broker, account_name) while get_ip_times < 3: try: item = get_ip_data() # 获取动态IP if item['ip'] in used_ip: get_ip_times += 1 else: self.log.info(f'代理IP已经获取{item}') break except Exception as e: self.log.error(f'获取IP失败，请检查网络连接{e}') time.sleep(5) # raise ConnectionError(f"获取IP失败，请检查网络连接。错误详情: {e}") # 设置全局代理 proxy_ip = item['ip'] proxy_port = item['port'] set_global_proxy(proxy_ip, proxy_port, enable=True) # exit_ip = get_proxy_ip(proxy_ip,proxy_port) time_with_change_proxy = time.time() - start_time self.log.info('全局代理设置成功') insert_data = {'as_of_date': datetime.datetime.fromtimestamp(start_time).strftime('%Y-%m-%d %H:%M:%S.%f'), 'broker': broker, 'account': account_name, 'proxy_ip': proxy_ip, 'proxy_port': proxy_port, 'ip_location': item['prov'] + ':' + item['city'], # 'exit_ip': exit_ip, 'ip_cross_check_result': '一致', 'ip_switch_time': time_with_change_proxy, # 'ip_expire_time':item['expire'] 'ip_survival_time': '3min' } # 写入数据表 writer = ExcelDataWriter() # 初始化ip_records 表格 writer.write_data(insert_data) self._app = pywinauto.Application().start(self.path) # wait login window ready self._app.top_window().Edit1.wait("ready") time.sleep(0.1) self._app.top_window().Edit1.type_keys(self.securities_name) time.sleep(0.1) self._app.top_window().Edit2.type_keys(self.securities_password) time.sleep(0.1) # while True: time.sleep(1) self._app.top_window().set_focus() self._app.top_window().Edit1.wait("ready") # 输入验证码 id_code = self.verify_code() time.sleep(0.2) self._app.top_window().Edit3.type_keys('{BACKSPACE}' * 6) time.sleep(0.2) self._app.top_window().Edit3.type_keys(id_code) time.sleep(0.2) pywinauto.keyboard.send_keys("{ENTER}") time.sleep(0.5) # 验证码错误的情况 try: self._app.top_window().set_focus() result_idcode = self._app.top_window().window(control_id=0x3EC, class_name='Static').window_text() if '验证码错误' in result_idcode: self._app.top_window().window(control_id=0x2, class_name="Button").click() time.sleep(0.5) # 重新获取id_code id_code = self.verify_code() time.sleep(0.2) self._app.top_window().Edit3.type_keys('{BACKSPACE}' * 6) time.sleep(0.2) self._app.top_window().Edit3.type_keys(id_code) time.sleep(0.2) pywinauto.keyboard.send_keys("{ENTER}") time.sleep(0.5) except Exception as e: pass time.sleep(3) self.main_wnd = self._app.window(title='网上股票交易系统5.0') # self.main_wnd = self._app.top_window() # 关闭广告 try: # self._app.top_window().set_focus() self._app.window(control_id=0x0, class_name="#32770").close() # pywinauto.keyboard.send_keys("{ESC}") # time.sleep(0.2) # pywinauto.keyboard.send_keys("{ENTER}") except Exception as e: # print(e) pass time.sleep(0.5) # 转换股票代码 def tranTicker(self, x): x = str(x) if len(x) == 8: pass else: if len(x) == 6: if x.startswith('6'): x = 'SH' + x else: x = 'SZ' + x else: num = 6 - len(x) x = 'SZ' + num * '0' + x return x # 窗口最小化 def minimize(self): self.main_wnd.minimize() # 获取验证码 def verify_code(self): time.sleep(1) self._app.top_window().Static.click() time.sleep(0.2) self._app.top_window().window(control_id=0x5DB, class_name='Static').capture_as_image().save( r'temp.png') time.sleep(0.2) im = io.imread(r'temp.png') id_code = pytesseract.image_to_string(im, lang='eng', config='--psm 6 --oem 3 -c tessedit_char_whitelist=0123456789').strip() # id_code = pytesseract.image_to_string(Image.open('temp.png')) id_code = id_code.replace(' ', '') return id_code # 复制数据时获取验证码 def get_copy_date_verify_code(self): time.sleep(1) while True: try: self._app.top_window().Static.click() break except Exception as e: continue time.sleep(0.2) self._app.top_window().window(control_id=0x965, class_name='Static').capture_as_image().save( r'copy.png') time.sleep(0.2) im = io.imread(r'copy.png') id_code = pytesseract.image_to_string(im, lang='eng', config='--psm 6 --oem 3 -c tessedit_char_whitelist=0123456789').strip() # id_code = pytesseract.image_to_string(Image.open('temp.png')) id_code = id_code.replace(' ', '') return id_code # 买 def buy(self, stock_no, price, num): time.sleep(0.5) # 点击买入 pyautogui.press('f1') time.sleep(0.2) pyautogui.press('f1') self.__trade(stock_no, price, num) def __trade(self, stock_no, price, amount): time.sleep(0.2) # 设置股票代码 self.main_wnd.window(control_id=0x408, class_name="Edit").click() self.main_wnd.window(control_id=0x408, class_name="Edit").set_text(str(stock_no)) time.sleep(0.5) # # 无法判断市场类型 # try: # self._app.top_window().set_focus() # result_a = self._app.top_window().window(control_id=0xBBA, class_name='Static').window_text() # result_b = self._app.top_window().window(control_id=0x7AF, class_name='Button').window_text() # result_c = self._app.top_window().window(control_id=0x7CD, class_name='Button').window_text() # if '该证券代码在多个市场存在，客户端无法判' in result_a: # if ticker_name in result_b: # self._app.top_window().window(control_id=0x7AF, class_name="Button").click() # time.sleep(0.5) # elif ticker_name in result_c: # self._app.top_window().window(control_id=0x7CD, class_name="Button").click() # time.sleep(0.5) # except Exception as e: # pass if price in ['b1','b2','b3','b4','b5','s1','s2','s3','s4','s5']: price = float(self.main_wnd.window(control_id=self.trade_control_id_group[price], class_name="Static").window_text()) # 设置价格 self.main_wnd.window(control_id=0x409, class_name="Edit").set_text(str(price)) time.sleep(0.5) # # 判断股票还是债券(1：债券) # if self.is_bond == 1: # # 买 # unit = self.main_wnd.window(control_id=0x579, class_name="Static").window_text() # if unit[-2] == '手': # amount = int(int(amount) / 10) # # 卖 # if self.is_buy == 0: # sell_num = int(self.main_wnd.window(control_id=0x40E, class_name="Static").window_text()) # if sell_num < int(amount): # amount = int(int(amount) / 10) # print('卖出数量已减少10倍') # 设置股数目 self.main_wnd.window(control_id=0x40A, class_name="Edit").set_text(str(amount)) time.sleep(0.5) # 点击卖出or买入 self.main_wnd.window(control_id=0x3EE, class_name="Button").click() time.sleep(1) # 小数后只能填2位情况 self._app.top_window().set_focus() result_1 = self._app.top_window().window(control_id=0x410, class_name='Static').window_text() if result_1 == '委托价格的小数部分应为 2 位，是否继续？': # 点击“否” self._app.top_window().window(control_id=0x7, class_name='Button').click() # 保留两位小数截断 price = str(price)[:-1] # 重新设置价格 self.main_wnd.window(control_id=0x409, class_name="Edit").set_text(str(price)) time.sleep(0.5) # 点击卖出or买入 self.main_wnd.window(control_id=0x3EE, class_name="Button").click() time.sleep(1) self._app.top_window().set_focus() result_1 = self._app.top_window().window(control_id=0x410, class_name='Static').window_text() else: # 摘取合同信息 self.__parse_contract(result_1) time.sleep(0.2) # # 摘取合同信息 # self._app.top_window().set_focus() # result_1 = self._app.top_window().window(control_id=0x410, class_name='Static').window_text() # self.__parse_contract(result_1) # time.sleep(0.2) # 确认买入 pywinauto.keyboard.send_keys("{ENTER}") time.sleep(0.5) # # 摘取合同信息2 # result_2 = self._app.top_window().window(control_id=0x410, class_name='Static').window_text() # # print(result_2) # self.__parse_contract(result_2) # time.sleep(0.2) self._app.top_window().set_focus() # 确认合同 pywinauto.keyboard.send_keys("{ENTER}") time.sleep(1) self._app.top_window().set_focus() # 确认合同 pywinauto.keyboard.send_keys("{ESC}") time.sleep(0.5) self._app.top_window().set_focus() # 确认合同 pywinauto.keyboard.send_keys("{ENTER}") time.sleep(0.5) # 卖 def sell(self, stock_no, price, num): time.sleep(0.5) # 点击卖出 # pywinauto.keyboard.send_keys("{F2}") pyautogui.press('f2') time.sleep(0.5) pyautogui.press('f2') self.is_buy = 0 self.__trade(stock_no, price, num) # 撤单 def get_cancel_entrust(self, ticker): # # 进入撤单页面 # # self._app.top_window().set_focus() # time.sleep(0.5) # pyautogui.press('f1') # time.sleep(0.5) pyautogui.press('f3') # pyautogui.press('f5') time.sleep(1) # 删除框内ticker self.main_wnd.window(control_id=0xD14, class_name='Edit').click() for i in range(7): pywinauto.keyboard.send_keys("{DELETE}") # 输入ticker self.main_wnd.window(control_id=0xD14, class_name='Edit').type_keys(ticker) time.sleep(0.2) # 选中ticker pywinauto.keyboard.send_keys("{ENTER}") time.sleep(0.1) # 点击撤单 self.main_wnd.window(control_id=0x44B, class_name='Button').click() time.sleep(0.1) # 确认撤单 pywinauto.keyboard.send_keys("{ENTER}") time.sleep(0.2) # result = self._app.top_window().window(control_id=0x3EC, class_name='Static').window_text() # print(result) pywinauto.keyboard.send_keys("{ENTER}") time.sleep(0.2) pywinauto.keyboard.send_keys("{ESC}") time.sleep(1) # 全撤 def cancel_entrust(self): # # 进入撤单页面 self._app.top_window().set_focus() # time.sleep(0.5) # pyautogui.press('f1') # time.sleep(0.5) pyautogui.press('f3') # pyautogui.press('f5') time.sleep(1) # 一键全撤 self.main_wnd.window(control_id=0x3001D1C, class_name='Button').click() self._app.top_window().set_focus() # self.main_wnd.window(control_id=0x6, class_name='Button').click() time.sleep(1) # 确认买入 pywinauto.keyboard.send_keys("{ENTER}") time.sleep(0.5) pywinauto.keyboard.send_keys("{ENTER}") # 获取未成交单 def get_unsettled_trades(self): time.sleep(0.5) pyautogui.press('f1') time.sleep(0.5) pyautogui.press('f3') time.sleep(0.5) # 刷新页面 pyautogui.press('f5') time.sleep(0.2) pyautogui.press('f5') time.sleep(0.2) # 复制表格数据 self._copy_data() if self.verify_code_wrong == 1: # 验证码错误时将self.verify_code_wrong置1 self.get_order() self.verify_code_wrong = 0 # 验证码正确时将self.verify_code_wrong置0 self._data_to_df() self.df_unsettled = self.df return self.df_unsettled # 获取当日实际持仓 def get_position(self): """获取资金情况""" pyautogui.press('f1') time.sleep(0.5) pyautogui.press('f4') time.sleep(0.5) # 刷新页面 pyautogui.press('f5') time.sleep(0.2) pyautogui.press('f5') time.sleep(0.2) # 复制表格数据 self._copy_data() if self.verify_code_wrong == 1: # 验证码错误时将self.verify_code_wrong置1 self.get_order() self.verify_code_wrong = 0 # 验证码正确时将self.verify_code_wrong置0 self._data_to_df() self.df_position = self.df # 处理self.df_position格式 del (self.df_position['交易所']) del (self.df_position['股东代码']) del (self.df_position['当前股数']) del (self.df_position['Unnamed: 14']) del (self.df_position['买入成交']) self.df_position.rename(columns={"证券代码": "Ticker", "证券名称": "Ticker_name", "股份余额": "Number_transactions", "可卖数量": "cash", "冻结数量": "frozen_quantity", "参考成本价": "cost_price", "当前价": "market_price", "最新市值": "market_value", "浮动盈亏": "profit_and_loss", "盈亏比例(%)": "profit_loss_ratio", }, inplace=True) return self.df_position # 获取资金情况 def get_balance(self): """获取资金情况""" pyautogui.press('f1') time.sleep(0.5) pyautogui.press('f4') time.sleep(0.5) # 刷新页面 pyautogui.press('f5') time.sleep(0.2) pyautogui.press('f5') time.sleep(0.2) result = {} self.main_wnd.window(control_id=0x51C, class_name="CVirtualGridCtrl").click() # 复制表格数据 keyboard.send_keys('^C') # 获取验证码 id_code = self.get_copy_date_verify_code() time.sleep(0.2) self._app.top_window().Edit.type_keys('{BACKSPACE}' * 6) time.sleep(0.2) self._app.top_window().Edit3.type_keys(id_code) time.sleep(0.2) pywinauto.keyboard.send_keys("{ENTER}") time.sleep(1) # 若验证码错误 try: if self._app.top_window().window(control_id=0x966, class_name='Static').window_text() == '验证码错误！！': self._app.top_window().window(control_id=0x2, class_name='Button').click() self.balance() except Exception as e: pass # 数据转为Dateframe data_table = clipboard.GetData() df_table = pd.read_csv(mio.StringIO(data_table), delimiter='\t', na_filter=False) # table_dict = df_table.to_dict('records') result['股票市值'] = float(df_table['总市值'].values[0]) result['可用金额'] = float(df_table['可用金额'].values[0]) result['总资产'] = float(df_table['总资产'].values[0]) result['冻结金额'] = float(df_table['冻结金额'].values[0]) return result # 获取左边窗口 def _get_left_handle(self): while True: try: self.left_handle = self.main_wnd.window(control_id=0x81, class_name='SysTreeView32') self.left_handle.wait('ready', 2) # sometime can't find handle ready, must retry break except Exception as ex: print(ex) pass # 复制数据 def _copy_data(self): # 复制表格数据 keyboard.send_keys('^C') # 获取验证码 id_code = self.get_copy_date_verify_code() time.sleep(0.2) self._app.top_window().Edit.type_keys('{BACKSPACE}' * 6) time.sleep(0.2) self._app.top_window().Edit3.type_keys(id_code) time.sleep(0.2) pywinauto.keyboard.send_keys("{ENTER}") time.sleep(1) # 若验证码错误 try: result_id = self._app.top_window().window(control_id=0x966, class_name='Static').window_text() if '验证码错误' in result_id: self._app.top_window().window(control_id=0x2, class_name='Button').click() self.verify_code_wrong = 1 else: self.verify_code_wrong = 0 except Exception as e: self.verify_code_wrong = 0 pass # 复制的数据转为DataFrame def _data_to_df(self): # 数据转为Dateframe data_table = clipboard.GetData() df_table = pd.read_csv(mio.StringIO(data_table), delimiter='\t', na_filter=False) table_dict = df_table.to_dict('records') if len(table_dict) == 0: self.df = [] return else: data = [] for i in table_dict: data.append(pd.DataFrame.from_dict(i, orient='index').T) self.df = pd.concat(data, ignore_index=True) # 获取委托单 def get_order(self): # 点击当日委托 path = ['查询[F4]', '当日委托'] self._get_left_handle() # 获取self.left_handle self.left_handle.get_item(path, exact=False).click_input(button=u'left', double=False, wheel_dist=0, pressed=u'') x, y = win32api.GetCursorPos() pywinauto.mouse.click(button='left', coords=(x, y + 100)) # 刷新页面 pyautogui.press('f5') time.sleep(0.2) pyautogui.press('f5') time.sleep(0.2) self._copy_data() if self.verify_code_wrong == 1: # 验证码错误时将self.verify_code_wrong置1 self.get_order() self.verify_code_wrong = 0 # 验证码正确时将self.verify_code_wrong置0 self._data_to_df() # print(self.df) return self.df # 获取当日成交 def get_today_trades(self): """ 返回： self.table_df:汇总前表格 self.df: 汇总后表格 """ time.sleep(1) # 点击当日成交 path = ['查询[F4]', '当日成交'] self._get_left_handle() # 获取self.left_handle self.left_handle.get_item(path, exact=False).click_input(button=u'left', double=True, wheel_dist=0, pressed=u'') # x_1, y_1 = win32api.GetCursorPos() # pywinauto.mouse.click(button='left', coords=(x_1, y_1 + 50)) # x_2, y_2 = win32api.GetCursorPos() # pywinauto.mouse.click(button='left', coords=(x_2, y_2 + 25)) # 关闭广告 try: self._app.top_window().set_focus() time.sleep(0.5) pywinauto.keyboard.send_keys("{ESC}") time.sleep(0.5) # pywinauto.keyboard.send_keys("{ENTER}") except Exception as e: pass # 刷新页面 pyautogui.press('f5') time.sleep(0.2) pyautogui.press('f5') time.sleep(0.2) # 数据转为DataFrame self._copy_data() if self.verify_code_wrong == 1: # 验证码错误时将self.verify_code_wrong置1 self.get_today_trades() self.verify_code_wrong = 0 # 验证码正确时将self.verify_code_wrong置0 self._data_to_df() if len(self.df) == 0: return self.table_df = self.df # 获取汇总前self.table_df # 窗口最大化 try: self.main_wnd.maximize() except Exception as e: pass time.sleep(0.5) # 数据汇总 pyautogui.click(self.position['中心']) time.sleep(0.1) # 右键 pyautogui.rightClick() # 右键 time.sleep(0.2) # 点击常见汇总 pyautogui.press('down') time.sleep(0.2) pyautogui.press('down') time.sleep(0.2) pyautogui.press('enter') time.sleep(0.2) # 点击代码+操作 pyautogui.press('down') time.sleep(0.2) pyautogui.press('enter') time.sleep(0.2) self._copy_data() if self.verify_code_wrong == 1: # 验证码错误时将self.verify_code_wrong置1 self.get_today_trades() self.verify_code_wrong = 0 # 验证码正确时将self.verify_code_wrong置0 self._data_to_df() # 获取self.df self.df['证券代码'] = self.df['证券代码'].map(lambda x: str("%06d" % int(x))) self.table_df['证券代码'] = self.table_df['证券代码'].map(lambda x: str("%06d" % int(x))) return self.df # 交易记录入库（一） def save_trades_sql(self): As_Of_Date = datetime.datetime.today().strftime('%Y-%m-%d') self.get_today_trades() if len(self.df) == 0: print('无当日成交---------------------') # return False else: # 通过展示所有字段,摘取所需字段 need_info_df = self.df[['证券代码', '证券名称', '操作', '成交数量', '成交均价']] need_info_df['Account_name'] = self.account_name need_info_df['As_Of_Date'] = As_Of_Date need_info_df['操作'] = need_info_df['操作'].apply( lambda x: '卖' if x == '卖出' else x) need_info_df['操作'] = need_info_df['操作'].apply( lambda x: '卖' if x == '证券卖出' else x) need_info_df['操作'] = need_info_df['操作'].apply( lambda x: '买' if x == '证券买入' else x) need_info_df['操作'] = need_info_df['操作'].apply( lambda x: '买' if x == '买入' else x) need_info_df.rename(columns={"证券代码": "Ticker", "证券名称": "Ticker_name", "操作": "Operate", "成交数量": "Number_transactions", "成交均价": "Average_price", }, inplace=True) need_info_df['Ticker'] = need_info_df['Ticker'].map( lambda x: 'SH' + str("%06d" % int(x)) if str("%06d" % int(x))[0] == '6' or str("%06d" % int(x))[ 0] == '5' else 'SZ' + str("%06d" % int(x))) # 去掉债券前面的SH/SZ need_info_df['Ticker'] = need_info_df['Ticker'].apply( lambda x: x.strip('SH').strip('SZ') if x.strip('SH').strip('SZ') in self.df_list else x) pd.set_option('display.max_columns', None) pd.set_option('display.max_rows', None) # print(need_info_df) # 入库 try: return need_info_df engine_auto_update_web_strategy = create_engine('mysql+pymysql://cn_ainvest_db:cn_ainvest_sd3a1@rm-2zewagytttzk6f24xno.mysql.rds.aliyuncs.com:3306/auto_update_web_strategy', encoding="utf-8", echo=False) need_info_df.to_sql('daily_transaction_record',engine_auto_update_web_strategy,if_exists='append',index=False,chunksize=1000) print(self.account_name,"入库成功!") except Exception as e: # print(e) print(self.account_name,"入库失败!!!!!!!!!!!!!!!!!!!!!!!!!!") # 交易记录存表（二） def save_trade_table(self): # 获取当日成交 As_Of_Date = datetime.datetime.today().strftime('%Y%m%d') As_Of_Date = As_Of_Date[-4:] # df = self.get_today_trades() if len(self.df) == 0: return else: # 填写备注 df_trade = self._get_df_trade() # pc_name111 = getpass.getuser() # 返回用户名 # df_trade = pd.read_excel(f'C:\\Users\\{pc_name111}\\Desktop\\{self.today}_股票交易列表.xls') df_trade['用户名'] = df_trade['用户名'].map(lambda x:str(x)) df_user = df_trade[df_trade['用户名'].isin([self.account_name])] df_user = df_user.drop_duplicates('股票代码') strategy_ticker_list = df_user['股票代码'].tolist() self.df['备注'] = '客户操作' self.df.loc[self.df['证券代码'].isin(strategy_ticker_list), '备注'] = '策略选股' # 填写成交时间 # if len(self.table_df) == 0: # pass # else: df_time = self.table_df.drop_duplicates('证券代码') time_ticker_list = df_time['证券代码'].tolist() for i in time_ticker_list: df_ticker_one = self.table_df[self.table_df['证券代码'].isin([i])] time = max(df_ticker_one['成交时间']) self.df.loc[self.df['证券代码'].isin([i]), '成交时间'] = time return self.df # self.df.to_excel(f'C:\\Users\\{pc_name111}\\Desktop\\{self.account_name}_{As_Of_Date}.xls') # self.df.to_excel(f'C:\\Users\\{pc_name111}\\Desktop\\{As_Of_Date}_交易记录\\{self.account_name}_{As_Of_Date}.xls') print('存表成功') # 读取交易指令表 def _get_df_trade(self): engine_user_center = create_engine( 'mysql+pymysql://cn_ainvest_db:cn_ainvest_sd3a1@rm-2zewagytttzk6f24xno.mysql.rds.aliyuncs.com:3306/user_center', encoding="utf-8", echo=False) today = str(datetime.date.today()) df_trade = pd.read_sql(f'select * from trade_instruction where As_Of_Date = "{today}"', engine_user_center) return df_trade # 解析合同 @staticmethod def __parse_contract(result): """ 解析买入卖出的结果 """ # 股东帐号：A217477596 # 证券代码：600000 # 买入价格：3.330 # 买入数量：100 result_list = result.split('\n') for i in result_list[:-1]: print(i,end=', ') if __name__ == '__main__': account_name = str('15810963933') is_bond = 0 user = str('802000078531') password = str('198910') a = XDClientTrader(account_name) a.login() # # 买入 # a.buy('600000', '3.33','100') # # 卖出 # a.sell('600000', '3.33','100') # # 撤单 # a.get_cancel_entrust('600000') # # 获取未成交单 # print(a.get_unsettled_trades()) # # 获取当日实际持仓 # print(a.get_position()) # # 获取资金情况 # print(a.balance()) # 获取委托单 # print(a.get_order()) # # 获取当日成交 # a.get_today_trades() # print(a.df) # # 交易记录入库 # a.save_trades_sql() # # 交易记录存表 # a.save_trade_table()