sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] bug with UnicodeCol

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

On Wed, Dec 29, 2004 at 10:19:17AM +0200, Max Ischenko wrote:
> dbusers = User.selectBy(isGuest=False, email=email)
> n = dbusers.count()

   Try dbusers = User.selectBy(isGuest=False, email=email.encode(dbEncoding))

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

Re: [SQLObject] Re: Preparing for 0.6.1

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

On Wed, Dec 29, 2004 at 08:02:15PM -0500, Brian Beck wrote:
> Starting with version 2.0 (a while ago), SQLite has supported 
> transactions, so maybe the documentation can be updated, and code if 
> necessary?  Doing some timings with insert/update and commit with 
> SQLObject seems to suggest that even though SQLite supports 
> transactions, SQLObject doesn't really take advantage of them.  Is this 
> the case?

   sqlobject/sqlite/sqliteconnection.py

      supportTransactions = True

   Looks ok. What is wrong in your case?

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

[SQLObject] generic _set method

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

Hi list,

im sure I'm overlooking something simple but I haven't found a way to 
define a way to overide the setter and not hardcoding the columns name. 
I can do something like:

def _setWorker(self, value):
   blabla
   self._SO_set_Worker(value)

but this applies to the Worker column only and I have to perform the 
same operation for all columns and rather like to avoid writing the same 
code for each column. Using _SO_setValue() seemed possible at first but 
one problem remains: How do I access the name of the column in the 
methods body? Furthermore I have no idea if messing with _SO_setValue() 
is a sane approach. Any ideas?

thanks
  Paul

[SQLObject] Re: Preparing for 0.6.1

[Brian B. <ex...@gm...>]

Ian Bicking wrote:
> Is there anything people know of that should be resolved before 0.6.1? 
> As far as I can remember, it's just been a bunch of bug fixes since 0.6 
> (most from Oleg, thanks!)

Starting with version 2.0 (a while ago), SQLite has supported 
transactions, so maybe the documentation can be updated, and code if 
necessary?  Doing some timings with insert/update and commit with 
SQLObject seems to suggest that even though SQLite supports 
transactions, SQLObject doesn't really take advantage of them.  Is this 
the case?

--
Brian Beck
Adventurer of the First Order

Re: [SQLObject] Preparing for 0.6.1

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

> Is there anything people know of that should be resolved before 0.6.1?

I am not sure if this is a bug or a feature or my misunderstanding,
but RelatedJoin does not create constrains on the intermediate table
(I am using PostgreSQL 7.4).
And a very related problem: the many-to-many relations between objects
are not deleted after destroying the objects. The following test does
not remove row from intermediate table:

class Person(SQLObject):
    name = StringCol()
    addresses = RelatedJoin('Address')

class Address(SQLObject):
    street = StringCol()
    persons = RelatedJoin('Person')

Person.dropTable(ifExists=True)
Person.createTable()
Address.dropTable(ifExists=True)
Address.createTable()

p = Person(name='John')
a = Address(street='Some street')
p.addAddress(a)
p.destroySelf()
a.destroySelf()
~                 

-- 
Ksenia

Re: [SQLObject] bug with UnicodeCol

