SQLObject transaction problems

[Jeff Watkins]

Because of the inability to do complex queries using SQLObject, I've
had to fall back to issuing direct SQL queries using the _connection
attribute of the model classes. However, this seems to be causing
some difficulty with transactions.

I've been getting the following exception:

> File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
> python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/
> dbconnection.py", line 728, in assertActive
> assert not self._obsolete, "This transaction has already gone
> through ROLLBACK; begin another transaction"
> AssertionError: This transaction has already gone through ROLLBACK;
> begin another transaction

I'm fairly certain this has something to do with this code:

> all= self._connection.queryAll('''[...really complex SQL query...]''')
> return [Product.get(row[0]) for row in all[:limit]]

In this particular case, self is an instance of a model object.
However, in other cases, I use the class connection as follows:

> all= cls._connection.queryAll('''[...very complex query...]''')
> return [cls.get(row[0]) for row in all[:limit]]

All seems to work OK for the first couple queries and then SQLObject
explodes.

Certainly someone else must have encountered this. I can't be the
only person using complex queries. How can I get around this problem?

Jeff

--
Jeff Watkins
http://newburyportion.com/

'I know about people who talk about suffering for the common good.
It's never bloody them! When you hear a man shouting "Forward, brave
comrades!" you'll see he's the one behind the bloody big rock and the
one wearing the only really arrow-proof helmet!'
-- Rincewind gives a speech on politics. (Terry Pratchett,
Interesting Times)

[Jeff Watkins]

Just a note to update the problem: I switched to getting my connection via hub.getConnection() and still have the same problem.

--

Jeff Watkins

http://newburyportion.com/

"Advertising directed at children is inherently deceptive and exploits children under eight years of age."

-- American Academy of Pediatrics

[midtoad]

Jeff:

are you saying that SQLObject has an inability to do complex queries or
that *you* have an inability? How about posting your query and letting
us see if we can find a way to do the query using SQLObject?

S

[Jeff Watkins]

While it *MIGHT* be possible to create this query using SQLBuilder,
it certainly wouldn't be any easier using SQLBuilder than raw SQL:

select t1.product_id,
t1.home*%(home_page_weight)s +
t1.product*%(product_page_weight)s +
coalesce(t2.num,0)*%(order_weight)s as score
from
category_product,
(select product_id, sum(home_view) as home,
sum(product_view) as product
from product_view group by product_id) as t1
left join
(select product_id, sum(product_id) as num
from order_entry group by product_id) as t2
on t1.product_id=t2.product_id
where t1.product_id=category_product.product_id and
category_product.category_id=%(category_id)s
order by score desc

This query is only likely to become *MORE* complex as I add different
view statistics (which will appear not as separate columns, but as a
view type).

--
Jeff Watkins
http://newburyportion.com/

"Daddy, I want a purple iMac. And I want ice cream!"
-- Unidentified 7-year-old to his father.

[Jonathan LaCour]

On Jan 2, 2006, at 7:07 PM, Jeff Watkins wrote:
> While it *MIGHT* be possible to create this query using SQLBuilder,
> it certainly wouldn't be any easier using SQLBuilder than raw SQL:
>
> select t1.product_id,
> t1.home*%(home_page_weight)s +
> t1.product*%(product_page_weight)s +
> coalesce(t2.num,0)*%(order_weight)s as
> score
> from
> category_product,
> (select product_id, sum(home_view) as home,
> sum(product_view) as product
> from product_view group by product_id) as t1
> left join
> (select product_id, sum(product_id) as num
> from order_entry group by product_id) as t2
> on t1.product_id=t2.product_id
> where t1.product_id=category_product.product_id and
> category_product.category_id=%(category_id)s
> order by score desc
>
> This query is only likely to become *MORE* complex as I add
> different view statistics (which will appear not as separate
> columns, but as a view type).

Why not just create an SQL view, and map the view as an SQLObject?
This is often a good solution for classes of problems where query
performance is more important than insert performance, like most web
apps that I have seen.

Granted, I feel your pain. SQLObject isn't particularly good at the
stuff like this that I rarely, but still need, to do. It sure does
simplify the 90% of CRUD on most tables though!

