import sqlite3 #导入模块
conn = sqlite3.connect('example.db') #连接数据库
c = conn.cursor()
#创建表
c.execute('''CREATE TABLE stocks(date text,trans text,symbol text,qty real,price real)''')
#插入一条纪录
c.execute("INSERT INTO stocks VALUES('2016-01-05','BUY','RHAT',100,35.14)")
#提交当前事务，保存数据
conn.commit()
#关闭数据库连接
conn.close() connSe = sqlite3.connect('example.db')
c = connSe.cursor()
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
print(row)
#调用自定义函数
import sqlite3
import hashlib #自定义函数
def md5sum(t):
return hashlib.md5(t).hexdigest() #在内存中创建临时数据库
conn = sqlite3.connect(":memory:")
#创建可在SQL语句中调用的函数
conn.create_function("md5",1,md5sum)
cur = conn.cursor()
#在SQL语句中调用自定义函数
cur.execute("select md5(?)",["中国北京".encode()])
print(cur.fetchone()[0])
#占位符的使用
import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE people(name_last,age)")
who='Dong'
age=''
#使用问号作为占位符
cur.execute('insert into people values(?,?)',(who,age))
#使用命名变量作为占位符
cur.execute('select *from people where name_last=:who and age=:age',{'who':who,'age':age})
print(cur.fetchone())
#迭代器生成数据
import sqlite3 #自定义迭代器，按顺序生成小写字母
class IterChars:
def __init__(self):
self.count = ord('a')
def __iter__(self):
return self
def __next__(self):
if self.count > ord('z'):
raise StopIteration
self.count +=1
return (chr(self.count-1))
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("create table characcters(c)")
#创建迭代器对象
theIter = IterChars()
#插入记录，每次插入一个英文小写字母
cur.executemany('insert into characcters(c)values(?)',theIter)
#读取并显示所有记录
cur.execute('select c from characcters')
print(cur.fetchall())
#迭代器生成更简洁的方式
#下面的代码则使用了更为简洁的生成器来产生参数：
import sqlite3
import string #包含yield语句的函数可以用来创建生成器对象
def char_generator():
for c in string.ascii_lowercase:
yield (c,) conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("create table characters(c)")
#使用生成器对象得到参数序列
cur.executemany("insert into characters(c)values(?)",char_generator())
cur.execute('select c from characters')
print(cur.fetchall())
import sqlite3
person=[('Hugo','Boss'),('Calvin','Klein')]
conn=sqlite3.connect(":memory:")
#创建表
conn.execute('create table person(firstname,lastname)')
#插入数据
conn.executemany('insert into person(firstname,lastname) values (?,?)',person)
#显示数据
for row in conn.execute('select firstname,lastname from person'):
print(row)
print('I just deleted',conn.execute('delete from person').rowcount,'rows')
'''用来读取数据
fetchone()
fetchmany(size=cursor,arraysize)
fetchall()
'''
import sqlite3 conn = sqlite3.connect('addressBook.db')
conn.execute('create table addressList(name,sex,phon,QQ,address)')
cur = conn.cursor() #创建游标
cur.execute("insert into addressList(name,sex,phon,QQ,address)values('王小丫','女','13111110010','66735','北京市')")
cur.execute("insert into addressList(name,sex,phon,QQ,address)values('李莉莉','女','11231110010','66755','天津市')")
cur.execute("insert into addressList(name,sex,phon,QQ,address)values('李发莉','女','11235410010','66723','开封市')")
conn.commit() #提交事务，把数据写入数据库 cur.execute('select *from addressList')
li=cur.fetchall() #返回所有查询结果
for line in li:
for item in line:
print(item,end=' ')
print() conn.close()
'''Row对象'''
#假设数据以下面的方式创建并插入数据:
import sqlite3
conn = sqlite3.connect('test.db')
c=conn.cursor()
c.execute("create table stocks(date text,trans text,symbol text,qty real,price real)")
c.execute("insert into stocks values('2016-01-05','BUY','RHAT',100,35.14)")
conn.commit() #使用下面的方式来读取其中的数据：
conn.row_factory = sqlite3.Row
c=conn.cursor()
c.execute('select * from stocks')
r=c.fetchone()
print(type(r))
print(tuple(r))
print(r[2])
print(r.keys())
print(r['qty'])
for field in r:
print(field)
conn.close()