WxPython FAQ Databases

Материал из Wiki.crossplatform.ru

Версия 10:09, 19 февраля 2009

Database driven applications account for a large part of all applications ever developed. And they will definitely in the future as well. Most of them are business applications. Companies work with large amount of data and they naturally need software for that. Well, you know, we live in a era called information revolution after all.

Some GUI toolkits are geared towards developing business applications. For example the WinForms or the Swing. They provide widgets that are adapted to the business application developing. A data grid widget is a good example. Swing toolkit has prorities like security or robustness. But definitelly not the look and feel.

Database is a structured collection of data that is stored in a computer. A computer program, that manages and queries a database is calles a Database Management System (DBMS). Some thirty years ago, DBMS were available only in the research laboratories of giant companies like IBM. Later on, they began to spread. But they were very expensive. These days, we can found DBMS everywhere. On the web, on our personal computers, in various mobile devices or portables. We can have many different databeses for little or no money that would cost thousands of dollars in the past. We live in interesting times. There are various database models. The most significant database model is the relational database model (RDBMS). The data is divided into tables. Among these tables we define relations. We all have heard about various database management systems. There are several well known commercial DBMS as well as open source ones.

Python programming language has modules for all above RDBMS.

SQLite

Starting from Python 2.5.x series, an SQLite library is included in the python language. SQLite is a small embeddable library. This means that programmers can integrate the libraty inside their applications. No server is needed to work with SQLite. Therefore SQLite is also called a zero-configuration SQL database engine.

SQLite has the following features.

• works with transactions
• no administration needed
• small code footprint, less than 250 KB
• simple to use and fast
• single file database structure
• supports databases up to 2 tebibytes (2⁴¹ bytes) in size</i>

SQLite supports these data types:

• TEXT
• INTEGER
• FLOAT
• BLOB
• NULL</i>

Before we start working with SQLite, we define some important terms. A database query is a search for information from a database. A query is written in SQL language.

Structured Query Language (SQL) is a computer language used to create, retrieve, update and delete data from the database. It was developed by the IBM corporation. SQL language has three subsets.

• DML
• DDL
• DCL

The DML (Data Manipulation Language) is used to add, update and delete data. SQLite understands insert, update and delete sql commands. The DDL (Data Definition Language) is used to define new tables and records. SQLite has create, drop, alter sql commands from this group. The DCL (Data Control Language) is used to set privileges for database users. SQLite does not have this subset.

A cursor is a database object used to traverse the results of a SQL query. A transaction is a unit of operation with a database management system. It can contain one or more queries. Transactions are used to ensure the integrity of data in a database. If everything is ok, transactions are commited. If one or more queries fails, transactions are rolled back. Databases that support transactions are called transactional databases. An SQLite database is a transactional database. An SQL result set is a set of rows and metadata about the query from a database. It is a set of records that results from running a query. A single unit of structured data within a database table is called a record or a row.

sqlite3

SQLite library includes a small command line utility called sqlite3. It is used to manually enter and execute SQL commands against a SQLite database. To launch this utility, we type sqlite3 into the shell. The command is to be followed by a database name. If the database does not exist, a new one is created. We work with sqlite3 with a definite set of dot commands. To show all available commands, we type .help. Some of the commands are shown in the following table.

First, we create a new database called 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
$

All commands of sqlite3 start with the dot "." character. To show all available commands, we simply type .help. The .databases command shows our current database. The .exit command quits the sqlite3 utility and returns to the shell.

Next we create a table.

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

The .tables command shows all available tables in the database. We create a table called neighbours. Our table will have three columns. We will use text and numeric data types. Notice that each SQL command is followed by a semicolon ";".

Now it is time to insert some real data.

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');

The SQL select command is probably the most widely used DML (data manipulation language) command.

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

The sqlite3 has several modes to display data. Namely:

The default mode is the list mode. We can see the current settings if we type the .show command.

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

I prefer the column mode. In the next step we change the default settings a bit.

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

We change the mode with the .mode command to the column mode. We set headers on with the .headers command. Finally we change the width of each column with the .width command. The default value is ten characters.

Backing up the data is the most important thing. sqlite3 has a simple solution. We utilize command .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;

