Re: [SQLObject] Trapping SQL Errors

[Ben Beuchler <insyte@em...>]

On Thu, Jun 24, 2004 at 05:29:54PM -0400, CLIFFORD ILKAY wrote:

> I am trying to trap a SQL error caused by attempting to INSERT a duplicate 
> unique key. Here is what I have tried.
> 
> #############
>         def writeContent(self):
>                 pr = self.writeln
>                 newForm = self.form
>                 if newForm.isSuccessful():
>                         theTelnumType = 
> TelnumType.byDescription(newForm.numType.value())
>                         if theTelnumType:
>                                 pr('''<p>There is already a Telnum Type = 
> '%s'</p>''' % newForm.numType.value())
>                         else: # not a duplicate so save
>                                 newTelnumType = TelnumType (
>                                         description = 
>                                         newForm.numType.value(),
>                                         modUserId = 'cilkay'
>                                         )
>                                 pr('''<p>Successfully saved Telnum Type = 
> '%s'</p>''' % newForm.numType.value())
>                 else:
> #############
> 
> So, if I already have a row in telnum_type where description = 'Home', no 
> problem, the code above will print out: "There is already a Telnum Type = 
> 'Home'". However, if there is no Telnum Type = 'Home', I now get a 
> SQLObjectNotFound error raised by SQLObject. If I just try to insert 
> without doing a test for a duplicate value, I of course get the usual SQL 
> error about violating a unique key constraint. How can I trap SQL errors, 
> not just unique key constraint violations, so that I can provide meaningful 
> messages to end users?

You want to catch the exception (http://www.python.org/doc/current/tut/node10.html).
You could either leave your test in place and catch the SQLObjectNotFound
exception or skip the test and catch the psycopg.ProgrammingError when you
try to insert the duplicate key.

###########
try:
   TelnumType.byDescription(newForm.numType.value())
except SQLObjectNotFound:
   TelnumType.new(description = blah, ... )
   pr('''<p>Successfully did some stuff</p>''')
###########

I don't like that approach, as it is conceptually ugly (the exception
would be the common case instead of, well, the exception) and you could
accomplish the same thing more efficiently by eliminating the test and
just catching the exception if you try to insert a duplicate.

###########
try:
   TelnumType.new(description = blah, ... )
except psycopg.ProgrammingError:
   pr('''<p>There is already a Telnum Type = '%s'</p>''' % newForm.numType.value())
###########

Exceptions rock.

-Ben

-- 
Ben Beuchler                                           There is no spoon.
insyte@...                                            -- The Matrix

[SQLObject] Trapping SQL Errors

[CLIFFORD ILKAY <clifford_ilkay@di...>]

Hi,

I am trying to trap a SQL error caused by attempting to INSERT a duplicate 
unique key. Here is what I have tried.

#############
         def writeContent(self):
                 pr = self.writeln
                 newForm = self.form
                 if newForm.isSuccessful():
                         theTelnumType = 
TelnumType.byDescription(newForm.numType.value())
                         if theTelnumType:
                                 pr('''<p>There is already a Telnum Type = 
'%s'</p>''' % newForm.numType.value())
                         else: # not a duplicate so save
                                 newTelnumType = TelnumType (
                                         description = newForm.numType.value(),
                                         modUserId = 'cilkay'
                                         )
                                 pr('''<p>Successfully saved Telnum Type = 
'%s'</p>''' % newForm.numType.value())
                 else:
#############

So, if I already have a row in telnum_type where description = 'Home', no 
problem, the code above will print out: "There is already a Telnum Type = 
'Home'". However, if there is no Telnum Type = 'Home', I now get a 
SQLObjectNotFound error raised by SQLObject. If I just try to insert 
without doing a test for a duplicate value, I of course get the usual SQL 
error about violating a unique key constraint. How can I trap SQL errors, 
not just unique key constraint violations, so that I can provide meaningful 
messages to end users?

Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, Ontario
Canada M4N 3P6

Tel: 416-410-3326  

Re: [SQLObject] Re: SQLObject

[Ian Bicking <ianb@co...>]

Ivo van der Wijk wrote:
> Ian Bicking wrote:
> 
>> Hi.  Sorry about not being involved the last couple weeks.  It's been 
>> hard to keep up with SQLObject, mostly because I'm not actually using 
>> it myself at the moment.  I'm trying to change that, but I have a hard 
>> time keeping motivated when I don't have any direct needs.
>>
>> But I don't mind if people hassle me about things like the wiki or 
>> repository.  Those are both back up.  Please email me directly if 
>> there are things that need to be done.  I'll try to catch up, though 
>> I'm about to go out of town for a week and I won't be able to look at 
>> anything before then.
>>
> 
> If anyone sends improvements/patches, would you still be available 
> (timely) to check/add them? Or would that have to wait as well?
> 
> I now onderstand you don't have the time / motivation to add my 
> suggestions - I could give it a try myself.

Several people on the list have commit access, and I'm willing to give 
access to other people as well.  People should feel particularly free to 
develop in a branch on the repository if they have ideas they want to 
experiment with or share.  Anyone who is interested should email me 
off-list.

   Ian

[SQLObject] How does SQLObject deal with SQL injection into queries?

[CLIFFORD ILKAY <clifford_ilkay@di...>]

Hi,

