Table of Contents

taxidermistplateSoftware and s/w Development

Nov 7, 2013 (3 years and 9 months ago)

126 views

Python & SQLite Da
tabases

1

Table of Contents
Python & SQLite Databases

................................
................................
................................
......................

2

Introduction

................................
................................
................................
................................
................................
....

2

Installations

................................
................................
................................
................................
................................
....

3

Installing SQLite3

................................
................................
................................
................................
..........................

3

Installing SQLite Database Browser

................................
................................
................................
..............................

3

Installing wxPython

................................
................................
................................
................................
.......................

4

In
stalling Boa Constructor

................................
................................
................................
................................
.............

4

SQLite statements

................................
................................
................................
................................
..........................

5

Creating a database

................................
................................
................................
................................
........................

5

Creating a table

................................
................................
................................
................................
..............................

5

Adding data to a table

................................
................................
................................
................................
....................

6

Deleting records

................................
................................
................................
................................
.............................

7

Displaying records

................................
................................
................................
................................
.........................

8

Editing records

................................
................................
................................
................................
...............................

9

Phase One

................................
................................
................................
................................
.................

10

Creating a SQLite database using Python

................................
................................
................................
....................

10

The Database Creator

................................
................................
................................
................................
...................

10

Deleting a CD
................................
................................
................................
................................
...............................

12

Adding CDs

................................
................................
................................
................................
................................
.

13

Displaying CDs

................................
................................
................................
................................
............................

13

Editing a CD

................................
................................
................................
................................
................................

14

The

Database Creator revisited. Introducing wxPython.

................................
................................
.............................

16

Delete a CD


Introducing Boa Constructor

................................
................................
................................
................

22

Menus

................................
................................
................................
................................
................................
...........

27

Add a
CD

................................
................................
................................
................................
................................
.....

30

Creating a Custom Dialog

................................
................................
................................
................................
............

32

Code Cleanup!
................................
................................
................................
................................
..............................

33

Find a CD

................................
................................
................................
................................
................................
.....

35

Edit

a CD

................................
................................
................................
................................
................................
......

36

Sort the display


The Radio Box Control

................................
................................
................................
...................

38

Display CDs


The Grid Control

................................
................................
................................
................................
..

39

Coding the Radio Box

................................
................................
................................
................................
..................

40

Code update

................................
................................
................................
................................
................................
.

42

The program icon

................................
................................
................................
................................
.........................

43

Owning your code and licensing it

................................
................................
................................
...............................

43

Phase Two

................................
................................
................................
................................
................

45

The Ref field

................................
................................
................................
................................
................................

46

The Rating field

................................
................................
................................
................................
...........................

49

The Genre field

................................
................................
................................
................................
............................

54

The Artist field

................................
................................
................................
................................
.............................

61

Finally …

................................
................................
................................
................................
.................

62

Images

................................
................................
................................
................................
................................
..........

62

Import/Export csv.

................................
................................
................................
................................
........................

66

We
bsite

................................
................................
................................
................................
................................
.........

68

Update

................................
................................
................................
................................
................................
..........

69

Python & SQLite Da
tabases

2

Python & SQLite Databases

Introduction

This tutorial will show you how to design and build a fully
-
functional database GUI for a CD
collection using a combination of Python 2.7.2, wxPytho
n 2.8.8.1, Boa Constructor 0.6.1, SQLite
3.7.13 and SQLite Database Browser 2.0b1


All of the above programs are free for Linux, Mac and Windows.

This tutorial is written with the needs
of Raspberry Pi users in mind and was written on a Raspberry Pi comp
uter using the Raspbian
operating system, based on Debian, a variation of Linux. All the programs in the tutorial were written
and tested on the same system. As the Raspberry Pi does not use OSX or Windows, the tutorial is
written with the Linux user in
mind.

The programs will also work on Mac and Windows systems
which have Python 2.7 and wxPython2.8 installed.


Recent versions of OSX


Leopard and later
-

have Python installed as default. Python 2.7.2 is
available for both OSX and Windows from the In
ternet for free and is available for all Linux variations
from the repositories. It is also possible to produce .exe files and .app files from Python scripts to run
as stand
-
alone programs on Windows and OSX respectively using the py2exe and py2app progra
ms.


If you have installed Raspbian on a Raspberry Pi computer you will already have two versions of
Python which you can use


please use Python 2 and not Python 3 as there is, at the time of writing, no
version of wxPython for Python 3.

You will need to

install wxPython, Boa Constructor, SQLite3 and
SQLite Database Browser.
SQLite Database Browser will not work with SQLite 2.
The instructions for
installing all of these programs follows later. The instructions are written to be of use to a Raspberry P
i
user running Raspbian and there may be easier ways to perform the same installations from a full
Linux, Mac or Windows setup. These will be explained along the way.


The tutorial will explain how to create a database using SQLite and how to manipulate re
cords in the
database. It will go on to explain how to use the same SQL statements from within Python scripts, so
they can be run as Python programs as and when needed. Then it will introduce the use of wxPython to
build a cross platform GUI which create
s the same database at the click of a button. Several other
useful GUIs will be made using wxPython. Finally, there will be an introduction to Boa Constructor,
which will be used to produce a fully functional cross
-
platform Python database GUI.


A basic u
nderstanding of SQL statements and the Python programming language would be a great help
to the reader.


All code in this tutorial and the sample programs is licensed under the GNU General Public License
version 3 which, basically, allows you to re
-
write,
adapt and alter it as much as you like. I would
encourage you to do so. The only thing you are not allowed to do is to sell the code.

Python & SQLite Da
tabases

3

Installations

Installing SQLite3

SQLite3 is the database program that will be used to set up your initial databases. It

is free for Linux,
Mac and Windows.

Installation is very straightforward on Raspbian on the Raspberry Pi. Simply open the terminal and
type:

sudo apt
-
get install sqlite3 libsqlite3
-
dev

press Enter and wait for the programs to install themselves.

Most ver
sions of Linux will have a similar way to install SQLite3 and nearly all of them will have it
available in the relevant software manager program. There are versions of SQLite3 available for both
Mac and Windows


they are free and available for download f
rom the internet at:

http://www.sqlite.org/download.html/

Versions of OSX Leopard and later already have SQLite3 pre
-
installed.

Windows users need to go to the internet address above and download the sq
lite
-
shell and sqlite
-
dll
files from the “Pre
-
compiled binaries for Windows” section. Unpack the files in your
“C:
\
WINDOWS
\
system32” folder.

Installing SQLite Database Browser

SQLite Database Browser is a very useful program for manipulating data in SQLite

databases and for
quickly creating new databases.

This is very straightforward on Raspbian on the Raspberry Pi. Simply open the terminal and type:

sudo apt
-
get install sqlitebrowser

press Enter and wait for the program to install itself.

Most versions of

Linux will have a similar way to install SQLite Database Browser and nearly all of
them will have it available in the relevant software manager program. There are versions of SQLite
Database Browser available for both Mac and Windows


the Mac version is

free and available for
download from the internet at:

http://
www.macupdate.com/app/mac/38584/sql
ite
-
database
-
browser

Windows users can find a free download at:

http://sqlite
-
database
-
browser.softpedia.com/



