利用python将沪深300股票历史数据存储在sqlite3

一、环境准备

1、python3中自带了sqlite3

参考https://www.runoob.com/sqlite/sqlite-tutorial.html

2、在sqlite中建表

CREATE TABLE [stock]
(
[id] NVARCHAR(48),
[name] NVARCHAR(24),	
[code] NVARCHAR(24),[date] INTEGER  NOT NULL,[open] REAL  ,[close] REAL  , [high] REAL  , [low] REAL  , [volume] REAL  ,[extend_num] REAL ,[extend1] NVARCHAR(30),[extend2] NVARCHAR(30),[extend3] NVARCHAR(30),CONSTRAINT [PK_stock] PRIMARY KEY  ([id])
);
DELETE  from stock ;
CREATE UNIQUE INDEX stock_code_date_I ON stock(code, date);

二、构造语句

1、查询

def select():db_path = r'D:\Users\Administrator\PycharmProjects\Stocks\tushareTest\stock\data_sqlite\finance.db'conn = sqlite3.connect(db_path)cur = conn.cursor()cur.execute('select * from stock')print(cur.fetchall())cur.close()conn.close()

2、插入单条

def insert():conn = sqlite3.connect(db_path)sql ='''INSERT INTO stock
(id, name, code, date, "open", "close", high, low, volume)
VALUES(?, ?, ?, ?, ?, ?,?, ?, ?);'''cur = conn.cursor()cur.execute(sql,(str(uuid.uuid4()),'招商银行','600036','2002-04-12',2.547,2.564,2.581,2.542,212564.63))conn.commit()cur.close()conn.close()print('done')

3、插入多条

def insert_many():conn = sqlite3.connect(db_path)sql ='''INSERT INTO stock
(id, name, code, date, "open", "close", high, low, volume)
VALUES(?, ?, ?, ?, ?, ?,?, ?, ?);'''cur = conn.cursor()cur.executemany(sql,[(str(uuid.uuid4()),'招商银行','600036','2002-04-12',2.547,2.564,2.581,2.542,212564.63),(str(uuid.uuid4()),'招商银行','600036','2002-04-13',2.547,2.564,2.581,2.542,212564.63)])conn.commit()cur.close()conn.close()print('done')

三、从tushare获取数据

1、将dataframe写入sqlite

def sqlite2df():sql = "select * from data"df = pd.read_sql(sql, conn)  # 完成数据库的查询读取到数据框dataframe 中
def df2sqlite(code):pf = ts.get_k_data(code, start='2000-00-00', end='2020-05-24', ktype='D')print(pf)con = sqlite3.connect(db_path)  # 如果路径里面没有这个数据库，会自动创pf.to_sql('stock', con=conn, if_exists='append', index=False)

2、沪深300写入

def hs300_k():data_df = pd.read_csv(filepath_or_buffer=r'D:\Users\Administrator\PycharmProjects\Stocks\tushareTest\stock\data\hs300s.csv')for code in data_df['code']:try:print(code)df2sqlite(str(code))except Exception as e:print(e)

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！