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

灰太狼 2021-07-26 15:26 736阅读 0赞

一、环境准备

1、python3中自带了sqlite3

参考[https://www.runoob.com/sqlite/sqlite-tutorial.html][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)

[https_www.runoob.com_sqlite_sqlite-tutorial.html]: https://www.runoob.com/sqlite/sqlite-tutorial.html