Python & SQLite Da
tabases

4

Installing wxPython

WxPython is a free GUI toolkit which can be used to build Pyt
hon GUIs. You can install wxPython on
Raspbian by typing the following into the Terminal:

sudo apt
-
get install python
-
wxgtk2.8 python
-
wxtools wx2.8
-
i18n libwxgtk2.8
-
dev

Nearly all versions of Linux will have this available from the relevant software manag
er.

Mac and Windows users can obtain a version from:

http://www.wxpython.org/download.php/

Instructions for installation are given on the site.

Installing Boa Constructor

Boa Constructor is a free Pytho
n RAD GUI constructor toolkit which can be used to build Python
GUIs. You can install Boa Constructor on Raspbian by typing the following into the Terminal:

sudo apt
-
get install boa
-
constructor

Nearly all versions of Linux will have this available from th
e relevant software manager.

Mac users can obtain a version from:

http://
mac.softpedia.co
m/get/Developer
-
Tools/Boa
-
Constructor.shtml/

Instructions for installation are given on the site.

Windows users please download the latest version from:

http://
sourceforge
/projects/boa
-
constructor/?
source=dlp

Ru
n the self
-
extracting installer to install the program.

Python & SQLite Da
tabases

5

SQLite statements

Creating a database

Assuming you have installed SQLite3 for your system, you can now create a database called cddb.db
simply by typing:

sqlite3 cddb.db


in your terminal at the prom
pt. Press Enter and you will see the following:


pi@raspberrypi ~ $ sqlite3 cddb.db

SQLite version 3.7.13 2012
-
06
-
11 02:05:22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

Creating a table

Now we can add a SQLite s
tatement to build a table called CDs which will contain a unique reference
number (CDID), a Title and an Artist. Title and Artist are both strings of up to 30 characters, indicated
by
varchar(30)
and CDID is a number which is automatically incremented by
the program, indicated
by
integer primary key autoincrement.


Words in upper case are specific to SQL. You don't have to type them in upper case, it's just a
convention, but it does make it a lot easier to keep track of things when you are writing complica
ted
SQL code.




Type the following after the sqlite prompt and then press Enter


CREATE TABLE CDs (CDID integer primary key autoincrement, Title varchar (30), Artist varchar(30));

Be careful to type all the brackets, commas and semi
-
colons correctly or S
QLite will complain!





Press Enter



T
ype
.exit

or .
quit



Press Enter to quit SQLite3


If you now open up SQLite Database Browser and use the File menu to locate and open your new
database you will see the database displayed as in Fig 1.1


If you click on the

+ sign next to the table name you will see the details of the three fields and if you
click on the Browse Data tab you will see that there is, as yet, no data in your database.


Python & SQLite Da
tabases

6

















Fig 1.1

Adding data to a table

To add some data to the datab
ase, open up SQLite3 again by typing
sqlite3 cddb.db
in the Terminal.





T
ype the following after the sqlite prompt:


INSERT INTO CDs VALUES (null, 'Electric Ladyland', 'Jimi Hendrix');

INSERT INTO CDs VALUES (null, 'Collection', 'Tracy Chapman');

INSERT IN
TO CDs VALUES (null, 'Are You Experienced', 'Jimi Hendrix');

INSERT INTO CDs VALUES (null, 'Goats Head Soup', 'Rolling Stones');




Press Enter



Type .quit



Press Enter again to exit SQLite 3


Now, when you open up the Database Browser and look in the Browse

Data tab you will see those four
records entered in the CDs table (see Fig 1.2). Notice how the CDID has been incremented from 1 to 4
as the records are entered. The value 'null' is used in the SQL statement to allow the autoincrement
feature to insert i
ts own value and keep track of the records.


As you can see from the above, it is very easy to create a database in SQLite3 and creating tables and
inserting data is very easy, too.






Python & SQLite Da
tabases

7

















Fig 1.2

Deleting records

Say you want to delete “Goa
ts Head Soup” from the database, how would you do that? Open the
database again by typing

sqlite3 cddb.db




T
ype the following at the sqlite prompt:


DELETE FROM CDs WHERE Title = 'Goats Head Soup';




Press Enter and Goats Head Soup will be deleted


If yo
u type:


DELETE FROM CDs WHERE Artist = 'Jimi Hendrix';


All the Hendrix CDs would be deleted.










Python & SQLite Da
tabases

8

Displaying records



Type in the following:


SELECT * FROM CDs;




Press Enter and you should see:


pi@raspberrypi ~ $ sqlite3 cddb.db

SQLite version 3.7.
13 2012
-
06
-
11 02:05:22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> DELETE FROM CDs where Title = 'Goats Head Soup';

sqlite> SELECT * FROM CDs;

1|Electric Ladyland|Jimi Hendrix

2|Collection|Tracy Chapman

3|Are Yo
u Experienced|Jimi Hendrix

sqlite>


Now there are only three records in the database


the Database Browser will show the same but this is
the SQLite way of viewing all the records in a table using
SELECT * FROM CDs;
The * simply means
select everything (
i.e. all fields). If you wanted to just see the Hendrix CDs, you could use the
following after the sqlite prompt:


SELECT * FROM CDs WHERE Artist='Jimi Hendrix';


And that would show:


pi@raspberrypi ~ $ sqlite3 cddb.db

SQLite version 3.7.13 2012
-
06
-
11 02
:05:22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> SELECT * FROM CDs WHERE Artist='Jimi Hendrix';

1|Electric Ladyland|Jimi Hendrix

3|Are You Experienced|Jimi Hendrix

sqlite>



And to put the titles in order you
can use:


SELECT * FROM CDs WHERE Artist='Jimi Hendrix' ORDER BY Title;


This will give you the following:


pi@raspberrypi ~ $ sqlite3 cddb.db

SQLite version 3.7.13 2012
-
06
-
11 02:05:22

Enter ".help" for instructions

Enter SQL statements terminated with
a ";"

sqlite> SELECT * FROM CDs WHERE Artist='Jimi Hendrix' ORDER BY Title;

3|Are You Experienced|Jimi Hendrix

Python & SQLite Da
tabases

9

1|Electric Ladyland|Jimi Hendrix

sqlite>


Notice the titles are now in order. You could also order by Artist if you wanted.

Editing records

Finally, as this is just a basic introduction to SQLite statements, this is how to edit a database entry.
Say you want to change the title 'Electric Ladyland' to 'Rainbow Bridge'


add this line of code after the
prompt:


UPDATE CDs SET Title='Rainbow Bri
dge' WHERE Title='Electric Ladyland';


Using
SELECT * FROM CDs;

now will show you:



pi@raspberrypi ~ $ sqlite3 cddb.db

SQLite version 3.7.13 2012
-
06
-
11 02:05:22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> UPDATE
CDs SET Title='Rainbow Bridge' WHERE Title='Electric Ladyland';

sqlite> SELECT * FROM CDs;

1|Rainbow Bridge|Jimi Hendrix

2|Collection|Tracy Chapman

3|Are You Experienced|Jimi Hendrix

sqlite>


Electric Ladyland has been changed to Rainbow Bridge.


That

