打造自己的天气预报之（六）——对数据库的操作

think3t

浏览: 70576 次
性别:
来自: 南京

最近访客更多访客>>

dqifa

独孤秋生

t358143105

kliyufei

博主相关

博客

微博

相册

留言

关于我

博客专栏

: 打造自己的天气预报
浏览量：27291

文章分类

社区版块

存档分类

博客分类：

Python
天气预报
编程

python 编程天气预报 sqlite 数据库

在上一篇

打造自己的天气预报之（五）——实现按钮功能之设置窗口

中，我实现了设置按钮的功能，其中涉及到了从数据库中读写数据。我使用的是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，转载请注明出处。