It's not faster. I use pyscopg2 directly on some other projects and
it's definitely a lot faster to just be able to write the query I want
than to try to think of how to convert it to SQLAlchemy's contexts and
functions. Maybe it's just learning curve thing, but as shown, I can't
get that join to actually work, and I was trying different things for
hours; it gives me silly errors of every which-a-kind. If I can just
write something as simple as that in under five minutes, why does it
take so long for SQLAlchemy to let me do it? There's definitely a
problem somewhere in there.
File
'/home/jeff/vspy/lib/python2.6/site-packages/WebError-0.10.1-py2.6.egg/weberror/evalexception.py',
line 431 in respond
app_iter = self.application(environ, detect_start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Beaker-1.4.1-py2.6.egg/beaker/middleware.py',
line 73 in __call__
return self.app(environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Beaker-1.4.1-py2.6.egg/beaker/middleware.py',
line 152 in __call__
return self.wrap_app(environ, session_start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Routes-1.10.3-py2.6.egg/routes/middleware.py',
line 130 in __call__
response = self.app(environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/wsgiapp.py',
line 125 in __call__
response = self.dispatch(controller, environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/wsgiapp.py',
line 324 in dispatch
return controller(environ, start_response)
File '/home/jeff/projecs/projecs/lib/base.py', line 18 in __call__
return WSGIController.__call__(self, environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 221 in __call__
response = self._dispatch_call()
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 172 in _dispatch_call
response = self._inspect_call(func)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 107 in _inspect_call
result = self._perform_call(func, args)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 60 in _perform_call
return func(**args)
File '/home/jeff/projecs/projecs/controllers/viewer.py', line 18 in index
c.assets = assets.list_assets()
File '/home/jeff/projecs/projecs/model/assets.py', line 7 in list_assets
return db.join(db.assets, db.asset_storage_schemes,
db.assets.asset_storage_scheme_id==db.asset_storage_schemes.asset_store_id).order_by(db.assets.asset_id.desc()).all()
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py',
line 1267 in all
return list(self)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py',
line 1361 in __iter__
return self._execute_and_instances(context)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py',
line 1364 in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/session.py',
line 755 in execute
clause, params or {})
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 824 in execute
return Connection.executors[c](self, object, multiparams, params)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 872 in _execute_clauseelement
parameters=params
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 938 in __create_execution_context
return dialect.execution_ctx_cls(dialect, connection=self, **kwargs)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/default.py',
line 170 in __init__
self.cursor = self.create_cursor()
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/default.py',
line 258 in create_cursor
return self._connection.connection.cursor()
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 576 in connection
raise exc.InvalidRequestError("Can't reconnect until invalid
transaction is rolled back")
InvalidRequestError: Can't reconnect until invalid transaction is rolled back
I don't fully understand what you're talking about. I have this error
and I need to make it stop. I just want SQLAlchemy to connect, run the
query I instructed, and give me the results back and do this reliably
without necessitating consistent server restarts. Thus far, it's been
a serious pain managing connection errors and so on. SQLSoup may
complicate this because they have no mention anywhere in their docs
explaining the necessity to close your connections, and all methods I
tried (explicit session.close()s at the end of each query, explicit
db.close()s, and now autoflush=True) to make sure that the resources
are being returned correctly to the pooler have failed and caused
other blow-up problem attacks.
Thanks for all your help. I'm frustrated with things so sorry if I
come across belligerently; I don't want to, I think SQLAlchemy is a
good thing, but I need it to let me work without fussing about
everything.
Signed
Jeff
On Thu, Oct 15, 2009 at 12:25 PM, Michael Bayer
<mike...@zzzcomputing.com> wrote:
>
> Jeff Cook wrote:
>>
>> Dear All People:
>>
>> I'm using SQLSoup and getting errors like InvalidRequestError: Can't
>> reconnect until invalid transaction is rolled back and MySQL Server
>> has gone away. I have set autoflush=True and this has helped mitigate
>> some errors, but not these, they happen with varying frequency when I
>> haven't restarted the servers for a while. I am almost never using
>> db.flush(). Will that fix this? How can I make this things work? I
>> really want them to work and I hate MySQL but am stuck using it for
>> now.
>>
>> SQLAlchemy thus far has added several hours to development time, which
>> I find sad. I have this block of code:
>> #this has consumed too much time
>> #will come back and fix but for now we have to give up on it
>> #ret = Session.query(sets).select_from(orm.outerjoin((slices,
>> slices.asset_slice_asset_id == id), (stores, stores.asset_store_id ==
>> slices.asset_slice_store_scheme_id))).filter(id == sets.asset_id).all
>> ()
>> #how I hate sqlalchemy right now
>> #the raw query took under five minutes
>> #the other thing took more than hour and still doesn't work
>> ret = db.bind.execute(""" SELECT * FROM assets a
>> LEFT OUTER JOIN asset_slices `as` on as.asset_slice_asset_id =
>> a.asset_id
>> LEFT OUTER JOIN asset_storage_schemes `astor` ON
>> astor.asset_store_id = as.asset_slice_store_scheme_id
>> WHERE a.asset_id = {bid}
>> ORDER BY asset_slice_row ASC""".format(bid = id))
>>
>> So I need that actual query to be executable via SQLAlchemy,
>> preferably with SQLSoup. It doesn't work right now.
>>
>
> These errors suggest operating within a transaction where an error has
> already occurred, and was swallowed. It's a general Python practice to
> allow exceptions to propagate and be reported as a general failure at some
> level where they are explicitly handled and at the very least logged. If
> you follow this procedure, and look for these errors occurring, you likely
> won't have mysterious exceptions complaining about the invalid state of a
> previous operation. There is also no way for anyone to help if you do
> not attach the full stack traces to your emails.
>
> I would also challenge your assertion that SQLAlchemy is adding hours to
> your development time - unless your application is extremely trivial, it
> is saving you dozens versus using raw DBAPI.
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