The .dump command transforms the table into a set of SQL text format. These SQL commands will recreate the table into the original state. We copy and paste these SQL commnads into a neighbours.sql text file.

In the next steps we drop a table and recreate it from our file.

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

We drop the neighbours table with the drop table SQL command. The command .tables shows no table. Then we type sqlite .read command to execute all SQL commands in the specified file. Finally, we verify our data.

SQLite python API

pysqlite is a python interface to the SQLite library. From python2.5x series, it is included in the python language. The pysqlite module is included under the package name sqlite3.

 import sqlite3 as lite

Simple steps

• create connection object
• create cursor object
• execute query
• fetch data (optional)
• close cursor and connection objects

To create a connection, we call the connect() module method.

 import sqlite3 as lite
 
 con = lite.connect('databasename')
 con = lite.connect(':memory:')

There are two ways for creating a connection object. We can create a connection to a database on the filesystem. We simply specify the path to the filename. We can also create a database in memory. This is done with a special string ':memory:'.

We launch a python interpreter. We will test our examples there.

$ python
Python 2.5.1c1 (release25-maint, Apr  6 2007, 22:02:36) 
[GCC 4.1.2 (Ubuntu 4.1.2-0ubuntu4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>>

>>> 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()

First we import the sqlite3 module. Then we connect to our people database. The database file is in our current directory. To create a cursor object, we call the cursor() method of the connection object. After that we call two cursor object methods. The execute() method executes SQL commands. The fetchall() method retrieves all data that we have selected. The kosher way to end our work is to close the cursor and the connection object.

Commiting changes

SQLite library works with transactions. It is important to understand how it works. According to the documentation, for every DML statement, SQLite opens up a transaction. We must commit our changes to apply them. For every DCL statement, SQLite library commits automatically the changes. We will demonstrate this in short examples.

>>> 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

What went wrong? We did not commit our changes. When we executed the select statement using the python sqlite api, we received results within a transaction context. The changes were not really written to the database. When we checked the data in the sqlite3 utility, we got age 18. The data was not changed.

>>> 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

After committing our changes with the commit() method of the connection object, the data changes are really written to the database.

In the next example we demonstrate that the DCL statements are committed automatically. We will use create table command, which is a part of the DCL language.

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

There is one more thing to mention. We can create a connection, which will automatically commit all our changes. This is done, when we set the isolation_level parameter to None.

>>> 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

Autoincremental primary key is a handy feature. We insert new rows and the key is incremented automatically by one. The implementation of the autoincrement feature may differ among RDMSs. In the next example we will show how it is done in SQLite database.

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>

The keyword autoincrement is used to create autoincremental primary key in SQLite.

Security considerations

It is possible but insecure to pass parameters this way.

bookname = 'atlante illustrato di filosofia'
bookauthor = 'ubaldo nicola'
cur.execute("insert into books(name, author) values ('%s', '%s')" % (bookname, bookauthor))

It is vulnerable to attacks. These attacks are called SQL injections. Don't do this.

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

The python Database API specification lists these possible parameter style passings:

• qmark
• numeric
• named
• format
• pyformat

Python SQLite API uses the qmark (question mark) quoting. The previous example rewritten in qmark style:

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

TODO blob

Putting it together

So far we have been looking at the SQLite3 library, databases and SQL language. Now it is time to put it all together with wxPython in a simple functional script. The next simple script will do only one specific thing. Insert data into a table. We will use our people database, neigbours table.

#!/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()

 gs = wx.FlexGridSizer(3, 2, 9, 9)

In our Dialog box we use items of different size. That's why we have chosen the wx.FlexGridSizer. Items in wx.GridSizer have always the same size.

 name = self.tc1.GetValue()
 age = self.sp.GetValue()
 remark = self.tc2.GetValue()
 cur.execute('insert into neighbours values(?, ?, ?)', (name, age, remark))

This is the crutial part of the code. In the first three lines, we get the values that the user has inserted. These values are inserted into the database with the appropriate the SQL code.

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

We have placed our database related code between the try - catch clause. This is because working with data and databases is prone to errors. The Error exception is a base class for all other exceptions implemented in SQLite library.

TODO: Supported Errors, Warning