a gadfly SQL question --- table creation

Andy Todd andy_todd at spam.free.yahoo.com
Thu Jul 5 01:26:39 EDT 2001

Joe Potter <jm7potter at hotmail.com> wrote in
<qlk7kto6lcqqsm2dslsmvrp5eeu3bhpdf7 at 4ax.com>: 

>On Thu, 05 Jul 2001 00:55:36 GMT, andy_todd at spam.free.yahoo.com (Andy
>Todd) wrote: 
>>Joe Potter <jm7potter at hotmail.com> wrote in
>><n237ktc10fithjhm8vj4pfkmrq7k4fjop8 at 4ax.com>: 
>>>Hello all,
>>>In working with the Python SQL database called Gadfly I have
>>>discovered something that I do not understand.
[ snipped my reply ]
>Thanks Andy. That really did help.
>Now, might I ask how one gets "1, Andy, Todd" out of Gadfly and back
>into a Python object for direct manipulation?
>Regards, Joe

Blimey, hard questions now ;-) I haven't got gadfly on my machine so 
apologies for any syntax errors, but try;

>>> connection=gadfly.gadfly()
>>> connection.startup("jnso", ".\\ndata")
>>> cursor.execute("SELECT st_id, first_name, last_name FROM students WHERE 
st_id='1' ")
>>> result=cursor.fetchone()
>>> result
( '1', 'Andy', 'Todd')

This is standard DB-API functionality. I highly recommend the 'stuff' at 
http://www.python.org/topics/database/ for general Python database 
information, and try googling for 'SQL' for resources on the lovely 
language. I wouldn't recommend anything that focusses too heavily on SQL 
Server or Oracle though as they have some non standard extensions which may 
come back and bite you on the bum if you try them in gadfly.

What the code does is establish a 'connection' to the database (I borrowed 
this from your original post so I'm presuming it works ;-), executes an 
arbitary SQL statement (I haven't used variable substitution but its really 
easy, honest) and then returns the results into 'result'. Note that the 
return of any cursor fetch is always a tuple, its up to you to muck about 
with that tuple and use its elements to populate an object if thats what 
you want to do. If you are changing data items and want to write these back 
to gadfly you need to do that via another SQL update statement - which I 
will leave as an exercise for the reader.

A final note is that in my example above I have used the 'fetchone' method 
and blithely carried on, this is because my query where predicate is 
reducing the result set based on the primary key so that I *know* that I'm 
going to return one and only one row. Most of the time you don't have this 
luxury so you will find the following construct *slightly* more useful;

for i in cursor.fetchall()
    <do something with i>

blimey-I-feel-like-I've-been-channeling-the-martellibot-ly y'rs,
Content free posts a speciality

More information about the Python-list mailing list