#-*- coding: utf-8 -*-

importrequestsfrom bs4 importBeautifulSoupimportlxmlimportjsonimportreimporttimeimporttushare as tsimportpandas as pdimportpymysqlfrom sqlalchemy importcreate_engine

pymysql.install_as_MySQLdb()defTstockbasic(conn):#調用stock_basic，獲取股票代碼、股票名稱、所屬行業、上市日期、上市場所

cursor=conn.cursor()#讀取數據庫已存儲數據

sql_stk_data = pd.read_sql("select ts_code from stk_basic_data",con=conn).values#創建空列表，存儲ts_code

code_list =[]

incount= upcount = fincount = fupcount =0

stk_basic= pro.stock_basic(list_status=‘L‘, fields=‘ts_code,symbol,name,industry,list_date,exchange‘)for index, row instk_basic.iterrows():#new_code = row[‘ts_code‘][7:]+row[‘ts_code‘][:6]

new_code = row[‘ts_code‘]

stk_code= row[‘symbol‘]

stk_name= row[‘name‘]

industry= row[‘industry‘]

list_date= row[‘list_date‘]

exchange= row[‘exchange‘]#code_list.append(new_code)

code_list.append(new_code[7:]+new_code[:6])#if new_code in sql_stk_data.values:

if new_code insql_stk_data:

sql= "update stk_basic_data set industry = ‘%s‘ where ts_code = ‘%s‘" %(industry,new_code)

cursor.execute(sql)

upcount+= 1

print(upcount)‘‘‘try:

cursor.execute(sql)

upcount +=1

print(upcount)

except:

fupcount +=1‘‘‘

else:

sql= "insert into stk_basic_data(ts_code,stk_code,stk_name,industry,list_date,exchange) values(‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘)"%(new_code,stk_code,stk_name,industry,list_date,exchange)

cursor.execute(sql)try:

cursor.execute(sql)

incount+=1

print(incount)except:

fincount+=1

print("股票列表成功更新：%d 條數據，更新失敗：%d條數據" %(upcount, fupcount))print("股票列表成功新增：%d 條數據，新增失敗：%d條數據" %(incount, fincount))

conn.commit()

conn.close()returncode_listif __name__ == "__main__":#設置tushare社區token

ts.set_token(‘ac16b470869c5d82db5033ae9288f77b282d2b5519507d6d2c72fdd7‘)

pro=ts.pro_api()#創建數據庫連接

conn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123456‘, database=‘quantitative_trading_data‘, charset=‘utf8‘)

engine= create_engine(‘mysql://root:[email?protected]/quantitative_trading_data?charset=utf8‘)#獲取當前年月日，傳遞給各函數

current = time.strftime("%Y%m%d", time.localtime())#調用Tstockbasich獲取股票列表，并完成數據更新

code_list = Tstockbasic(conn)

總結

以上是生活随笔為你收集整理的mysql load 占位符_Mysql占位符插入的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。