[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.
