# coding:utf-8
# Practice of oprating SQLite with Python
# by redice 2010.11.09
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import sqlite3
# 連接數據庫，數據庫文件不存在則創建
conn = sqlite3.connect("users.db")
conn.text_factory = lambda x: unicode(x, 'utf-8', 'replace')
curs = conn.cursor()
# 檢查users表是否存在，不存在則創建
# 很強大，不需要使用管理工具事先創建
#curs.execute(create table users(username VARCHAR(20) UNIQUE,password VARCHAR(32),groupe INTEGER);)
curs.execute('''create table if not exists users(username VARCHAR(20) UNIQUE,password VARCHAR(32),groupe INTEGER)''')
# 對數據庫有修改的操作要調用commit提交事務
conn.commit()
# insert data
# 采用這種方法插入數據，不用擔心數據要轉義。數據庫接口會自動幫你轉義
curs.execute("insert into users values(?,?,?);", ('redice',123456,1))
curs.execute("insert into users values(?,?,?);", ('qipeng',123456,0))
curs.execute("insert into users values(?,?,?);", ('zhangsan',123456,0))
curs.execute("insert into users values(?,?,?);", ('laowu',123456,0))
curs.execute("insert into users values(?,?,?);", ('lisi',123456,0))
curs.execute("insert into users values(?,?,?);", ('wangwu',123456,0))
conn.commit()
# 查找數據
# 對數據庫沒有修改的操作不需要用commit提交事務
curs.execute("select * from users where username=?;" ,(raw_input("input user name:"),))
row = curs.fetchone()
if row:
print row
conn.close()
