[Tutor] MySQL mangling & list subscription question

[Brad Chandler]

This is something I use to connect to a MySQL server running on my windows
box.  It should work exactly the same on linux except for the import and
database connection lines.  I don't have an example of that at the moment.

import dbi, odbc

mydb=odbc.odbc("mysql/brad/homer") #this function takes an odbc data source,
name, pass
cursor = mydb.cursor()
cursor.execute("select * from mytable")
result = cursor.fetchall()

The "result" variable will contain an array of all the rows and columns
returned from your query. If it's just one item, you should be able to
assign to a variable like this:

myitem = result[0][0]

If it returns multiple rows and columns you'll have to loop through the
"result" variable, something like this:

for row in result:
    for cell in row:
        print cell

The following code will put your result into a dictionary, using the column
names as the keys. This slows things down a bit, but it makes my programs a
lot easier to read when I can access my fields by the column name rather
than their position.

result = cursor.fetchall()
numrows = len(result)
rowarray = []
for x in range(0,numrows):
    numfields = len(result[x])
    rowdict = {}
    for y in range(0,numfields):
        rowdict[cursor.description[y][0]] = result[x][y]

    rowarray.append(rowdict)

I guess this would be an array of dictionaries, if your result returns more
than one row. You can then access the fields like this:

for x in rowarray:
    print x.get('colname','notfound')

I hope some of this helps.


----- Original Message -----
From: "Rob Andrews" <rob@jam.rr.com>
To: "Tutor@Python. Org" <tutor@python.org>
Sent: Friday, August 03, 2001 2:56 PM
Subject: [Tutor] MySQL mangling & list subscription question


> An associate recently stopped heckling Python for just long enough to
> look at some Python code and has now fallen insanely in love with the
> language. (He's a PHP man, historically.)
>
> However, he told me he's been unable to subscribe to the Tutor list
> because of the problems with www.python.org and asked me to forward this
> query to the list.
>
> #
> # 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.
> #
>
> For a little context, he is trying to pull data from a MySQL database
> and assign it to a variable in Python. I can do a fair bit of Python and
> a little SQL, but I've yet to mix the two. Can anyone point to some
> current resources or show a quick example I can pass on?
>
> Thanks,
> Rob
>
> Your one-stop shop for newbie source code!
> Useless Python: http://www.lowerstandard.com/python/
>
>
> _______________________________________________
> Tutor maillist  -  Tutor@python.org
> http://mail.python.org/mailman/listinfo/tutor