Cacher is the code snippet organizer for pro developers

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()

# 导入MySQL驱动:
import mysql.connector
# 注意把password设为你的root口令:
conn = mysql.connector.connect(user='root', password='password', database='test', use_unicode=True)
cursor = conn.cursor()

# 创建user表:
cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')

# 插入一行记录:
cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])
cursor.rowcount		#1
# 提交事务:
conn.commit()
cursor.close()

# 运行查询:
cursor = conn.cursor()
cursor.execute('select * from user where id = %s', ('1',))
values = cursor.fetchall()	#[(u'1', u'Michael')]
cursor.close()
conn.close()