[Stuart B. <st...@st...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ian Bicking wrote:

| That is true.  I'm not sure how to best resolve this.  There were a
| couple ideas when unicode columns first came up.  One was adding an
| encoding to converters.UnicodeConverter; really there should be *some*
| UnicodeConverter even in converters, and perhaps a default encoding
| (which might default to ASCII, which is implicitly the case now).
|
| Ideally, each column would do its own quoting, so that a UnicodeCol
| would know its own encoding.  But while that would allow for a database
| with multiple encodings (or maybe multiple databases with multiple
| encodings), that might not be a common-enough use case.  I want to get
| rid of .q entirely, and make columns descriptors with a __sqlrepr__
| method; at that point it would be much easier to make this addition.

Is there actually a use case for allowing each column to have a
different encoding? I know for PostgreSQL it is simply a matter of
setting the database encoding to Unicode and sending everything as UTF-8
by simply encoding the entire query (which takes care of other issues
like Unicode column names as well). The only use cases I can come up
with for your scenario should be usng BINARY columns instead of VARCHAR
- - in particular, since the database doesn't know the encoding you are
using then all your basic string operations, sorting etc. are now broken.

Hmm... perhaps if you need to store text in some encoding that doesn't
contain the ASCII character set it might be necessary, but I don't know
what character sets these are or if any databases actually support them.
I've gone through the list of encodings PostgreSQL supports and they all
contain the basic latin letters and can be used to encode SQL
statements, so I suspect this is not a requirement.

As I previously mentioned on this list, we are using an SQLObject
patched to do just this - no need for UnicodeCol at all. Just encode the
entire query before sending it to the backend, and decode all strings to
Unicode on the way back out. Best practice, and no risk of acidently
polluting your database with badly encoded data or booby traps set off
when code that assumed ASCII gets who-knows-what encoded data.

- --
Stuart Bishop <st...@st...>
http://www.stuartbishop.net/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFB0zYwAfqZj7rGN0oRAjA5AJsExqjU86R8obzdugpYm46WJpurrgCgnELn
xVxoOF3PGLo39KdoE6ZbhAo=
=q/y6
-----END PGP SIGNATURE-----

Re: [SQLObject] Preparing for 0.6.1

[Ian B. <ia...@co...>]

Oleg Broytmann wrote:
> On Wed, Dec 29, 2004 at 11:06:48AM -0600, Ian Bicking wrote:
> 
>>Also, I've been trying to add documentation as people note things are 
>>missing.
> 
> 
>    I cannot build SQLObject.txt using rst2html.py - SQLObject.txt
> references external code snippets that are not exist.

Woops, fixed in 505.

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org

Re: [SQLObject] Preparing for 0.6.1

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

On Wed, Dec 29, 2004 at 11:06:48AM -0600, Ian Bicking wrote:
> Also, I've been trying to add documentation as people note things are 
> missing.

   I cannot build SQLObject.txt using rst2html.py - SQLObject.txt
references external code snippets that are not exist.

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

Re: [SQLObject] bug with UnicodeCol

[Ian B. <ia...@co...>]

Max Ischenko wrote:
> getting and setting the value with unicode string works fine. I.e. both
> user.email = u'something'
> and
> print user.email # -> u'something'
> works fine.
> 
> The problem is with the following code:
> 
> dbusers = User.selectBy(isGuest=False, email=email)
> n = dbusers.count()
> 
> It yields an error:
...
>   File "D:\Python23\Lib\site-packages\sqlobject\dbconnection.py", line 
> 196, in _executeRetry
>     return cursor.execute(query)
> TypeError: argument 1 must be str, not unicode
> 
> 
> Guess this is because email has not been coerced from unicode string to 
> db encoding as being done by normal getters and setters.

That is true.  I'm not sure how to best resolve this.  There were a 
couple ideas when unicode columns first came up.  One was adding an 
encoding to converters.UnicodeConverter; really there should be *some* 
UnicodeConverter even in converters, and perhaps a default encoding 
(which might default to ASCII, which is implicitly the case now).

Ideally, each column would do its own quoting, so that a UnicodeCol 
would know its own encoding.  But while that would allow for a database 
with multiple encodings (or maybe multiple databases with multiple 
encodings), that might not be a common-enough use case.  I want to get 
rid of .q entirely, and make columns descriptors with a __sqlrepr__ 
method; at that point it would be much easier to make this addition.

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org

[SQLObject] Preparing for 0.6.1

[Ian B. <ia...@co...>]

Is there anything people know of that should be resolved before 0.6.1? 
As far as I can remember, it's just been a bunch of bug fixes since 0.6 
(most from Oleg, thanks!)

Also, I've been trying to add documentation as people note things are 
missing.  So, if there's any other omissions people notice, or ideas for 
restructuring or whatever, I'd be interested in comments.

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org

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

[Ian B. <ia...@co...>]

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
> :-(
> 
> Now, just out of curiosity: is selectBy undocumented just for lack of
> time, or is it because it's not recommended?

Forgetfulness.  I've added a note to the svn docs.

With reference to the rest of the discussion -- I don't like the findOne 
function, because it throws things away.  I'd rather it raise an error 
when more than one record was found; I don't think there's a use case 
for throwing rows away, it's just easy to implement.  In most cases 
alternateID fills the role of select-one, though another way of doing 
this might be useful.  Actually, I think it's mostly useful because of 
the error issue.  If you implement ad hoc findOne-like methods, you'll 
probably throw away extra columns.  Maybe it could be a method for 
SelectResult methods, like:

inst = MyTable.selectBy(...).getOne()

But I don't like "getOne" as a method name.


SQL injection won't be a problem, since we do all the necessary quoting 
-- that's part of the point of SQLObject and SQLBuilder, after all.  You 
only have to be sure that all your columns are "public", more or less, 
since a person might be able to inject their own form variables.  But 
it's also easy to test that your keys are limited to a specific set of 
columns.

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org

[SQLObject] bug with UnicodeCol

[Max I. <ma...@uc...>]

Hi,

I believe I found a bug with either new UnicodeCol or the selectBy 
method, but I'm not sure hence post it here instead of SF bug tracker.

I have a table which has a column declared like this:

class User(SQLObject):
   ...
   email = UnicodeCol(length=96, default=None, unique=True)


getting and setting the value with unicode string works fine. I.e. both
user.email = u'something'
and
print user.email # -> u'something'
works fine.

The problem is with the following code:

dbusers = User.selectBy(isGuest=False, email=email)
n = dbusers.count()

It yields an error:
   File "D:\Python23\Lib\site-packages\sqlobject\main.py", line 1322, in 
count
     count = self.accumulate('COUNT(*)')
   File "D:\Python23\Lib\site-packages\sqlobject\main.py", line 1316, in 
accumulate
     return conn.accumulateSelect(self,expression)
   File "D:\Python23\Lib\site-packages\sqlobject\dbconnection.py", line 
256, in accumulateSelect
     val = int(self.queryOne(q)[0])
   File "D:\Python23\Lib\site-packages\sqlobject\dbconnection.py", line 
230, in queryOne
     return self._runWithConnection(self._queryOne, s)
   File "D:\Python23\Lib\site-packages\sqlobject\dbconnection.py", line 
125, in _runWithConnection
     val = meth(conn, *args)
   File "D:\Python23\Lib\site-packages\sqlobject\dbconnection.py", line 
223, in _queryOne
     self._executeRetry(conn, c, s)
   File "D:\Python23\Lib\site-packages\sqlobject\dbconnection.py", line 
196, in _executeRetry
     return cursor.execute(query)
TypeError: argument 1 must be str, not unicode


Guess this is because email has not been coerced from unicode string to 
db encoding as being done by normal getters and setters.

Re: [SQLObject] Inheritance

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

>    Well, I think I've found and nailed the bug. The fix was committed to
> the inheritance branch at revision 502. Please run your tests and report
> the results.

It works like a charm! The statement (person.id = author.id) is now
always added.
Thank you very much for the fix! It's really cool to have bugs fixed
that quickly.
I am using inheritance branch for one of the projects and will
continue to report any encountered problems.

Happy New Year ;-)

-- 
Ksenia

Re: [SQLObject] Inheritance

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

On Tue, Dec 28, 2004 at 01:04:56PM +0300, Oleg Broytmann wrote:
>    This cause the internal loop that builds the list of parent classes
> to behave differently. This is a bug, of course, and I'm continuing
> hunting for it.

   Well, I think I've found and nailed the bug. The fix was committed to
the inheritance branch at revision 502. Please run your tests and report
the results.

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

Re: [SQLObject] Inheritance

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

On Fri, Dec 24, 2004 at 11:26:56PM +0200, Ksenia Marasanova wrote:
> 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?!

   Not at all. The magic is in the order of registry keys. Registry is a
dictionary, and Python handles dictionary's keys in a random order:

>>> print {"Author": 1, "Person": 1, "Retired": 1}.keys()
['Person', 'Retired', 'Author']
>>> print {"Employee": 1, "Person": 1, "Retired": 1}.keys()
['Employee', 'Person', 'Retired']

   This cause the internal loop that builds the list of parent classes
to behave differently. This is a bug, of course, and I'm continuing
hunting for it.

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

Re: [SQLObject] SELECT id, FROM atable

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

On Tue, Dec 28, 2004 at 11:20:48AM +0300, Oleg Broytmann wrote:
>    I am going to fix it in the trunk and in the inheritance branch.

   The patch committed at revisions 497 and 498. All tests passed.

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

Re: [SQLObject] SELECT id, FROM atable

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

On Mon, Dec 27, 2004 at 04:04:01PM +0200, Ksenia Marasanova wrote:
> Sorry, I wanted to simplify the example :) I do have columns, but not
> a regular ones - MultipleJoin columns. The same error occures.

   This is not related to inheritance. The following simple test