Maybe one day SQLObject, or something else, will come along to make
building reporting-like queries easier than writing raw SQL, but
thats a pretty tall order.

--
Jonathan LaCour
http://cleverdevil.org

[Jeff Watkins]

On 2 Jan, 2006, at 7:44 pm, Jonathan LaCour wrote:
> Why not just create an SQL view, and map the view as an SQLObject?
> This is often a good solution for classes of problems where query
> performance is more important than insert performance, like most
> web apps that I have seen.

First, SQLite doesn't support Views (as I understand) and since this
is intended to be a sample that is distributed with a book (yes,
TurboGears will be appearing in another book, but the primary subject
is Ajax), I really want to stick with something that can be installed
and run quickly: hence SQLite.

Mostly, I don't think this is *about* the query. The query runs just
fine. The problem seems to be that SQLObject's transactions get all
out of whack when I'm doing a real query.

> Maybe one day SQLObject, or something else, will come along to make
> building reporting-like queries easier than writing raw SQL, but
> thats a pretty tall order.

You might want to take a look at SQLAlchemy <http://sqlalchemy.org/>.
I think it's a much more full fledged solution to the problem. I've
been working with the author Michael Bayer to add a declarative layer
(similar to SQLObject) that would make it more attractive to many
python developers.

Jeff

[Jonathan LaCour]

>> Why not just create an SQL view, and map the view as an
>> SQLObject? This is often a good solution for classes of problems
>> where query performance is more important than insert performance,
>> like most web apps that I have seen.
>
> First, SQLite doesn't support Views (as I understand) and since
> this is intended to be a sample that is distributed with a book
> (yes, TurboGears will be appearing in another book, but the primary
> subject is Ajax), I really want to stick with something that can be
> installed and run quickly: hence SQLite.

Fair enough. I didn't notice that you were using SQLite :)

> Mostly, I don't think this is *about* the query. The query runs
> just fine. The problem seems to be that SQLObject's transactions
> get all out of whack when I'm doing a real query.

Also fair. I have had lots of issues with the way that SQLObject
handles both connections, and transactions. It just seems wrong to
me, so I totally understand your gripe (and share it).

I was just trying to provide a way around the query aspect of the
problem.

>> Maybe one day SQLObject, or something else, will come along to
>> make building reporting-like queries easier than writing raw SQL,
>> but thats a pretty tall order.
>
> You might want to take a look at SQLAlchemy <http://sqlalchemy.org/
> >. I think it's a much more full fledged solution to the problem.
> I've been working with the author Michael Bayer to add a
> declarative layer (similar to SQLObject) that would make it more
> attractive to many python developers.

I have looked at SQLAlchemy, and I like what I see. I have installed
it and played with it quite a bit, and have even written a few little
TurboGears test applications with it.

I find it to excel at the complex, and falter at the simple. I am
extremely excited to hear that you are working on a more declarative
SQLObject-like layer to SQLAlchemy, as it would address my *one* big
gripe with it: its just too complex for the simple stuff.

That being said, the way that SQLAlchemy handles connections and
transactions seems a lot more straightforward and easy to understand
(and follow... SQLObject is far too odd and magical in this sense).

I can't wait to see the SQLAlchemy declarative layer. Please share
it as soon as you have something to show! :)

[Kevin Dangoor]

On 1/1/06, Jeff Watkins <je...@newburyportion.com> wrote:
>
> Because of the inability to do complex queries using SQLObject, I've
> had to fall back to issuing direct SQL queries using the _connection
> attribute of the model classes. However, this seems to be causing
> some difficulty with transactions.

When are you doing these queries? In controller methods (and not in a
filter or something)? TurboGears *should* be getting you a valid
transaction on the way in and then committing or rolling back on the
way out within a controller method.

Are you doing any rollbacks yourself?

Kevin

[Jeff Watkins]

Kevin Dangoor wrote:
> When are you doing these queries? In controller methods (and not in a
> filter or something)? TurboGears *should* be getting you a valid
> transaction on the way in and then committing or rolling back on the
> way out within a controller method.

It seems that some of the query stuff seems to be happening in the KID
processing. I have *no* idea why. I'll post the full stack trace when I
get home.

