Python操作sqlite3

最近用到了Python，比较基础的东西，记录一下！

主要内容：

• Python中连接sqlite3
• Python中Select、insert、update、delete的用法
• 关于占位符以及字符串的用法

1、连接sqlite3

import sqlite3
conn = sqlite3.connect('PASSManager.db')
cur=conn.cursor()
print "Opend database successfully so easy"
conn.close()

2、Select用法

print "======Select sample1=========================================================" 
cur.execute("select * from pm_group")
res = cur.fetchall()
for line in res:
for f in line:
print f,
print

print "======Select sample2========================================================="
cur.execute("select id,groupname from pm_group")
for row in cur:
print "ID = ", row[0], "|", "Groupname = ", row[1],
print

3、Insert用法

print "======Insert sample========================================================="
for t in [(4,'日本語'),(5, '天神'),(6,'AAA')]:
    cur.execute("insert into pm_group(id,groupname) values (?,?)", t)
conn.commit()
print "Insert done successfully"
print "======Insert sample========================================================="

4、Update用法

print "======Update sample========================================================="
b=4
c='日本語'
cur.execute('update pm_group set groupname= "中国語"  where id = ? or groupname = ?', (b,c,))
conn.commit()
print "Update ",cur.rowcount , "Record"

5、Delete用法

print "======Delete sample========================================================="
cur.execute("delete from pm_group where groupname = '天神' or groupname = '中国語'")
conn.commit()
print "Delete ",cur.rowcount,"Record"

6、操作数据库时，占位符的用法

print "======占位符 sample1========================================================="
d ='中国語'
e ='AAA'
cur.execute("delete from pm_group where groupname=? or groupname=?", (d,e,))
conn.commit()
print "Delete ",cur.rowcount,"Record"

print "======占位符 sample2========================================================="
from string import Template #要导入之后Template可以正常使用
e="天神"
s=Template("delete from pm_group where groupname='$groupname'; ")
cur.execute(s.substitute(groupname=e))
conn.commit()
print "Delete ",cur.rowcount,"Record"

print "======占位符 sample3========================================================="
from string import Template #要导入之后Template可以正常使用
s=Template("delete from pm_group where groupname='$groupname'; ")
e={}
e['groupname'] = '天神'
cur.execute(s.substitute(e))
conn.commit()
print "Delete ",cur.rowcount,"Record"

以上