triggers the bug in code from SQLObject-trunk:

class Person(SQLObject):
   address = MultipleJoin("Address")

class Address(SQLObject):
   person = ForeignKey("Person")
   street = StringCol()

Person.dropTable(ifExists=True)
Person.createTable()

Address.dropTable(ifExists=True)
Address.createTable()

print list(Person.select())

   I am going to fix it in the trunk and in the inheritance branch.

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

Re: [SQLObject] Feature Request: Can Registry call a factory method if a class is not found?

[Scott S. <sc...@st...>]

On Dec 6, 2004, at 11:10 AM, Ian Bicking wrote:
> Maybe it would be helpful if the registry had a method to return the 
> names of all the classes that are still pending?  That would be:
>
> def pendingClasses(self):
>     return self.callbacks.keys()

I just implemented this in my own code (registry.callbacks.keys()), and 
it works great.  I would not worry about patching SQLObject at this 
point, as that pointer was exactly what I needed.

Thanks guys,
Scott

Re: [SQLObject] Inheritance

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

> What is your use case for such a table? Do you want to mark some
>persons as employees, but don't want to add columns? If there is no a
>reasonable use case for a columnless table there is no bug to fix - just
>add a column to tha table Employee and all will work again. 

Sorry, I wanted to simplify the example :) I do have columns, but not
a regular ones - MultipleJoin columns. The same error occures.
 