As Jeremy pointed out, debugging Kid stuff is a real pain.

> Are you doing any rollbacks yourself?

Nope. I'm not doing *anything* complicated, really.

Is it possible that SQLObject is sending back a proxied query object or
something?

[Kevin Dangoor]

On 1/3/06, Jeff Watkins <je...@newburyportion.com> wrote:
> It seems that some of the query stuff seems to be happening in the KID
> processing. I have *no* idea why. I'll post the full stack trace when I
> get home.

Queries can happen if you access relationship attributes from your template.

There *was* a bug in the automatic transaction handling because
commit/rollback happened before Kid processing. I changed this,
though, so that it would happen after.

> Is it possible that SQLObject is sending back a proxied query object or
> something?

Have you tried turning on query debugging to see exactly what
SQLObject is after?

It still seems like the problem has to be related to when the
automatic transaction handling does its thing.

Kevin

--
Kevin Dangoor
Author of the Zesty News RSS newsreader

email: k...@blazingthings.com
company: http://www.BlazingThings.com
blog: http://www.BlueSkyOnMars.com

[Jeff Watkins]

The following is the complete exception stack trace starting with the
ROLLBACK command someone is issuing to SQLObject. Just a note, I
don't issue a rollback, so I assume an exception must have been
thrown and this is crashing during the rollback. Only, I don't see
any log of the exception.

1/ROLLBACK:
2006/01/03 21:38:09 INFO Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/CherryPy-2.1.0-py2.4.egg/cherrypy/
_cphttptools.py", line 271, in run
main()
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/CherryPy-2.1.0-py2.4.egg/cherrypy/
_cphttptools.py", line 502, in main
body = page_handler(*args, **cherrypy.request.paramMap)
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/CherryPy-2.1.0-py2.4.egg/cherrypy/lib/
cptools.py", line 122, in default
return getattr(self, args[0])(*args[1:], **kwargs)
File "/Users/jeff/Projects/Web/turbogears/turbogears/
controllers.py", line 242, in newfunc
html, *args, **kw)
File "/Users/jeff/Projects/Web/turbogears/turbogears/database.py",
line 189, in run_with_transaction
retval = func(*args, **kw)
File "/Users/jeff/Projects/Web/turbogears/turbogears/
controllers.py", line 262, in _execute_func
return _process_output(tg_format, output, html)
File "/Users/jeff/Projects/Web/turbogears/turbogears/
controllers.py", line 64, in _process_output
output = view.render(output, tg_format, template=template)
File "/Users/jeff/Projects/Web/turbogears/turbogears/view.py",
line 62, in render
return engine.render(info, format, fragment, template)
File "/Users/jeff/Projects/Web/turbogears/turbogears/
kidsupport.py", line 141, in render
return t.serialize(encoding=self.defaultencoding, output=format,
fragment=fragment)
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/kid-0.8-py2.4.egg/kid/__init__.py", line 232,
in serialize
return serializer.serialize(self, encoding, fragment)
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/kid-0.8-py2.4.egg/kid/serialization.py", line
51, in serialize
text = list(self.generate(stream, encoding, fragment))
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/kid-0.8-py2.4.egg/kid/serialization.py", line
149, in generate
for ev, item in self.apply_filters(stream):
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/kid-0.8-py2.4.egg/kid/serialization.py", line
77, in balancing_filter
for ev, item in stream:
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/kid-0.8-py2.4.egg/kid/pull.py", line 203, in
_coalesce
for ev, item in stream:
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/kid-0.8-py2.4.egg/kid/filter.py", line 21, in
transform_filter
for ev, item in apply_matches(stream, template, templates,
apply_func):
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/kid-0.8-py2.4.egg/kid/filter.py", line 31, in
apply_matches
item = stream.expand()
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/kid-0.8-py2.4.egg/kid/pull.py", line 95, in
expand
for ev, item in self._iter:
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/kid-0.8-py2.4.egg/kid/pull.py", line 164, in
_track
for p in stream:
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/kid-0.8-py2.4.egg/kid/pull.py", line 203, in
_coalesce
for ev, item in stream:
File "/Users/jeff/Sites/turbo/shop/shop/templates/category.py",
line 282, in _pull
File "/Users/jeff/Sites/turbo/shop/shop/templates/master.py", line
235, in product_table
File "<string>", line 1, in <lambda>
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/main.py",
line 971, in _SO_loadValue
selectResults = self._connection._SO_selectOne(self, dbNames)
File "/Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/
dbconnection.py", line 787, in __getattr__
self.assertActive()