There has been an interesting thread over on the WebWare discussion list 
about how to prevent SQL injection on database queries. The subject line is 
"FormKit Validator question". How does SQLObject deal with this?

Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, Ontario
Canada M4N 3P6

Tel: 416-410-3326 

[SQLObject] subscrive

[Roberto Melo Cavalcante <zerok@se...>]

[SQLObject] Transactions and RelatedJoins.

[Ben Beuchler <insyte@em...>]

I'm retrofitting some code to use transactions and have run into a snag:
How do you pass a transaction to a table that does not have a class?  Ex:

class Machine(SQLObject):
    _cacheValue = False
    ....
    os = RelatedJoin('OsT', intermediateTable='machine_os_assoc',
                      joinColumn='machine_id', otherColumn='os_id',
                      addRemoveName='Os')

class Os(SQLObject):
    _cacheValue = False
    ....
    machines = RelatedJoin('MachineT',
                           intermediateTable='machine_os_assoc',
                           joinColumn='os_id', otherColumn='machine_id')

When I instantiate Machine, I pass "connection=trans", where "trans" is a
transaction instance.  For Machine, trans.rollback(), trans.commit(), and
trans.begin() all work as expected.  However, if I call the .addOs()
method of my Machine instance, it uses the normal connection, *not* the
transaction.  What is the correct way to make this work?

I can't just change the global __connection__ instance to be a
transaction, as that would break too much existing code...

Thanks!

-Ben

-- 
Ben Beuchler                                           There is no spoon.
insyte@...                                            -- The Matrix

[SQLObject] Re: suggestion: sqlbuilder magic query object accept foreign key

[Karl Chen <quarl@no...>]

I ended up writing this for myself; if anyone is interested here's
the patch.  Now you can do:
  PhoneNumber.selectBy(user=myuser) 
in addition to
  PhoneNumber.selectBy(userID=myuser.id)



Index: dbconnection.py
===================================================================
--- dbconnection.py	(revision 1186)
+++ dbconnection.py	(working copy)
@@ -418,12 +418,20 @@
                     self.sqlrepr(firstValue),
                     self.sqlrepr(secondValue)))
 
+    def _SO_kwpair2dbpair(self, soClass, key, value):
+        dbcolumn = soClass._SO_columnDict.get(key)
+        if dbcolumn:
+            return (dbcolumn.dbName, self.sqlrepr(value))
+        dbcolumnid = soClass._SO_columnDict.get(key+'ID')
+        if dbcolumnid:
+            return (dbcolumnid.dbName, self.sqlrepr(value.id))
+        raise Exception, 'Invalid keyword "%s" to selectBy' %(key)
+
     def _SO_columnClause(self, soClass, kw):
         return ' AND '.join(['%s = %s' %
-                         (soClass._SO_columnDict[key].dbName,
-                          self.sqlrepr(value))
-                         for key, value
-                         in kw.items()])
+                             self._SO_kwpair2dbpair(soClass, key, value)
+                             for key, value
+                             in kw.items()])
 
     def sqlrepr(self, v):
         return sqlrepr(v, self.dbName)


-- 
Karl 2004-06-24 04:51

Re: [SQLObject] Re: SQLObject

[Ivo van der Wijk <ivo@am...>]

Ian Bicking wrote:
> Hi.  Sorry about not being involved the last couple weeks.  It's been 
> hard to keep up with SQLObject, mostly because I'm not actually using it 
> myself at the moment.  I'm trying to change that, but I have a hard time 
> keeping motivated when I don't have any direct needs.
> 
> But I don't mind if people hassle me about things like the wiki or 
> repository.  Those are both back up.  Please email me directly if there 
> are things that need to be done.  I'll try to catch up, though I'm about 
> to go out of town for a week and I won't be able to look at anything 
> before then.
> 

If anyone sends improvements/patches, would you still be available (timely) to 
check/add them? Or would that have to wait as well?

I now onderstand you don't have the time / motivation to add my suggestions - I 
could give it a try myself.

Cheers

	Ivo

-- 
Drs. I.R. van der Wijk                                      -=-
Korte Leidsedwarsstraat 12                                 Amaze
1017 RC Amsterdam, NL                                       -=-
T +31-20-4688336         F +31-20-4688337       Zope/Plone/Content Management
W http://www.amaze.nl    E info@...           Open Source Solutions
W http://vanderwijk.info E ivo@...                 Consultancy
PGP http://vanderwijk.info/pgp

[SQLObject] Re: SQLObject

[Ian Bicking <ianb@co...>]

Hi.  Sorry about not being involved the last couple weeks.  It's been 
hard to keep up with SQLObject, mostly because I'm not actually using 
it myself at the moment.  I'm trying to change that, but I have a hard 
time keeping motivated when I don't have any direct needs.

But I don't mind if people hassle me about things like the wiki or 
repository.  Those are both back up.  Please email me directly if there 
are things that need to be done.  I'll try to catch up, though I'm 
about to go out of town for a week and I won't be able to look at 
anything before then.


On Jun 23, 2004, at 12:06 PM, Justus Pendleton wrote:
> Just wondering if you're still maintaining SQLObject?  The subversion
> respository has been down for a while and the wiki for even longer.  I
> haven't seen you post anything on the mailing list in the past couple
> of weeks, either.