-- 
Ksenia

Re: [SQLObject] Default value

[Stuart B. <st...@st...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hong Yuan wrote:
| 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)

| 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 = ...)

This works for PostgreSQL (and should for all ANSI complient databases).
First, define this small helper:

class Default(object):
~    def __sqlrepr__(self, dbName):
~        return "DEFAULT"
DEFAULT = Default()


Then define your field as:

~    field = Col(default=DEFAULT)

- --
Stuart Bishop <st...@st...>
http://www.stuartbishop.net/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFBz8Q/AfqZj7rGN0oRAgc8AJ9R1gnlbnXqV85K2udMYAsqD/6x2QCeLcWK
L+KWBXSdprTtCSgH84xmRHE=
=Y5kr
-----END PGP SIGNATURE-----

Re: [SQLObject] Inheritance

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

Hello!

On Sun, Dec 26, 2004 at 01:07:07AM +0200, Ksenia Marasanova wrote:
> Here is another bug. The attached script produces this sql:
> SELECT employee.id,  FROM employee WHERE  1 = 1

   That one is easy to understand and easy to fix, if it needs to be
fixed at all. The problem is not related to inheritance. You've created
a table (Employee) that does not have any column - only id. And
SQLObject dislikes such a table. With a reason - why does anyone would
want a table with only an id column?
   What is your use case for such a table? Do you want to mark some
persons as employees, but don't want to add columns? If there is no a
reasonable use case for a columnless table there is no bug to fix - just
add a column to tha table Employee and all will work again.

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

Re: [SQLObject] SQLObject Database connection

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

Sam Nilsson wrote:
> Here is a simplified version of how I am handling this. If I have an 
> SQLObject class called Image then:
> 
> sqlstring = "SELECT id FROM imagelist"

sorry, it would probably look more like this:

sqlstring = "SELECT id FROM Image"

- Sam

Re: [SQLObject] SQLObject Database connection

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

Hong Yuan wrote:
> I am using SQLObject with a PostgreSQL database. Part of my program uses 
> SQLObject to access the database, part of it uses psycopg directly.
> 
> The question is, do I have to initiailze two separate database 
> connections, one with psycopg.connect for my own code, and another with 
> connectionForURI('postgres://...') for use with SQLObject?
> 
> Can I create a connectino for SQLObject from an existing psycopg 
> connection of vice versa, get an psycopg connection back from an 
> SQLObject connection object?
> 
> Greetings,
> 

Here is a simplified version of how I am handling this. If I have an 
SQLObject class called Image then:

sqlstring = "SELECT id FROM imagelist"
imagelist = Image._connection.queryAll(sqlstring)
idlist = [row[0] for row in imagelist]
print "list: ", idlist
return idlist

Good Luck!
- Sam Nilsson

[SQLObject] SQLObject Database connection

[Hong Y. <hon...@ho...>]

I am using SQLObject with a PostgreSQL database. Part of my program uses 
SQLObject to access the database, part of it uses psycopg directly.

The question is, do I have to initiailze two separate database 
connections, one with psycopg.connect for my own code, and another with 
connectionForURI('postgres://...') for use with SQLObject?

Can I create a connectino for SQLObject from an existing psycopg 
connection of vice versa, get an psycopg connection back from an 
SQLObject connection object?

Greetings,

-- 
HONG Yuan
Homemaster Trading Co., Ltd.
No. 601, Bldg. 41, 288 Shuangyang Rd. (N)
Shanghai 200433, P.R.C.
Tel: +86 21 55056553
Fax: +86 21 55067325
E-mail: hon...@ho...

Re: [SQLObject] Inheritance

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

Here is another bug. The attached script produces this sql:

SELECT employee.id,  FROM employee WHERE  1 = 1


Greetings,
-- 
Ksenia