从tushare网站获取股票基本信息并存入数据库中的python源代码

技术2022-07-10 108

要对股票进行分析，需要关注股票的基本信息，例如上市时间、市值，所属行业等等，本文以从tushare网站获取相关信息为例，展示如何获取并存入到mysql数据库中，闲言少叙，直接上代码：

#coding:utf8 import tushare as ts import pandas as pd from sqlalchemy import create_engine import pymysql import configparser import time startTime=int(time.time()) # print('StartTime'+str(startTime)) # print(type(startTime)) conf = configparser.ConfigParser() conf.read('config.ini') userName = conf['db']['name'] password= conf['db']['password'] dbHost = conf['db']['host'] dbPort = conf['db']['port'] dbName = conf['db']['dbname'] tsToken = conf['tushare']['token'] dbConnect=create_engine('mysql+pymysql://'+userName+":" +password+"@"+dbHost+":"+dbPort+"/"+dbName+"?charset=utf8") conn = pymysql.connect(host=dbHost,user=userName, password=password,database=dbName,charset='utf8') cursor=conn.cursor() ts.set_token(tsToken) cursor.execute("drop table if exists basiclist") cursor.execute("drop table if exists stock_company") cursor.execute("drop table if exists tmp_basicInfo") conn.commit() pro=ts.pro_api() #the basic information of stock print("Getting the basic Information with stock_basic-------") tmpSql='ts_code,symbol,name,area,industry,fullname,enname,market,' \ 'exchange,curr_type,list_status,list_date,delist_date,is_hs' data1=pro.stock_basic(exchange='',list_status='L',fields=tmpSql) data2=pro.stock_basic(exchange='',list_status='D',fields=tmpSql) data=data1.append(data2) data3=pro.stock_basic(exchange='',list_status='P',fields=tmpSql) data=data.append(data3) pd.io.sql.to_sql(data,'basiclist',dbConnect,schema='stocks', if_exists='replace',index=True) print("Getting the company Information with stock_company-------") #the basic information of stock company tmpSql='ts_code,exchange,chairman,manager,secretary,reg_' \ 'capital,setup_date,province,city,introduction,website,' \ 'email,office,employees,main_business,business_scope' df1 = pro.stock_company(exchange='SZSE',fields=tmpSql) df2 = pro.stock_company(exchange='SSE',fields=tmpSql) df=df2.append(df1) pd.io.sql.to_sql(df,'stock_company',dbConnect,schema='stocks', if_exists='replace',index=True) print("drop table if exists tmp_basicInfo") cursor.execute("drop table if exists tmp_basicInfo") print("create tmp table and insert data") tmpSql="create table tmp_basicInfo select a.ts_code," \ "a.symbol,a.name,a.area,a.industry,a.fullname,a.enname," \ "a.market,a.curr_type,a.list_status,a.list_date," \ "a.delist_date,a.is_hs,b.chairman,b.manager,b.secretary," \ "b.reg_capital,b.setup_date,b.province,b.city,b.introduction," \ "b.website,b.email,b.office,b.employees,b.main_business," \ "b.business_scope from basiclist as a left join stock_company" \ " as b on a.ts_code =b.ts_code " cursor.execute(tmpSql) print("delete from basicInfo") cursor.execute("delete from basicInfo") print("insert the new data into basicInfo") tmpSql="insert into basicInfo(ts_code,symbol,name,area,industry," \ "fullname,enname,market,curr_type,list_status,list_date," \ "delist_date,is_hs,chairman,manager,secretary,reg_capital," \ "setup_date,province,city,introduction,website,email,office," \ "employees,main_business,business_scope) select ts_code," \ "symbol,name,area,industry,fullname,enname,market,curr_type," \ "list_status,list_date,delist_date,is_hs,chairman,manager," \ "secretary,reg_capital,setup_date,province,city,introduction," \ "website,email,office,employees,main_business,business_scope " \ " from tmp_basicInfo" cursor.execute(tmpSql) cursor.execute("update basicInfo set employees=0 where employees is NULL") cursor.execute("update basicInfo set reg_capital=0 where reg_capital is NULL") cursor.execute("drop table if exists basiclist") cursor.execute("drop table if exists stock_company") cursor.execute("drop table if exists tmp_basicInfo") conn.commit() endTime=int(time.time()) strSql="insert into crontablog(name,start,end,timelong) value('basicInfo',"+str(startTime)+","+str(endTime)+","+str(endTime-startTime)+")" print(strSql) cursor.execute(strSql) conn.commit() cursor.close() conn.close()

-------------------- 正文到此结束------------------------

推荐一个公众号：健哥聊量化，会持续推出股票相关基础知识，以及python实现的一些基本的分析代码。欢迎大家关注，二维码如下：