sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Inheritance

[Oleg B. <ph...@ph...>]

On Fri, Dec 24, 2004 at 11:26:56PM +0200, Ksenia Marasanova wrote:
> Okay, I think I found it. Please try to run the test script, and I
> hope you'll see that the extra statement (person.id = author.id) is
> not added...

   It is not. :(

> I changed the name of Employee class to Author. This is the most weird
> part of it... if you change it back to Employee than it works!! Does
> it have to do something with Python / Postgres reserved keywords?!

   Yes, I see it too. Wierd! I will look into it...

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Inheritance

[Ksenia M. <kse...@gm...>]

Hi Oleg,

Okay, I think I found it. Please try to run the test script, and I
hope you'll see that the extra statement (person.id = author.id) is
not added...
I changed the name of Employee class to Author. This is the most weird
part of it... if you change it back to Employee than it works!! Does
it have to do something with Python / Postgres reserved keywords?!

Cheers,
-- 
Ksenia

Re: [SQLObject] Default value

[Oleg B. <ph...@ma...>]

On Fri, Dec 24, 2004 at 05:33:54PM +0800, Hong Yuan wrote:
> I have a field in a PostgreSQL database table for which I would like the 
> database to calculate the default value.

   SQLObject cannot do it yet.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Default value

[Hong Y. <ob...@21...>]

Hi,

I have a field in a PostgreSQL database table for which I would like the 
database to calculate the default value. The field itself is declared as 
NOT NULL DEFAULT (some function)

When defining the SQLObject class, if I use

  field = Col()

and then try to create an item I get the error that the field must have 
a value.

If I use instead:

  field = Col(default=None)

I am not required to give a value but it is translated into INSERT ... 
SET FIELD VALUE NULL, which is not allowed by the table definition.

How can I make SQLObject create an INSERT statement without the field 
'FIELD' so that the database will take over the calculation of the 
default value? It wouldn't make sense to duplicate the database logic of 
default value calculation in Python to be used in Col(default = ...)

Your help is appreciated.

Hong Yuan

Re: [SQLObject] Inheritance

[Oleg B. <ph...@ma...>]

On Fri, Dec 24, 2004 at 01:13:57AM +0200, Ksenia Marasanova wrote:
> >    Please try the attached patch and run
> > Employee.select(orderBy=Person.q.firstName)
> >    Does it help?
> 
> Thank you, for the test case it does produce a correct query: 
> 
> SELECT employee.id, employee.position FROM employee, person WHERE  (1
> = 1 AND (employee.id = person.id)) ORDER BY person.first_name

   I've committed the patch at revision 494.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Inheritance

[Ksenia M. <kse...@gm...>]

> 
>    Please try the attached patch and run
> Employee.select(orderBy=Person.q.firstName)
>    Does it help?


Thank you, for the test case it does produce a correct query: 

SELECT employee.id, employee.position FROM employee, person WHERE  (1
= 1 AND (employee.id = person.id)) ORDER BY person.first_name

However, it doesn't have effect on another database. There must be
some class / table / property combination in it that SQLObject doesn't
like. I'll try to locate the problem by starting with very basic
tables and adding the rest piece by piece...

Thanks!
-- 
Ksenia

Re: [SQLObject] Newbie prob - Class generation

[Oleg B. <ph...@ma...>]

On Thu, Dec 23, 2004 at 11:02:05AM -0500, Dot Not wrote:
>     Comments = StringCol(length=1000)
> 
> But I'm getting
> "Client() did not get expected keyword argument Comments"
> back from Python.

   Comments = StringCol(length=1000, default=None)

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Newbie prob - Class generation

[Dot N. <do...@do...>]

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.811 / Virus Database: 552 - Release Date: 12/13/2004

Re: [SQLObject] Generic "find" & findOne method

[Oleg B. <ph...@ma...>]

On Wed, Dec 22, 2004 at 04:29:07PM -0200, Carlos Ribeiro wrote:
> It turns out that Ian's time machine is almost as good as Guido's one.
> There *is* a find() method on the current SQLObject; it's called
> selectBy and it's not documented anywhere else. I only found it today
> while looking for something else. BTW, I admit being ashamed about it
> :-(

   Oops, me too. I seldom use it, and have forgotten about it.

> Now, just out of curiosity: is selectBy undocumented just for lack of
> time, or is it because it's not recommended?

   There is not much documentation for SQLObject, alas! :(

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Generic "find" & findOne method

[Carlos R. <car...@gm...>]

Well,

It turns out that Ian's time machine is almost as good as Guido's one.
There *is* a find() method on the current SQLObject; it's called
selectBy and it's not documented anywhere else. I only found it today
while looking for something else. BTW, I admit being ashamed about it
:-(

Now, just out of curiosity: is selectBy undocumented just for lack of
time, or is it because it's not recommended?

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: car...@gm...
mail: car...@ya...

Re: [SQLObject] DatabaseIndex

[Oleg B. <ph...@ma...>]

On Wed, Dec 22, 2004 at 12:25:19PM -0500, Duncan McGreggor wrote:
> Hey Ian,

   I am not Ian, can I answer? :)

> I hope I'm not being stupidly blind... but I can't find DatabaseIndex 
> anywhere. I read about it here:
> 
>   http://sqlobject.org/docs/SQLObject.html#indexes
> 
> but couldn't use it, didn't know where to import it from, and grep'ed 
> the source code to no avail. I also searched the SQLObject mail list 
> archives, which still turned up nothing.
> 
> I don't *think* I've typo'ed it... got any pointers? In particular, I 
> am trying to create an index across multiple columns...

http://svn.colorstudy.com/trunk/SQLObject/sqlobject/index.py

   Checkout it using Subversion:

svn co svn://colorstudy.com/trunk/SQLObject

   Or wait for 0.6.1.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] DatabaseIndex

[Duncan M. <py...@ad...>]

Hey Ian,

I hope I'm not being stupidly blind... but I can't find DatabaseIndex 
anywhere. I read about it here:

   http://sqlobject.org/docs/SQLObject.html#indexes

but couldn't use it, didn't know where to import it from, and grep'ed 
the source code to no avail. I also searched the SQLObject mail list 
archives, which still turned up nothing.

I don't *think* I've typo'ed it... got any pointers? In particular, I 
am trying to create an index across multiple columns...

Thanks!

Duncan

Re: [SQLObject] Generic "find" & findOne method

[<pk...@gm...>]

Oleg Broytmann wrote:

>On Tue, Dec 21, 2004 at 02:43:38PM -0200, Carlos Ribeiro wrote:
>  
>
>>person.find({'name':'...', 'address':'...'})
>>    
>>
>
>   What is the difference between
>person.find({'name':'...', 'address':'...'})
>   and
>person.select(AND(person.q.name == '...', person.q.address == '...'))
>   ??
>  
>
I really like the interface of the first version. IMO it's much more 
"pythonic" and you can easily write generic (i.e not bound to colum 
names) queries without hardcoding any column names in the body of the 
method. ATM I can't think of a way to do this using the select(AND(... 
approach -- besides, it looks  very sqlish...

just a thought
 Paul

Re: [SQLObject] Generic "find" & findOne method

[Oleg B. <ph...@ma...>]

On Tue, Dec 21, 2004 at 07:39:58PM -0200, Carlos Ribeiro wrote:
> At this point, please bear in mind that I am just *locating* a record,
> not filling it with data. So most of the security issues that you
> point out are not really valid.

   There is a huge security risk even in locating data. Does the term
"SQL injection" appeal to you?!

> While I respect you opinion, I still think that the proposed function
> is useful enough. However, I think that I may not have presented it
> clearly enough. I'll try to write a less verbose (and cleaner)
> explanation and present it again.

   Please do.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Defer creating/updating objects in the database

[Oleg B. <ph...@ma...>]

On Wed, Dec 22, 2004 at 03:27:53PM +1300, Peter Butler wrote:
> Thanks very much for the advice, I thought there must be a nicer way to
> do it.  I ended up using apply() with the class name and the keyword
> dictionary.

   You only need apply() for very old Python versions. In newer Python
(and SQLObject requires such a new Python) you can just use the
following simple syntax:

   args = [...]
   keywords = {...}
   function(*args, **keywords)

> I tried _lazyUpdate = True and it does half of what I need (i.e. defers
> updates to the database until syncUpdate() is called).  Is there a
> similar mechanism for deferring inserts?

   There is no, because when you create an object, it have to know its
id, so SQLOBject does INSERT and asks for new id.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Defer creating/updating objects in the database

[Peter B. <pet...@14...>]

>    Wow! That's a long and a very wrong way of doing things. This is
> Python! Do not evaluate strings - just manipulate objects.

Thanks very much for the advice, I thought there must be a nicer way to
do it.  I ended up using apply() with the class name and the keyword
dictionary.

I've put the changed code at http://www.clever.co.nz/FormValues.py if
you're interested.

I tried _lazyUpdate = True and it does half of what I need (i.e. defers
updates to the database until syncUpdate() is called).  Is there a
similar mechanism for deferring inserts?  I hope so, then I can get rid
of this ugly code for good!

Cheers

Peter

Re: [SQLObject] SOLVED RelatedJoins in 0.6

[Sam N. <sa...@se...>]

Sam Nilsson wrote:
> Hello,
> 
> I am new to sqlobject, and i'm finding it to be a truly wonderful layer
> and model. I have had some problems with RelatedJoin attributes and I
> have seen some talk of that recently on the list. The suggestion to
> explicitly define joinColumn, otherColumn, and joinMethodName solved a
> few different problems that I was having.
> 
> Unsolved Problems - I have pasted my 'Group' class below. If I do a
> dir(Group), I see attributes for 'admins' 'users' and more. I also see
> the methods 'addUser' and 'removeUser', *but* there are no corresponding
> 'addAdmin' and 'removeAdmin' methods!
> 
> I have gathered from the list that the current revision in subversion
> solves some of these problems but may not solve all of them. I am not
> familiar with the state of development, so I want to ask, should I
> update to the newest revision? Is it more stable and less buggy than v.6?
> 
> Can anyone help with info about my problem? If the consensus is to
> update to a newer sqlobject, then I will do so and report back.
> 
> Thanks for any help!
> - Sam Nilsson
> 
> 
> from sqlobject import *
> from Store import conn
> 
> class Group(SQLObject):
> 
>         _table = "group_table"
>         _connection = conn
> 
>         name = StringCol()
>         description = StringCol()
> 
>         users = RelatedJoin("User",
> intermediateTable='group_table_user', joinColumn='group_table_id',
> otherColumn='user_id', joinMethodName='users')
>         admins = RelatedJoin("User",
> intermediateTable='group_table_admin', joinColumn='group_table_id',
> otherColumn='user_id', joinMethodName='admins')
>         categories = MultipleJoin("Category", joinColumn='group_id',
> joinMethodName='categories')
> 

I don't know why nobody wrote back to me. Maybe because my answer was 
already in the online documentation! I was missing the totally important

addRemoveName='alternateName'

My new and working Group class looks like this now:

from sqlobject import *
from Store import conn

class Group(SQLObject):

         _table = "group_table"
         _connection = conn

         name = StringCol()
         description = StringCol()

         users = RelatedJoin("User",
		intermediateTable='group_table_user',
		joinColumn='group_table_id',
		otherColumn='user_id',
		joinMethodName='users')

         admins = RelatedJoin("User",
		intermediateTable='group_table_admin',
		joinColumn='group_table_id',
		otherColumn='user_id',
		joinMethodName='admins',
		addRemoveName='Admin')
		#### addRemoveName above allows sqlobject to create
		#### addAdmin() and removeAdmin() methods

         categories = MultipleJoin("Category",
		joinColumn='group_id',
		joinMethodName='categories')

Re: [SQLObject] Generic "find" & findOne method

[Carlos R. <car...@gm...>]

On Tue, 21 Dec 2004 23:33:13 +0300, Oleg Broytmann <ph...@ma...> wrote:
> On Tue, Dec 21, 2004 at 06:12:39PM -0200, Carlos Ribeiro wrote:
> > Let's assume that you have the result of a web form. You can simply
> > feed the form data to findOne as an argument. One possible example
> > (using a slightly different and improved syntax):
> >
> > person.findOne(**form.getData())
> 
>   Are you passing form data to SQL without validation?! Wow!! Isn't
> there a security risk?

Don't worry, validation is being done. That's the problem of
simplifications -- the example was intentionally simple just to
illustrate the use case. As I've told you, I'm trying to keep modules
as decoupled as possible. I'm also aggressively using TDD, and this
approach makes writing unit tests really easy. Data validity is
checked at *every* step. The form itself does some validation, using
client-side Javascript code. When the HTTP request is handled, the
form data is also passed to a validation object, using the same
intermediate representation for a slightly stricter validation (it
also avoids the problem that would occur if data was sent directly
without passing by the client-side validation; for example, if urllib
were used to bypass it). Also, code would only get that far after
passing several security checks (login, session management, etc.). In
the end, it's something along these lines (pseudo code, untested, not
optimized):

# builds a validator object using the data fetched from the form
validator = MyValidator(form.getData())
# warning and errors are lists of objects; if empty, everything is ok
if not validator.warnings and not validator.errors:
    person.findOne(**form.getData())

At this point, please bear in mind that I am just *locating* a record,
not filling it with data. So most of the security issues that you
point out are not really valid. Also, please note that the code on the
findOne() method iterates over the keys, and only reads values if the
key is a valid column name, which avoids the risk of a security
exploit.

>   Well, I am against such addition to the SQLObject. It is too
> specific. You can easily do it in your own project by creating a parent
> class like this:
> 
> class Finding(SQLObject):
>   def findOne(self, dict):
>      ...
> 
> and use it as the base for all your tables:
> 
> class Person(Finding):
>   ...

While I respect you opinion, I still think that the proposed function
is useful enough. However, I think that I may not have presented it
clearly enough. I'll try to write a less verbose (and cleaner)
explanation and present it again.

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: car...@gm...
mail: car...@ya...

Re: [SQLObject] Generic "find" & findOne method

[Oleg B. <ph...@ma...>]

On Tue, Dec 21, 2004 at 06:12:39PM -0200, Carlos Ribeiro wrote:
> Let's assume that you have the result of a web form. You can simply
> feed the form data to findOne as an argument. One possible example
> (using a slightly different and improved syntax):
> 
> person.findOne(**form.getData())

   Are you passing form data to SQL without validation?! Wow!! Isn't
there a security risk?

   Well, I am against such addition to the SQLObject. It is too
specific. You can easily do it in your own project by creating a parent
class like this:

class Finding(SQLObject):
   def findOne(self, dict):
      ...

and use it as the base for all your tables:

class Person(Finding):
   ...

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Generic "find" & findOne method

[Carlos R. <car...@gm...>]

I'll reply both messages at once, if you don't mind...

On Tue, 21 Dec 2004 19:54:12 +0300, Oleg Broytmann <ph...@ma...> wrote:
> On Tue, Dec 21, 2004 at 02:43:38PM -0200, Carlos Ribeiro wrote:
> > findOne returns a single record. It raises an exception if no item is
> > found. If more than one item is found, it returns the first one
> 
>   Which one? Just random? Is there "orderBy" in findOne?

Random. The use case for findOne assumes that only one record should
be found, anyway. I just don't want to complicate the code or raise an
exception if there is more than one record in the result set. More on
this later.

> > If this code is useful, I can contribute a patch back to SQLObject. It
> > involves simple modifications on dbconnection.py and main.py.
> 
>   Why dbconnection? Seems like a trivial addition in main.py - just
> process the dictionary to generate appropriate WHERE clause, and call
> self.select().

For what I could see, it's discouraged to write SQL code inside the
SQLObject class (at main.py). Also, the select & get methods already
call stuff that is on dbconnection.py.

> > person.find({'name':'...', 'address':'...'})
> 
>   What is the difference between
> person.find({'name':'...', 'address':'...'})
>   and
> person.select(AND(person.q.name == '...', person.q.address == '...'))
>   ???

Let's assume that you have the result of a web form. You can simply
feed the form data to findOne as an argument. One possible example
(using a slightly different and improved syntax):

person.findOne(**form.getData())

There is a good justification for the entire idea. I'm developing a
web application, and in the process, I'm trying to maintain the tiers
as decoupled as possible. All communication between the interface code
and the application code is done using data-only classes, that support
both attribute-style and dict-style access. These intermediate data
structures can also be serialized using something similar to JSON (a
Javascript notation for data exchange). The advantage of this method
is that I can automatically test the application code without the need
to invoke the interface code, just by feeding some hard-coded data
structures to it.

The use case for findOne follows as a consequence. Web forms return a
bunch of data, that are the keys to locate a unique record in the
database. Instead of calling a table.byField() method (using an
alternate key), it's easier and more dynamic just to pass the fields &
values to a generic findOne method. It also makes a little bit easier
to handle the case of composite unique keys, which aren't support by
SQLObject yet.

** As a curiosity, Delphi had such a method (I think it was called
locate) as part of its database API.

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: car...@gm...
mail: car...@ya...

Re: [SQLObject] Generic "find" & findOne method

[Oleg B. <ph...@ma...>]

On Tue, Dec 21, 2004 at 02:43:38PM -0200, Carlos Ribeiro wrote:
> person.find({'name':'...', 'address':'...'})

   What is the difference between
person.find({'name':'...', 'address':'...'})
   and
person.select(AND(person.q.name == '...', person.q.address == '...'))
   ???

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Generic "find" & findOne method

[Oleg B. <ph...@ma...>]

On Tue, Dec 21, 2004 at 02:43:38PM -0200, Carlos Ribeiro wrote:
> findOne returns a single record. It raises an exception if no item is
> found. If more than one item is found, it returns the first one

   Which one? Just random? Is there "orderBy" in findOne?

> If this code is useful, I can contribute a patch back to SQLObject. It
> involves simple modifications on dbconnection.py and main.py.

   Why dbconnection? Seems like a trivial addition in main.py - just
process the dictionary to generate appropriate WHERE clause, and call
self.select().

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Generic "find" & findOne method

[Carlos R. <car...@gm...>]

Hi,

I'm writing a class method that finds a row in a table using arbitrary
column names & values, as in:

person.find({'name':'...', 'address':'...'})

find works like a select, but it takes a dictionary with column names
and desired values. It search for records by AND'ing all clauses, and
returns a list of rows.

person.findOne({'name':'...', 'address':'...'})

findOne returns a single record. It raises an exception if no item is
found. If more than one item is found, it returns the first one, and
no exception is rased.

The goal here is not efficiency, but flexibility. When writing code
for web applications it's useful to have this generic query feature,
and these methods are more convenient than building a select on the
fly.

If this code is useful, I can contribute a patch back to SQLObject. It
involves simple modifications on dbconnection.py and main.py.

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: car...@gm...
mail: car...@ya...

[SQLObject] RelatedJoins in 0.6

[Sam N. <sa...@se...>]

Hello,

I am new to sqlobject, and i'm finding it to be a truly wonderful layer
and model. I have had some problems with RelatedJoin attributes and I
have seen some talk of that recently on the list. The suggestion to
explicitly define joinColumn, otherColumn, and joinMethodName solved a
few different problems that I was having.

Unsolved Problems - I have pasted my 'Group' class below. If I do a
dir(Group), I see attributes for 'admins' 'users' and more. I also see
the methods 'addUser' and 'removeUser', *but* there are no corresponding
'addAdmin' and 'removeAdmin' methods!

I have gathered from the list that the current revision in subversion
solves some of these problems but may not solve all of them. I am not
familiar with the state of development, so I want to ask, should I
update to the newest revision? Is it more stable and less buggy than v.6?

Can anyone help with info about my problem? If the consensus is to
update to a newer sqlobject, then I will do so and report back.

Thanks for any help!
- Sam Nilsson


from sqlobject import *
from Store import conn

class Group(SQLObject):

         _table = "group_table"
         _connection = conn

         name = StringCol()
         description = StringCol()

         users = RelatedJoin("User",
intermediateTable='group_table_user', joinColumn='group_table_id',
otherColumn='user_id', joinMethodName='users')
         admins = RelatedJoin("User",
intermediateTable='group_table_admin', joinColumn='group_table_id',
otherColumn='user_id', joinMethodName='admins')
         categories = MultipleJoin("Category", joinColumn='group_id',
joinMethodName='categories')

Re: [SQLObject] Validating data

[Oleg B. <ph...@ma...>]

On Mon, Dec 20, 2004 at 04:20:06PM +0100, Hendrik Mans wrote:
> what would be the "cleanest" way of doing data validation for
> new/updated SQLObjects?

   There is a validation framework already. Look at validators.py.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.