在上一篇
中,我实现了设置按钮的功能,其中涉及到了从数据库中读写数据。我使用的是SQLite数据库,这是一种轻量级的基于单一磁盘文件的关系数据库,不需要单独的服务器即可使用。从2.5版本开始,Python已经集成了SQLite模块(模块名:sqlite3),所以对于天气预报这种小型程序来说,用SQLite那可是相当方便。本文出自三思之旅博客http://think3t.iteye.com,转载请注明出处。
本篇简单介绍一下Python如何操纵SQLite数据库。注意,我讲的重点是Python操纵SQLite,而不是讲数据库的。因为SQLite也是一种关系型数据,常用的SQL语句都支持,所以本文不再赘述。
Python操纵SQLite数据库,可以分为如下几步:
- 连接数据库
- 建立游标
- 执行SQL命令
- 获取查询结果(仅查询需要)
- 提交当前事务(查询不需要)
- 关闭数据库连接
先说一下本程序用到的数据库。本人对SQL语句也不是很懂,所以程序中只是进行查询、增加条目、删除条目、更改条目的操作,没有建表、删表的操作,表是我事先通过图形界面的SQLite数据库浏览器建的。数据库中一共两个表————cityInfo和userInfo。cityInfo中存储的是全国各省市县区的信息,一共4个字段,依次是id(城市代码)、prov(省)、city(市)、zoon(县区);userInfo中存储的是用户信息,一共4个字段,依次是mail(邮箱地址)、city(城市代码)、main(是否主城市,1是0不是)、note(备注信息,即城市名称)。大家可以在下图中看到本程序所用数据库的结构。
然后我们举个例子,比如从数据库中查找主城市代码和增加一个新用户:本文出自三思之旅博客http://think3t.iteye.com,转载请注明出处。
def getMainCityCode(self):
self.conn = sqlite.connect('data.db') #连接数据库data.db,如果不存在将会创建
self.curs = self.conn.cursor() #建立游标
query = 'SELECT city FROM userInfo WHERE main = 1' #SQL语句
self.curs.execute(query) #执行SQL语句
rows = self.curs.fetchall() #获取查询结果,是一个二维列表
return rows[0][0] #返回主城市代码
def addItem(self, table, values):
self.conn = sqlite.connect('data.db') #连接数据库data.db,如果不存在将会创建
self.curs = self.conn.cursor() #建立游标
query = 'INSERT INTO ' + table + ' VALUES (?,?,?,?)' #SQL语句
self.curs.execute(query, values) #执行SQL语句
self.conn.commit() #提交事务,保存数据库
return True
通常情况下,SQL操作需要传入一些值,就像上述代码中第二个方法一样。这种情况下,不要使用Python的字符串操作把值组合进SQL语句,因为这样不安全,会使程序易于受到SQL注入攻击。更安全的做法是,使用参数替换。就像上述代码一样,每个值用一个问号代替,然后把真正的值(元组形式)作为execute()方法的第二个参数传入。注意此处,第二个参数一定是元组形式,即使只有一个值,也要以无组形式传入,这种情况下应该用小括号括住并且在值后加一个逗号,即(value,)这种形式。举例如下:
# Never do this -- insecure!
symbol = 'IBM'
c.execute("select * from stocks where symbol = '%s'" % symbol)
# Do this instead
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)
# Larger example
for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'IBM', 500, 53.00),
]:
c.execute('insert into stocks values (?,?,?,?,?)', t)
我为了使用方便,把这些对数据库的操作封装在MySearcher类中,代码很简单。本文出自三思之旅博客http://think3t.iteye.com,转载请注明出处。
#! /usr/bin/env python
# coding=utf-8
import sqlite3 as sqlite
class MySearcher():
def __init__(self, database='data.db'):
self.database = database
self.conn = sqlite.connect(self.database)
self.curs = self.conn.cursor()
def addItem(self, table, values):
query = 'INSERT INTO ' + table + ' VALUES (?,?,?,?)'
self.curs.execute(query, values)
self.conn.commit()
return True
def delItem(self, mail, city):
query = 'DELETE FROM userInfo WHERE mail=? AND city=?'
self.curs.execute(query, (mail, city))
self.conn.commit()
return True
def searchItem(self, table, item, value):
if item == '':
query = 'SELECT * FROM ' + table
self.curs.execute(query)
else:
query = 'SELECT * FROM ' + table + ' WHERE ' + item + ' =?'
self.curs.execute(query, (value,))
rows = self.curs.fetchall()
return rows
def getRowCount(self, table):
query = 'SELECT * FROM ' + table
self.curs.execute(query)
rowCount = len(self.curs.fetchall())
return rowCount
def listProvs(self):
query = 'SELECT DISTINCT prov FROM cityInfo ORDER BY id'
self.curs.execute(query)
rows = self.curs.fetchall()
result = []
for row in rows:
result.append(row[0])
return result
def listCityOfProv(self, prov=u'北京'):
query = 'SELECT DISTINCT city FROM cityInfo WHERE prov = ? ORDER BY id'
self.curs.execute(query, (prov,))
rows = self.curs.fetchall()
result = []
for row in rows:
result.append(row[0])
return result
def listZoonOfCity(self, prov=u'北京', city=u'北京'):
query = 'SELECT zoon FROM cityInfo WHERE prov = ? AND city = ? ORDER BY id'
self.curs.execute(query, (prov, city))
rows = self.curs.fetchall()
result = []
for row in rows:
result.append(row[0])
return result
def getCityCode(self, prov=u'北京', city=u'北京', zoon=u'北京'):
query = 'SELECT id FROM cityInfo WHERE prov=? AND city=? AND zoon = ?'
self.curs.execute(query, (prov, city, zoon))
rows = self.curs.fetchall()
return rows[0][0]
def getMainCityCode(self):
query = 'SELECT city FROM userInfo WHERE main = 1'
self.curs.execute(query)
rows = self.curs.fetchall()
return rows[0][0]
def setMainCity(self, mail, city):
query = 'UPDATE userInfo set main=? WHERE mail=? AND city=?'
self.curs.execute(query, (True, mail, city))
self.conn.commit()
return True
def isMainCity(self, mail, city):
query = 'SELECT main FROM userInfo WHERE mail=? AND city=?'
self.curs.execute(query, (mail, city))
rows = self.curs.fetchall()
return rows[0][0]
def getUserInfo(self):
query = 'SELECT mail,city,note FROM userInfo'
self.curs.execute(query)
rows = self.curs.fetchall()
return rows
def clearMainCity(self):
query = 'UPDATE userInfo SET main=0 WHERE main=1'
self.curs.execute(query)
self.conn.commit()
return True
def close(self):
self.conn.close()
if __name__ == '__main__':
mysch = MySearcher()
print mysch.listProvs()
print mysch.listCityOfProv(u'北京')
另外,我也把我建的数据库放出来,里面包含了我从中国天气网上提取的全国几千个城市的城市代码,提取这些信息也费了我不少功夫,大家可以直接拿来用。OK,本篇文章到此为止,欢迎大家继续关注。本文出自三思之旅博客http://think3t.iteye.com,转载请注明出处。