Re: [sqlalchemy] Querying mssql time columns
Thanks Michael, you're correct I'm using pyodbc with FreeTDS, which doesn't recognize the time datatype (and it seems FreeTDS doesn't support anything above SQLServer 2005). And you're right, this is not a SQLAlchemy issue. Sorry about that. I guess I can work around this by using time.isoformat() in filter constraints. Hope that's portable to windows! Thanks (again) for your help--this is the second time you've helped me out here. Much appreciated. Simon. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/pyeq1WoO18IJ. 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.
[sqlalchemy] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
Hi all, I'm seeing this error intermittently in my application since switching to MySQL - it occurs on several different pages and I can't track down why: StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) 'SELECT [... the same select every time, which gets a user from the database] I have searched on the error and read the various discussions, for example this one: http://groups.google.com/group/sqlalchemy/browse_thread/thread/a8031eefc4d5d0cd My understanding is that somewhere in my code I need to be ensuring that I do rollback/commit/close when the operation is complete. The problem is... where? Is there a way I can get a log to see the original query/point in my code which triggers the error. If it is relevant, I'm using Pyramid and have recently also suffered from 'MySQL has gone away errors'. Thanks, Ben [also described this problem on the Pyramid list, apologies to those of you that subscribe to both] -- 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.
Re: [sqlalchemy] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
On Oct 31, 2011, at 8:45 AM, Benjamin Sims wrote: Hi all, I'm seeing this error intermittently in my application since switching to MySQL - it occurs on several different pages and I can't track down why: StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) 'SELECT [... the same select every time, which gets a user from the database] how are you handling threading, as well as Session lifecycle ? this kind of error usually occurs from one of two things: a. sharing Sessions, or objects attached to them, between threads or b. no clear point of Session start/end, incorrect handling of exceptions, etc. I have searched on the error and read the various discussions, for example this one: http://groups.google.com/group/sqlalchemy/browse_thread/thread/a8031eefc4d5d0cd My understanding is that somewhere in my code I need to be ensuring that I do rollback/commit/close when the operation is complete. The problem is... where? Is there a way I can get a log to see the original query/point in my code which triggers the error. sure you should turn on SQLAlchemy logging fully (http://www.sqlalchemy.org/docs/core/engines.html?highlight=logging#configuring-logging) , and also make sure you aren't squashing exceptions (i.e. except MyException, e: pass type of thing) - also you'd want to add code such as log.error(exception occurred, exc_info=True) to the point at which exceptions are caught so that a full stack trace is written out to the log. -- 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.
Re: [sqlalchemy] Problem/bug with column_property on eagerloaded polymophic table
Thank you, very much. I actually did try to use the actually Column, but I could figure out how to resolve my interdependencies since my column_property is actually a subselect, and apparently I didn't test it on my test case. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/l3wBUQTi7jMJ. 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.
Re: [sqlalchemy] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
I suspect that my understanding of both threading and Sessions is going to be found pretty wanting here; I've basically just lifted things from Pyramid/SQLAlchemy examples. My understanding is that the framework handles threading, and that based on examples and http://www.sqlalchemy.org/docs/orm/session.html#contextual-thread-local-sessionsthis is the correct way to create a session available throughout the request: DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) (this is in my models file) I then use this either by doing: DBSession.query() or from models import DBSession session = DBSession() session.query() So, I'm not doing anything explicit in turns of ending the session - I guess I should be, but I had understood that this would happen on issue of next query/completion of request. Thanks, I'll look into it - I was used to Paster, where I could see the logs in real time. I now get errors in the Apache error log... but not whatever is initially causing the one I can see. Ben PS: I do have a separate process that runs in a script via cron and uses the database which would seem like a candidate, but disabling that does not stop the errors On 31 October 2011 14:38, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 31, 2011, at 8:45 AM, Benjamin Sims wrote: Hi all, I'm seeing this error intermittently in my application since switching to MySQL - it occurs on several different pages and I can't track down why: StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) 'SELECT [... the same select every time, which gets a user from the database] how are you handling threading, as well as Session lifecycle ? this kind of error usually occurs from one of two things: a. sharing Sessions, or objects attached to them, between threads or b. no clear point of Session start/end, incorrect handling of exceptions, etc. I have searched on the error and read the various discussions, for example this one: http://groups.google.com/group/sqlalchemy/browse_thread/thread/a8031eefc4d5d0cd My understanding is that somewhere in my code I need to be ensuring that I do rollback/commit/close when the operation is complete. The problem is... where? Is there a way I can get a log to see the original query/point in my code which triggers the error. sure you should turn on SQLAlchemy logging fully ( http://www.sqlalchemy.org/docs/core/engines.html?highlight=logging#configuring-logging) , and also make sure you aren't squashing exceptions (i.e. except MyException, e: pass type of thing) - also you'd want to add code such as log.error(exception occurred, exc_info=True) to the point at which exceptions are caught so that a full stack trace is written out to the log. -- 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. -- 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.
Re: [sqlalchemy] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
On Oct 31, 2011, at 12:56 PM, Benjamin Sims wrote: I suspect that my understanding of both threading and Sessions is going to be found pretty wanting here; I've basically just lifted things from Pyramid/SQLAlchemy examples. My understanding is that the framework handles threading, and that based on examples and http://www.sqlalchemy.org/docs/orm/session.html#contextual-thread-local-sessions this is the correct way to create a session available throughout the request: DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) (this is in my models file) I then use this either by doing: DBSession.query() or from models import DBSession session = DBSession() session.query() So, I'm not doing anything explicit in turns of ending the session - I guess I should be, but I had understood that this would happen on issue of next query/completion of request. that's a question for the Pyramid folks. There should also be a single path for handling of exceptions - any unexpected exception would propagate to the same point, there's a Session.rollback() right there, and it gets reported. -- 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.
[sqlalchemy] Re: Setup to best support multi-threads
I'm getting the error sqlalchemy.exc.ProgrammingError: (ProgrammingError)
SQLite objects created in a thread can only be used in that same thread.The
object was created in thread id 5808 and this is thread id 7936 None None
with my current setup, I'm not sure what I've done wrong.
I set up this little test to see if I could write to the same table from
multiple threads. the table has 3 columns all of type int.
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session,sessionmaker
from sqlalchemy.ext.declarative import declarative_base
db_engine= create_engine(sqlite:///database.db),echo=True)
Base= declarative_base(db_engine)
class Failures(Base):
__tablename__= failures
__table_args__= {autoload:True}
def __repr__(self):
return Failures('%s','%s','%s') %(self.Id,self.action,self.reason)
metadata= Base.metadata
Session= scoped_session(sessionmaker(bind=db_engine))
class TestWriteToDB(threading.Thread):
def __init__(self,start):
threading.Thread.__init__(self)
self.session= Session()
self.insert_list=[]
for i in range(start,start+10):
f=Failures(resourceId=i,action=i,reason=i)
self.insert_list.append(f)
def run(self):
self.session.add_all(self.insert_list)
self.session.commit()
if __name__ == __main__:
for i in range(1,40,10):
t=TestWriteToDB(i)
t.start()
--
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.
Re: [sqlalchemy] Re: Setup to best support multi-threads
a little programming puzzle.The scoped_session() is a thread local registry
which links a Session to the current thread. When you say self.session =
Session(), you're invoking the registry, generating a Session local to the
current thread, then assigning it to a local variable.In fact all four
TestWriteToDB objects get a hold of the same Session object. Then they enter
run() within a new thread and all begin using the same session.
So you'd need to either use the proxy pattern provided by the scoped_session,
that is just call Session.add_all(), or if you want to assign the local Session
object do it in the thread.
On Oct 31, 2011, at 4:24 PM, Paul wrote:
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session,sessionmaker
from sqlalchemy.ext.declarative import declarative_base
db_engine= create_engine(sqlite:///database.db),echo=True)
Base= declarative_base(db_engine)
class Failures(Base):
__tablename__= failures
__table_args__= {autoload:True}
def __repr__(self):
return Failures('%s','%s','%s') %(self.Id,self.action,self.reason)
metadata= Base.metadata
Session= scoped_session(sessionmaker(bind=db_engine))
class TestWriteToDB(threading.Thread):
def __init__(self,start):
threading.Thread.__init__(self)
self.session= Session()
self.insert_list=[]
for i in range(start,start+10):
f=Failures(resourceId=i,action=i,reason=i)
self.insert_list.append(f)
def run(self):
self.session.add_all(self.insert_list)
self.session.commit()
if __name__ == __main__:
for i in range(1,40,10):
t=TestWriteToDB(i)
t.start()
--
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.
