# -*- coding: utf-8 -*-
"""
Created on Fri Jul 19 15:48:09 2019

@author: 无敌钢牙小白狼
"""

import sqlite3 


conn = sqlite3.connect("jqstockdata.db")
cur = conn.cursor()
sql = '''select  * from "{}" '''.format('000001.XSHE')
cur.execute(sql)

df = cur.fetchall()


#%%简便取法
import pandas as pd

df=pd.read_sql('select  * from "{}"'.format('000001.XSHE'),index_col = 'index',con= conn )
df.index= pd.to_datetime(df.index,format='%Y-%m-%d')

#%%
import sqlite3

conn = sqlite3.connect("test.db")
#如果没有就是新建了一个 db 文件夹

cur = conn.cursor()
sql = '''create table if not exists student 
    (id int primary key, 
    name varchar(20), 
    score int, 
    sex varchar(10), 
    age int)'''
cur.execute(sql)
#创建一个table excel表，（名字 类型）


students = [(2, 'mark', 80, 'male', 18),
            (3, 'tom', 78, 'male', 17),
            (4, 'lucy', 98, 'female', 18),
            (5, 'jimi', 60, 'male', 16)]
cur.execute("insert into student(id, name, score, sex, age) values (1,'jack',80,'male',18)")
cur.executemany('insert into student values (?,?,?,?,?)', students)
#插入数据的两种方法


sql = ''' select * from student order by score desc '''
cur.execute(sql)
df = cur.fetchall()
#查找的方法 fetchall 和 fetchone

sql = ''' update student set name = ?  where id = ? '''
cur.execute(sql,('lucy',0))
#更改的方法

sql = ''' delete from student where id = 1 '''
cur.execute(sql)
#删除的方法

#%%
#显示所在文件夹 所有数据库集合
sql = ''' pragma database_list ''' 
cur.execute(sql)
print(cur.fetchall())

#显示所有table
cur.execute("select name from sqlite_master where type='table' order by name")
a = cur.fetchall()
print(a)

#显示所有table信息
cur.execute("PRAGMA table_info('000001.XSHE')")
print(cur.fetchall())

#%%
#pandas的相关操作
import pandas as pd


sql = '''select * from '000001.XSHE' '''
df2 = pd.read_sql(sql,conn)