covers the use of INSERT, DELETE, CREATE, UPDATE and SELECT in SQLite3. There are
many tutorials available on SQL and SQLite as well as online tutorials if you should wish to go further
into the subject. There are also a huge number of books available o
n the subject. I do not intend to
cover the use of multiple tables as yet as I would like to focus more on the Python code at first.


Next step


how to run SQL statements from within python scripts ...



Python & SQLite Da
tabases

10

Phase One

Creating a SQLite database using Python

This part of the tutorial will show how to add all the above SQLite statements to a Python script and
how to make those scripts interactive so that they can be used at any time to add, delete, edit or display
records. I will start with a database creator
script which can be easily adapted to create other SQLite
databases.
Remember to use Python 2, not Python 3 as there is no wxPython for Python3 at the time of
writing this and this will make it difficult to follow some of the examples.

The Database Crea
tor

As this is a Python script you will need to write it using a plain text editor such as Leaf pad or Gedit. Or
Notepad in Windows. I use Gedit, which is available in most Linux software managers and features
code highlighting which makes it easier to re
ad the code.


Please don't try to write these scripts with Word or OpenOffice or any other word processing
program as they will add formatting code which will prevent the scripts from working.




Delete your database file cddb.db


We will now re
-
create it

from a Python script.




Open your choice of code editor and type in this code:


#!/usr/bin/python


""" sqlite python demo"""


# Import the required Python module

import sqlite3


# Create the database, a connection to it and a cursor

connection = sqlite3
.connect('cddb.db')

cursor = connection.cursor()


# Execute the SQLite statement to create the CDs table

cursor.execute("""

CREATE TABLE CDs (

CDID integer primary key autoincrement,

Title varchar(30),

Artist varchar(30)

)""")


# Populate the tabl
e with some data ...

cursor.execute("INSERT INTO CDs VALUES (null, 'Electric Ladyland', 'Jimi Hendrix')")

cursor.execute("INSERT INTO CDs VALUES (null, 'Collection', 'Tracy Chapman')")

Python & SQLite Da
tabases

11

cursor.execute("INSERT INTO CDs VALUES (null, 'Are You Experienced',

'Jimi Hendrix')")

cursor.execute("INSERT INTO CDs VALUES (null, 'Goats Head Soup', 'Rolling Stones')")


# Commit the transaction and close the connection

connection.commit()

cursor.close()

connection.close()




Save the file as cddb_creator.py.


The #!
symbols in the first line are called a “shebang”
-

somewhat cryptically. If you are writing this
on a Linux system right
-
click on the saved file, click on Properties and then click the Permissions tab
and click on “make the file executable”. This saves

the file as an executable Python script.


On Windows, Python should have associated the .py extension to itself when it was installed. The
system should recognise the .py extension as being associated to Python and execute the files
accordingly. If th
is doesn't happen you will need to find out how to associate the .py extension with
Python


this varies from version to version of Windows and the information is available on the
Internet.


On OSX you can run Python scripts from a terminal window exactly
the same way as on any UNIX
box. OSX is based on UNIX after all. But on the Mac, you can also run Python programs from the
Finder, either by dragging the script file to the Python Launcher app or by configuring Python
Launcher as the default application fo
r opening your script (or, optionally, all files with a .py
extension.) There are several options for using Python on a Mac. If you're interested in writing
administrative scripts for Mac OS X, you should look at packages that bridge the gap between Apple'
s
Open Scripting Architectures (OSA) and Python. Two such packages are appscript and PyOSA.




Open your Terminal



Navigate to where your file is saved and type:


python cddb_creator.py


This will execute the script and produce a new cddb.db file with the ori
ginal four records in it.


The line import sqlite3 shows that we are importing the sqlite3
module.

After this line we create the
database, set up a connection to it and create a cursor. The
cursor

is what carries out or executes all the
SQL statements wh
en we later create the table and data.


