SQLite3 使用

连接到SQLITE3

import sqlite3 as lite
con = lite.connect('test.sqlite')
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
data = cur.fetchone()
print(data)
con.close()

with lite.connect('test.sqlite') as con:
  cur = con.cursor()
  cur.execute('SELECT SQLITE_VERSION()')
  data = cur.fetchone()
  print(data)

新增、查询

1、新建表格

with lite.connect('test.sqlite') as con:
  cur =con.cursor()
  cur.execute('DROP TABLE IF EXISTS PhoneAddress') # cur.execute 执行后面的sql语句
  cur.execute('CREATE TABLE PhoneAddress(phone CHAR(10) PRIMARY KEY, address TEXT, name TEXT unique,age INT)
  cur.execute("INSERT INTO PhoneAddress VALUES('1111111111','Tom Jack','nowhere',23)")
  cur.execute("INSERT INTO PhoneAddress VALUES('2222222222','Tom Jerry','nowhere',12)")
  cur.fetchone() # 一条记录，生成元组
  cur.fetchall() # 所有记录，生成列表，！！注意指针移动！！

2、使用pandas

import sqlite3 as lite
import pandas
df  = pandas.DataFrame(PhoneAddress)
with lite.connect('test.sqlite') as db:
  df.to_sql(name='PhoneAddress',con = db,if_exists='replace',index=None)

3、使用pandas读取sqlite

with lite.connect('test.sqlite') as db:
  df2 = pandas.read_sql('SELECT * FROM PhoneAddress',con=db)