import sqlite3
# 连接到SQLite数据库
# 数据库文件是test.db
# 如果文件不存在,会自动在当前目录创建:
conn = sqlite3.connect('test.db')
# 创建一个Cursor:
cursor = conn.cursor()
# 执行一条SQL语句,创建user表:
cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
#清除user表
sql = 'drop table if exists user'
#插入记录:
cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
# 通过rowcount获得插入的行数:
cursor.rowcount #1
#更新
sql = 'update pagerank set score=%f where urlid=%d' % (pr,urlid)
# 查询:
cursor.execute('select * from user where name=? and pwd=?', ('abc', '123456'))
# 查询不重复结果
sql = 'select distinct fromid from link where toid=%d' % urlid
# 查询结果排序
sql = 'selcect * from pagerank order by score desc'
# 联合查询
sql = 'select link.fromid,link.toid from linkwords,link where linkwords.wordid=%d and linkwords.linkid=link.rowid' % wordid
# 获得查询结果集:
values = cursor.fetchall() #[(u'1', u'Michael')]
res = cursor.fetchone()
if res!= None: value = res[0]
cursor.close()
conn.close()
#要确保出错的情况下也关闭掉Connection对象和Cursor对象,用try:...except:...finally:...
# 关闭Cursor:
cursor.close()
# 提交事务:
conn.commit()
# 关闭Connection:
conn.close()
#从一张表取出结果插入另一张表
#初始化每个url. 令其PageRank值为1
for (urlid,) in self.con.execute('select rowid from urllist'):
self.con.execute('insert into pagerank(urlid,score) values (%d,1.0)' % urlid)
self.dbcommit()
#或
self.con.execute('insert into pagerank select rowid, 1.0 from urllist')
self.dbcommit()