Notice the similarity between the Python code and the actual SQL statements used in SQLite. The line
breaks are important, as are the triple quotes
"""
. Once the cursor has executed all our commands with
the
connec
tion.commit()

code, we close the cursor and the connection.


There is nearly always the same sequence with Python SQL code; we set up a connection to the
database, execute some SQL code and then sever the connection. The code is nearly always very
similar
. Incidentally, it is quite easy to adapt this code for say, MySQL or PostgreSQL and many others
Python & SQLite Da
tabases

12

as only the code for the connection needs to be changed.


Next, how to delete a record ...

Deleting a CD

This is a basic script for deleting one record from th
e database. Save it as delete_CD.py. It is very
simple and uses the same method as the previous example
-

set up the connection, execute the SQL
code and sever the connection. There is not much new in this example, yet:

#!/usr/bin/python


""" sqlite pyt
hon demo"""


# Import the required Python module

import sqlite3


# Create a connection to the database and a cursor

connection = sqlite3.connect('cddb.db')

cursor = connection.cursor()


# Delete a CD from the database

cursor.execute("DELETE FROM CDs WH
ERE Title='Goats Head Soup'")


# Commit the transaction and close the connection

connection.commit()

cursor.close()

connection.close()


It would be more use if this program was interactive and asked the user to enter the CD title


the
program could be u
sed over and over again then. We can change it to do this quite easily using the
raw_input()

function to obtain some input from the user:




Save this version again as delete_CD.py to overwrite the previous version


#!/usr/bin/python


""" sqlite python dem
o"""


# Import the required Python module

import sqlite3


# Create a connection to the database and a cursor

connection = sqlite3.connect('cddb.db')

cursor = connection.cursor()


# Delete a CD from the database

searchVal = raw_input("CD title? ")

curs
or.execute("DELETE FROM CDs WHERE Title = '" + searchVal + "'")


# Commit the transaction and close the connection

Python & SQLite Da
tabases

13

connection.commit()

cursor.close()

connection.close()


If you run the program now you will see that it asks you for a CD title before the de
letion. A much
more useful program!





Please delete the database file cddb.db and run the cddb_creator.py program again to recreate
the database with all the records in place.


Adding CDs

This short program for adding a CD is very similar to the one use
d to delete a CD. The only changes
are the use of two raw_input statements and a change to the actual SQLite statement. Run this program
using the values “Tattoo You” and “Rolling Stones”. If you now open the database with the SQLite
Database Browser yo
u will see the new record has been added.




Save the following code as add_CD.py


#!/usr/bin/python


""" sqlite python demo"""


# Import the required Python module

import sqlite3


# Create a connection to the database and a cursor

connection = sqlite
3.connect('cddb.db')

cursor = connection.cursor()


# Add a CD to the database

titleVal = raw_input("CD title? ")

artistVal = raw_input("Artist? ")

cursor.execute("INSERT INTO CDs VALUES (null, '%s', '%s')" % (titleVal, artistVal))


# Commit the trans
action and close the connection

connection.commit()

cursor.close()

connection.close()


Next we can write a program to list the CDs for us without having to use the Database Browser ...

Displaying CDs

This program for displaying a list of all CDs is very
similar to the previous one.
Be careful to use four
spaces for the indentation for the print block


don't use Tab as this can cause problems
.

There is a
new function in this code
recordset = cursor.fetchall()
As the name indicates, this is an instructio
n
Python & SQLite Da
tabases

14

to the cursor to fetch all of the CD records from the database.

Note that the SQL statement to order
the records comes
before

the request to fetch all the records and save them as a recordset. There is then
a simple for loop which displays all the reco
rds using
for (CDID, Title, Artist) in recordset:










Save the following code as display_CDs.py


#!/usr/bin/python


""" sqlite python demo"""


# Import the required Python module

import sqlite3


# Create a connection to the database and a cursor

connection = sqlite3.connect('cddb.db')

cursor = connection.cursor()


# List all CDs in order of Title

cursor.execute("SELECT * FROM CDs ORDER BY Title")

recordset = cursor.fetchall()

print "========== movies =================="

for (CDID, Title, Arti
st) in recordset:


print "================================"


print "CDID: %s" % CDID


print "Title: %s" % Title


print "Artist: %s" % Artist


print


# Commit the transaction and close the connection

connection.commit()

cursor.close()

connection.close()


Next we can write a program to edit the details of a CD.

Editing a CD

This is almost identical to the code used for adding a CD but is more complex as we have to first obtain
some input from the user, then fetch the record to edit and

finally re
-
write the new version of the record
to the database and save it.



Save this file as edit_CD.py


Python & SQLite Da
tabases

15

#!/usr/bin/python


""" sqlite python demo"""


# Import the required Python module

import sqlite3


# Create a connection to the database and a c
ursor

connection = sqlite3.connect('cddb.db')

cursor = connection.cursor()


# Edit a CD in the database

searchVal = raw_input("CD title? ")

cursor.execute("SELECT * FROM CDs WHERE Title ='" + searchVal + "'")

recordset = cursor.fetchall()

for (CDID,

Title, Artist) in recordset:


print "================================"


print "CDID: %s" % CDID


print "Title: %s" % Title


print "Artist: %s" % Artist


print


titleVal = raw_input("New CD title? ")

artistVal = raw_input("New Artist?

")



cursor.execute("UPDATE CDs SET Title='" + titleVal + "', Artist='" + artistVal + "'
\

WHERE Title='" + searchVal + "'")


print

print "Record updated"

print


# Commit the transaction and close the connection

connection.commit()

cursor.close()

con
nection.close()


That covers using INSERT, DELETE, CREATE, UPDATE and SELECT in SQLite3 from within
Python scripts.


Note that so far I haven't covered errors at all. It is quite possible for users to enter CD records with no
title or to

try to delete a r
ecord which doesn't exist in the first place. I have done this deliberately to
keep the code as short and clear as possible but of course it would not be acceptable in a real database
program.


The next section will introduce wxPython and will also show

how to work with possible error
situations.



Python & SQLite Da
tabases

16

The Database Creator revisited. Introducing wxPython.

The first thing to know about using wxPython is that we have to explicitly ask our programs to use the
wx module by including the line
import wx

at the s
tart of the program. WxPython is a collection of
widgets that allow us to construct GUIs (Graphical User Interfaces) that allow the user to interact with
the database. I will show how to create small applications that carry out all the usual actions


cr
eate a
database, delete a record, add a record, edit a record, etc. and how to add menus. The first three of
these small applications will be written in Python code using a text editor.


My intention, again, is to try to keep this as simple as possible as

the next stage will be to combine all
the small applications into one fully functional database program using Boa Constructor. I will
introduce Boa Constructor at the end of this section.


This first version creates a small window with one button on it t
hat simply prints the message “Nothing
happening yet!” The process is always the same with wxPython


first create, size and position your
window. Then place controls on the window


in this case a single button called buttonCreate. Then
place a panel o
n the window. Next, bind an event to the control


you can call this event whatever you
like


here it is called onCreate. Next we have a line that shows the window and the panel and
controls.


We then have to define the OnCreate event and for this examp
le it simply prints a message to the
terminal. The four lines at the end define the application and give the main frame a title before starting
the application up with
app.MainLoop()
. Don't concern yourself too much with the wxPython code as
once we start

to use Boa Constructor we won't need to understand it in any great detail. This example
is simply to demonstrate how we add controls to an application and how we link events to the controls.
Please save this as cddb_creator_1.py and run it. You should
see:
















Fig 1.3

Python & SQLite Da
tabases

17


Click the button and see the helpful message!

#!/usr/bin/python


import wx


# Create the main program window

class MainWindow(wx.Frame):


def __init__(self, parent, title):




# Create the window


wx.Fra
me.__init__(self, parent, title=title, pos=(100, 100), size=(180,50))




# Place a button on the window


buttonCreate= wx.Button(self, wx.ID_ANY, "&Create Data",
\


pos=(40,10),size=(100,25))



# Place a panel on the
window


self.control = wx.Panel(self)




# Set events. Bind the event 'OnCreate', defined below, to the button.


self.Bind(wx.EVT_BUTTON, self.
OnCreate
, buttonCreate)




# Show the window


self.Show(True)




def
OnCreate
(self,e):


print "Nothing happening yet!"


app = wx.App(False)

# Give the window a title

frame = MainWindow(None, "DB Creator")

app.MainLoop()


Following is the Python code for our second wxPython application.
Please note, agai
n, that all indents
need to be 4 spaces, not a Tab.




Please delete your copy of cddb.db again before running this program



Please save this program as cddb_creator_2.py


The only changes to this program are the new import statements and the definition of
the onCreate
function, which now incorporates all the SQL statements for creating our cddb.db database file and the
table and data for it. First the code uses the os.path.exists() function to check if the database already
exists and displays a message if
it does. If the database doesn't exist, the code written under the else:
statement is carried out and connects to the database to execute the SQL before severing the
connection. The os module is imported as it is needed to check that the database doesn't

already exist.


Please note that the red backstrokes
\

are there to indicate a line break and should not be
included in the code. When you write this code in your text editor do not break the lines, either.

Python & SQLite Da
tabases

18







#!/usr/bin/python


import sqlite3

impo
rt wx

import os


# Create the main program window

class MainWindow(wx.Frame):


def __init__(self, parent, title):


# Create the window


wx.Frame.__init__(self, parent, title=title, pos=(100, 100), size=(180, 50))


# Place a b
utton on the window


buttonCreate = wx.Button(self, wx.ID_ANY, "&Create Data",pos=(40, 10),
\


size=(100, 25))


# Place a panel on the window


self.control = wx.Panel(self)


# Set events. Bind the event defined

below to the control.


self.Bind(wx.EVT_BUTTON, self.OnCreate, buttonCreate)


self.Show(True)




def OnCreate(self,e):


# Check the database doesn't already exist
.


fname = "cddb.db"


if os.path.exists(f
name):


dlg = wx.MessageDialog( self, "Database already exists", "DB Creator", wx.OK)


dlg.ShowModal() # Show it


dlg.Destroy() # finally destroy it when finished.


# This code is carried out if the database does
n't already exist.


else:


connection = sqlite3.connect('cddb.db')


cursor = connection.cursor()


cursor.execute("""


