In this tutorial we will use sqlite to create and maintain a simple phone list. was simply imported. The phone list was a list of lists where each sublist contained the name and phone number.
This was very convenient when keeping the code as small as possible is the primary concern. The Python compiler becomes the parser for the input data and the "str" function can format the entire phonelist in order to write it back to the disc.
Another simple way to store the phone list might be in a text file with one entry per line. This would require more code of our own to convert this file to (and from) the internal format but would be useful in other ways. For one it would be easier to edit with a standard text editor like emacs or vi. For another, simple utilities like "grep" could be used for rapid searching from a command line. That is unless the phone list grows to the size of a phone book.
For more sophisticated data storage we need more. If several people are using our GUI at the same time to update phone numbers, we would like to see those changes immediately and have the changes made by one person not interfere with those of another. If two people pull up the phone list and make changes to seperate records, the second person to save the phone list back to the disc will wipe out the change made by the first person.
SQL databases will synchornize update and offer many other features besides.
We'll look at a very simple example of using Mysql with our phone list. Another tutorial is available oriented around PostSql
Just enough SQL
A standard client, the program "sqlite", lets you interact directly with the
database file thru the keyboard or other stdin input.
A database consists of tables which in turn consists of rows and columns. Columns in a table are fields such as "name" and "phone". Each column has a datatype such as varchar (roughly equivalent to a Python string but with a maximum length) or integer. For our gui we are going to make a table called "phones" with 2 varchar columns called "name" and "phone".
Creating our table
Creating a table involves listing the name and type of each column.
mysql> create table phones (id int, name varchar(20), phone varchar(12));
Our table "phones" has 3 fields; id, name, and phone. The integer "id" field will be used as a handle on a row. All rows will be assigned a unique id, just increasing numbers. It will become clearer why this is a good idea as we proceed.
Inserting data into the table
The SQL insert command lets us populate the table with rows. Here is an example.
insert into phones values (1,'Meyers, Chris', '343-4349');
We supply values for id, name and phone. Incidentally there is another format for the insert command where only designated columns are set and other columns are set to a default value or null (equivalent to None in Python).
The files phones.sql contains commands to create the database and populate it with insert commands. It may be piped to the client program "sqlite3" to initialize the database table.
sqlite test.db <phones.sql
Accessing data in the table
The SQL select command lets us find data we are interested in. The simplest format will dump the entire table.
mysql> select * from phones; +------+-----------------+----------+ | id | name | phone | +------+-----------------+----------+ | 1 | Meyers, Chris | 343-4349 | | 2 | Smith, Robert | 689-1234 | | 3 | Jones, Janet | 483-5432 | | 4 | Barnhart, Ralph | 683-2341 | | 5 | Nelson, Eric | 485-2689 | | 6 | Prefect, Ford | 987-6543 | | 7 | Zigler, Mary | 567-8901 | | 8 | Smith, Bob | 689-1234 | +------+-----------------+----------+ 8 rows in set (0.11 sec)
But we can be more restrictive by using a "where" clause. For example.
mysql> select * from phones where id=6; +------+---------------+----------+ | id | name | phone | +------+---------------+----------+ | 6 | Prefect, Ford | 987-6543 | +------+---------------+----------+ 1 row in set (0.05 sec)
We can also request just certain columns.
mysql> select name,phone from phones where name like "Smith%"; +---------------+----------+ | name | phone | +---------------+----------+ | Smith, Robert | 689-1234 | | Smith, Bob | 689-1234 | +---------------+----------+ 2 rows in set (0.05 sec)
Here the "%" is the wildcard character.
Updating data in the table
The SQL update command is used to change column values in rows specified by a "where" clause. Here is an example.
mysql> update phones set name='Chase, Chevy' where id=6; Query OK, 1 row affected (0.06 sec) mysql> select * from phones where id=6; +------+--------------+----------+ | id | name | phone | +------+--------------+----------+ | 6 | Chase, Chevy | 987-6543 | +------+--------------+----------+ 1 row in set (0.06 sec)
Deleting rows from the table
Finally, the SQL delete command will erase any rows matching the "where" clause. If there is no where clause, all rows are deleted.
mysql> delete from phones where id=6; Query OK, 1 row affected (0.05 sec) select * from phones; +------+-----------------+----------+ | id | name | phone | +------+-----------------+----------+ | 1 | Meyers, Chris | 343-4349 | | 2 | Smith, Robert | 689-1234 | | 3 | Jones, Janet | 483-5432 | | 4 | Barnhart, Ralph | 683-2341 | | 5 | Nelson, Eric | 485-2689 | | 7 | Zigler, Mary | 567-8901 | | 8 | Smith, Bob | 689-1234 | +------+-----------------+----------+ 7 rows in set (0.00 sec)
It should now be clear why the id column is important. If we want to be sure we are modifying or deleting a single row, this provides a mechanism. We may have a given name in the table more than once (maybe she has a cellphone) and the phone number may linked to multiple people (a house phone).
Python and sqlite
A Python program may be an sql client as well. In fact, the interface has much the same look and feel as the standard sqlite client. A few examples will demonstate. We'll start by creating a connection object called db and telling it to use the test.db
>>> import sqlite
>>> db = sqlite.connect('test.db')
Next let's do a query. The variable "c" is set to a cursor object which can fetch rows for us in a list of lists. The data is returned in the same format as in the previous program.
>>> c = db.execute("select * from phones")
>>> rows = c.fetchall()
>>> for row in rows : print row
...
[1, 'Meyers, Chris', '343-4349']
[2, 'Smith, Robert', '689-1234']
[3, 'Jones, Janet', '483-5432']
[4, 'Barnhart, Ralph', '683-2341']
[5, 'Nelson, Eric', '485-2689']
[7, 'Zigler, Mary', '567-8901']
[8, 'Smith, Bob', '689-1234']
>>>
Inserting, updating and deleting rows is, by comparsion, quite simple. We just pass the command to db.query and, voila, it happens.
>>> db.execute("update phones set phone='338-1233' where id=1")
>>> c = db.execute("select * from phones where id=1")
>>> c.fetchall()
[[1, 'Meyers, Chris', '338-1233']]
>>>
Adapting our GUI for SQL
The changes required to use mysql with our GUI are actually fairly minor. Click here to see the full code. Let's look at the changes one at a time.
At the top of the program we import MySQL and set up a database connection to the test database.
import sqlite3
db = sqlite3.connect('test.db')
Let's look next at the function "setSelect" which fills in our list control. Here, instead of importing the phone list, we simply use fetchrows to get the same list of lists.
def setSelect () :
global phoneList
c = db.execute("select id,name,phone from phones order by name")
phoneList = c.fetchall()
select.delete(0,END)
for id,name,phone in phoneList :
select.insert (END, name)
All other SQL commands are channeled to the function "dosql" which makes sure setSelect is called after the update, delete or insert happens. This also catch any changes made in the meantime by other users. For learner feedback "dosql" also prints the sql command to the launch window.
About the only other feature worth remarking on is the generation of new id numbers as new rows are inserted.
c = db.execute("select max(id)+1 from phones")
id = c.fetchone()[0]
The SQL max function does what you would expect. Adding one gives us a new unique id to be used in the insert immediately following.
There is a potential problem with this however. If hundreds of users were using the program at the same time, two might inadvertantly fetch the same "max(id)+1" before either does their insert. Then we would have two rows in the table with the same id; something we definitely don't want. Databases have ways of dealing with these "racing" conditions, from providing automatic id columns to transaction processing, where multiple SQL statements can be guarenteed concurrent (and non-interrupted) execution.