[Kevin Dangoor]

Here's _execute_func from controllers.py:

def _execute_func(self, func, tg_format,
html, *args, **kw):
output = func(self, *args, **kw)
if html and html.startswith("."):
html = func.__module__[:func.__module__.rfind('.')]+html
return _process_output(tg_format, output, html)

The database transaction is wrapped around this, and your exception is
appearing in the _process_output call at the end of this. What we can
deduce from this is:

1) your controller method (func, above) didn't raise an exception
2) that means that TurboGears' transaction wrapper wasn't responsible
for rolling back the transaction
3) the only code in TurboGears itself that does a rollback is the
stuff in database.py.

So, if *you're* not doing a rollback, that would imply that this
transaction is somehow being reused from somewhere else that *did*
cause a rollback. The transaction didn't go away from a previous
request, possibly? A filter gone awry? (TurboGears comes with a filter
now that attempts to end the transaction at the very end of the
cycle.)

Kevin

On 1/3/06, Jeff Watkins <je...@newburyportion.com> wrote:
>

[Jeff Watkins]

Just for the sake of helping out the next poor sod who has this problem:

I have the following model class:

class Category(SQLObject):
slug= UnicodeCol( length=40 )
name= UnicodeCol( length=40 )
description= UnicodeCol()
parent= ForeignKey( "Category", dbName="parent_id", default=None )
children= MultipleJoin( "Category", joinColumn="parent_id" )
items= RelatedJoin( "Product",
intermediateTable="category_product" )

def deep_items( self, items=[] ):
'''
Retrieve ALL items in this category regardless of whether
they appear
in a subcategory.
'''
items.extend( self.items )
for cat in self.children:
cat.deep_items( items )
return items

The problem stemmed from the default value for items. The next time
deep_items was run, the items list contained the entries from the
previous invocation. And of course, their transactions had already
expired.

I now have the method defined as:

def deep_items( self, items=None ):
'''
Retrieve ALL items in this category regardless of whether
they appear
in a subcategory.
'''
if not items:
items=[]
items.extend( self.items )
for cat in self.children:
cat.deep_items( items )
return items

And it seems to work.

I definitely echo Jeremy's sentiment: Kid templates are a pain to
debug. It was only on my wife's suggestion that I strip out all the
computed values with dummy data and slowly add stuff back that I
found the problem.

--
Jeff Watkins
http://newburyportion.com/

“In science it often happens that scientists say, ‘You know that’s a
really good argument; my position is mistaken,’ and then they
actually change their minds and you never hear that old view from
them again. They really do it. It doesn’t happen as often as it
should, because scientists are human and change is sometimes painful.
But it happens every day. I cannot recall the last time something
like that happened in politics or religion.” Carl Sagan, 1987

[rick]

I too am having the same problem with a different operation.
I am simply iterating through a list of objects returned from a call
to:
countries = Country.select()

the operation looks like:

for obj in v.choiceTable:
print "DEBUG: obj - %s choiceCol - %s" % (obj, v.choiceCol)
choice = getattr(obj, v.choiceCol)

here's the kicker.. it sucessfully iterates through 233 of the 233
items in the list before going south. So there is a 1 off error or
something in an iterator that is either rolling back, committing or
just setting __obsolete to True, and then the process continues for one
more iteration.

here is the trace and the last part of the debug:
DEBUG: obj - <Country 231 name='u"Korea, Democrat..."' abbrev=u'KP'>
choiceCol - name
DEBUG: obj - <Country 232 name=u'French Guiana' abbrev=u'GF'>
choiceCol - name
DEBUG: obj - <Country 233 name=u'Mayotte' abbrev=u'YT'> choiceCol -
name
2006/01/12 16:47:36 INFO Traceback (most recent call last):
File
"d:\python24\lib\site-packages\CherryPy-2.1.0-py2.4.egg\cherrypy\_cphttptools.py",