CREATE TABLE CDs (


CDID integer primary key autoincrement,



Title varchar(30),


Artist varchar(30)


)""")



cursor.execute("INSERT INTO CDs VALUES (null,'Electric Ladyland',
\



'Jimi Hendrix')")


cursor.execute("INSERT INTO CDs VALUES (null,'Collection'
,'Tracy Chapman')")


cursor.execute("INSERT INTO CDs VALUES (null,'Are You Experienced',
\



'Jimi Hendrix')")


cursor.execute("INSERT INTO CDs VALU
ES (null,'Goats Head Soup',
\



'Rolling Stones')")

Python & SQLite Da
tabases

19


cursor.execute("INSERT INTO CDs VALUES (null,'Tattoo You',
\



'Rolling Stones')")





connection.commit()


cursor.close()


connection.close()



# This shows a message dialog box with the message'Database created'


dlg = wx.MessageDialog( self, "Database created.", "DB Creator
", wx.OK)


dlg.ShowModal() # Show it


dlg.Destroy() # finally destroy it when finished.


app = wx.App(False)

frame = MainWindow(None, "DB Creator")

app.MainLoop()

If you delete your cddb.db file and run this program it will recre
ate the database file. Click the button a
second time and you will see the “Database already exists” message.


Next is the third and final version of this program. Please save it as cddb_creator_3.py. The program
has several additional features such as

a menu, menubar, status bar and an About window. It also has
new event definitions for onExit and onAbout which are linked to items on the menu. Hovering the
mouse over a menu item will produce a status bar message. I strongly recommend changing the
me
ssage wording, sizes and positions in the example to see how it affects the program and get a feel for
the code. I will show how to add menus, etc. in the sections on Boa Constructor.


#!/usr/bin/python


import os

import wx

import sqlite3


# Create th
e main program window

class MainWindow(wx.Frame):


def __init__(self, parent, title):


# Create the window


wx.Frame.__init__(self, parent, title=title, pos=(100, 100), size=(180,150))


# Place a button on the window


bu
ttonCreate = wx.Button(self, wx.ID_ANY, "&Create Data",pos=(40,10),size=(100,25))


# Place a panel on the window


self.control = wx.Panel(self)


# Place a status bar on the window


self.CreateStatusBar() # A StatusBar in the

bottom of the window


# Setting up the menu.


filemenu= wx.Menu()



# Add items to the menu


# wx.ID_ABOUT and wx.ID_EXIT are standard ids provided by wxWidgets.


menuCreate = filemenu.Append(wx.ID_ANY, "&Create Data
","Create database.")


menuAbout = filemenu.Append(wx.ID_ABOUT, "&About","About this program.")


menuExit = filemenu.Append(wx.ID_EXIT,"E&xit"," Terminate program.")



Python & SQLite Da
tabases

20


# Creating the menubar.


menuBar = wx.MenuBar()


menuBar.Append(filemenu,"&File") # Adding the "filemenu" to the MenuBar


self.SetMenuBar(menuBar) # Adding the MenuBar to the Frame content.




# Set events. Bind the events defined below to the various controls.


self
.Bind(wx.EVT_BUTTON, self.OnCreate, buttonCreate)


self.Bind(wx.EVT_MENU, self.OnCreate, menuCreate)


self.Bind(wx.EVT_MENU, self.OnAbout, menuAbout)


self.Bind(wx.EVT_MENU, self.OnExit, menuExit)



# Show the window.



self.Show(True)



def OnAbout(self,e):


# A message dialog box with an OK button. wx.OK is a standard ID in wxWidgets.


dlg = wx.MessageDialog( self, "A database creator", "About DB Creator", wx.OK)


dlg.ShowModal() # Show it


dlg.Destroy() # finally destroy it when finished.





def OnCreate(self,e):


# Check the database doesn't already exist


using the os module


fname = "cddb.db"


if os.path.exists(fname):


dlg = wx.Mes
sageDialog( self, "Database already exists", "DB Creator", wx.OK)


dlg.ShowModal() # Show it


dlg.Destroy() # finally destroy it when finished.


# This code is carried out if the database doesn't already exist.


else:



connection = sqlite3.connect('cddb.db')


cursor = connection.cursor()


cursor.execute("""


