[sqlalchemy] Re: Working with large IN lists
This depends upon the execution plan of the query and is more really a
postgresql question. Google postgresql IN performance and you will get
a good idea of it.
By the look of your code, Second option would obviously be faster as
it hits database once whereas first one flush after every change.
Regards,
On Feb 21, 6:07 pm, Vlad K. v...@haronmedia.com wrote:
Hi all,
I have to read thousands of rows from a table and compile some data, but
in certain conditions update those rows, all with same value. The ratio
of reads and writes here is widest possible. Sometimes no rows,
sometimes few, and sometimes all rows that are read have to be updated.
The last case scenario is making me concerned.
for row in query.yield_per(100):
# Do something with data
if some_condition:
row.some_column = 123
session.flush()
I am thinking about just adding the row's ID to a list:
list_of_ids = []
for row in query.yield_per(100):
# Do something with data
if some_condition:
list_of_ids.append(row.primary_key)
and near the end of transaction do:
session.query(Model).filter(Model.primary_key.in_(list_of_ids)).update({so
me_column
: 123}, False)
Yes I'm aware of increased memory requirements to store the ID list on
the application side, and no I don't need to lock the rows for update,
the logic of atomic update at the end is sufficient for my case.
But I think, and the real use benchmarks will probably show, I haven't
tested yet, that single update query will work faster. I need lowest
transaction processing time on the application side for entire call,
even if takes more memory and more database iron.
What I'm concerned with here is if there are any limits or significant
overheads with large .in_ lists?
The backend is PostgreSQL via psycopg2.
Thanks
--
.oO V Oo.
--
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] Usage of begin nested
My use case requires that if insertion of object of Myclass succeeds then insert Object of Myclass1 . Even if inserting of Myclass1 object fails insertion of Myclass should not be rolled back. I mean adding Myclass is permanent and does not depend on failure or success of insertion of Myclass1. I have written following code and want to know if am understanding right usage of begin_nested and not writing buggy code. I am using postgresql 9.0 try: obj =Myclass() session.add(obj) if condition true: session.begin_nested() try: n = Myclass1(arguments) session.add(n) except SQLAlchemyError: db_session.rollback() session.commit() except SQLAlchemyError: session.rollback() raise Code is running f9, just want to make sure of some unknown gotcha in this code. Other option will be I commit after adding Myclass and perform insertion of Myclass1 in separate transaction but this way is not appealing to me. Regards, Manav Goel -- 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: Understanding sqlalchemy memory usage and releasing unused memory
Hey Thanks for the answer even after my half researched questions which I should not have done. :) Yeah I realised that after posting the question and I dig deep into python memory issues and have improved knowledge a lot. In fact objgraph module is a great one for understanding these issues. It helped in understanding sqlalchemy a lot by drawing great detailed graphs. On Feb 7, 3:18 pm, Gunnlaugur Briem gunnlau...@gmail.com wrote: Hi Manav, the final question did touch SQLAlchemy: the one about expiring and expunging. 1. expiring an object creates a weak reference - this is inaccurate. All object references held by the the session (except for objects whose addition, deletion or attribute change is not yet flushed) are weak references in the first place, seehttp://docs.sqlalchemy.org/en/latest/orm/session.html#session-attributes and expiring an object just marks its attributes out of date, it does not change the session's reference to the object itself 2. Does expunging an object do the same - expunging an object means the session no longer holds a reference (weak or otherwise) to that object. But the reference was weak in the first place (unless the object was in new, dirty or deleted), so expunging neither helps nor hurts in getting the object collected. Your other questions do not involve SQLAlchemy, and that's why nobody here is answering them. You might find some other group where questions about python basics and process memory management are in scope ... but the One True Way to learn these things is to grit your teeth and google and read. That may be more work than you were hoping, but such is life. :) Also, this talk is good:http://blip.tv/pycon-us-videos-2009-2010-2011/pycon-2011-dude-where-s... - Gulli -- 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] Understanding sqlalchemy memory usage and releasing unused memory
I wanted to get idea about how much memory my sqlalchemy objects are taking. I used pympler library and used its class tracking function. I am using declarative base for defining the model and hence tables. I am pretty new to web development and python in general. So some questions may not be entirely due to sqlalchemy but due to my less knowledge about python. My User model which contain 13 column attributes, 1 association proxy for many to many connections and 6 one to many attributes set using lazy='dynamic'. When I tracked its memory usage only 6 attributes were loaded as others were set deffered and memory used was 1.6 mb. I tried to do rough calculations and thought if one object was taking this much then loading 100 will take 160mb. Then I created 50 user objects and then tracked memory and to my surprise total memory consumed was only 2 mb! What is the reason of this?? Are this objects sharing some base which is is around 1.5 mb and actual object is just in kbs? I tracked another type of object defined using 5 fields took only about 30kb and another object containing only 2 fields was 90 kb. Why is this different behavior in sizes? Reading about memory usage and sqlalchemy in this group and other places that once python process accumulates memory then they release only once closed I am little confused. Does it mean when I have loaded 100 objects and their usage is over, after that its memory will not be released back to system?? Final question expiring an object creates a weak reference and can be collected by gc if not used. Does expunging an object does the same? mainly how can i make memory taken by object free after usage is over. Thas quite a lot of questions. Thank you for reading. Regards, Manav Goel -- 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] and_(condition1,condition2) or condition1 and condition2
Hello I noticed that in filter method I can use either and_(condition1,condition2) or condition1 and condition2 But I searched and did not find any mention about second way anywhere. I want to know if both options are equal or there some catch in using second option. Regards, Manav Goel -- 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] Strange behavior from Association object in many to many relationships
Hi I created a many to many relation between two tables as shown in the tutorial using Association object as I have extra fields in connecting table. I have table User(user_id), Book(book_id) and UserBook(user_id, book_id) This code gave me Integrity error of UserBook.user_id of being null. Basically it is not user id of user automatically to UserBook object : def con(user, mybook): ub = UserBook() ub.book = mybook user.reads.append(ub) session.commit() But this code worked : def con(user, mybook): ub = UserBook() ub.book = mybook ub.user = user user.reads.append(ub) session.commit() Why the first one is not working? Regards, -- 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] convert_unicode option when using postgre and psycopg2
Hi I am using sqlalchemy with postgresql and psycopg2. I want to write unicode compatible web application. I noticed that when I pass string data to sqlalchemy string column, its get converted to unicode after commit. So does setting convert_unicode option True has any meaning in this combination of postgre and psycopg2? If it converts str to unicode in any case would it be useful to use only unicode from start? I mean assign u prefix before passing normal english string. Regards, Manav Goel -- 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] Cant understand behavior of sqlalchemy while adding same object twice in a session
Hello,
I am working in eclipse IDE.
I have this code in the file :
class User(Base):
__tablename__ = 'users'
id = Column('user_id',Integer,primary_key = True)
name = Column('user_name',String(20))
addresses = relationship(Address)
def __repr__(self):
return User(%s) % self.name
session = Session(bind=engine)
u=User('myname')
session.add(u)
session.flush()
print u.id
I ran this file and it worked fine but then I changed
something.
Now to see the effect I have to run file again and then I
noticed this
Instead of adding two different objects with two different
ids but same name. It simply updated the id of already existing object.
Now I have two questions :
1. What is the reason of this behavior? First of all it should have
made two different objects . But I think it did not do that because it thought
this object is similar to existing one. In that case it should have raised
exception instead of simply my primary key.
2. WHat happened when I ran the file again? It created a brand new
session or continued the previous one?
3. Due to problem mentioned in point no 1. it wasted my early id nos as
when I committed it saved the last one?
I know there is something that I am missing here as I am just a beginner in
sqlalchemy.
Thanks in advance.
Regards,
Manav Goel
--
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] Declarative Mapping vs Classic Mapping
My question is regarding sqlalchemy version 0.7.2. Are there any limitations in using declarative or classic mapping while using sqlalchemy? My main concern is there any limitation of declarative mapping which can put me in some situation where I am stuck with the tables it will create? Also what about table schema changes, If I want to add a column or remove a column from table I simply add or remove that attribute in declarative class? I have completed my table design and thinking to create database in one go using declarative mapping. -- 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] Declarative Mapping vs Classic Mapping
Thanks for the quick reply. One question here, suppose I add column using Alter Table command and then in class declaration the attribute. Will it see the newly added column then or not? Concern is not issuing ALTER command by hand but is the class can see the newly added column. Regards, Manav Goel On 17-Oct-2011, at 11:06 PM, Michael Bayer wrote: On Oct 17, 2011, at 1:05 PM, Manav Goel wrote: My question is regarding sqlalchemy version 0.7.2. Are there any limitations in using declarative or classic mapping while using sqlalchemy? there's not, a declarative mapping is nothing more than a small organizing layer on top of the classical system of class + Table + mapper() - all three elements are used in the same way. My main concern is there any limitation of declarative mapping which can put me in some situation where I am stuck with the tables it will create? Declarative allows the full range of table specification that plain Table() allows, and additionally you can use the Table construct directly with any declarative class (I use this style in my current project) as demonstrated at http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#using-a-hybrid-approach-with-table . Also what about table schema changes, If I want to add a column or remove a column from table I simply add or remove that attribute in declarative class? That's a different story. SQLAlchemy's table metadata is only an in-python document describing the structure of an existing schema in a remote database. While table metadata has the ability to emit CREATE statements to this remote database, that's as far as it goes. When using relational databases, adding columns means that an ALTER statement must be emitted on the target database. You'd need to emit these ALTER commands yourself, if you'd like an existing schema to gain new columns that you've added to your SQLalchemy model. Or if you're in development, you can alternatively (and IMHO this is much easier, assuming you're working only with development databases) drop the whole database and recreate it, where the new columns will be present. There is also the approach of using a tool like SQLAlchemy-Migrate which gives you a place to define table alterations, and does the work of composing the ALTER statements in a semi-automated fashion. Regardless, the choice of declarative versus classical has no impact here, save for the fact that SQLAlchemy-Migrate works a little more clearly when you give it Table constructs to work with, rather than copies of your declared classes. When I've used migrate in the past, it's entirely unnecessary to copy the full table definition as its docs suggest, I tend to just use Table(mytable, metadata, autoload=True) to get at the current Table object before applying alterations, so the usage of declarative has no impact. I have completed my table design and thinking to create database in one go using declarative mapping. should be fine it's not that much of a commitment ! -- 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.
