pythoncard-users

[Pythoncard-users] Re: creating SQL insert/Update stmts

[ralph h. <1st...@1I...>]

I am looking for an easy way to create the insert/update SQL statements and how to reference self.components.<TextField>.text objects.

Is there a programatic way to do a for fieldname in self.components.fields???

How would I handle date columns?

insstmt="INSERT INTO MEMBER ("
            for colname in self.rowsDict: insstmt=insstmt+colname+","
            insstmt=insstmt[1:len(insstmt)-1]+") VALUES ("
            ##for textvalue in self.components: 
            insstmt=insstmt+"'"+self.components.SSN.text+"',"
            insstmt=insstmt+"'"+self.components.FirstName.text+"',"
            ... (60+fields)




_____________________________________________________________
 
 
 
=======================================
    www.StrictlyEmail.com ...our name says it all!

RE: [Pythoncard-users] Re: creating SQL insert/Update stmts

[Kevin A. <al...@se...>]

Another pre morning coffee email, let's see if I do better this time <wink>

> From: ralph heimburger
>
> I am looking for an easy way to create the insert/update SQL
> statements and how to reference self.components.<TextField>.text objects.
>
> Is there a programatic way to do a for fieldname in
> self.components.fields???

self.components is a custom dictionary based on the UserDict class.

http://www.python.org/doc/current/lib/module-UserDict.html

Since components is a "mapping" type object that means the standard mapping
operations apply.

http://www.python.org/doc/current/lib/typesmapping.html

Using that info, we can run the widgets sample with the shell (-s on the
command-line) to experiment. There are actually several ways you could solve
your problem. If you just want to match components of type TextField and not
subclasses of TextField like TextArea, then the second comparison below
(c.__class__ is TextField) is probably what you want to use. You can also do
a string compare against the __class__.__name__ if you don't want to import
the class.

>>> from PythonCardPrototype.components.textfield import TextField
>>> for c in self.components.itervalues():
...     if isinstance(c, TextField):
...         print c.name
...
fldPasswordField
fldTextFieldNoBorder
fldTextArea
fldTextField
>>> for c in self.components.itervalues():
...     if c.__class__ is TextField:
...         print c.name
...
fldTextFieldNoBorder
fldTextField
>>> self.components.fldTextField.__class__.__name__
'TextField'
>>>

You can also give the fields you want to process special names or a special
prefix that you can compare against in a loop. You might also want to keep
your own list of what you want to process. For example, in the
openBackground event handler, create a list like:

    self.fieldNames = ['SSN', 'FirstName']

Then when you need to process those fields you can loop over the list. The
loop below would print out the text of the SSN and FirstName fields.

    for name in self.fieldNames:
        print self.components[name].text

> How would I handle date columns?

I don't know what you mean.

> insstmt="INSERT INTO MEMBER ("
>             for colname in self.rowsDict: insstmt=insstmt+colname+","
>             insstmt=insstmt[1:len(insstmt)-1]+") VALUES ("
>             ##for textvalue in self.components:
>             insstmt=insstmt+"'"+self.components.SSN.text+"',"
>             insstmt=insstmt+"'"+self.components.FirstName.text+"',"
>             ... (60+fields)

ka
p.s. Just in case anyone was wondering, yes components will probably become
a direct subclass of the built-in dict type instead of UserDict. When
PythonCard was started we were using Python 2.1. I'll add that to the 0.8 to
do list.

Re: [Pythoncard-users] Re: creating SQL insert/Update stmts

[Mike C. F. <mcf...@ro...>]

Kevin Altis wrote (quoting ralph heimburger):
...

>>insstmt="INSERT INTO MEMBER ("
>>            for colname in self.rowsDict: insstmt=insstmt+colname+","
>>            insstmt=insstmt[1:len(insstmt)-1]+") VALUES ("
>>            ##for textvalue in self.components:
>>            insstmt=insstmt+"'"+self.components.SSN.text+"',"
>>            insstmt=insstmt+"'"+self.components.FirstName.text+"',"
>>            ... (60+fields)
>>    
>>
Eeps, don't do this (just dump the text in with ' and ')!  Not only do 
you open yourself to all sorts of security problems, you're not getting 
any escaping of characters in the text, so it will fail if you have 
someone enter ain't can't or don't (which is also where the security 
problems start).  If you need something which generates SQL 
insert/update/refresh/delete code from a schema PyTable has code you can 
likely crib fairly easily:

http://cvs.sourceforge.net/viewcvs.py/pytable/table/pytable/dbrow.py?only_with_tag=HEAD&view=markup

The RowAction and it's various sub-classes being the ones in which 
you're likely to be interested.  Not sure how much of a schema you've 
got in "components", but the code there doesn't really do all *that* 
much with the schema other than figuring out the unique keys to use to 
specify the row.

At a minimum, use the database driver's escaping by passing in the query 
string with %(name)s or %s placeholders for each field and then pass the 
values in as extra arguments to execute.  (The SQLQuery class on which 
the RowAction is based actually does two-level escaping, in case you're 
wondering how the query is being composed in the dbrow module above, so 
the first substitution changes the query string and the second is done 
by the driver to get data-type escaping).

I know, I know, not quite on topic, but it just made me go "ouch" when I 
saw it...
Mike


_______________________________________
  Mike C. Fletcher
  Designer, VR Plumber, Coder
  http://members.rogers.com/mcfletch/

RE: [Pythoncard-users] Re: creating SQL insert/Update stmts

[Kevin A. <al...@se...>]

> From: Kevin Altis
>
> You can also give the fields you want to process special names or
> a special
> prefix that you can compare against in a loop. You might also want to keep
> your own list of what you want to process. For example, in the
> openBackground event handler, create a list like:
>
>     self.fieldNames = ['SSN', 'FirstName']
>
> Then when you need to process those fields you can loop over the list. The
> loop below would print out the text of the SSN and FirstName fields.
>
>     for name in self.fieldNames:
>         print self.components[name].text

I should have mentioned that every component has a 'userdata' string
attribute that you can use to store whatever string info you need; userdata
is never touched by the framework, it is just for your use. The attribute is
editable in the resourceEditor. That might be an even simpler place for you
to stick info that you can check when you iterate over the components.
Personally, I like the list of field names solution above.

ka