Редактирование: WxPython FAQ Databases

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. 

<b>Database</b> is a structured collection of data that is stored in a computer. A computer program, that manages and queries a database is calles a <b>Database Management System (DBMS)</b>.
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 <b>relational database model (RDBMS)</b>.
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. 

{|
|-
| Commercial RDBMS
<!--[[image: wxPython_faq_line.png | center]]-->
* Oracle
* Sybase
* MS SQL
* Access
<!--[[image: wxPython_faq_line.png | center]]-->
| Opensource RDBMS
<!--[[image: wxPython_faq_line.png | center]]-->
* MySQL
* PostgreSQL
* Firebird
* SQLite
<!--[[image: wxPython_faq_line.png | center]]-->
|}

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<sup>41</sup> 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 <b>query</b> is a search for information from a database. A query is written in SQL language. 

<b>Structured Query Language</b> (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 <b>cursor</b> is a database object used to traverse the results of a SQL query. 
A <b>transaction</b> 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 <b>result set</b> 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 <b>record</b> or a <b>row</b>.

=== 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 <i>.help</i>.
Some of the commands are shown in the following table.

{|
|-
!Command
!Description
|-
| id="gray" | .databases
| id="gray" | show a database name
|-
|.dump table
|dump a table into an SQL text format
|-
| id="gray" | .exit
| id="gray" | exit the sqlite3 program
|-
|.headers ON|OFF
|show or hide column headers
|-
| id="gray" | .help
| id="gray" | show help
|-
|.mode mode table
|change mode for a table
|-
| id="gray" | .quit
| id="gray" | same as .exit
|-
|.read filename
|execute SQL commands in a filename
|-
| id="gray" | .show
| id="gray" | show sqlite3 settings
|-
|.tables pattern
|list tables that match pattern
|-
| id="gray" | .width num num ...
| id="gray" | set width for columns
|}

First, we create a new database called people. 

<source lang="python">
$ 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
$ 
</source>

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

Next we create a table. 

<source lang="python">
sqlite> .tables
sqlite> create table neighbours(name text, age numeric, remark text);
sqlite> .tables
neighbours
</source>

The <i>.tables</i> 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. 

<source lang="python">

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

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

<source lang="python">
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
</source>

The sqlite3 has several modes to display data.  Namely:

{|
|-
!Mode
!Description
|-
| id="gray" | csv
| id="gray" | comma separated values
|-
|column
|left aligned columns
|-
| id="gray" | html
| id="gray" | html table code
|-
|insert
|SQL insert statements for table
|-
| id="gray" | line
| id="gray" | one value per line
|-
|list
|values delimited by .separator string
|-
| id="gray" | tabs
| id="gray" | tab separated values
|}

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

<source lang="python">
sqlite> .show
     echo: off
  explain: off
  headers: off
     mode: list
nullvalue: ""
   output: stdout
separator: "|"
    width:

</source>

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

<source lang="python">
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 
</source>

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

Backing up the data is the most important thing. sqlite3 has a simple solution. We utilize command <i>.dump</i>.

<source lang="python">
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;
</source>

The <i>.dump</i> 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. 

<source lang="python">
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
</source>

We drop the neighbours table with the drop table SQL command. The command <i>.tables</i> shows no table. Then we type sqlite <i>.read</i> 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.
<source lang="python">
 import sqlite3 as lite
</source>

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 <i>connect()</i> module method.
<source lang="python">
 import sqlite3 as lite

 con = lite.connect('databasename')
 con = lite.connect(':memory:')
</source>

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.

<source lang="python">
$ 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.
>>>
</source>

<source lang="python">
>>> 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()
</source>

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 <i>cursor()</i> method of the connection object. After that we call two cursor object methods. The <i>execute()</i> method executes SQL commands. The <i>fetchall()</i> 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.

<source lang="python">
>>> 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
</source>

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.

<source lang="python">
>>> 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

</source>
After committing our changes with the <i>commit()</i> 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.

<source lang="python">
>>> cur.execute('create table relatives(name text, age numeric)')
>>> cur.close()
>>> con.close()
>>> (CTRL + D)
$ sqlite3 people 
sqlite> .tables
neighbours  relatives 
</source>

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 <i>isolation_level</i> parameter to None.

<source lang="python">
>>> 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> 
</source>

=== 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.
<source lang="python">
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> 
</source>
The keyword <i>autoincrement</i> is used to create autoincremental primary key in SQLite.

===  Security considerations ===
It is possible but insecure to pass parameters this way.
<source lang="python">
bookname = 'atlante illustrato di filosofia'
bookauthor = 'ubaldo nicola'
cur.execute("insert into books(name, author) values ('%s', '%s')" % (bookname, bookauthor))
</source>

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

<source lang="python">
 >>> import sqlite3 as lite
 >>> print lite.paramstyle
 qmark
</source>

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:

<source lang="python">
 bookname = 'atlante illustrato di filosofia'
 bookauthor = 'ubaldo nicola'
 cur.execute('insert into books(name, author) values (?, ?)', (bookname, bookauthor))
</source>

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.

<source lang="python">
#!/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()
</source>

<source lang="python">
 gs = wx.FlexGridSizer(3, 2, 9, 9)
</source>

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

<source lang="python">
 name = self.tc1.GetValue()
 age = self.sp.GetValue()
 remark = self.tc2.GetValue()
 cur.execute('insert into neighbours values(?, ?, ?)', (name, age, remark))
</source>

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. 

<source lang="python">
 except lite.Error, error:
     dlg = wx.MessageDialog(self, str(error), 'Error occured')
     dlg.ShowModal()
</source>

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 <i>Error</i> exception is a base class for all other exceptions implemented in SQLite library.

[[image: wxPython_faq_insertdata.png | center]]

TODO: Supported Errors, Warning