Re: [sqlalchemy] Re: DetachedInstanceError
On Mon, 23 Feb 2015 07:19:34 -0800 (PST) Jonathan Vanasco jonat...@findmeon.com wrote: On Sunday, February 22, 2015 at 10:09:18 PM UTC-5, Ed Rahn wrote: Let mw know if you need anything else! What version of SqlAlchemy are you using? That may affect someone's answer. I was running the latest version in pip, .9 I think. I tried pulling from github a couple of nights again, and still getting the same thing, although a different error message. -- Ed Rahn edsr...@gmail.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] DetachedInstanceError
I am occassionally and randomly getting a DetachedInstanceError when I try to access an object atttribute that is a reference. I will run this several thousand times and only get it twice. What I don't understand is I use the same exact base query, which is where the reference takes place, and it works fine. Then I call a function which does some calculations and then it creeps up. I don't close or commit the sesssion or anything weird. I did some research about this error, and some possible causes are duplicate sessions and using the default expire_on_commit. I've updated my call to session_maker to set expire_on_commit to False. And although I'm using MultiProcessing and creating a new scopped_session for each instance, the only conclusion I can come to is this is causing the problem, but why only occassionally? The retreival of the problem object and all the proceding function calls happen in the same process, so I'm stuck looking for other solutions. I've tried calling session.merge() on the problem object and I have the same problem. So I'm wondering what could also cause a problem like this or what shouldn't I be doing that would cause this? There is a lot of code and it isn't anywhere public and I havn't been able to make a minimial example, but this is a snippet of the problem code: https://gist.github.com/edrahn/ec4b1b757313a0f5c3c3 And here is one of the tracebacks: https://gist.github.com/edrahn/bcc1aba82b3c70168a43 Let mw know if you need anything else! Thanks. -- Ed Rahn edsr...@gmail.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] idle in transaction
On 02/13/2015 11:30 PM, Michael Bayer wrote: Ed Rahn edsr...@gmail.com wrote: I have several programs that are Multi Process and long running, they open up 30 or so connections and do selects periodically. For a select query, the default behaviour is to begin a transaction if not currently in one, but not commit afterwards. This leaves a large number of postgresql processes with a status of idle in transaction. Which means they “lock” tables, so you can not drop them or alter them including add and drop indexes. I have also seen some problems were connections do not get closed if the connecting process exits, although I haven’t verified this is the cause. Is this a problem others have had in the past or am I just being overly worried? yeah that’s kind of bad. you want the connections to be “idle”, but definitely not “idle in transaction”. that will cause problems. If it is a problem is there any other way to fix it beside commit()’ing after each select query? I tried add an event handler after queries get run, but I didn't see a way to get to the current transaction. Well at least on the connection itself, if its used in non-autocommit mode (by which I refer to psycopg2’s autocommit flag), the “idle in transaction” will remain until either commit() or rollback() is called on that connection. So if you have a Session, and want to stay at that level, your options are to commit() it, to rollback() it, to close() it which returns the connection to the pool which does a connection-level rollback, or you could use the Session in autocommit=True mode, which means after each query it returns the connection to the pool for the same effect. I'd really like to not use autocommit mode. There are parts of the code that I need to maintain DB consistency with transactions. And I need to keep the objects attached to a session so automatically closing it isn't an option. So I guess my only option is to commit after each select, which seems like a lot of work as the code base is fairly large. This seems like a fairly common use case, do people just not care about it or how do they handle it? If you want to turn off transactions completely with the DBAPI connection itself, even though this overhead is very minimal for Postgresql you could set it to isolation level of AUTOCOMMIT which for psycopg2 sets the “autocommit” flag on the connection. The commit()/ rollback() calls from SQLAlchemy would have no effect. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] idle in transaction
I have several programs that are Multi Process and long running, they open up 30 or so connections and do selects periodically. For a select query, the default behaviour is to begin a transaction if not currently in one, but not commit afterwards. This leaves a large number of postgresql processes with a status of idle in transaction. Which means they lock tables, so you can not drop them or alter them including add and drop indexes. I have also seen some problems were connections do not get closed if the connecting process exits, although I haven’t verified this is the cause. Is this a problem others have had in the past or am I just being overly worried? If it is a problem is there any other way to fix it beside commit()'ing after each select query? I tried add an event handler after queries get run, but I didn't see a way to get to the current transaction. thanks Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Slow commit after large select
On 01/10/2015 04:04 PM, Michael Bayer wrote: Ed Rahn edsr...@gmail.com wrote: On 01/10/2015 01:51 PM, Jonathan Vanasco wrote: It's hard to tell what could cause the issue: How many horses are there? 463827 What is going on in that other function? Just a regex search, two selects and a commit Are there foreign key checks involved with the commit? Yep When I set up the DB to log all queries, I can see the commit happening in less than a second. So I'm assured it's somewhere between my function and the DB. If you want to break a huge batch into smaller commits, there is the `Windowed Range Query` strategy -- https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/WindowedRangeQuery That's kinda what I'm doing right now, but it just seems so hacky. I'd like to find a solution for the problem. From that page that's too large to fetch all at once, I have plenty of memory, how can I tell SQLA or psycopg2 to load all the results into memory? first off, psycopg2 loads everything into memory at once in all cases, as does the SQLAlchemy ORM, unless specific steps are taken to not do this.For 46 objects, this will be *very* slow, that’s a lot of rows to turn into fully persisted and session-tracked Python objects, and in Python even allocating the memory to hold them all will build up palpable time. However, this is all before that commit(), which is where you claim the slowness is. This seems doubtful, but if it really is in the commit() and not just the fetch, then something else would be going on. If you have 40 objects in memory, and they are all clean, session.commit() is not slow at all. Unless, you have some kind of event going on that is tracking them all, or you’re using a very old version of SQLAlchemy with mutable types, or something else that I could only guess (and I try not to spend time guessing on this list). So before you begin trying to solve a problem, it’s best to understand the nature of the problem, so I’d suggest walking through the steps in SQLAlchemy’s own frequently asked questions (as this is certainly a question that is frequently asked, make no mistake!) How can I profile a SQLAlchemy powered application?” at http://docs.sqlalchemy.org/en/rel_0_9/faq/performance.html#how-can-i-profile-a-sqlalchemy-powered-application. This is the very best list of steps to fully analyze where things are going wrong. In your case I think something is going to stand out under code profiling. It may very well be something SQLAlchemy is doing that is taking too long; if we identify it, we can talk about strategies to mitigate whatever it might be (there are always ways to mitigate). I didn't get a chance to look at this until now. However the commit after the select never returns, so I don't know why that changed. Profiling the code says that the most time is taken in _expire in state.py https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/state.py#L360 Where should I go from here? Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: make join at orm level without fk on sql definition level
On 01/14/2015 11:40 AM, Mehdi wrote: OK, I misunderstood the joining concept. if i do like below everything's fine: | session.query(MyTable).join(Given,Mytable.given_id==Given.gid).filter(Given.given.col_1 ==value) | But i wonder why sqlalchemy doesn't support query like this? | session.query(MyTable).filter(MyTable.relation1.col_a==value) | I think an orm should works more object-oriented and pythonic rather sql-ish! why i have to use join while i already define relation in my model? You can join a table in different ways, which need to be specified. You can join with less key strokes by: session.query(MyTable).join(Given) assuming you have the appropriate relationship's set up On Tuesday, January 13, 2015 at 10:05:38 PM UTC+3:30, Mehdi wrote: Hi I am looking for a solution to make two separate tables(at sql defenition level) query-able at orm level. There is a give database which i have no right to change existing tables and i just add two tables to it which one of my tables have a column holding id value of a table in give db: | classGiven(Base): __tablename__ =given_table gid =Column(Integer,primary_key=True) col_1 =... col_2 =... ... classMyTable(Base): __tablename__ =my_table id =Column(Integer,primary_key=True) given_id =Column(Integer) ... | I've managed to fill *'given_id'* column with proper *'gid'* based on some conditions. but with or without '*given_id'* column, i am looking for a way to have *'Given'* model joinable and query-able by sqlalchemy. i.e: | session.query(MyTable).filter(MyTable.given.col_1 ==value) | I'm not sure how could i use hybrid property or any other way for this situation. Please help me. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Slow commit after large select
On 01/10/2015 01:51 PM, Jonathan Vanasco wrote: It's hard to tell what could cause the issue: How many horses are there? 463827 What is going on in that other function? Just a regex search, two selects and a commit Are there foreign key checks involved with the commit? Yep When I set up the DB to log all queries, I can see the commit happening in less than a second. So I'm assured it's somewhere between my function and the DB. If you want to break a huge batch into smaller commits, there is the `Windowed Range Query` strategy -- https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/WindowedRangeQuery That's kinda what I'm doing right now, but it just seems so hacky. I'd like to find a solution for the problem. From that page that's too large to fetch all at once, I have plenty of memory, how can I tell SQLA or psycopg2 to load all the results into memory? Thanks Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Slow commit after large select
I have the following bit of code: horses = (data.session.query(data.Horse). filter(or_(data.Horse.sire == None, data.Horse.dam == None)). filter(data.Horse.skip_pedigree == False, data.Horse.foaled != None)) data.session.commit() for horse in horses: print horse dl_pedigree_name(horse) the commit()s in dl_pedigree_name() take upwards of 9 seconds, but if I run the same query but with [:100]: horses = (data.session.query(data.Horse). filter(or_(data.Horse.sire == None, data.Horse.dam == None)). filter(data.Horse.skip_pedigree == False, data.Horse.foaled != None))[:100] they take less than a second. Any ideas why and what I can do about it? thanks Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] relationship to self
I have the following code:
class Horse(Base):
__tablename__ = 'horse'
id = Column(Integer, primary_key=True)
name = Column(String(150), index=True, unique=True)
color = Column(Integer)
sex = Column(Integer)
foaled = Column(Date)
skip = Column(Boolean, default=False)
sire_id = Column(Integer, ForeignKey('horse.id'), index=True)
sire = relationship('Horse', foreign_keys=[sire_id])
dam_id = Column(Integer, ForeignKey('horse.id'), index=True)
dam = relationship('Horse', foreign_keys=[dam_id])
state_born_id = Column(Integer, ForeignKey('state.id'), index=True)
race_entries = relationship(RaceEntry, backref='horse',
cascade_backrefs=False)
claims = relationship(ClaimedHorse, backref='horse',
cascade_backrefs=False)
I am trying to have sire and dam link to Horse
I get when I run and assign to sire or dam:
TypeError: Incompatible collection type: Horse is not list-like
What's the correct way to do this?
thanks,
Ed
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: SQLAlchemy: Table creation before data insertion?
take your Base object and Base.metadata.create_all(engine) On 12/08/2014 01:25 AM, SF Markus Elfring wrote: http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.MetaData.create_all I do not want to create meta-data in my use case explicitly. Should the class library handle that for me automatically because of a derivation from declarative_base()? How can this automatism be informed to activate the desired tables immediately? Regards, Markus -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Handling big Python objects
What do you want to be able to do with the objects? I'd recommed storing them as files and then referencing the filename in the database. Ed On 12/03/2014 04:18 PM, Andrea Gavana wrote: Hello list, sorry for the possibly noob question, I've googled around without much success looking for an answer. Basically, I am given a series of this huge Python class (a Simulation object), which contains an enormous amount of information - when I cPickle it (with highest protocol), it can result to files 200-250 MB in size, although rarely it can get up to 2 GB. I am looking for intelligent ways to store these objects into a database. I have to say that I don't have that much control on this Simulation class, so I can't change its internal structure - I'm just looking for a better alternative to what I am doing. So, what I am doing now is basically storing this huge object as a string. I have these two methods: import cPickle import zlib import base64 def serialize(my_simulation): my_db_object = base64.b64encode(zlib.compress(cPickle.dumps(obj, cPickle.HIGHEST_PROTOCOL))) return my_db_object def deserialize(my_db_object): my_simulation = cPickle.loads(zlib.decompress(base64.b64decode(my_db_object))) return simulation I can use them to store/retrieve this big Python classes to/from the database, but I feel it's not a particularly effective way to handle this problem. I've tried to get my head around BLOBs and LargeBinary stuff, but I'm not sure I'm on the right path here. I appreciate any suggestion on how to approach the problem, to make the storing/retrieving of these objects a bit less time/memory consuming (especially time). On a related note, I am now using MySQL as a backend - but I am open to suggestions about possible alternatives that may make this problem more tractable: I have some difficulties in installing other backends (like PostgreSQL or psycopg2), but I know I can use Access and Oracle backends. I know that Access is not particularly famous in the SQLAlchemy world. Of course, if any other backend has advantages over MySQL in dealing with this issue, I may try to convince IT to get it installed on our machines. All suggestions and comments are most welcome. Thank you in advance for your help. Andrea. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
