Python连接SQLite数据库

SQLite作为一款轻型数据库，管理工具有很多，比如SQLite Expert Professional，很适合用来存储Python网站，爬虫的相关数据，下面列出基本的增删查改操作

读取操作：

conn1 = sqlite3.connect(board.DatabasePath)
conn1.row_factory = sqlite3.Row
conn1.execute("pragma foreign_key=on")
c1 = conn1.cursor()

try:
#执行查询操作
    c1.execute("\
select username as username \
from \
    register \
where name=?;", \
              (username,))
#异常处理
except (sqlite3.DatabaseError) as e:
    print e
    return None
else:
    user_row = c1.fetchone()
    #调用数据
finally:
    conn1.close()
#关闭连接

增加数据：

conn = sqlite3.connect(board.DatabasePath)
conn.row_factory = sqlite3.Row
conn.execute("pragma foreign_key=on")
c = conn.cursor()

md5 = hashlib.md5()
md5.update(password)
encrypted_passwd = md5.hexdigest()

try:
#增加数据
    c.execute("\
              insert into register (name,work,email,username,password) values (?,?,?,?,?);", \
              (name, workplace, email, username, encrypted_passwd))
    c.execute("select last_insert_rowid() as user_id from register;")

except (sqlite3.DatabaseError) as e:
    print e
    conn.rollback()
    return False
else:
    conn.commit()
    return True
finally:
    conn.close()

删除操作：

conn=sqlite3.connect(board.DatabasePath)
conn.execute("pragma foreign_key=on")
conn.row_factory=sqlite3.Row
c=conn.cursor()
try:
    c.execute("\
delete from setting where key=? and parent_id=?;",\
(self.slick.key,self.id))
except (sqlite3.DatabaseError) as e:
    print e
    conn.rollback()
    return False
else:
    conn.commit()
    self.slick.items.pop(self.id)
    self=None
    return True
finally:
    conn.close()    

修改操作：

conn=sqlite3.connect(board.DatabasePath)
conn.execute("pragma foreign_key=on")
conn.row_factory=sqlite3.Row
c=conn.cursor()
try:
    c.execute("\
update setting set value=? where key=? and name=?;",\
(self.title,self.key,"jumbotron_title"))
    c.execute("\
update setting set value=? where key=? and name=?;",\
(self.content,self.key,"jumbotron_content"))
except (sqlite3.DatabaseError) as e:
    print e
    conn.rollback()
    return False
else:
    conn.commit()
    return True
finally:
    conn.close()

 

转载于:https://www.cnblogs.com/FZfangzheng/p/7588793.html