mysql 占位符使用_Mysql占位符插入

#-*- 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)