In the previous tutorial we kept our phone list data in a python module that 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
Mysql, like other database systems, is a client/server application. The server program (or daemon) is called "mysqld" and it actually does all of reading and writing to the disc. Client programs then request actions from the daemon, such as inserting, updating, deleting or just searching for data.
A standard client, the program "mysql", lets you interact directly with the database daemon 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". Our table will be part of the database "test" which comes built-in.
Unless the mysql daemon is already running you may have to start it. If you get an error message when running mysql, check with your system administrator.
mysql>use test; mysql>show tables; Empty Set (0.00 sec)
The commands requested the daemon to use the "test" database and then to show tables in the test database. Since we haven't created any (nor has anyone else) it is currently empty.
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. Once our table is created the "describe" command shows off its structure. Don't worry about the last 4 columns.
mysql> describe phones; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | phone | varchar(12) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.11 sec)
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 "mysql" to initialize the database table.
mysql <phones.sql
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.
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)
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) 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 | | 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).
A Python program may be an sql client as well. In fact, the interface has much the same look and feel as the standard mysql client. A few examples will demonstate. We'll start by creating a connection object called db and telling it to use the test database.
>>> import MySQL
>>> db = MySQL.connect('')
>>> db.selectdb("test")
>>>
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.query("select * from phones")
>>> rows = c.fetchrows()
>>> 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']
>>>
Although we won't use it in the program, the cursor object may also retrieve a list of dictionaries. This can be more convenient since we don't have worry about which column is in which position.
>>> d = c.fetchdict()
>>> for row in d : print row
...
{'phones.name': 'Meyers, Chris', 'phones.phone': '343-4349', 'phones.id': 1}
{'phones.name': 'Smith, Robert', 'phones.phone': '689-1234', 'phones.id': 2}
{'phones.name': 'Jones, Janet', 'phones.phone': '483-5432', 'phones.id': 3}
{'phones.name': 'Barnhart, Ralph', 'phones.phone': '683-2341', 'phones.id': 4}
{'phones.name': 'Nelson, Eric', 'phones.phone': '485-2689', 'phones.id': 5}
{'phones.name': 'Zigler, Mary', 'phones.phone': '567-8901', 'phones.id': 7}
{'phones.name': 'Smith, Bob', 'phones.phone': '689-1234', 'phones.id': 8}
>>>
Inserting, updating and deleting rows is, by comparsion, quite simple. We just pass the command to db.query and, voila, it happens.
>>> db.query("update phones set phone='338-1233' where id=1")
>>> c = db.query("select * from phones where id=1")
>>> c.fetchrows()
[[1, 'Meyers, Chris', '338-1233']]
>>>
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 MySQL
db = MySQL.connect('')
db.selectdb("test")
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.query("select id,name,phone from phones order by name")
phoneList = c.fetchrows()
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.query("select max(id)+1 from phones")
id = c.fetchdict()[0].values()[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.
Copyright © 2003-2015 Chris Meyers