CREATE TABLE CDs (


CDID integer primary key autoincrement,


Title varchar(30),



Artist varchar(30)


)""")




cursor.execute("INSERT INTO CDs VALUES (null,'Electric Ladyland',
\



'Jimi Hendrix')")


cursor.execute("INSERT INTO CDs VALUES (null,'Collection','Tracy Chapman')")



cursor.execute("INSERT INTO CDs VALUES (null,'Are You Experienced',
\


'Jimi Hendrix')")


cursor.execute("INSERT INTO CDs VALUES (null,'Goats Head Soup',
\


'Rolling Stones')")


cursor.execute("INSERT I
NTO CDs VALUES (null,'Tattoo You',
\


'Rolling Stones')")



connection.commit()


cursor.close()


connection.close()



dlg = wx.MessageDialog( self, "Database created.", "DB Creator", wx.OK)

Python & SQLite Da
tabases

21



dlg.ShowModal() # Show it


dlg.Destroy() # finally destroy it when finished.



def OnExit(self,e):


self.Close(True) # Close the frame.


app = wx.App(False)

frame = MainWindow(None, "DB Creator")

app.MainLoop()

















Fig 1.4



This has come a long way from the first database creator program and is now a fully
-
featured GUI with
all the bells and whistles!


Personally, I find it very difficult to work directly with wxPython as it is hard to keep track of the code
w
hen there are a lot of buttons and controls on an application. It's difficult to visualise where controls
are. It is also hard to position the controls by writing code. I prefer to use Boa Constructor for all these
reasons.


Much more information on wxPy
thon can be obtained from the excellent book “wxPython in Action”
by Noel Rappin and Robin Dunn ISBN 1
-
932394
-
62
-
1. The book is out
-
of
-
date as it is 6 years old, but
it covers everything you could possibly want to know about wxPython. The website
www.wxpython.org

is full of excellent material and links for this subject.


We can now move on to start using Boa Constructor to write our wxPython code for us and save us a
lot of headaches. If you have ever used Visual
Studio, Visual Basic, REALbasic, Lazarus or Delphi
you will find Boa Constructor very familiar.


If you haven't


don't worry, all will be explained...


Python & SQLite Da
tabases

22

Delete a CD


Introducing Boa Constructor

Before we start working with Boa Constructor, a word or two

of advice! Boa Constructor is still only a
0.6.1 version, it is not yet a fully
-
functional 1.0.0 version and is still a little “buggy” in places. At the
time of writing it was still “beta” software.

I have found that it is best to
save your work any t
ime you make changes

(fairly obvious) and any time
you make design changes like adding, deleting or re
-
sizing controls
it is necessary to click on the big
blue tick symbols in the Editor or the Inspector windows
. If you don't, the changes to your design
will
not be saved. This is referred to as “Posting the session” for some strange reason! Sometimes, the tick
will be available in both windows, sometimes only in one and other times not at all. The program is
intelligent enough to realise when you are ma
king changes and need to save them.

The program may occasionally crash due to certain bugs!


This is why it is important to save your
work regularly.

One bug is that opening a file by right
-
clicking and then selecting open with Boa Constructor will
usually

open
the previous file that was open in Boa Constructor
and not always the one you want. It's
best to open files using the File/Open option in Boa Constructor.



Open Boa Constructor and click on New.



Locate the wx.Frame item (6th button along) and click
it



Click on the 12th button along in the Code Editor window (a small box with a green arrow in
it.)

This will open the Frame Designer for you which is where we design our application GUIs. The frame
will be called Frame1.



Click on Edit in the Code E
ditor



Click on “add module runner”

This will add some code to the appplication which will show the window and allow it to run. If you
forget to do this, the program will still run but you won't see anything!



Select File in the Code Editor



Save your pro
gram as bare_frame.py

Close Boa Constructor and run the program from your Terminal and you should see:







Fig 1.5

Python & SQLite Da
tabases

23

You can also click on the yellow Run arrow in the Editor frame to run the program.


If you look at the code for bare_frame.py you should
be able to see where the position of the frame on
the screen is set and where the size of the frame is set. The rest is standard Boa Constrictor code for
setting up a frame. Personally, I'm very glad it does all that itself and saves me the job. It mean
s I can
concentrate on getting the database to work!




Re
-
open Boa Constructor



Open bare_frame.py



Save it again as delete_CD_1.py



Open the Frame Designer again



Open the Containers/Layout tab



Select wx.StatusBar


the seventh button



Drag and drop a cop
y of the wx.StatusBar onto the Frame. It should appear as a thin bar at the
top
of the Frame



Select the Frame in the Obs tab of the Inspector window



Select the Props tab



Click on the far right of the StatusBar property



Select self.statusBar1 from th
e drop
-
down menu. The StatusBar should now appear as a thicker
bar at the
bottom
of the Frame



Click one of the blue tick icons to post the session



Save everything


Note that the StatusBar
must
be added before the Panel, unlike all the other controls.




O
pen the Containers/Layout tab again



Select wx.Panel


the first button



Drag and drop a copy of the wx.Panel to your wx.Frame



Click on the bottom right corner of the wx.Frame and drag it very slightly down and to the
right. The wx.Panel will be automat
ically resized to fit the wx.Frame



Save again


It is
always

best in wxPython to place the Panel first and then place other controls on the panel.
Placing controls directly on a Frame can cause problems. However, this is not true for the StatusBar


see a
bove.


To the left of the Frame Designer there is a window called the Inspector.




Click on the Obs Tab in the Inspector



Double
-
click on Frame1. This will open up the Inspector window for the main frame.



Click on the Constr Tab



Change the Title to 'C
D Database'



Click again on the Obs Tab

Python & SQLite Da
tabases

24



Double click Frame1



Change wx.Size to (200, 150)



Click the blue tick to post the session and save the project again



Click the Buttons Tab at the top of the screen



Select wx.Button



Drag and drop a copy of the Bu
tton onto your Panel



Change the Label to &Delete CD



Change the position to (58, 20)



Change the name to btnDelete



Save the project again



Select the button again in the Frame Designer



This time select the Evts Tab in the Inspector



Click on ButtonEvent
. This will write wx.EVT_BUTTON in the right
-
hand side panel



Double
-
click on the words wx.EVT_BUTTON and OnBtnDeleteButton should appear below in
the panel



Double click on the word OnBtnDeleteButton



Choose Rename



Rename this as OnDelete



Click the tick

mark in the Inspector window to post the session



Save everything. This will produce a place in the code for us to define the event that occurs
when the button is pressed.



Close the frame in the Frame Editor and this will re
-
open the Editor window


Tow
ards the end of the code you should see the lines:


def OnDelete(self, event):


event.Skip()


This is where we write our code to delete a record. At the moment the code simply says to skip an
event


i.e. Do nothing.




Delete the line
event.Skip()



Insert this code:


dialog = wx.TextEntryDialog(None,

"CD title?",

"CD delete", "", style=wx.OK|wx.CANCEL)

if dialog.ShowModal() == wx.ID_OK:


searchVal = dialog.GetValue()


connection = sqlite3.connect('cddb.db')


cursor = connection.cursor
()


cursor.execute("DELETE FROM CDs WHERE Title = '" + searchVal + "'")


connection.commit()




# A message dialog box with an OK button. wx.OK is a standard ID

Python & SQLite Da
tabases

25


# in wxWidgets.


dlg = wx.MessageDialog(self, "CD deleted", "DB GUI", wx.OK)


dlg.ShowModal() # Show it


dlg.Destroy() # finally destroy it when finished.


cursor.close()


connection.close()

dialog.Destroy()


The first three lines of this code define a text entry dialog window with the title “CD delete”. The
Dialog

asks for the CD title, which we type into the text box. The dialog has two buttons, OK and
Cancel. If the user clicks the OK button then the usual SQL code for deleting is executed, the
connection to the database is closed and a dialog is shown to say t
he record has been deleted. The
message dialog and the text entry dialog are then destroyed after the user clicks OK.




Move to the beginning of all the code for the application and type:


# Import the required Python module

import sqlite3


Directly benea
th where it says import wx. This imports the required module for interacting with
SQLite.




Save everything



Close Boa Constructor



Run the program from your Terminal.


You should see a simple application which opens a dialog box when you click the button
. The dialog
will allow you to select a CD to delete and then it will inform you when the CD has been deleted. Next
we will add code to make it impossible to try to delete a CD that doesn't exist and we will add menus,
status bars, and an About window.


As you can see, working with Boa Constructor takes all the guesswork and frustration out of using
wxPython and also automatically generates perfect code without you having to type a line apart from
defining your functions


which is mostly SQL statements.

There is a lot of help and guidance on using
Boa Constructor at
http://boa
-
constructor.sourceforge.net/

and the program itself has a full offline help
system which can be accessed by clicking any of
the three book icons on the main screen. There are
also introductory videos and tutorials available on the internet if you are not used to using a RAD GUI
building program. These offer a gentle introduction into the world of RAD GUIs.


Now let's change o
ur OnDelete code a little to make it impossible to try to delete a non
-
existent record.




Change the code for OnDelete to the following:


dialog = wx.TextEntryDialog(None,

"CD title?",

"CD delete", "", style=wx.OK|wx.CANCEL)

Python & SQLite Da
tabases

26

if dialog.ShowModal() == wx.I
D_OK:


searchVal = dialog.GetValue()


connection = sqlite3.connect('cddb.db')


cursor = connection.cursor()


sql = "SELECT * FROM CDs WHERE Title = '" + searchVal + "'"


cursor.execute(sql)


recordset = cursor.fetchall()


if len
(recordset) <> 0:


cursor.execute("DELETE FROM CDs WHERE Title = '" + searchVal + "'")


connection.commit()


# A message dialog box with an OK button. wx.OK is a standard ID


# in wxWidgets.


dlg = wx.MessageDialog(s
elf, "CD deleted", "DB GUI", wx.OK)


dlg.ShowModal() # Show it


dlg.Destroy() # finally destroy it when finished.


else:


dlg2 = wx.MessageDialog(self, "CD does not exist", "DB GUI", wx.OK)


dlg2.ShowModal() # Show it



dlg2.Destroy() # finally destroy it when finished.


cursor.close()


connection.close()

dialog.Destroy()


Now try to delete a CD that doesn't exist in the database. You will see the message “CD does not
exist”. The code first executes an SQL

statement to retrieve records containing the search string.
These are saved as a

recordset

and then the length of the recordset is checked with len(recordset). If
the length of the recordset is
not

0 then the recordset contains records and after the rec
ord is deleted the
“Record deleted” message is shown. Otherwise, the recordset is of 0 length (contains no records) and
the “Record does not exist” message is shown.


Bear in mind when writing code to check for errors that SQLite will
not
give any error m
essages if you
declare one of the fields in your database to be numerical and then write text to it (or vice
-
versa). You,
as a programmer, need to deal with this by writing the code to ensure that numerical fields can only
accept numbers. Remember also t
hat phone numbers are normally set as
text
fields because people
often write them with gaps in (0111 222 9999) and it doesn't make much sense to do maths with phone
numbers.




Save everything as delete_CD_1.py


Python & SQLite Da
tabases

27

Menus



Open your copy of delete_CD_1.py again




Save it again, this time as delete_CD_2.py.


Now we are going to add a menubar to the program. Menubars are common components on windows
programs and you will probably want to add one to just about every program you write. Our menu bar
will contain t
wo entries, File and Help. Selecting either of these will display a dropdown menu. The
user can then select an option from the drop down menu.




Open Boa Constrictor



Open your new delete_CD_2.py file



Select the Frame1 tab in the Editor to ensure that we
are editing the Frame



Start the Designer, by clicking the design button.



On the Palette select the tab called 'Utilities (Data)'.



The dropdown menu (wx.Menu) is one of the components listed on that tab.



Select the wx.Menu



Click the left mouse button eith
er in the Editor's Data View or in the Designer, in which case
you have to be careful to ensure that you click on 'Frame1' area and not on the 'statusbar'.


Move the mouse over the buttons. Help tips show that one of these buttons represents the wx.Menu
c
ontrol. This is the control we want. Click on this button. The button should change shading to indicate
that it is pressed. The Palette contains a checkbox to show the currently selected component type. This
should say wx.Menu.
The menu will not be visib
le on the frame.

However, there will now be an
entry on the Data View.



Repeat the above procedure. You should now have two wx.Menus in the Data View, called menu1 and
menu2.




Select menu1 using the mouse. The Inspector will now display the Name and Tit
le for menu1.



Edit the name of the first wx.Menu to call it menuFile.



Call the second wx.Menu menuHelp.



Set the titles to File and Help respectively.



Double click the menuHelp entry in the Data View. This opens the 'Collection Editor'. The
Collection E
ditor is used to add items to our menus.



Click the 'New' button on the Collection Editor. This creates a new menu item within the
dropdown menu. This becomes the current item in the Inspector.



Edit the field 'Label'. Set the name from 'Items0' to 'About'




I would also recommend changing the ItemId from 'ITEMS0' to 'ABOUT'.



Change the field 'Help String' to 'A Python CD database'.



The Collection Editor toolbar contains a 'Refresh' button. Press this button to see the Inspector
change in the Collection Ed
itor window.

Python & SQLite Da
tabases

28

The Inspector is the pane used to configure events. We need to configure the action which occurs when
the 'About' menu item is selected. When the 'About' menu item is selected, and event called
'EVT_MENU' is generated and sent to our program.

We need to add a method to our class to handle
this event. The left hand side of the events pane shows the groups of events which are available. For the
menu item, there is only the 'MenuEvent' group.


The right hand side of the events pane shows the eve
nts in the selected group. For the menu item, there
is only one event wx.EVT_MENU in the 'MenuEvent' group.


The bottom of the Events pane shows the event handlers in your application for the current component
(the 'About' menu item). You should now have
a new handler called OnMenuHelpAboutMenu. This is
the name of the method which will be invoked when the 'About' option is selected from the Help menu.


Notice the naming of the event handler. Boa Constructor generates the names in this manner. The event
i
s the last part (Menu). The component is the middle part and here it is the 'About' sub
-
component of
the 'menuHelp' component. Finally, Boa Constructor follows the convention of prefixing all event
handlers with the word 'On'.




Double
-
click the menuHelp:M
enu item in the Editor



Click the 'About' menu item



In the Inspector, select the Evts Tab



Click MenuEvent



Double
-
click wx.EVT_MENU. The word OnMenuHelpAboutMenu will appear in the lower
pane



Close the collection Editor.


A space is created in the code for

us to define the OnMenuHelpAboutMenu event. Now we have to
repeat the process to add options to the File menu:




From the Data View in the Editor, double click on the 'menuFile' item to open the Collection
Editor. Add two new items.



Select each menu ite
m in turn, and label them 'Add CD' and 'Delete CD'




Change the ItemId to ADD and DELETE respectively.



Enter some help text for each menu item “Add a CD” and “Delete a CD”.



Press the Collection Editor refresh button to display the new labels.



Select each

menu item in turn.



For each item select the Events pane in the Inspector, and add an event handler for
EVT_MENU to each item.



Close the collection Editor.



Now we are going to create the menubar:




On the Palette window, select the 'Utilities (Data)'

pane.

Python & SQLite Da
tabases

29



On this pane select the menubar component.



Move the cursor over the Editor Data View.



Click the left mouse button on this pane to add a menubar called 'menuBar1' to the application.



Double click on the menuBar1 item to open it's Collection Edito
r.



Add two items to the menubar using the collection Editor.



Select Menus0, in the Inspector Constructor Pane,



Edit the 'Menu' field. This is a pop
-
up menu, with three items, the wx.Menu() constructor, and
our two dropdown menus, select the self.menuFile

item and set the title to 'File'.



This makes the menu 'menuFile' the first dropdown menu on the menubar.



In the Collection Editor select the second item.



Repeat the process to link Menus1 to the Help dropdown menu, with the label 'Help'.



Select the m
ain Frame, Frame1 on the Designer.



The frame should now be the current control in the Inspector.




Select the Properties pane ('Props') in the Inspector.



Edit the field 'MenuBar'. This is a pop
-
up menu.



Select your new menubar self.menuBar1. This proper
ty defines which menuBar to associate
with the frame.



Save the changes using either Post button to close the Designer and let Boa generate the source
code.



Make sure the import lines are present at the start of the program


Boa Constructor doesn't
inse
rt these for you.



Save the source code for your generated source file delete_CD_2.py


Run the program. You should now see the menus and the status bar. When you select a menu option,
the help text should appear in the status bar. For now clicking on th
e File menu items will not do
anything. We need to add the code next. Clicking on Help/About will show a short message.












Fig 1.6



Open the Editor window



Find the code for the OnMenuFileDeleteMenu function and delete it


it says
event.Skip()




C
opy the code from the
OnDelete()

function and paste it where the
event.Skip()

line was.

Python & SQLite Da
tabases

30



The Delete CD item in the File menu now has exactly the same functionality as the Delete
button.



Save your work again, this time as delete_CD_3.py


Now to add anoth
er button for adding a new CD...

Add a CD



Open your delete_CD_3.py file in Boa Constructor



Click on the Design button.



Click the Buttons Tab at the top of the screen



Select wx.Button.



Drag and drop a copy of the Button onto your Panel to the right of
the Delete button.



The exact position doesn't matter as we will move it again later.



Make sure it is the same width and height as the Delete button.



Change the Label to &Add



Change the name to btnAdd.



Post the session



Save the project again.




Sele
ct the button again in the Frame Designer.



Select the Evts Tab in the Inspector



Click on ButtonEvent.



This will write wx.EVT_BUTTON in the right
-
hand side panel.



Double
-
click on the words wx.EVT_BUTTON



OnBtnAddButton should appear below in the panel
.



Double click on the word OnBtnAddButton



Choose Rename.



Rename this as OnAdd.



Click the tick mark in the Inspector window to post the session



Save everything.


This will produce a place in the code for us to define the event that occurs when the b
utton is pressed.
At this point, close the frame in the Frame Editor and this will re
-
open the Editor window.


Towards the end of the code you should see the lines:


def OnAdd(self, event):


event.Skip()

This is where we write our code to add a CD.

At the moment the code simply says to skip an event


i.e. Do nothing. Delete the line:


event.Skip()


Python & SQLite Da
tabases

31

and insert this code:


dlg = CustomDlg.Dialog1(self)

try:


if dlg.ShowModal() == wx.ID_OK:


Title = dlg.txtTitle.GetValue()


Arti
st = dlg.txtArtist.GetValue()


connection = sqlite3.connect('cddb.db')


cursor = connection.cursor()


sql = "SELECT * FROM CDs WHERE Title = '" + Title + "'"


cursor.execute(sql)


recordset = cursor.fetchall()



if len(recordset) <> 0:


dlg2 = wx.MessageDialog(self, "Title already exists", "CDDB", wx.OK)


dlg2.ShowModal() # Show it


dlg2.Destroy() # finally destroy it when finished.


else:


cursor.execute(
"INSERT INTO CDs VALUES (null, '%s', '%s')" % (Title, Artist))


connection.commit()


# A message dialog box with an OK button. wx.OK is a standard ID in wxWidgets.


dlg2 = wx.MessageDialog( self, "CD added", "CDDB", wx.O
K)


dlg2.ShowModal() # Show it


dlg2.Destroy() # finally destroy it when finished.


finally:


cursor.Close()


connection.Close()


dlg.Destroy()




Add the line
import CustomDlg

just below the line
import sq
lite3

at the very beginning of the
program.

This code opens a
custom

dialog called CustomDlg with two text boxes for entering the Title and Artist,
obtains the data from the user, connects to the database and inserts the CD record before severing the
con
nection and showing a brief message. A custom dialog is a dialog which has been created by us for
a special purpose


in this case, to obtain two items of text at the same time. We need a custom dialog
because the usual dialogs that are available only ha
ve one text entry box.

The code we have written above for the Add button will not work at the moment as the custom
Python & SQLite Da
tabases

32

dialog doesn't exist yet


we still have to create it ...




For now, save your program as CDDB.py

Creating a Custom Dialog

This example of
a custom dialog is a very simple one, but it is possible to create much more elaborate
ones if necessary, with text entry boxes, numerical entry boxes, radio boxes, tick boxes and drop
-
down
lists. How complex they are depends on how many items you have in

each record in your database and
the type of fields you have.

Custom dialogs are saved as a separate Python document altogether, apart from the main program and
are called by the program with the
import

line. The custom dialog form should be in the same
folder as
the main program file for this reason. Once you have written a custom dialog you can use it over and
over in other programs or in the same program for different purposes. They can easily be adapted for
other purposes. The one we are about to w
rite will be used for two purposes.

Custom Dialogs are a very important part of the database app design process because the Dialog can
be designed to ensure that only valid data is allowed to be saved to the database. For this reason they
have to be desig
ned very carefully.



Open up Boa Constructor and choose wx.Dialog from the New tab.



Save the project as CustomDlg.py in the same folder as your CDDB.py file.



Switch to Design view in the Editor



Add two wx.StaticText controls and two wx.TextCtrl control
s to the dialog.



Change the Label property of the first wx.StaticText control to Title:



Change the Label of the second to Artist:



Change the name of the first wx.TextCtrl to txtTitle



Change the name of the second to txtArtist



Change the title of Dia
log1 to CDDB.



Click the arrow to post the session



Save the file.




Add two buttons below the controls, one to the side of the other.



Change the id property of the first to wx.ID_OK



Change the Label to &OK



Change the Name to btnOK.



Change the id of t
he second button to wx.ID_CANCEL



Change the Label to &Cancel



Change the Name to btnCancel.



Click the arrow to post the session



Save the file.


The Dialog can only be run from within your main program. Now, when you run the program and click
on File/Add
, you will see this dialog:

Python & SQLite Da
tabases

33








Fig 1.7

and you can now add a CD to the database using the Custom Dialog form. It doesn't really matter if
your version of CustomDlg isn't arranged exactly the same as the above. As long as it has two
wx.TextCtrl contr
ols with the names txtTitle and txtArtist


and the OK and Cancel buttons, everything
will work.



Follow the procedure shown above for the File/Delete menu item to copy the code from the Add
button to the File/Add menu item



Save the whole project again a
s CDDB.py

Code Cleanup!

If you have a look at the code for CDDB.py you will notice a couple of fairly glaring problems. Firstly,
it is already hard to follow exactly what does what at each part in the code


it's all getting a bit busy.
Secondly, the ent
ire block of code for both the
OnAdd()

and
OnDelete()

functions is duplicated for the
button and the menu entry. By the time we add two or three more functions and a few more buttons it's
going to be a very long and complicated piece of code. Fortunately
, there is a way to improve this! We
can create a new Python file called func.py


for example


and move the code for the functions to this
new file. We then add
import func

at the beginning of CDDB.py. We can then
call
the functions from
the main CDDB
.py file using the code
func.Add()

or
func.Delete().

This has the added benefit that if we should need to change something in the function later, we only
have to do this in the func.py file and the changes will carry through to the main program. If all th
at
sounded complicated, it isn't in practice and you will soon see how easy it is to do and how much better
it looks.

Don't forget that one of the most important jobs you do as a programmer is making your own life
easier!



Open your CDDB.py file in your tex
t editor and copy the code for
OnDelete()
.



Open a new file and call it func.py



Add the line
import wx

at the beginning of func.py



Add the line
import sqlite3

at the beginning of func.py



Add the line
import CustomDlg

at the beginning of func.py



Paste th
e code for the OnDelete function (including the line
def OnDelete(self, event):
) into
func.py

Python & SQLite Da
tabases

34



Save func.py.



Replace the OnDelete function in CDDB.py with:




def OnDelete(self, event):



func.OnDelete(self, event)



Add the line
import func

at the
start of CDDB.py.



Save the program and run it.



Test the delete button and it should work as before.



Now do the same for the OnMenuFileDeleteMenu function in CDDB.py



Replace the OnMenuFileDeleteMenu finction with:




def OnMenuFileDeleteMenu(self, even
t)



func.OnDelete(self, event)
.

Now follow the same procedure for the OnAdd and OnMenuFileAddMenu functions to move all the
code into func.py. As you can see, we now have two much shorter files and the main CDDB.py file is
much easier to follow. I break

mine up into four sections with comments like # IMPORT
STATEMENTS: and # USER
-