[sqlalchemy] writing on db performances
I'm doing some experiments to see what is the best approach to write a lot of data on disk, On file and running commit after every opoeration: Function in_file took 64.531976 seconds to run In memory and not dumping to file: Function in_memory took 0.242011 seconds to run On file and committing only at the end: Function in_file_end took 0.633998 seconds to run On file and using autocommit=True in the transaction: Function in_file_auto took 0.259341 seconds to run The most surprising result is definitively the last one, how can it be almost as fast as the in memory version? I guess it does some magic tricks behind the back, avoiding committing all the time, is that correct? Then probably if I want to have the file always updated the autocommit sounds like the best option.. -- 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] Single Table Inheritance with mult-column keys
Hi Michael, I have a similar (but subtly different) problem to this, trying to mix single- and joined-table inheritance. Essentially my model looks as follows: Product(Base) PhysicalProduct(Product) NonPhysicalProduct(Product) The Physical/NonPhysicalProduct use single table inheritance whilst objects inheriting from them use joined tables... I have a fully working model --- and there's no question that it works!! But I can't help feeling that I've missed something relating to the __mapper_args__ which is then requiring explicit calls to __init__ objects higher up the tree. (rather than bunging up this message, please see the attached file) I'd be really grateful if you could take a look and hopefully point me in the right direction. Many thanks, Rob On Wednesday, 17 August 2011 00:42:28 UTC+1, Michael Bayer wrote: On Aug 16, 2011, at 5:37 PM, Mike Gilligan wrote: I have a single table that looks similar to the following: class Equipment(Base): type = Column(CHAR(1), primary_key=True) sub_type = Column(CHAR(1), primary_key=True) code = Column(CHAR(5), primary_key=True) For historical purposes, I cannot modify this table. I would like to setup multi-level inheritance similar to this, however it does not work: class Equipment(Base): type = Column(CHAR(1), primary_key=True) sub_type = Column(CHAR(1), primary_key=True) code = Column(CHAR(5), primary_key=True) __mapper_args__ = {'polymorphic_on': type} class Vehicle(Equipment): __mapper_args__ = {'polymorphic_identity': 'V', 'polymorphic_on': sub_type} class Bus(Vehicle) __mapper_args__ = {'polymorphic_identity': 'B'} class Rail(Vehicle) __mapper_args__ = {'polymorphic_identity': 'R'} I can concatenate the multiple column values into a single discriminator column_property but then I do not have an easy way to retrieve all vehicles. Any ideas? The inheritance querying does handle multi-level inheritance so if your discriminator was on a concatenation of both things would work just fine, i.e. if you queried for Vehicle, etc. Each object's polymorphic_identity would need to include the concatenated value, of course. Unfortunately we're just beginning to support inheritance discriminators on a column_property(), and you need to use a very specific approach to make this work right now. There's some tickets in trac to allow this functionality out of the box.Attached is an example script which exercises the above mapping - it uses declarative to minimize the impact of the workaround. -- 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/-/KzPgMan_6MIJ. 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. mixed single and joined table inheritance. from sqlalchemy import * from sqlalchemy import types from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base, declared_attr Base = declarative_base() class Product(Base): __tablename__ = 'products' id = Column(types.Integer, primary_key=True) discriminator = Column('product_type', types.String(50), nullable=False) __mapper_args__ = {'polymorphic_on': discriminator} def price_history(self): return [] class PhysicalProduct(Product): p_discr = Column(types.String(50)) @declared_attr def __mapper_args__(cls): return {'polymorphic_on': cls.p_discr, 'polymorphic_identity' : 'physical_product'} def __init__(self, **kw): print init PP kwargs:, kw self.discriminator = 'physical_product' def inventory(self): return computed inventory class NonPhysicalProduct(Product): np_discr = Column(types.String(50)) @declared_attr def __mapper_args__(cls): return {'polymorphic_on': cls.np_discr, 'polymorphic_identity' : 'nonphysical_product'} def __init__(self, **kw): print init NP kwargs:, kw self.discriminator = 'nonphysical_product' def somefunc(self): return someval class Newspaper(PhysicalProduct): __tablename__ = 'newspapers' __mapper_args__ = {'polymorphic_identity': 'newspaper'} id = Column(types.Integer, ForeignKey('products.id'), primary_key=True ) title = Column(types.String(50)) def __init__(self, title): self.title = title super(Newspaper, self).__init__() class NewspaperDelivery(NonPhysicalProduct): __tablename__ = 'deliveries' __mapper_args__ = {'polymorphic_identity': 'delivery'} id = Column(types.Integer, ForeignKey('products.id'),
Re: [sqlalchemy] NOT LIKE
On Aug 22, 2012, at 11:38 PM, Warwick Prince wrote: Thanks Michael I struggle sometimes to find examples of the simple things, so eventually searched out the like_op as it was in the same place as eq() etc. So, on that subject - is it better to use query.where(eq(a, b)) or query.where(a==b), or does it make no difference really? it makes no difference at all. The closest thing we have to a rundown of operators is here: http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#common-filter-operators I suppose having an exhaustive list, with lots of links pointing to it, over here: http://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html would be helpful. not sure why the like_op and nolike_op have come into your normal vocabulary here as they are usually just the ops used internally. LIKE is column.like(other) and NOT LIKE is ~column.like(other). On Aug 22, 2012, at 9:15 PM, Warwick Prince wrote: Hi When creating a basic query, how does one code a NOT LIKE using SA? I can do this; query = table.select().where(like_op(table.c.name, 'fred%')) I can not find a NOT LIKE operator. The ones there notlike_op and notilike_op raise NotImplemented. I've placed it in as text('%s NOT LIKE %s') % (column, value) for now, but I just KNOW that's going to blow up when I change databases down the track.. Is there another way? I've been burnt badly recently changing from MySQL to Postgres with text(), so I'm trying to avoid at all costs! :-) Cheers Warwick -- 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. -- 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] 0.6 - unexpected keyword argument 'transactional'
Sorry to bring back a dead thread, but assume others like myself may stumble here for help with this issue. I added a hack to make sqlalchemy backwards with regards to the transactional keyword and thought it may be useful for future releases. While upgrading a project to avoid deprecated calls is ideal, my personal case involved porting an unfamiliar codebase to a new platform, with library dependencies that used the old methologies which couldn't be upgraded easily. The following code may be added to the beginning of the __init__ method in the Sess class which resides in lib/sqlalchemy/orm/session.py: Allows sqlalchemy be backwards compatible wrt 'transactional' keyword. Removes transactional and sets autocommit based on documentation at http://www.sqlalchemy.org/trac/wiki/06Migration#DeprecatedRemovedORMElements if ('transactional' in kwargs): if (not('autocommit' in kwargs)): #overwrite if not already set kwargs['autocommit'] = not (kwargs['transactional']) del kwargs['transactional'] Apologies in advance if this topic has already been vetted and I missed it, or for any inefficiencies with my code, as I only started looking at SQLAlchemy (and Python for that matter) yesterday. Please feel free to modify and distribute as you see fit. Cheers, Bala On Monday, December 14, 2009 12:50:37 PM UTC-5, Michael Bayer wrote: mando wrote: Hi! I'm trying to pass from sqlalchemy 0.5 to 0.6, but I found some trouble. This code, that run correctly with 0.5: def query(self, n): class_name = n #engine = self.connection() Session = sessionmaker(bind=self.engine, autoflush=True, transactional=True) session = Session() query = session.query(class_name) return query.all() return me this error: . ... File /Users/mac/.qgis//python/plugins/pyarchinitus/modules/db/ pyarchinit_db_manager.py, line 92, in query session = Session() File /Library/Frameworks/Python.framework/Versions/2.6/lib/ python2.6/site-packages/sqlalchemy/orm/session.py, line 180, in __init__ super(Sess, self).__init__(**local_kwargs) TypeError: __init__() got an unexpected keyword argument 'transactional' def query(self, n): class_name = n #engine = self.connection() Session = sessionmaker(bind=self.engine, autoflush=True, transactional=True) session = Session() query = session.query(class_name) return query.all() It's a change between 0.5 and 0.6? What part of the changelog I must to read? the first thing would be to look at the warnings your 0.5 app generates: from sqlalchemy.orm import * s = sessionmaker(transactional=True)() __main__:1: SADeprecationWarning: The 'transactional' argument to sessionmaker() is deprecated; use autocommit=True|False instead. next we have a list of ORM elements removed in 0.6. 'transactional' is first in the list: http://www.sqlalchemy.org/trac/wiki/06Migration#DeprecatedRemovedORMElements Thanks a lot -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlal...@googlegroups.comjavascript:. To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. 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 view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/poFxdTLLq6cJ. 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] SQLAlchemy with Flask -- hybrid models?
On Thursday, August 23, 2012 3:01:50 AM UTC+1, Michael Bayer wrote: On Aug 22, 2012, at 5:33 PM, David McKeone wrote: I suppose I should be more clear. This is really a long term question, I was just looking for some kind of answer now because I don't want to code myself into a corner in the short term. Currently I can make requests outside of a flask request context by using the app.test_request_context() context manager, and it seems to do the right thing. In the long term I'm looking for 2 (maybe 3) things that I already get from Flask-SQLAlchemy: 1) Session teardown for every request (looks like that is done with a call to session.remove() in the request teardown) you can use the Session provided by flask-sqlalchemy, which has the nice quality that it aligns itself with the current request. He can make that feature more open-ended though. I should be able to say flask_sqlalchemy.request_scope(some_sessionmaker) to set that up with any sessionmaker of my choosing. 2) Debug query tracking for use with Flask-DebugToolbar (Plus compatibility with other plug-ins that may expect Flask-SQLAlchemy) the logic i see in flask-sqlalchemy related to debug tracking has no connection to the db.Model class at all. its just some connection events which are ultimately established via the SQLAlchemy class. Your existing non-flask SQLA models will participate in the Session/Engine used by Flask without impacting this functionaltiy. 3) The Model.query behaviour (it's nice, but I could live without it, since its really just syntactic) scoped_session has a query_property available, so you can pull that from Flask's scoped_session using SQLAlchemy public APIs like this: sa = SQLAlchemy(db) # API: http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#sqlalchemy.orm.scoping.ScopedSession.query_property Base.query = sa.session.query_property or to get exactly flask's, which appears to add three methods get_or_404, first_or_404, paginate: Base.query = flask_sqlalchemy._QueryProperty(sa) Didn't say this explicitly; for now I will do what you say and forge ahead with things. I think I see the path, but I'll make sure to let you (the list) know if I run into trouble. good luck ! Slugged it out today and got this working, hooray! Thanks again for your help Mike (and for the time you probably put in to parse the Flask-SQLAlchemy code). If you are at PyCon this year I WILL find you and I WILL buy you beer, unless you don't drink, in which case I WILL buy you soda or coffee. I haven't done the Base.query part, and I may never do it (more below), but everything else works great and all my tests pass after switching to the new method. The more I use the new system the more I wish I would have started with it. Perhaps I can get it documented as an option, because I find it makes it far more clear where the models belong in the grand scheme of things. Now, not everyone has 93 tables, a boat-load of relationships and requirements for doing things outside of HTTP like I do, so I can understand why it's been done the way that it's been done, but having to pass the db instance into all of my model definitions (and the resulting project structure issues I had) just wasn't worth it. I've also found that having to use the session directly makes it far more clear which session is being used, and how. Not to mention the benefits from being able to decouple my models from Flask completely. So, in the name of Google search completeness, here is the solution that I ultimately ended up with, adapted for the simple User model from above, for those like me who want/need it. (It's quite simple, and I'm amazed that it hadn't occurred to me to try it like this) from flask import Flask from flask_sqlalchemy import SQLAlchemy from sqlalchemy import Column, Integer, String from sqlalchemy.ext.declarative import declarative_base app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:tmp/test.db' db = SQLAlchemy(app) Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50), unique=True) email = Column(String(120), unique=True) def __init__(self, name=None, email=None): self.name = name self.email = email def __repr__(self): return 'User %r' % (self.name) @app.before_first_request def setup(): # Recreate database each time for demo Base.metadata.drop_all(bind=db.engine) Base.metadata.create_all(bind=db.engine) db.session.add(User('Bob Jones', 'b...@gmail.com')) db.session.add(User('Joe Quimby', 'e...@joes.com')) db.session.commit() @app.route('/') def root(): users = db.session.query(User).all() return ubr.join([u{0}: {1}.format(user.name, user.email) for user in users]) if __name__ == '__main__': app.run('127.0.0.1', 5000)
Re: [sqlalchemy] NOT LIKE
OK - cool. I had looked at the first ORM tutorial, but I guess I had glossed over it, as it was talking about session.query, and I believed I was looking for something lower level than that for the direct table.select. Obviously not. :-) Makes sense that it would all follow suit, but I was expecting to have to use lower level constructs - I'm pleasantly surprised. Thanks for your seemingly infinite patients! On Aug 22, 2012, at 11:38 PM, Warwick Prince wrote: Thanks Michael I struggle sometimes to find examples of the simple things, so eventually searched out the like_op as it was in the same place as eq() etc. So, on that subject - is it better to use query.where(eq(a, b)) or query.where(a==b), or does it make no difference really? it makes no difference at all. The closest thing we have to a rundown of operators is here: http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#common-filter-operators I suppose having an exhaustive list, with lots of links pointing to it, over here: http://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html would be helpful. not sure why the like_op and nolike_op have come into your normal vocabulary here as they are usually just the ops used internally. LIKE is column.like(other) and NOT LIKE is ~column.like(other). On Aug 22, 2012, at 9:15 PM, Warwick Prince wrote: Hi When creating a basic query, how does one code a NOT LIKE using SA? I can do this; query = table.select().where(like_op(table.c.name, 'fred%')) I can not find a NOT LIKE operator. The ones there notlike_op and notilike_op raise NotImplemented. I've placed it in as text('%s NOT LIKE %s') % (column, value) for now, but I just KNOW that's going to blow up when I change databases down the track.. Is there another way? I've been burnt badly recently changing from MySQL to Postgres with text(), so I'm trying to avoid at all costs! :-) Cheers Warwick -- 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. -- 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. -- 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.