Python_sqlite3

 1 import sqlite3  #导入模块
 2 conn = sqlite3.connect('example.db')    #连接数据库
 3 c = conn.cursor()
 4 #创建表
 5 c.execute('''CREATE TABLE stocks(date text,trans text,symbol text,qty real,price real)''')
 6 #插入一条纪录
 7 c.execute("INSERT INTO stocks VALUES('2016-01-05','BUY','RHAT',100,35.14)")
 8 #提交当前事务，保存数据
 9 conn.commit()
10 #关闭数据库连接
11 conn.close()
12 
13 
14 connSe = sqlite3.connect('example.db')
15 c = connSe.cursor()
16 for row in c.execute('SELECT * FROM stocks ORDER BY price'):
17     print(row)

#调用自定义函数

 1 import sqlite3
 2 import hashlib
 3 
 4 #自定义函数
 5 def md5sum(t):
 6     return hashlib.md5(t).hexdigest()
 7 
 8 #在内存中创建临时数据库
 9 conn = sqlite3.connect(":memory:")
10 #创建可在SQL语句中调用的函数
11 conn.create_function("md5",1,md5sum)
12 cur = conn.cursor()
13 #在SQL语句中调用自定义函数
14 cur.execute("select md5(?)",["中国北京".encode()])
15 print(cur.fetchone()[0])

#占位符的使用

 1 import sqlite3
 2 
 3 conn = sqlite3.connect(":memory:")
 4 cur = conn.cursor()
 5 cur.execute("CREATE TABLE people(name_last,age)")
 6 who='Dong'
 7 age='38'
 8 #使用问号作为占位符
 9 cur.execute('insert into people values(?,?)',(who,age))
10 #使用命名变量作为占位符
11 cur.execute('select *from people where name_last=:who and age=:age',{'who':who,'age':age})
12 print(cur.fetchone())

#迭代器生成数据

 1 import sqlite3
 2 
 3 #自定义迭代器，按顺序生成小写字母
 4 class IterChars:
 5     def __init__(self):
 6         self.count = ord('a')
 7     def __iter__(self):
 8         return self
 9     def __next__(self):
10         if self.count > ord('z'):
11             raise StopIteration
12         self.count +=1
13         return (chr(self.count-1))
14 conn = sqlite3.connect(":memory:")
15 cur = conn.cursor()
16 cur.execute("create table characcters(c)")
17 #创建迭代器对象
18 theIter = IterChars()
19 #插入记录，每次插入一个英文小写字母
20 cur.executemany('insert into characcters(c)values(?)',theIter)
21 #读取并显示所有记录
22 cur.execute('select c from characcters')
23 print(cur.fetchall())

#迭代器生成更简洁的方式

 1 #下面的代码则使用了更为简洁的生成器来产生参数：
 2 import sqlite3
 3 import string
 4 
 5 #包含yield语句的函数可以用来创建生成器对象
 6 def char_generator():
 7     for c in string.ascii_lowercase:
 8         yield (c,)
 9 
10 conn = sqlite3.connect(":memory:")
11 cur = conn.cursor()
12 cur.execute("create table characters(c)")
13 #使用生成器对象得到参数序列
14 cur.executemany("insert into characters(c)values(?)",char_generator())
15 cur.execute('select c from characters')
16 print(cur.fetchall())

 1 import sqlite3
 2 person=[('Hugo','Boss'),('Calvin','Klein')]
 3 conn=sqlite3.connect(":memory:")
 4 #创建表
 5 conn.execute('create table person(firstname,lastname)')
 6 #插入数据
 7 conn.executemany('insert into person(firstname,lastname) values (?,?)',person)
 8 #显示数据
 9 for row in conn.execute('select firstname,lastname from person'):
10     print(row)
11 print('I just deleted',conn.execute('delete from person').rowcount,'rows')

 1 '''用来读取数据
 2 fetchone()
 3 fetchmany(size=cursor,arraysize)
 4 fetchall()
 5 '''
 6 import sqlite3
 7 
 8 conn = sqlite3.connect('addressBook.db')
 9 conn.execute('create table addressList(name,sex,phon,QQ,address)')
10 cur =  conn.cursor()    #创建游标
11 cur.execute("insert into addressList(name,sex,phon,QQ,address)values('王小丫','女','13111110010','66735','北京市')")
12 cur.execute("insert into addressList(name,sex,phon,QQ,address)values('李莉莉','女','11231110010','66755','天津市')")
13 cur.execute("insert into addressList(name,sex,phon,QQ,address)values('李发莉','女','11235410010','66723','开封市')")
14 conn.commit()   #提交事务，把数据写入数据库
15 
16 cur.execute('select *from addressList')
17 li=cur.fetchall()   #返回所有查询结果
18 for line in li:
19     for item in line:
20         print(item,end=' ')
21     print()
22 
23 conn.close()

 1 '''Row对象'''
 2 #假设数据以下面的方式创建并插入数据:
 3 import sqlite3
 4 conn = sqlite3.connect('test.db')
 5 c=conn.cursor()
 6 c.execute("create table stocks(date text,trans text,symbol text,qty real,price real)")
 7 c.execute("insert into stocks values('2016-01-05','BUY','RHAT',100,35.14)")
 8 conn.commit()
 9 
10 
11 #使用下面的方式来读取其中的数据：
12 conn.row_factory = sqlite3.Row
13 c=conn.cursor()
14 c.execute('select * from stocks')
15 r=c.fetchone()
16 print(type(r))
17 print(tuple(r))
18 print(r[2])
19 print(r.keys())
20 print(r['qty'])
21 for field in r:
22     print(field)
23 conn.close()