line 271, in run
main()
File

"d:\python24\lib\site-packages\CherryPy-2.1.0-py2.4.egg\cherrypy\_cphttptools.py",

line 502, in main
body = page_handler(*args, **cherrypy.request.paramMap)
File

"d:\python24\lib\site-packages\TurboGears-0.9a0dev_r466-py2.4.egg\turbogears\controllers.py",
line 240, in newfun
c
html, fragment, *args, **kw)
File
"d:\python24\lib\site-packages\TurboGears-0.9a0dev_r466-py2.4.egg\turbogears\database.py",
line 189, in run_with_

transaction
retval = func(*args, **kw)
File

"d:\python24\lib\site-packages\TurboGears-0.9a0dev_r466-py2.4.egg\turbogears\controllers.py",
line 257, in _execu
te_func

output = func(self, *args, **kw)

File
"d:\python24\lib\site-packages\TurboGears-0.9a0dev_r466-py2.4.egg\turbogears\identity\conditions.py",
line 230, i
n _wrapper
return fn( self, *args, **kwargs )
File
"D:\devstuff\Eltopia_PPG\eltopiawholesale\eltopiawholesale\reseller.py",
line 73, in Configuration
main, msg = genCompanyPage(self.cpnydict)
File
"D:\devstuff\Eltopia_PPG\eltopiawholesale\eltopiawholesale\reseller_content.py",
line 106, in genCompanyPage
html = createDataForm(data, title=name,
nav="/reseller/Configuration",mode="company",op="mod")
File
"D:\devstuff\Eltopia_PPG\eltopiawholesale\eltopiawholesale\htmlutils.py",
line 97, in createDataForm
print "DEBUG: obj - %s choiceCol - %s" % (obj, v.choiceCol)
File
"d:\python24\lib\site-packages\SQLObject-0.7.1dev_r1457-py2.4.egg\sqlobject\main.py",
line 1472, in __repr__
return '<%s %r %s>' \
File
"d:\python24\lib\site-packages\SQLObject-0.7.1dev_r1457-py2.4.egg\sqlobject\main.py",
line 1493, in _reprItems
value = getattr(self, col.name)

File "<string>", line 1, in <lambda>
File

"d:\python24\lib\site-packages\SQLObject-0.7.1dev_r1457-py2.4.egg\sqlobject\main.py",
line 982, in _SO_loadValue

selectResults = self._connection._SO_selectOne(self, dbNames)
File

"d:\python24\lib\site-packages\SQLObject-0.7.1dev_r1457-py2.4.egg\sqlobject\dbconnection.py",
line 795, in __geta
ttr__
self.assertActive()
File
"d:\python24\lib\site-packages\SQLObject-0.7.1dev_r1457-py2.4.egg\sqlobject\dbconnection.py",
line 736, in assert

Active
assert not self._obsolete, "This transaction has already gone
through ROLLBACK; begin another transaction"
AssertionError: This transaction has already gone through ROLLBACK;
begin another transaction

I'll keep looking into this.. i think i have the source of the error
pretty well pinpointed.. just not sure why its failing

[rick]

curiouser and curiouser

I put a traceback.print_stack on the dbconnection.py rollback and
commit.
It would appear.. for some reason unbeknownst to me.. that on the very
last iteration of my loop through this list of Country objects,
turbogears.database.run_with_transaction is catching some wacky
exception and calling rollback_all

but that is okay.. i think.. because i have no business iterating over
this list anymore because i have gone past its boundary and i'm not
sure why.. even changing the size of the list still makes it move down
one more.

rick

[rick]

I added two lines of code:
print "run_with_transaction retval = func() failed"
traceback.print_stack()

to the part of run_with_transaction:

except:
rollback_all()
raise

