PYTHON_sqlite

import pandas as pd
import sqlite3
cx=sqlite3.connect(path+'database.db')
cur=cx.cursor()
#1.查询建表语句
cur.execute("select sql from sqlite_master where tbl_name='table1' and type='table' ")
print(cur.fetchall())
#2.查看有哪些表
cur.execute('select name from sqlite_master where type=\'table\' order by name )
print(cur.fetchall())
#3.查看表的数据格式
cur.execute('PRAGMA table_info(table1)')
print(cur.fetchall())
#4.查看表的列名
table_name='table1'
cur.execute("select * from (%s)"%table_name)
col_name_list=[tuple[0] for tuple in cur.description]
#5.查看表数据
#一般使用该方法
sql_test="select * from table1 limit 10"
data_test=pd.read_sql(sql_test,cx)
#备用方法
table_name='table1'
cur.execute("select * from (%s) limit 10 "%table_name)
data=cur.fetchall()
#6.dataframe,存入数据库
pd.io.sql.to_sql(data,'table1',con=cx,if_exists='replace',index=FALSE,dtype={'col1':'TEXT','col2':'REAL','col3':'INTEGER'})
#if_exists='append':追加
#if_exists='releace':如果存在就删除，重建
#if_exists='fail':如果存在，就不操作
#7.删除表
cur.execute("drop table if exists table1")
#8.删除记录
cur.execute("delete from table1 if id>7")