[Tutor] MySQL mangling & list subscription question

[Danny Yoo]

On Fri, 3 Aug 2001, Rob Andrews wrote:

> # Rob we are trying to display the customer information in a form.
> #
> # 1. How do we pull specific customer records from the data base
> #
> # 2. assign the data to a variable
> #
> # 3. display the variable in the form and change the variable
> # in the record.
> #

First things first: your friend will need to download a database module
for MySQL.  You can direct your friend here;

    http://python.org/topics/database/modules.html

for a list of the popular ones.  There's a very good one for MySQL called
MySQLdb, which can be found from sourceforge.net here:

http://sourceforge.net/project/showfiles.php?group_id=22307&release_id=37980

Doing SQL queries in Python is fairly regular between all the database
modules --- first, you'll want to get a connection, the object that will
let us talk to the database.  We can imagine that it will look like this:

###
import MySQLdb

connection = MySQLdb.connect(user="bilbo", passwd="elbereth",
                             db="The Red Book")
###


Once we have this connection, we can grab a "cursor" from the connection
that will let us execute queries:

###
cursor = connection.cursor()
###


What sort of thing might we want to know about?  Perhaps we're looking for
all the names in a merry band of thieves raiding a dragon's den.  What we
want to do is tell Python to start up the search:

###
cursor.execute("""select name, email from addressbook
                  where height < 3""")
###

But it's not enough to tell it to do the search... we also want it to give
us back the results!  We have several options here: we can pull them out,
one at a time:

###
name, email = cursor.fetchone()
###

or we can do it all at once:

###
thief_information = cursor.fetchall()
###


I have NOT been able to test this code though, so I might have made a
mistake somewhere.  It's probably a good idea to refer your friend to the
"Python Database API v2.0", which is very dry, but at least it's
authoritative about what functions can be called:

    http://python.org/topics/database/DatabaseAPI-2.0.html


The other question about changing values in the database is an
SQL-specific one: ask your friend to take a look at:

    http://devshed.com/Server_Side/MySQL/Intro

so that they can get a brief overview on how do do SQL queries that do
"UPDATE"'s and "INSERT"'s.  I think UPDATE is what your friend will need
to change values in the database.  Have them practice on a throw-away
database first though: SQL makes it really easy to do something silly like
"DELETE FROM IMPORTANT_CRITICAL_DATA" without any warnings.

Feel free to ask more questions about this.  Hope this helps!