[sqlalchemy] Re: foreign key problem when using reflection and schemas
jason kirtland wrote: That should be working now in r5203. The reflection code was missing an edge case where an explicit schema= is the same as the connection's schema. Switching those to schema=None should work as intended if you need a workaround on a released version. Thanks a lot for the quick fix, it seems to work for me! Working with the trunk is all right for now. Regards, Martijn --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: InvalidRequestError and ConcurrentModification problems
the message you're getting is due to an exception being raised,but rollback() not being called on the session. When you catch those ConcurrentModification exceptions, you have to issue a rollback(). I dont see anything else with the code that would suggest the same row being deleted in two places, although it does seem like theres likely a more succinct way of structuring that code, and I also dont see the purpose that the session.expire_all() accomplishes. Things missing here include what scope is the session managed under (i.e. where is it created/destroyed/shared among threads) as well as what other concurrency exists within this section of the application. On Oct 27, 2008, at 9:15 PM, Doug Farrell wrote: Hi all, I'm using Python 2.5.1 with SqlAlchemy 0.5rc2 with Sqlite on Windows Server 2003 and I'm having a problem with my application throwing InvalidRequestError and ConcurrentModification exceptions. Here is my simplified declarative class: class Job(srsmanagerdb.Base): STATUS_INIT = 0 STATUS_RUN = 1 STATUS_DONE = 2 STATUS_FAIL = 3 __tablename__ = jobs id= Column(Integer, primary_key=True, autoincrement=True) nas = Column(String(12), default=None) filename = Column(String(64), default=None, index=True) filesize = Column(Integer, default=None) created = Column(DateTime, default=None) job_id= Column(String(32), default=None) productType = Column(String(1), default=None) contentType = Column(String(10), default=None) priorityType = Column(String(10), default=None) priority = Column(Integer, default=None) assignedPress = Column(Integer, default=None) status= Column(Integer, default=None) def __init__(self, objrefs, fileDetails): nas, filename, filesize, ctime = fileDetails self.nas = nas self.filename = filename self.filesize = filesize self.created = datetime.datetime(*time.strptime(ctime[:ctime.find(.)], %Y-%m-%d %H:%M:%S)[0:6]) This object is used to track state information about jobs being handled by a looping state machine. I keep a list of all active jobs in a Jobs collection class, so there are many active intances of the above class. The simplified Jobs collection class looks like this: class Jobs(AppContext): def __init__(self, objrefs): self._logger = __logger__ self._jobs = [] self._markedForRemoval = [] def markForRemoval(self, job): self._markedForRemoval.append(job) def removeMarkedJobs(self): # throws exception in here session = srsmanagerdb.Session() for markedJob in self._markedForRemoval: try: session.expire_all() session.delete(markedJob) session.commit() self._jobs.remove(markedJob) except sqlalchemy.exceptions.ConcurrentModificationError, e: self._logger.warn(%s threw exception %s % (job.filename, e)) self._markedForRemoval = [] def process(self): for job for self._jobs: job.process() if job.status == Job.STATUS_DONE: self.markForRemoval(job) self.removeMarkedJobs() The above simplified code runs for awhile (10s of minutes) with hundreds of jobs and then it throws the exception below in the removeMarkedJobs() method. I've worked really hard trying to figure out what's going wrong here. This is the only place where I delete jobs and commit that delete to the database. One question I have is if it's a good idea to keep a list of active Job instances (database rows) in a Python list. In my removeMarkedJobs() I'm deleting the job instances, and then removing the job instance from the list. Is this necessary or good practice? I haven't figured out if just deleting the job instance from the list (self._jobs.remove(markedJob)) will also delete the job from the database or not. Anyway, here's the traceback of the exception I'm getting. Any help would be appreciated. Thanks, Doug 2008-10-27 18:15:54 srsmanager ERRORunexpected error, restarting: Traceback (most recent call last): File c:\cygwin\home\ripadmin\dev\srsmanager\srsprocess.py, line 154, in runjobs isActive = self._jobs.process() File c:\cygwin\home\ripadmin\dev\srsmanager\jobs.py, line 436, in process self.removeMarkedJobs() File c:\cygwin\home\ripadmin\dev\srsmanager\jobs.py, line 397, in removeMarkedJobs self._logger.warn(%s threw exception %s % (markedJob.filename, e)) File c:\python\2.5\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg \sqlalchem y\orm\attributes.py, line 135, in __get__ return self.impl.get(instance_state(instance)) File c:\python\2.5\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg \sqlalchem y\orm\attributes.py, line 327, in get value = callable_() File
[sqlalchemy] Re: Declarative application example
On Oct 28, 2008, at 9:32 AM, writeson wrote: Hi everyone, I'm struggling to try and use SqlAlchemy (0.5rc2) with a project I'm working on. The database part isn't very hard, it's just a flat table, but I keep running into problems with SA throwing exceptions. The SA documentation is good, but as of yet I haven't gotten the ah ha moment of how to put it all together. What I need to see is a simple, complete application example that would show me how the pieces fit together as a whole. In particular I'm looking for things like this: 1)When and how to update the database when modifying a Class instance. the ORM tutorial steps through this process fairly methodically, and touches upon the full lifecycle of an object. Also a good read of the session chapter explains exactly what the Session is doing. 0.5's default session configuration follows a model whereby you generally don't have to deal with anything except add/ delete/commit. 2)Can database object instances be maintained in a Python list, or should they be handled one at a time? there's no restriction on how mapped instances are structured or persisted. 3)Does deleting a database object instance (del obj) delete the row from the database, or should session.delete(obj) be called first and then del obj? del obj just dereferences the variable named obj from the local namespace in the Python process.If no further references remain on the object, it will be garbage collected, including from SQLA's Session. This does not communicate a database delete operation to the session.SQLAlchemy records an object for pending deletion using Session.delete(obj). If you issue this call, the Session temporarily creates a strong reference to the object's mapped information so that it stays in scope, at least within the Session, until the transaction commits. 4)Is it possible to apply a Python __cmp__() method to a list of database objects, or should SA order by operations be used instead? either approach is feasable depending on the situation --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Declarative application example
Michael, Thanks for your response. I'll take another look at the ORM tutorial and the Session chapter now that I've got a little experience and see if some lights go on. Your text about deleting instances matches my understanding of how things work, but it's good to get confirmation and that I'm not missing something. I'm guessing the Session having a strong reference keeps the object alive even though I delete it from the list. Again, thanks! Doug -Original Message- From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Michael Bayer Sent: Tuesday, October 28, 2008 10:30 AM To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Declarative application example On Oct 28, 2008, at 9:32 AM, writeson wrote: Hi everyone, I'm struggling to try and use SqlAlchemy (0.5rc2) with a project I'm working on. The database part isn't very hard, it's just a flat table, but I keep running into problems with SA throwing exceptions. The SA documentation is good, but as of yet I haven't gotten the ah ha moment of how to put it all together. What I need to see is a simple, complete application example that would show me how the pieces fit together as a whole. In particular I'm looking for things like this: 1)When and how to update the database when modifying a Class instance. the ORM tutorial steps through this process fairly methodically, and touches upon the full lifecycle of an object. Also a good read of the session chapter explains exactly what the Session is doing. 0.5's default session configuration follows a model whereby you generally don't have to deal with anything except add/ delete/commit. 2)Can database object instances be maintained in a Python list, or should they be handled one at a time? there's no restriction on how mapped instances are structured or persisted. 3)Does deleting a database object instance (del obj) delete the row from the database, or should session.delete(obj) be called first and then del obj? del obj just dereferences the variable named obj from the local namespace in the Python process.If no further references remain on the object, it will be garbage collected, including from SQLA's Session. This does not communicate a database delete operation to the session.SQLAlchemy records an object for pending deletion using Session.delete(obj). If you issue this call, the Session temporarily creates a strong reference to the object's mapped information so that it stays in scope, at least within the Session, until the transaction commits. 4)Is it possible to apply a Python __cmp__() method to a list of database objects, or should SA order by operations be used instead? either approach is feasable depending on the situation 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Noob WARNING, sqlalchemy for Desktop Apps?
I am just learning the basics of Sqlalchemy, I am reading through the O'Reilly book. I was just wondering if anyone is using Sqlalchemy for desktop applications? Does anyone know of specific problems I might have doing so? Thanks in advance-Patrick --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Can SQLAlchemy use existing db connections?
On Oct 28, 2008, at 12:09 PM, Felix Schwarz wrote: Hi, I develop an extension for the well known Trac. trac itself has an extremly simple db 'layer' for creating tables and the like but all sql queries must be generated manually. This sucks - however, if you only have simple queries, it still works. But now I'm debugging a somewhat bigger query with 3-4 joins, some unions and multiple sub-selects. This is really the point where I should start with SQLAlchemy :-) Now I have an open db connection which is managed by trac. Can I re- use this connection for my SQLAlchemy needs? In the beginning, I don't want to use the declarative layer or anything. No transaction control etc. sqlalchemy would want the connection to be involved in an Engine() somehow. If there's just one connection for the whole thing, you'd put it into a creator() such as: from sqlalchemy import create_engine from sqlalchemy.pool import StaticPool e = create_engine('postgres://', creator = lambda: my_trac_connection, poolclass=StaticPool) the StaticPool means the SQLA pool wont be involved in creating new connections or closing any of them, it just calls the creator once and thats it. that approach above is generally only as threadsafe as the original connection object itself. SQLA also likes the engine to be created just once per application. So if trac has more of an opening/closing connections thing going on as opposed to just one per-process connection, you'd want to wire into that, which may be easiest by writing a custom pool class. you only need to create one or two methods. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: InvalidRequestError and ConcurrentModification problems
Michael, I'm not sure why the ConcurrentModification is occurring at all, my application doesn't use threads. I'll look into issuing the rollback() call and see how that helps my app. I'm sure there are better ways to structure my code and use of SA, this is the result of prototyping along in an effort to learn, understand and use SA. The session.expire_all() was injected recently as the result of reading some stuff during a Google Search and just casting about to solve the thrown exceptions. I also had a job = session.merge(job) in there at one time. I have one module called managerdb.py where I create the engine and the first session object. All other modules reference this module to create their own session objects. By the way, with locally scoped session variables should I do a session.close() or not? Thanks, Doug -Original Message- From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Michael Bayer Sent: Tuesday, October 28, 2008 10:20 AM To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: InvalidRequestError and ConcurrentModification problems the message you're getting is due to an exception being raised,but rollback() not being called on the session. When you catch those ConcurrentModification exceptions, you have to issue a rollback(). I dont see anything else with the code that would suggest the same row being deleted in two places, although it does seem like theres likely a more succinct way of structuring that code, and I also dont see the purpose that the session.expire_all() accomplishes. Things missing here include what scope is the session managed under (i.e. where is it created/destroyed/shared among threads) as well as what other concurrency exists within this section of the application. On Oct 27, 2008, at 9:15 PM, Doug Farrell wrote: Hi all, I'm using Python 2.5.1 with SqlAlchemy 0.5rc2 with Sqlite on Windows Server 2003 and I'm having a problem with my application throwing InvalidRequestError and ConcurrentModification exceptions. Here is my simplified declarative class: class Job(srsmanagerdb.Base): STATUS_INIT = 0 STATUS_RUN = 1 STATUS_DONE = 2 STATUS_FAIL = 3 __tablename__ = jobs id= Column(Integer, primary_key=True, autoincrement=True) nas = Column(String(12), default=None) filename = Column(String(64), default=None, index=True) filesize = Column(Integer, default=None) created = Column(DateTime, default=None) job_id= Column(String(32), default=None) productType = Column(String(1), default=None) contentType = Column(String(10), default=None) priorityType = Column(String(10), default=None) priority = Column(Integer, default=None) assignedPress = Column(Integer, default=None) status= Column(Integer, default=None) def __init__(self, objrefs, fileDetails): nas, filename, filesize, ctime = fileDetails self.nas = nas self.filename = filename self.filesize = filesize self.created = datetime.datetime(*time.strptime(ctime[:ctime.find(.)], %Y-%m-%d %H:%M:%S)[0:6]) This object is used to track state information about jobs being handled by a looping state machine. I keep a list of all active jobs in a Jobs collection class, so there are many active intances of the above class. The simplified Jobs collection class looks like this: class Jobs(AppContext): def __init__(self, objrefs): self._logger = __logger__ self._jobs = [] self._markedForRemoval = [] def markForRemoval(self, job): self._markedForRemoval.append(job) def removeMarkedJobs(self): # throws exception in here session = srsmanagerdb.Session() for markedJob in self._markedForRemoval: try: session.expire_all() session.delete(markedJob) session.commit() self._jobs.remove(markedJob) except sqlalchemy.exceptions.ConcurrentModificationError, e: self._logger.warn(%s threw exception %s % (job.filename, e)) self._markedForRemoval = [] def process(self): for job for self._jobs: job.process() if job.status == Job.STATUS_DONE: self.markForRemoval(job) self.removeMarkedJobs() The above simplified code runs for awhile (10s of minutes) with hundreds of jobs and then it throws the exception below in the removeMarkedJobs() method. I've worked really hard trying to figure out what's going wrong here. This is the only place where I delete jobs and commit that delete to the database. One question I have is if it's a good idea to keep a list of active Job instances (database rows) in a Python list. In my removeMarkedJobs() I'm deleting the job instances, and then
[sqlalchemy] Re: InvalidRequestError and ConcurrentModification problems
On Oct 28, 2008, at 1:57 PM, Doug Farrell wrote: Michael, I'm not sure why the ConcurrentModification is occurring at all, my application doesn't use threads. I'll look into issuing the rollback() call and see how that helps my app. the concurrent ness of the error only means two different actors deleted the same row. they can be in the same thread. this module to create their own session objects. By the way, with locally scoped session variables should I do a session.close() or not? sessions close themselves implicitly when they fall out of scope. close() is useful just to ensure the connection resources and objects are freed immediately. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: column name capitalization in mysql
Corrected in r5204. http://www.sqlalchemy.org/trac/changeset/5204 Ticket details: http://www.sqlalchemy.org/trac/ticket/1206 -Tom H On Oct 27, 4:35 pm, Tom H [EMAIL PROTECTED] wrote: Thanks Michael, I posted a new ticket at:http://www.sqlalchemy.org/trac/ticket/1206 Best Regards, -Tom H On Oct 27, 4:13 pm, Empty [EMAIL PROTECTED] wrote: Tom, On Mon, Oct 27, 2008 at 4:03 PM, Tom H [EMAIL PROTECTED] wrote: I'm experiencing the following problem with SA 0.4.7 The table has the following columns: id - int primary key DOB - varchar(25) ... extra columns not needed for report It's a table from legacy app that I'm accessing from SA to generate reports, etc. I'm having the table definition auto load (reflected from the database) like this: myTable = Table('sample_table', metadata, autoload=True, include_columns=['id', 'DOB']) When I later retreive data, the DOB column is not reflected. If I change 'DOB' to 'dob' for the include_columns list, the column is reflected as 'DOB' ... strange that the reflected name is capitalized, but when I specify all caps for include_columns the field is not reflected. Any recommendations or suggestions for dealing with this issue? The problem is related to this bit of code.: if only and name.lower() not in only: self.logger.info(Omitting reflected column %s.%s % (table.name, name)) return If you would please submit a ticket we can get it fixed. Thanks, Michael --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Noob WARNING, sqlalchemy for Desktop Apps?
Checkout is built on SQLAlchemy (see http://www.sqlalchemy.org/news.html). Maybe the authors want to share some insight. On 28 Okt., 17:12, Patrick [EMAIL PROTECTED] wrote: I am just learning the basics of Sqlalchemy, I am reading through the O'Reilly book. I was just wondering if anyone is using Sqlalchemy for desktop applications? Does anyone know of specific problems I might have doing so? Thanks in advance-Patrick --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---