a gadfly SQL question --- table creation

[Joe Potter]

On Thu, 05 Jul 2001 05:26:39 GMT, andy_todd at spam.free.yahoo.com (Andy Todd) wrote:

>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>: 
>>>
<snip>
>>
>>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;
>

But easy for you, I see. :-)

>
>"""
>>>> 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.
>

I am reading a book on SQL that covers MySQL. 

But, in this one post you covered my main problem. I could not find where the magic
words like "cursor.fetchall()" were coming from. I was reduced to looking at the
Gadfly code and making a list of magic code.

But, with DB-API spec I see where they are coming from.

All praise to Andy on this fine morning.

Thanks ever so much.




>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,
>Andy


Thanks again.

Regards, Joe