Help - Metadata variable passing parameters to DML queries

Steve Holden sholden at holdenweb.com
Thu Nov 9 11:19:32 EST 2000


[posted & mailed]

arflores1 at earthlink.net wrote:
> 
> Hi,
> 
> I'm a newbie in python needing some help to overcome my frustration
> in breaking my learning curve with Python.  I can't get a good grip
> on how to code the right syntax for passing variable to a Select,
> Insert, Update and Delete. Here's a sample code that I'm trying to
> solve its based on a database metadata concepts.
> 
> Here's the sample code
> 
> mColumn1 = 'PK_ID'
> mColumn2 = 'Name'
> mColumn3 = 'Phone'
> mFrom = 'Person'
> mWhere = 'Email'
> 
> import dbi
> import odbc
> 
> What is the correct Select syntax coding for this stuff based on my
> input variable defination.
> 
> mCursor.execute('Select %(mColumn1)s, %(mColumn2)s, %(mColumn3)s\
>                    From %(mFrom)s\
>                    Where %(mWhere)s')
> 
> Thanks
> 
> Alex

A previous poster has shown you the way to insert simple variables.
The syntax you have adopted inside the mCursor.execute() call implies
that you have heard, or remember reading somewhere, about using a
dictionary for such substitution.  If you wanted that statement to
work that way then you would have had to load the substitution values
into a dictionary, as in

myDict = {"mColumn1":'PK_ID', "mColumn2":'Name', "mColumn3":'Phone',
		"mFrom":'Person', "mWhere" = "Email='sholden at bellatlantic.net'"}

[note: you will also need the "where" item to be a valid SQL condition
rather than just a fieldname, although your example would work if Email
were Boolean; I've made that change, and you can see the value of the
advice about using double rather tahn single quotes around your Python
strings -- that way you can use single quotes for your SQL strings.  I
have ignored the complexities you might hit trying to use strings which
contain single quotes: that's just a detail for now].

Then your statement could have read

mCursor.execute('Select %(mColumn1)s, %(mColumn2)s, %(mColumn3)s\
                    From %(mFrom)s\
                    Where %(mWhere)s' % myDict)

Then Python will replace %(mColumn1)s withthe string value of

	myDict["mColumn1"]

and so on.  There's a package called dtuple, which I only just discovered
myself, which is REALLY USEFUL.  It allows you to transform a tuple or
list as returned from executing a SELECT into an object whose fields can
be accessed by numeric offset (like a list), by fieldname (like a
dictionary) or as attributes (like an object).

I downloaded it from

  http://www.lyra.org/greg/python/dtuple.py

It takes a little getting used to, so you may wish to defer its adoption,
but when you do you'll be glad you did!

regards
 Steve
-- 
Helping people meet their information needs with training and technology.
703 967 0887      sholden at bellatlantic.net      http://www.holdenweb.com/





More information about the Python-list mailing list