And I can no longer reproduce the error. :{

[mike bayer]

Jonathan LaCour wrote:
>
> I find it to excel at the complex, and falter at the simple. I am
> extremely excited to hear that you are working on a more declarative
> SQLObject-like layer to SQLAlchemy, as it would address my *one* big
> gripe with it: its just too complex for the simple stuff.
>

Hi Jonathan -

Can you give me an example of a simple task it falters with ? So far,
youve made other comments to this effect, but you havent mentioned what
exactly is so complicated. For my part, I cant imagine how much
simpler it can be.

A few people were helpful enough to make suggestions on the mailing
list, such as a "select_by" and a "get_by" method, and easier ways to
attach these methods to a class; theyve been added (as will any other
reasonable suggestion). The "select_by"/"get_by" methods are even
more powerful than typical active record "find_by" methods, while
adding zero complexity on the calling side, in that they will even pull
in a related join if you search by a key that is not on the main class
but on one of its relations.

Similarly, it seems that people are accustomed to saying
"foo=ForeignKey(), bar=MultipleJoin()", and are thrown off by how these
keywords are not present in SQLAlchemy. This is an example of a
simpler approach being mistaken for a more complicated one, just
because it is unfamiliar. With SQLAlchemy, all you say is,
"foo=relation(), bar=relation()". It *figures out* the relationship
for you based on what it naturally is based on foreign keys. If you
have auto-loaded your tables from the database which has proper foreign
key constraints, SQLAlchemy will literally automatically configure
object relations based on the database schema, with no explicit code in
the application. Simpler ! More automatic ! Only one function to
learn instead of four or five !

Combine features like this with all the extras, like eager loading,
deferred column loading, automatic support for self-referential tables,
and the fact that O/R complexity will increase with a relatively smooth
correlation to the complexity of the application and database itself,
rather than making a sudden jump beyond simple cases, and SQLAlchemy
becomes even easier to live with....all for just the simple price of
looking outside the way youve been doing something all these years.

I know that people are comfortable with SQLObject, and may prefer its
style. However, what I am trying to do with SQLAlchemy is make life
easier for those who understand how databases and relational structures
work; for those people, a tool like SQLAlchemy makes simple tasks
*simpler*. And the more it allows those not accustomed to relational
structures to learn to see their database as the powerful relational
tool it is rather than a substandard object repository, the more they
will see SQLAlchemy's relational concepts making their jobs easier than
with the object/table mappers they are used to.

[Kevin Dangoor]

err... that's odd. More than just a little odd.

Kevin

[Jonathan LaCour]

On Jan 13, 2006, at 3:14 AM, mike bayer wrote:
> Can you give me an example of a simple task it falters with ? So far,
> youve made other comments to this effect, but you havent mentioned
> what
> exactly is so complicated. For my part, I cant imagine how much
> simpler it can be.

Let me just start out by saying that since I made my initial
impressions of sqlAlchemy, I have taken a bit more time to play with
it, and I really like it quite a bit. My issues with SQLObject are
largely technical, and sqlAlchemy seems to deal with all of those
issues quite well (simpler connection and transaction management,
less magical, better at mapping complex schemas, fewer concurrency
problems, a clearer direction, etc). I really really like
sqlAlchemy. You have done a bang-up job!

Now, on to my simple task that I find sqlAlchemy falters with:
mapping simple schemas. Its not that it can't do it, its just a
stylistic issue.

In fact, my issues with sqlAlchemy are largely stylistic. I find
SQLObject's declarative mapping to be a lot easier to read than
sqlAlchemy's insistence to separate database mapping from class
design. This aspect of sqlAlchemy feels very much like hibernate,
which I have used in the past, and found absolutely nauseating. Its
not that I don't understand the principle, or see the reasoning for
it, but most of the time I find it irritating, and more importantly,
it confuses _other people_ who read my code.

I have heard that there are some people working on a declarative
wrapper around sqlAlchemy's mapping API, and I think that this will
likely eliminate my stylistic issues.

> A few people were helpful enough to make suggestions on the mailing
> list, such as a "select_by" and a "get_by" method, and easier ways to
> attach these methods to a class; theyve been added (as will any other
> reasonable suggestion). The "select_by"/"get_by" methods are even
> more powerful than typical active record "find_by" methods, while
> adding zero complexity on the calling side, in that they will even
> pull
> in a related join if you search by a key that is not on the main class
> but on one of its relations.

I noticed this as well, and it was one of my biggest gripes before!
I definitely like this change, and it shows a clear effort on your
part to make sqlAlchemy a joy to use.

> Similarly, it seems that people are accustomed to saying
> "foo=ForeignKey(), bar=MultipleJoin()", and are thrown off by how
> these
> keywords are not present in SQLAlchemy. This is an example of a
> simpler approach being mistaken for a more complicated one, just
> because it is unfamiliar. With SQLAlchemy, all you say is,
> "foo=relation(), bar=relation()". It *figures out* the relationship
> for you based on what it naturally is based on foreign keys. If you
> have auto-loaded your tables from the database which has proper
> foreign
> key constraints, SQLAlchemy will literally automatically configure
> object relations based on the database schema, with no explicit
> code in
> the application. Simpler ! More automatic ! Only one function to
> learn instead of four or five !

I don't have a _huge_ issue with this, but playing devil's advocate
-- "explicit is better than implicit." Someone reading my SQLObject
code will easily be able to tell exactly what tables exist, which
columns exist (and what type they are), what relationships exist, and
what kind of relationships they are -- without any prior knowledge
about SQLObject.

I think that if a person with no sqlAlchemy knowledge looked at one
of my sqlAlchemy models, it would take them significantly longer to
figure out what is going on.

That being said, this isn't really a deal-breaker for me, as I can
simply comment the code to clarify any potential mapping confusion.
But it would be nice if I didn't have to ;)

> Combine features like this with all the extras, like eager loading,
> deferred column loading, automatic support for self-referential
> tables,
> and the fact that O/R complexity will increase with a relatively
> smooth
> correlation to the complexity of the application and database itself,
> rather than making a sudden jump beyond simple cases, and SQLAlchemy
> becomes even easier to live with....all for just the simple price of
> looking outside the way youve been doing something all these years.

All of this is true. As I said before: you have done a great job
with sqlAlchemy. Its definitely got what it takes to handle the
simple cases, and scale all the way up to the complex ones. My
issues have nothing to do with what it can and can't do: I just wish
it was a little prettier.

> I know that people are comfortable with SQLObject, and may prefer its
> style. However, what I am trying to do with SQLAlchemy is make life
> easier for those who understand how databases and relational
> structures
> work; for those people, a tool like SQLAlchemy makes simple tasks
> *simpler*. And the more it allows those not accustomed to relational
> structures to learn to see their database as the powerful relational
> tool it is rather than a substandard object repository, the more they
> will see SQLAlchemy's relational concepts making their jobs easier
> than
> with the object/table mappers they are used to.

I understand very well how databases and relational structures work,
and you are absolutely right that sqlAlchemy is a powerful tool in
the hands of someone like myself :) However, I work on a team, and
my code is not just for my own consumption -- its for the entire
team. At some level, I think thats the issue I have with sqlAlchemy:
its built for experts. I think its important that sqlAlchemy gives
beginners a leg-up.

There are two parts of this last part of your message that I think
are very telling.

The first is this: "what I am trying to do with SQLAlchemy is make

life easier for those who understand how databases and relational

structure work." I congratulate you on trying to make life easier
for the small number of experts, but what about beginners? There are
a lot more people that I would classify as "beginners" in this world,
and you are not targeting them very well, in my opinion.

The second is that you want people to "see their database as the

powerful relational tool it is rather than a substandard object

repository." This sounds a bit preachy to me. I have written
several extremely useful TurboGears applications using SQLObject, and
I found it to be a pleasure to use, and easy to bring other people up
to speed on. Sure, there were things that I wish it did differently,
and I had some technical problems along the way, but everyone who has
taken a look at the code immediately *gets* it. Sometimes (many
times, in fact) all you need is a substandard object repository :)

If you want to empower people, you need to get them interested
first. I am all about sqlAlchemy, and can easily see it replacing
SQLObject inside TurboGears, but not without a layer to make it a bit
more palatable. Personally, I think this is bound to happen, and I
look forward to it.

sqlAlchemy is a great piece of work, and I really don't want you to
feel like I am knocking it. I really want it to succeed!