Ross Wan's World!

Python, Ajax, PHP and Linux.

wxPython:数据库

Posted by Ross Wan 于 2008/04/29

       目前有多种数据库类型,其中以关系型数据库最为流行(RDBMS),它将数据分布在多个表上,而我们可以定义各个表之间的关系。知名的关系型数据库,既有商业的也有开源的:

商业关系型数据库(Commercial RDBMS

  • Oracle
  • Sybase
  • MS SQL
  • Access

开源关系型数据库(Opensource RDBMS)

  • MySQL
  • PostgreSQL
  • Firebird
  • SQLite

       Python 有相应的模块支持上面提到的 RDBMS。


SQLite

       从 Python 2.5.x 开始,Python 已经内建模块对 SQLite 数据库支持。SQLite 是一个小巧的内嵌型数据库,它没有服务器,可以方便的整合到程序里面。

SQLite 具有以下的特性:

  • 支持事务(transactions)
  • 无须管理
  • 小巧(小于250 KB)
  • 简单快速
  • 单文件数据库结构
  • 支持最大可达2 tebibytes (241 bytes) 的数据库

SQLite 支持下面这些数据类型:

  • TEXT
  • INTEGER
  • FLOAT
  • BLOB
  • NULL

       在我们使用 SQLite 之前,必须先熟悉一些重要的数据库相关述语。一个数据库查询(query)是指从数据库搜寻信息,使用 SQL 语言来编写查询。SQL 语言(Structured Query Language)是一门数据库语言,用于对数据库进行相关的操作:如创建、检索(retrieve)、更新、删除等等。SQL 语言由 IBM 开发,它有3个子集:

  • DML
  • DDL
  • DCL

       DML(Data Manipulation Language 数据操纵语言)用来添加、更新、删除数据,SQLite 支持相应的 insert、update、delete 等 SQL 命令。DDL(Data Definition Language 数据库定义语言)用来创建一个新的表和记录,SQLite 支持相应的 create、drop、alter 等相应的 SQL 命令。DCL(Data Control Language 数据控制语言)用来控件数据库的用户权限的,SQLite 不支持这个特性。

       事务(transaction)是 DBMS(database management system)的一个操作单元。一个事务可以包含有多个 SQL 操作(例如查询等),只有当所有的操作都顺利完成时,事务才被提交。如果有一两个失败的话,事务将会回滚。一个支持事务的数据库,我们又称其为事务型数据 库,SQLite 就是一个事务型数据库。

       游标(cursor)是一个对象,用来遍历 SQL 查询返回的结果集(result set)。结果集(result set)是一个包含行和元数据的集合,它是数据库查询返回的结果。数据库的表的一个单元称为记录(record)或者行(row)。

sqlite3

       sqlite3 是 SQLite 数据库的一个命令行,用来对 SQLite 数据库执行 SQL 命令。我们在 Shell 下键入 sqlite3 ,即可启动,如果后面不带一个数据库名作参数的话,会新建一个数据库。sqlite3 的预定义命令集是以“.”开头的,想知道所有可用的命令,可以键入 .help,会看到如下的一些命令:

命令(Command)     描述(Description)
.databases                   显示当前数据库名
.dump table                 将一个表 dump 到一个包含 SQL 命令的文本格式
.exit                            退出 sqlite3
.headers ON|OFF          显示或者隐藏列 headers
.help                           显示帮助信息
.mode mode table         改变一个表的显示方式
.quit                            跟 .exit 一样
.read filename               执行一个文件里的 SQL 命令
.show                          显示 sqlite3 的设置
.tables pattern              列举匹配某个 pattern 的表
.width num num …        设置列的宽度

       下面,我们创建了一个名为 people 的数据库。

$ sqlite3 people
SQLite version 3.3.13
Enter “.help” for instructions
sqlite>
sqlite> .databases
seq  name             file
—  —————  ———————————————————-
0    main             /home/vronskij/tmp/people
sqlite> .exit
$

       下面创建一个表。

sqlite> .tables
sqlite> create table neighbours(name text, age numeric, remark text);
sqlite> .tables
neighbours

       .tables 命令显示数据库里所有的表。使用 SQL 命令 create table 创建了一个名为 neighbours 的表,它包含三列。注意,一个 SQL 命令以 “;”结束。

       下面,进行插入数据操作。

sqlite> insert into neighbours values(‘sandy’, 7, ‘stubborn’);
sqlite> insert into neighbours values(‘jane’, 18, ‘beautiful’);
sqlite> insert into neighbours values(‘mark’, 28, ‘lazy’);
sqlite> insert into neighbours values(‘steven’, 34, ‘friendly’);
sqlite> insert into neighbours values(‘alice’, 17, ‘slick’);
sqlite> insert into neighbours values(‘tom’, 25, ‘clever’);
sqlite> insert into neighbours values(‘jack’, 89, ‘wise’);
sqlite> insert into neighbours values(‘lucy’, 18, ‘cute’);

       下面进行查询操作。select 命令是最常用的 DML 命令。

sqlite> select * from neighbours;
sandy|7|stubborn
jane|18|beautiful
mark|28|lazy
steven|34|friendly
alice|17|slick
tom|25|clever
jack|89|wise
lucy|18|cute

       sqlite3 支持下面的几种数据显示方式:

模式(mode)     描述(Description)
csv                    使用“,”分隔数据
column               所有列向左对齐
html                   html table code
insert                 SQL insert statements for table
line                    每行仅显示一个值
list                     使用 “.”分隔数据
tabs                   使用制表符分隔数据

       默认的显示方式是 list。可以使用 .show 来查看当前的 sqlite3 的设置。

sqlite> .show
     echo: off
  explain: off
  headers: off
     mode: list
nullvalue: “”
   output: stdout
separator: “|”
    width:

        下面我们使用 column 显示方式。

sqlite> .mode column
sqlite> .headers on
sqlite> .width 10 4 15
sqlite> select * from neighbours;
name        age   remark
———-  —-  —————
sandy       7     stubborn
jane        18    beautiful
mark        28    lazy
steven      34    friendly
alice       17    slick
tom         25    clever
jack        89    wise
lucy        18    cute

       使用 .mode column 改变为 column 显示方式,.headers on 显示列 headers,.width 来设置列的宽度,默认是10个字符。

       对数据备份是一件相当重要的事情,sqlite3 提供了一个简单的解决方案:.dump。

sqlite> .tables
neighbours
sqlite> .dump neighbours
BEGIN TRANSACTION;
CREATE TABLE neighbours(name text, age numeric, remark text);
INSERT INTO “neighbours” VALUES(‘sandy’,7,’stubborn’);
INSERT INTO “neighbours” VALUES(‘jane’,18,’beautiful’);
INSERT INTO “neighbours” VALUES(‘mark’,28,’lazy’);
INSERT INTO “neighbours” VALUES(‘steven’,34,’friendly’);
INSERT INTO “neighbours” VALUES(‘alice’,17,’slick’);
INSERT INTO “neighbours” VALUES(‘tom’,25,’clever’);
INSERT INTO “neighbours” VALUES(‘jack’,89,’wise’);
INSERT INTO “neighbours” VALUES(‘lucy’,18,’cute’);
COMMIT;

       .dump 命令将一个表转变为一个包含多个 SQL 命令的文本格式,通过这些 SQL 命令,可以还原表的原始状态。我们复制和粘贴这些 SQL 命令到一个 neighbours.sql 文件里。

       下面,我们删除这个表格,并用上面的文件重建它。

sqlite> drop table neighbours;
sqlite> .tables
sqlite> .read ../neighbours.sql
sqlite> .tables
neighbours
sqlite> select * from neighbours;
name        age         remark
———-  ———-  ———-
sandy       7           stubborn
jane        18          beautiful
mark        28          lazy
steven      34          friendly
alice       17          slick
tom         25          clever
jack        89          wise
lucy        18          cute

       上面,使用 SQL 命令 drop 来删除一个表。.read 命令会执行一个文件里的所有 SQL 命令。

SQLite python API

       pysqlite 是一操作 SQLite 数据库的程序包,从 Python 2.5.x 开始,它已经集成到 Python 里面。在 Python 里,pysqlite 模块包含在名为 sqlite3 的模块里。

import sqlite3 as lite

       下面是使用 pysqlite 的一些简单步骤:

  • 创建一个连接对象
  • 创建一个游标对象
  • 执行查询操作
  • 取出数据(可选)
  • 关闭游标和连接对象

       使用 connect() 方法来创建一个连接对象:

 import sqlite3 as lite

 con = lite.connect(‘databasename’)
 con = lite.connect(‘:memory:’)

       有两种方式创建一个连接对象:我们可以连接文件系统里的一个数据库,只需要指定数据库文件的路径即可;也可以在内存里创建一个数据库,需要在 connect() 里指定为’:memory:’。

       现在我们测试一个例子:

>>> import sqlite3 as lite
>>> con = lite.connect(‘people’)
>>> cur = con.cursor()
>>> cur.execute(‘select name from neighbours’)
>>> print cur.fetchall()
[(u’sandy’,), (u’jane’,), (u’mark’,), (u’steven’,), (u’alice’,), (u’tom’,), (u’jack’,), (u’lucy’,)]
>>> cur.close()
>>> con.close()

       我们创建一个连接对象 con 连接到名为 people 的数据库()位于当前目录下。然后调用连接对象的 cursor() 方法创建一个游标对象 cur,接着调用游标对象的两个方法 execute() 和 fetchall() 分别执行查询数据库和取数据。最后,别忘记关闭游标和连接对象。

提交更新(Committing changes)

       SQLite 支持事务,我们必须理解它的工作方式。我们打开一个事务后,必须通过提交更新(commit)才生效。先看看下面的例子:

>>> cur.execute(“update neighbours set age=29 where name=’lucy'”)
>>> cur.execute(“select age from neighbours where name=’lucy'”)
>>> print cur.fetchone()
(29,)
>>> cur.close()
>>> con.close()
>>> (CTRL + D)
$ sqlite3 people
sqlite> select age from neighbours where name=’lucy’;
18

       什么地方出错了吗?这是因为我们忘记提交更新。

>>> cur.execute(“update neighbours set age=29 where name=’lucy'”)
>>> con.commit()
>>> cur.close()
>>> con.close()
>>> (CTRL + D)
$ sqlite3 people
sqlite> select age from neighbours where name=’lucy’;
29

       在下面的例子中,我们会看到 DCL statements 是自动提交更新,create table 命令属于 DCL 的一部分。

>>> cur.execute(‘create table relatives(name text, age numeric)’)
>>> cur.close()
>>> con.close()
>>> (CTRL + D)
$ sqlite3 people
sqlite> .tables
neighbours  relatives

       注意,如果我们创建一个连接对象,并将 isolation_level 设置为 None时,SQLite 要以自动提交更新。

>>> import sqlite3 as lite
>>> con = lite.connect(‘people’, isolation_level=None)
>>> cur = con.cursor()
>>> cur.execute(“insert into neighbours values (‘rebecca’, 16, ‘shy’)”)
>>> cur.close()
>>> con.close()
>>> (CTRL + D)
$ sqlite3 people
sqlite> select * from neighbours where name=’rebecca’;
rebecca|16|shy
sqlite>

Autoincrement

       Autoincrement 主键是一个方便的特性。当我们插入一个新行时,主键值会自动增加1。注意,Autoincrement 这个特性在不同的 RDMSs 中的实现方式是不同的。下面我们演示如何在 SQLite 里实现:

sqlite> create table books(id integer primary key autoincrement not null, name text, author text);
sqlite> insert into books (name, author) values (‘anna karenina’, ‘leo tolstoy’);
sqlite> insert into books (name, author) values (‘father goriot’, ‘honore de balzac’);
sqlite> select * from books;
1|anna karenina|leo tolstoy
2|father goriot|honore de balzac
sqlite>

       仅需要在定义表的时候为主键添加为一个 autoincrement 即可。

安全(Security considerations)

       使用下面的方式来传递参数是不安全的:

bookname = ‘atlante illustrato di filosofia’
bookauthor = ‘ubaldo nicola’
cur.execute(“insert into books(name, author) values (‘%s’, ‘%s’)” % (bookname, bookauthor))

       因为很容易受到攻击——SQL 注入攻击。

 >>> import sqlite3 as lite
 >>> print lite.paramstyle
 qmark

       Python 数据库 API 规范(Python Database API specification)支持下面的参数传递方式:

  • qmark
  • numeric
  • named
  • format
  • pyformat

       pysqlite 使用 qmark 引用(question mark quoting)。上面的例子可以重写为:

 bookname = ‘atlante illustrato di filosofia’
 bookauthor = ‘ubaldo nicola’
 cur.execute(‘insert into books(name, author) values (?, ?)’, (bookname, bookauthor))

总结

       下面,我们用一个例子,将上面涉及的东西集合到一起。

#!/usr/bin/python
# insertdata.py

import wx
import sqlite3 as lite

class InsertData(wx.Frame):
    def __init__(self, parent, id, title):
        wx.Frame.__init__(self, parent, id, title, size=(280, 200))

        panel = wx.Panel(self, -1)

        gs = wx.FlexGridSizer(3, 2, 9, 9)
        vbox = wx.BoxSizer(wx.VERTICAL)
        hbox = wx.BoxSizer(wx.HORIZONTAL)

        name = wx.StaticText(panel, -1, ‘Name’)
        remark = wx.StaticText(panel, -1, ‘Remark’)
        age = wx.StaticText(panel, -1, ‘Age’)
        self.sp = wx.SpinCtrl(panel, -1, ”, size=(60, -1), min=1, max=125)
        self.tc1 = wx.TextCtrl(panel, -1, size=(150, -1))
        self.tc2 = wx.TextCtrl(panel, -1, size=(150, -1))

        gs.AddMany([(name), (self.tc1, 1, wx.LEFT, 10),
                (remark), (self.tc2, 1, wx.LEFT, 10),
                (age), (self.sp, 0, wx.LEFT, 10)])

        vbox.Add(gs, 0, wx.ALL, 10)
        vbox.Add((-1, 30))

        insert = wx.Button(panel, -1, ‘Insert’, size=(-1, 30))
        cancel = wx.Button(panel, -1, ‘Cancel’, size=(-1, 30))
        hbox.Add(insert)
        hbox.Add(cancel, 0, wx.LEFT, 5)
        vbox.Add(hbox, 0, wx.ALIGN_CENTER | wx.BOTTOM, 10)
        
        self.Bind(wx.EVT_BUTTON, self.OnInsert, id=insert.GetId())
        self.Bind(wx.EVT_BUTTON, self.OnCancel, id=cancel.GetId())

        panel.SetSizer(vbox)

        self.Centre()
        self.Show(True)

    def OnInsert(self, event):
        try:
            con = lite.connect(‘people’)
            cur = con.cursor()
            name = self.tc1.GetValue()
            age = self.sp.GetValue()
            remark = self.tc2.GetValue()
            cur.execute(‘insert into neighbours values(?, ?, ?)’, (name, age, remark))
            con.commit()
            cur.close()
            con.close()

        except lite.Error, error:
            dlg = wx.MessageDialog(self, str(error), ‘Error occured’)
            dlg.ShowModal()

    def OnCancel(self, event):
        self.Close()

app = wx.App()
InsertData(None, -1, ‘Insert Dialog’)
app.MainLoop()

Advertisements

2条回应 to “wxPython:数据库”

  1. Ross Wan said

    翻译自:The wxPython tutorial

  2. vCDXky said

    Visit Website ambien overdose fatal dosage – ambien cr goes generic

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s

 
%d 博主赞过: