[018量化交易] python将数据写入sqlite3 数据库

import numpy as np
import pandas as pd
import tushare as ts
import pandas_datareader.data as web
import matplotlib.pyplot as plt
# import mpl_finance as mpf
import datetime
import time
# import talib
import sqlite3
from sqlalchemy import create_engine
from pandas.io import sql

# 设置token
token = ''  # 输入你的token
pro = ts.pro_api(token)  # 初始化pro接口

# from Code_7_for_stock_data import bs_k_data_stock, pro_daily_stock, json_to_str

conn = sqlite3.connect('stock-data.db')
c = conn.cursor()



try:
    # # 创建表
    # c.execute('''CREATE TABLE SZ000002
    #        (ID           INT PRIMARY KEY   NOT NULL,
    #        TIME          TEXT    NOT NULL,
    #        CODE          TEXT    NOT NULL,
    #        HIGH          REAL,
    #        LOW           REAL,
    #        CLOSE         REAL,
    #        OPEN          REAL,
    #        DESCRIPTION CHAR(50));''')
    # conn.commit()

    # 查询表结构
    c.execute("PRAGMA table_info(SZ000002)")
    print(c.fetchall())
    # [(0, 'ID', 'INT', 1, None, 1), (1, 'TIME', 'TEXT', 1, None, 0), (2, 'CODE', 'TEXT', 1, None, 0), (3, 'HIGH', 'REAL', 0, None, 0), (4, 'LOW', 'REAL', 0, None, 0), (5, 'CLOSE', 'REAL', 0, None, 0), (6, 'OPEN', 'REAL', 0, None, 0), (7, 'DESCRIPTION', 'CHAR(50)', 0, None, 0)]

    # # 插入表
    # c.execute("INSERT INTO SZ000002 (ID,TIME,CODE,HIGH,LOW,CLOSE,OPEN,DESCRIPTION) \
    #       VALUES (1, '2019-1-1', 000002, 10.12, 10.12, 10.12, 10.12,'Buy Signal' )")
    #
    # c.execute("INSERT INTO SZ000002 (ID,TIME,CODE,HIGH,LOW,CLOSE,OPEN,DESCRIPTION) \
    #       VALUES (2, '2019-1-2', 000002, 10.13, 10.13, 10.13, 10.13,'Sell Signal' )")
    #
    # c.execute("INSERT INTO SZ000002 (ID,TIME,CODE,HIGH,LOW,CLOSE,OPEN,DESCRIPTION) \
    #       VALUES (3, '2019-1-3', 000002, 10.14, 10.14, 10.14, 10.14,'Buy Signal' )")
    #
    # c.execute("INSERT INTO SZ000002 (ID,TIME,CODE,HIGH,LOW,CLOSE,OPEN,DESCRIPTION) \
    #       VALUES (4, '2019-1-4', 000002, 10.15, 10.15, 10.15, 10.15,'Sell Signal' )")
    # conn.commit()

    # 查询表内容
    c.execute("select * from SZ000002")
    print(c.fetchall())
    # [(1, '2019-1-1', '2', 10.12, 10.12, 10.12, 10.12, 'Buy Signal'), (2, '2019-1-2', '2', 10.13, 10.13, 10.13, 10.13, 'Sell Signal'), (3, '2019-1-3', '2', 10.14, 10.14, 10.14, 10.14, 'Buy Signal'), (4, '2019-1-4', '2', 10.15, 10.15, 10.15, 10.15, 'Sell Signal')]

    # 更新表
    # c.execute("UPDATE SZ000002 set DESCRIPTION = 'None' where ID=1")
    # conn.commit()
    # c.execute("select * from SZ000002")
    # print(c.fetchall())
    # # [(1, '2019-1-1', '2', 10.12, 10.12, 10.12, 10.12, 'None'), (2, '2019-1-2', '2', 10.13, 10.13, 10.13, 10.13, 'Sell Signal'), (3, '2019-1-3', '2', 10.14, 10.14, 10.14, 10.14, 'Buy Signal'), (4, '2019-1-4', '2', 10.15, 10.15, 10.15, 10.15, 'Sell Signal')]
    #
    # # 选择表
    # cursor = conn.execute("SELECT id, time, code, description from SZ000002 where HIGH < 10.15 and HIGH > 10.12")
    # for row in cursor:
    #     print("ID = {}; TIME = {}; CODE = {}; description = {};".format(row[0], row[1], row[2], row[3]))
    #     # ID = 2; TIME = 2019-1-2; CODE = 2; description = Sell Signal;
    #     # ID = 3; TIME = 2019-1-3; CODE = 2; description = Buy Signal;
    #
    # # 删除表数据
    # c.execute("DELETE from SZ000002 where ID=2;")
    # conn.commit()
    # c.execute("select * from SZ000002")
    # print(c.fetchall())
    # # [(1, '2019-1-1', '2', 10.12, 10.12, 10.12, 10.12, 'None'), (3, '2019-1-3', '2', 10.14, 10.14, 10.14, 10.14, 'Buy Signal'), (4, '2019-1-4', '2', 10.15, 10.15, 10.15, 10.15, 'Sell Signal')]
    #
    # # 删除一个表
    # c.execute("drop table SZ000002")
    # conn.commit()
    conn.close()
except:
    # 删除一个表
    # c.execute("drop table SZ000002")
    # conn.commit()
    conn.close()

# import tushare as ts# 导入tushare
# # 初始化pro接口
# pro = ts.pro_api('9f61340e69c5695ac3fe99b33ea58ce9dc55fb235f10aacd781c37df')
#
# # 拉取数据
# df = pro.stock_basic(**{
#     "ts_code": "",
#     "name": "",
#     "exchange": "",
#     "market": "",
#     "is_hs": "",
#     "list_status": "",
#     "limit": "",
#     "offset": ""
# }, fields=[
#     "ts_code",
#     "name",
# ])
# # print(df)
# print(type(df))
# # df.to_csv("D:/stock_industry0829.csv", encoding="gbk", index=False)#写入csv文件中  行数
#
# for row in df.itertuples():
#     print(getattr(row, 'ts_code'), getattr(row, 'name')) # 输出每一行  打印出所有股票代码 股票名称
#     # print(row)