[sqlalchemy] Re: SQLAlchemy ORM Object caching with relationships and invalidation
Hi Mike, I'll let the others add more detail about your questions, but for the broad strokes I thought I'd add that I ran into similar issues with my simple caching method and ultimately ended up using the new Dogpile.cache stuff that Mike recommended on his bloghttp://techspot.zzzeek.org/2012/04/19/using-beaker-for-caching-why-you-ll-want-to-switch-to-dogpile.cache/. (The example file is here: https://groups.google.com/d/msg/sqlalchemy/MrKA6AygZ14/o95dmUdLS70J ) It is far more integrated with the internals of SQLAlchemy relationship management, so it may behave better. On Thursday, September 27, 2012 7:28:12 AM UTC+2, mikepk wrote: Unlike most of the SQLAlchemy caching examples, I'm not using query-level caching but rather caching at the instance / object level. I'm using the ORM and I have a SQLAlchemy object that I load and occasionally store in a second level cache. I have a custom 'get' loading function that checks the second level cache based on primary key and returns the cached copy if present and populates the cache if not. I also have an event handler on 'after_commit' that checks the session for dirty objects that are cached (with a simple class property of __cached__=True) and does either an invalidate or write-back to the cache when these objects are dirty and cached. This pattern is pretty simple and works great. My problem is that I'd like to be able to use this same (or similar) pattern for more complex SQLAlchemy objects containing relationships. I'd like the cache to contain not only the 'base' object but all (lazy) loaded related obejcts. I have no problem storing and retrieving these objects (and relationships) from the cache, my problem comes from the invalidation/write-back part. Lets say I have object A that's in the cache and it has a relationship, A.B that was stored along with it in the cache. If I retrieve A from the cache I can get A.B and I get the cached copy of B. If B is modified, however, then my simple cache invalidator event handler doesn't see that B is cached (no __cached__ property on B) and B gets committed to the database without the cached copy of A being invalidated. Now subsequent cache hits of A will have a stale A.B relationship. So my question is, is there a clean / simple way to mark A for invalidation when B is modified? I've come up with a few schemes but all of them seem brittle, complicated, and my intuition is telling me that I'm reinventing the wheel; that some facility in SQLAlchemy itself may be useful in walking this relationship graph to find loaded, connected relationships who's cached represenations might need to be invalidated. Alternatively, is there another pattern that would be better suited to this kind of object-level caching? Thanks! -Mike -- 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/-/s9rf5eY9f2IJ. 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] Dogpile.cache w/ SQLAlchemy
This example has worked really well so far, but I thought I'd mention that I ran into few small things and made some changes. 1) This is was minor. I wanted to be able to use the cache_regions on multiple databases from the same program so I made the CachingQuery class take a parameter with the regions dict it operates on -- simple enough, and similar to the Beaker example which takes a cache manager instance. 2) This was a little more interesting. I replaced an existing simplistic caching implementation that stored database results with the new dogpile.cache implementation and I saw my request times slow down from 16ms to 66ms. It kind of shocked me that it was so much slower, so I ran cProfile and gprof2dot against it to see what was happening. I found out that the key generation algorithm in _key_from_query -- specifically visitors.traverse -- was causing the slow-down. Once I added a small feature to use explicit cache keys, it was back to normal. Admittedly the tables I'm caching in this case are quite large; three preference tables that have 100+ columns each. However, I think it does serve as a bit of a warning, since I imagine the traverse only gets slower as the query gets more complicated (although I haven't tested that). Automatic cache-key generation based on the query is nice, but there is certainly a price to be paid in some circumstances. Anyway, thanks again for the example Mike. I imagine that some (maybe all) of these things could be due to the fact that it is just an example, but since it is new I thought I'd pass along my experience. It's simplicity certainly helped me to see where all the pieces fit and to start making these changes. On Monday, September 24, 2012 4:15:29 PM UTC+2, Michael Bayer wrote: yes, the example in 0.8 should be changed to this, but I haven't done it yet. dogpile's usage is similar to Beaker as far as the general calling pattern. A tutorial format of the example using dogpile is attached. -- 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/-/hO3TuPbT8AAJ. 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] Dogpile.cache w/ SQLAlchemy
Fair enough about the example stuff; I kind of figured, but thought I'd just share my experience. It's such a fine-line between authors getting a bead on real-world usage vs. hearing the same question over and over again. Previously, I had it just using the binds that were in the Query, but we had cases where bound parameters were embedded in the statement too affecting things. So as a quick fix I changed it to just traverse the whole statement, but this can be optimized significantly more than it is. It's the way it is so that it works more completely on the first go-around. Is there a thread I can pull at here? I'd love to here about performant key generation options that are more flexible than explicit cache keys (obviously that is highly prone to developer error). Looking at the query class, I'm guessing you are still using md5(unicode(query.statement)) for the query portion of the key, but what are you using for binds? (Sorry, not quite familiar with the internals of SQLAlchemy yet -- and I can't find that attribute or anything similar in the source) Are there other alternatives for cache keys that are worth exploring? (and feel free to just post links or what have you, I'm happy to do the reading) -- 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/-/JvefOSuw1FwJ. 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] Dogpile.cache w/ SQLAlchemy
As per this comment: http://techspot.zzzeek.org/2012/04/19/using-beaker-for-caching-why-you-ll-want-to-switch-to-dogpile.cache/#comment-503780670 Has any work been put into an example for using Dogpile.cache with SQLAlchemy? I'm about to embark on implementing caching and I don't want to re-invent the wheel by creating my own adaptation if I don't need to. -- 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/-/zf0HBD7s7SwJ. 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] Dogpile.cache w/ SQLAlchemy
Great, thanks Mike! On Monday, September 24, 2012 4:15:29 PM UTC+2, Michael Bayer wrote: yes, the example in 0.8 should be changed to this, but I haven't done it yet. dogpile's usage is similar to Beaker as far as the general calling pattern. A tutorial format of the example using dogpile is attached. -- 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/-/ULgsSiDYpfIJ. 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] SELECT * FROM function() with declarative
ok, I think I found the solution. Thought I'd post what I did and what I
tried; partly because it might help someone else and partly because someone
may have suggestions for a better way.
This is what worked:
my_function = func.my_function(...args...)
func_subquery = select(['id'], from_obj=[my_function])
results =
session.query(Table1).join(Table2).filter(Table1.id.in_(func_subquery))
and results in a query that looks like this:
SELECT table1.id, table1.col1, table1.col2... FROM table1 JOIN table2 ON
table1.t2_id = table2.id WHERE table1.id IN( SELECT id FROM
my_function(...args...) )
This differs somewhat from what I was initially thinking:
SELECT table1.id, table1.col1, table1.col2... FROM (SELECT * FROM
my_function(...args...)) as table1 JOIN table2 ON table1.t2_id = table2.id
When I run EXPLAIN ANALYZE in PostgreSQL the IN() version seems to be
slightly more efficient according to the planner (and real run times are
more or less the same)
IN: Nested Loop (cost=12.75..889.97 rows=35432 width=222) (actual
time=42.200..42.209 rows=2 loops=1)
JOIN: Nested Loop (cost=0.25..4386.37 rows=1000 width=226) (actual
time=41.052..41.061 rows=2 loops=1)
-
Things I tried on the path to getting my answer:
-
I attempted to use a combination of a label and an alias with my function
(modelled after this:
http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html?highlight=aliased#sqlalchemy.orm.aliased):
my_function = func.my_function(...args...).label(Table1.__tablename__)
my_function_alias = aliased(Table1, alias=my_function, adapt_on_names=True)
but that failed miserably. Using the labeled function in a regular core
select resulted in an AttributeError exception, so I think that might have
been part of the problem:
print select('*', from_obj=[my_function])
Traceback (most recent call last):
File stdin, line 1, in module
File /Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py,
line 1790, in __str__
return unicode(self.compile()).encode('ascii', 'backslashreplace')
File /Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py,
line 1778, in compile
return self._compiler(dialect, bind=bind, **kw)
File /Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py,
line 1784, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File /Library/Python/2.7/site-packages/sqlalchemy/sql/compiler.py, line
277, in __init__
engine.Compiled.__init__(self, dialect, statement, **kwargs)
File /Library/Python/2.7/site-packages/sqlalchemy/engine/base.py, line
705, in __init__
self.string = self.process(self.statement)
File /Library/Python/2.7/site-packages/sqlalchemy/engine/base.py, line
724, in process
return obj._compiler_dispatch(self, **kwargs)
File /Library/Python/2.7/site-packages/sqlalchemy/sql/visitors.py, line
72, in _compiler_dispatch
return getter(visitor)(self, **kw)
File /Library/Python/2.7/site-packages/sqlalchemy/sql/compiler.py, line
873, in visit_select
froms = select._get_display_froms(existingfroms)
File /Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py,
line 4807, in _get_display_froms
toremove = set(itertools.chain(*[f._hide_froms for f in froms]))
AttributeError: '_Label' object has no attribute '_hide_froms'
Using the aliased function would include the original table as well as the
aliased functioned, and without a join condition it would just do a
cartesian product:
results = session.query(my_function_alias).first()
SELECT table1.id, table1.col1, table1.col2... FROM table1, (SELECT * FROM
my_function(...args...))
So that didn't work either. After doing this I realized that if I have to
include the table and the function sub-select I might as well attempt it as
an IN(), and that is what brought me to my final answer.
Thanks again for your suggestion Mike, it definitely put me on the right
path to the solution.
On Thursday, September 20, 2012 7:10:52 PM UTC+2, Michael Bayer wrote:
you can make a function act like a table by creating a select() out of it,
check out the fifth example at
http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html#functions
On Sep 20, 2012, at 1:09 PM, David McKeone wrote:
Hi M,
Is creating something in the database the only way to do it? How would I
coerce the view's return type into my object? How do I substitute the view
in the FROM part of my clause instead?
On Thursday, September 20, 2012 5:52:28 PM UTC+2, A.M. wrote:
On Sep 20, 2012, at 11:49 AM, David McKeone wrote:
I've googled around can't seem to find an answer to this, so hopefully
someone knows how to do it here.
I'm using PostgreSQL and I have a PL/PGSQL function that filters and
modifies a particular table based on a number of conditions and then
returns a set of rows as the result
[sqlalchemy] SELECT * FROM function() with declarative
I've googled around can't seem to find an answer to this, so hopefully someone knows how to do it here. I'm using PostgreSQL and I have a PL/PGSQL function that filters and modifies a particular table based on a number of conditions and then returns a set of rows as the result. This pattern has allowed the system to use the functions as if they were tables so that joins can still be done on the resulting values. So instead of: SELECT * FROM table1 INNER JOIN table2 ON table1.t2_id = table2.id I do: SELECT * FROM my_function( ...args... ) as table1 INNER JOIN table2 ON table1.t2_id = table2.id That part works ok in plain SQL (and as well in the system I'm converting from) So now with SQLAlchemy I have my declarative definitions for those tables: class Table1(Base): __tablename__ = 'table1' id = Column() t2_id = Column(ForeignKey()) table2 = Relationship( ... ) # Join condition is specified explicitly class Table2(Base); __tablename__ = 'table2' id = Column() and I'm trying to figure out how I would execute a query that looks like this: result = session.query(Table1).join(Table1.table2).options(contains_eager(Table1.table2)) but using a function to 'fake' Table1 instead. So basically I'm attempting to get SQLAlchemy to treat the result of my function as if it was the normal Table1 object. I've tried using select_from() to inject my call to func.my_function() but that doesn't seem to work and since what I'm doing seems like it might be tricky (or not portable across SQL) I thought I'd ask if it's even possible. Thanks for any help! -- 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/-/bYCl5PH0yNIJ. 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] Portable date intervals?
Great, thanks Mike! I only need to do PostgreSQL, but I will look into the @compiles decorator to make a more general solution. . -- 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/-/JwSkrcDaPDwJ. 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
[sqlalchemy] SQLAlchemy with Flask -- hybrid models?
I've been using SQLAlchemy with Flask via the Flask extension
Flask-SQLAlchemy. Everything works great so far, but I foresee a potential
problem once I start to use my database model outside of Flask. In the
future I'd like to be able to use my models with non-Flask SQLAlchemy (a
worker daemon process or with a PySide interface). Well just use standard
SQLAlchemy, you may say, and fore-go the use of the extension. That
was my first thought, but sadly some useful extensions (notably
Flask-DebugToolbar) seem to like using the extension version and it is nice
to be able to have Flask manage the database sessions in the way that it
does. I'd like to not throw the baby out with the bath water.
I realize that this is somewhat specific to Flask, but is there a way that
I could do both? Can I create models with standard SQLAlchemy declarative
and then somehow inject them into Flask-SQLAlchemy's way of doing things?
If it helps with the solution, I don't need to use any of the Model
specific methods that Flask-SQLAlchemy provides (get_or_404, paginate,
etc..) and I also specify a __tablename__ for all of my models, so I don't
rely on Flask-SQLAlchemy generating that for me.
I took a look at the source of Flask-SQLAlchemy
(https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy.py)
and from what I can tell it seems that it's using Flask's signalling
capabilities by customizing SQLAlchemy's session and mapper, but that is
where my understanding ends (I'm still new to this whole stack, Python,
Flask, SQLAlchemy) and I could use some pointers for how to proceed.
To visualize what I'm talking about, here are the two types of models.
A basic Flask-SQLAlchemy model looks like
(http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application):
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:tmp/test.db'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
def __init__(self, username, email):
self.username = username
self.email = email
def __repr__(self):
return 'User %r' % self.username
Note the db.Model, db.Integer and db dot everything.
The plain declarative SQLAlchemy equivalent would be
(http://flask.pocoo.org/docs/patterns/sqlalchemy/):
from sqlalchemy import Column Integer, String, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:tmp/test.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
autoflush=False, bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()
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)
Thanks for your help!
--
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/-/fyqvIAUBbcsJ.
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 Wednesday, August 22, 2012 2:23:28 PM UTC+1, Simon King wrote:
On Wed, Aug 22, 2012 at 12:51 PM, David McKeone
davidm...@gmail.comjavascript:
wrote:
I've been using SQLAlchemy with Flask via the Flask extension
Flask-SQLAlchemy. Everything works great so far, but I foresee a
potential
problem once I start to use my database model outside of Flask. In the
future I'd like to be able to use my models with non-Flask SQLAlchemy (a
worker daemon process or with a PySide interface). Well just use
standard
SQLAlchemy, you may say, and fore-go the use of the extension. That
was
my first thought, but sadly some useful extensions (notably
Flask-DebugToolbar) seem to like using the extension version and it is
nice
to be able to have Flask manage the database sessions in the way that it
does. I'd like to not throw the baby out with the bath water.
I realize that this is somewhat specific to Flask, but is there a way
that I
could do both? Can I create models with standard SQLAlchemy declarative
and
then somehow inject them into Flask-SQLAlchemy's way of doing things?
If it helps with the solution, I don't need to use any of the Model
specific
methods that Flask-SQLAlchemy provides (get_or_404, paginate, etc..) and
I
also specify a __tablename__ for all of my models, so I don't rely on
Flask-SQLAlchemy generating that for me.
I took a look at the source of Flask-SQLAlchemy
(
https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy.py)
and from what I can tell it seems that it's using Flask's signalling
capabilities by customizing SQLAlchemy's session and mapper, but that is
where my understanding ends (I'm still new to this whole stack, Python,
Flask, SQLAlchemy) and I could use some pointers for how to proceed.
To visualize what I'm talking about, here are the two types of models.
A
basic Flask-SQLAlchemy model looks like
(
http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application):
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:tmp/test.db'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
def __init__(self, username, email):
self.username = username
self.email = email
def __repr__(self):
return 'User %r' % self.username
Note the db.Model, db.Integer and db dot everything.
The plain declarative SQLAlchemy equivalent would be
(http://flask.pocoo.org/docs/patterns/sqlalchemy/):
from sqlalchemy import Column Integer, String, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:tmp/test.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()
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)
From a very quick read of the Flask-SQLAlchemy docs, I would have
thought you could just use your flask-based classes in non-Flask-based
apps without any issue. The quickstart guide that you referenced above
illustrates a command-line session using them, so there's no reason
why you couldn't do the same in a worker process.
Have you already tried it and found that it doesn't work?
Simon
Perhaps I've missed the important bit, but my understanding is that there
are two ways to do it:
1) Use SQLAlchemy with manual session control (
http://flask.pocoo.org/docs/patterns/sqlalchemy/)
2) Use SQLAlchemy with the Flask-SQLAlchemy extension which does session
control for you, but requires you to use it's own declarative base class,
db.Model (
http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application
)
The problem is that other extensions give you additional features if you
use solution #2, but not if you use solution #1, because solution #1
implies that you are going to do everything yourself manually. I'm fine
with the way solution #2 handles the sessions and everything else, I just
want to be able to use my own declarative base so that the models are
more-or-less independent of the app that they are being used in.
--
You received this message
Re: [sqlalchemy] SQLAlchemy with Flask -- hybrid models?
On Wednesday, August 22, 2012 2:33:01 PM UTC+1, David McKeone wrote:
On Wednesday, August 22, 2012 2:23:28 PM UTC+1, Simon King wrote:
On Wed, Aug 22, 2012 at 12:51 PM, David McKeone davidm...@gmail.com
wrote:
I've been using SQLAlchemy with Flask via the Flask extension
Flask-SQLAlchemy. Everything works great so far, but I foresee a
potential
problem once I start to use my database model outside of Flask. In the
future I'd like to be able to use my models with non-Flask SQLAlchemy
(a
worker daemon process or with a PySide interface). Well just use
standard
SQLAlchemy, you may say, and fore-go the use of the extension.
That was
my first thought, but sadly some useful extensions (notably
Flask-DebugToolbar) seem to like using the extension version and it is
nice
to be able to have Flask manage the database sessions in the way that
it
does. I'd like to not throw the baby out with the bath water.
I realize that this is somewhat specific to Flask, but is there a way
that I
could do both? Can I create models with standard SQLAlchemy
declarative and
then somehow inject them into Flask-SQLAlchemy's way of doing things?
If it helps with the solution, I don't need to use any of the Model
specific
methods that Flask-SQLAlchemy provides (get_or_404, paginate, etc..)
and I
also specify a __tablename__ for all of my models, so I don't rely on
Flask-SQLAlchemy generating that for me.
I took a look at the source of Flask-SQLAlchemy
(
https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy.py)
and from what I can tell it seems that it's using Flask's signalling
capabilities by customizing SQLAlchemy's session and mapper, but that
is
where my understanding ends (I'm still new to this whole stack, Python,
Flask, SQLAlchemy) and I could use some pointers for how to proceed.
To visualize what I'm talking about, here are the two types of models.
A
basic Flask-SQLAlchemy model looks like
(
http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application):
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:tmp/test.db'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
def __init__(self, username, email):
self.username = username
self.email = email
def __repr__(self):
return 'User %r' % self.username
Note the db.Model, db.Integer and db dot everything.
The plain declarative SQLAlchemy equivalent would be
(http://flask.pocoo.org/docs/patterns/sqlalchemy/):
from sqlalchemy import Column Integer, String, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:tmp/test.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()
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)
From a very quick read of the Flask-SQLAlchemy docs, I would have
thought you could just use your flask-based classes in non-Flask-based
apps without any issue. The quickstart guide that you referenced above
illustrates a command-line session using them, so there's no reason
why you couldn't do the same in a worker process.
Have you already tried it and found that it doesn't work?
Simon
Perhaps I've missed the important bit, but my understanding is that there
are two ways to do it:
1) Use SQLAlchemy with manual session control (
http://flask.pocoo.org/docs/patterns/sqlalchemy/)
2) Use SQLAlchemy with the Flask-SQLAlchemy extension which does session
control for you, but requires you to use it's own declarative base class,
db.Model (
http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application
)
The problem is that other extensions give you additional features if you
use solution #2, but not if you use solution #1, because solution #1
implies that you are going to do everything yourself manually. I'm fine
with the way solution #2 handles the sessions and everything else, I just
want to be able to use my own declarative base so that the models are
more-or-less
Re: [sqlalchemy] SQLAlchemy with Flask -- hybrid models?
On Wednesday, August 22, 2012 3:12:02 PM UTC+1, Simon King wrote:
On Wed, Aug 22, 2012 at 2:44 PM, David McKeone
davidm...@gmail.comjavascript:
wrote:
On Wednesday, August 22, 2012 2:33:01 PM UTC+1, David McKeone wrote:
On Wednesday, August 22, 2012 2:23:28 PM UTC+1, Simon King wrote:
On Wed, Aug 22, 2012 at 12:51 PM, David McKeone davidm...@gmail.com
wrote:
I've been using SQLAlchemy with Flask via the Flask extension
Flask-SQLAlchemy. Everything works great so far, but I foresee a
potential
problem once I start to use my database model outside of Flask. In
the
future I'd like to be able to use my models with non-Flask
SQLAlchemy
(a
worker daemon process or with a PySide interface). Well just use
standard
SQLAlchemy, you may say, and fore-go the use of the extension.
That was
my first thought, but sadly some useful extensions (notably
Flask-DebugToolbar) seem to like using the extension version and it
is
nice
to be able to have Flask manage the database sessions in the way
that
it
does. I'd like to not throw the baby out with the bath water.
I realize that this is somewhat specific to Flask, but is there a
way
that I
could do both? Can I create models with standard SQLAlchemy
declarative and
then somehow inject them into Flask-SQLAlchemy's way of doing
things?
If it helps with the solution, I don't need to use any of the Model
specific
methods that Flask-SQLAlchemy provides (get_or_404, paginate, etc..)
and I
also specify a __tablename__ for all of my models, so I don't rely
on
Flask-SQLAlchemy generating that for me.
I took a look at the source of Flask-SQLAlchemy
(
https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy.py)
and from what I can tell it seems that it's using Flask's signalling
capabilities by customizing SQLAlchemy's session and mapper, but
that
is
where my understanding ends (I'm still new to this whole stack,
Python,
Flask, SQLAlchemy) and I could use some pointers for how to proceed.
To visualize what I'm talking about, here are the two types of
models.
A
basic Flask-SQLAlchemy model looks like
(
http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application):
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:tmp/test.db'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
def __init__(self, username, email):
self.username = username
self.email = email
def __repr__(self):
return 'User %r' % self.username
Note the db.Model, db.Integer and db dot everything.
The plain declarative SQLAlchemy equivalent would be
(http://flask.pocoo.org/docs/patterns/sqlalchemy/):
from sqlalchemy import Column Integer, String, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:tmp/test.db',
convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()
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)
From a very quick read of the Flask-SQLAlchemy docs, I would have
thought you could just use your flask-based classes in non-Flask-based
apps without any issue. The quickstart guide that you referenced above
illustrates a command-line session using them, so there's no reason
why you couldn't do the same in a worker process.
Have you already tried it and found that it doesn't work?
Simon
Perhaps I've missed the important bit, but my understanding is that
there
are two ways to do it:
1) Use SQLAlchemy with manual session control
(http://flask.pocoo.org/docs/patterns/sqlalchemy/)
2) Use SQLAlchemy with the Flask-SQLAlchemy extension which does
session
control for you, but requires you to use it's own declarative base
class,
db.Model
(
http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application)
The problem
Re: [sqlalchemy] SQLAlchemy with Flask -- hybrid models?
On Wednesday, August 22, 2012 3:12:02 PM UTC+1, Simon King wrote:
On Wed, Aug 22, 2012 at 2:44 PM, David McKeone
davidm...@gmail.comjavascript:
wrote:
On Wednesday, August 22, 2012 2:33:01 PM UTC+1, David McKeone wrote:
On Wednesday, August 22, 2012 2:23:28 PM UTC+1, Simon King wrote:
On Wed, Aug 22, 2012 at 12:51 PM, David McKeone davidm...@gmail.com
wrote:
I've been using SQLAlchemy with Flask via the Flask extension
Flask-SQLAlchemy. Everything works great so far, but I foresee a
potential
problem once I start to use my database model outside of Flask. In
the
future I'd like to be able to use my models with non-Flask
SQLAlchemy
(a
worker daemon process or with a PySide interface). Well just use
standard
SQLAlchemy, you may say, and fore-go the use of the extension.
That was
my first thought, but sadly some useful extensions (notably
Flask-DebugToolbar) seem to like using the extension version and it
is
nice
to be able to have Flask manage the database sessions in the way
that
it
does. I'd like to not throw the baby out with the bath water.
I realize that this is somewhat specific to Flask, but is there a
way
that I
could do both? Can I create models with standard SQLAlchemy
declarative and
then somehow inject them into Flask-SQLAlchemy's way of doing
things?
If it helps with the solution, I don't need to use any of the Model
specific
methods that Flask-SQLAlchemy provides (get_or_404, paginate, etc..)
and I
also specify a __tablename__ for all of my models, so I don't rely
on
Flask-SQLAlchemy generating that for me.
I took a look at the source of Flask-SQLAlchemy
(
https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy.py)
and from what I can tell it seems that it's using Flask's signalling
capabilities by customizing SQLAlchemy's session and mapper, but
that
is
where my understanding ends (I'm still new to this whole stack,
Python,
Flask, SQLAlchemy) and I could use some pointers for how to proceed.
To visualize what I'm talking about, here are the two types of
models.
A
basic Flask-SQLAlchemy model looks like
(
http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application):
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:tmp/test.db'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
def __init__(self, username, email):
self.username = username
self.email = email
def __repr__(self):
return 'User %r' % self.username
Note the db.Model, db.Integer and db dot everything.
The plain declarative SQLAlchemy equivalent would be
(http://flask.pocoo.org/docs/patterns/sqlalchemy/):
from sqlalchemy import Column Integer, String, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:tmp/test.db',
convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()
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)
From a very quick read of the Flask-SQLAlchemy docs, I would have
thought you could just use your flask-based classes in non-Flask-based
apps without any issue. The quickstart guide that you referenced above
illustrates a command-line session using them, so there's no reason
why you couldn't do the same in a worker process.
Have you already tried it and found that it doesn't work?
Simon
Perhaps I've missed the important bit, but my understanding is that
there
are two ways to do it:
1) Use SQLAlchemy with manual session control
(http://flask.pocoo.org/docs/patterns/sqlalchemy/)
2) Use SQLAlchemy with the Flask-SQLAlchemy extension which does
session
control for you, but requires you to use it's own declarative base
class,
db.Model
(
http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application)
The problem
Re: [sqlalchemy] SQLAlchemy with Flask -- hybrid models?
On Wednesday, August 22, 2012 5:36:22 PM UTC+1, Michael Bayer wrote: On Aug 22, 2012, at 7:51 AM, David McKeone wrote: I've been using SQLAlchemy with Flask via the Flask extension Flask-SQLAlchemy. Everything works great so far, but I foresee a potential problem once I start to use my database model outside of Flask. In the future I'd like to be able to use my models with non-Flask SQLAlchemy (a worker daemon process or with a PySide interface). Well just use standard SQLAlchemy, you may say, and fore-go the use of the extension. That was my first thought, but sadly some useful extensions (notably Flask-DebugToolbar) seem to like using the extension version and it is nice to be able to have Flask manage the database sessions in the way that it does. I'd like to not throw the baby out with the bath water. I realize that this is somewhat specific to Flask, but is there a way that I could do both? Can I create models with standard SQLAlchemy declarative and then somehow inject them into Flask-SQLAlchemy's way of doing things? If it helps with the solution, I don't need to use any of the Model specific methods that Flask-SQLAlchemy provides (get_or_404, paginate, etc..) and I also specify a __tablename__ for all of my models, so I don't rely on Flask-SQLAlchemy generating that for me. I took a look at the source of Flask-SQLAlchemy ( https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy.py) and from what I can tell it seems that it's using Flask's signalling capabilities by customizing SQLAlchemy's session and mapper, but that is where my understanding ends (I'm still new to this whole stack, Python, Flask, SQLAlchemy) and I could use some pointers for how to proceed. There's no reason I can see in the source that flask-sqlalchemy would get in the way of entirely plain SQLAlchemy mapped objects. At the end of the day, a class that extends Flask's db.model is just a mapped class, just like a non-flask class. Both kinds of classes are freely usable with any SQLAlchemy Session, including the Session that Flask-SQLA provides.It's important to note the distinction between mapper configuration, which has to do with class structure, and session configuration, which only deals with instances of objects. These two processes work together at a core level that various extensions only ride on top of, unless those extensions define additional dependencies above that level. Flask-sqlalchemy appears only to define one very trivial such dependency which is some coordination to enable the before_models_committed and models_committed hooks, which themselves are just for end-user convenience ( http://packages.python.org/Flask-SQLAlchemy/signals.html). The Flask-SQLA approach is really just assigning event listeners to sessions and mappers. It's doing so in a way that is a bit brittle, but also this system precedes SQLAlchemy's 0.7 event model. Armin's immediate goal with flask-sqlalchemy is to migrate the extension to use the new event model, which would actually remove the need for the styles of registration I see here as the new system allows registration of event listeners on all sessions/mappers non-intrusively. There's also a custom Query class in use here, though it doesn't seem to be consistently integrated with the Session, but using custom Query classes like this as well as adding the MyClass.query hook is a widely used pattern. So if you were to use plain SQLAlchemy models with flask-SQLA out of the box, these particular events wouldn't fire off as much, unless you also set up the flask_sqlalchemy._SignalTrackingMapperExtension with your normal mappers. I think if you just tried using regular models with flask models, and didn't rely on those two particular signals, you'd see everything pretty much works without any issue. Thanks for your great response Mike. Forgive my ignorance, but I don't understand enough of the underpinnings to get my first steps out of this (the downside of starting with something that gives you stuff for free, I suppose). I'm definitely going to walk through what you've said and reference it against the documentation, but while your mind is fresh on the topic, I was wondering if you could just clarify how I might convert a standard model object into a flask-sqlalchemy model object. Using the two examples above, would I just take my User(Base) class and then assign it property out of the Flask db properties? Something like: User.session = db.session() User.engine = db.engine() I know those properties don't actually exist on User, but is that the kind of thing I should be looking to do? Move certain pieces into all of the models? or is there something higher level going on that will do this for me in some way? something else? ... but also this system precedes SQLAlchemy's 0.7 event model. Armin's
Re: [sqlalchemy] SQLAlchemy with Flask -- hybrid models?
On Wednesday, August 22, 2012 9:03:55 PM UTC+1, Michael Bayer wrote: On Aug 22, 2012, at 3:51 PM, David McKeone wrote: I was wondering if you could just clarify how I might convert a standard model object into a flask-sqlalchemy model object. why do you need to do this ?What flask-sqlalchemy-specific features would you hope for the model objects to have ? There's pretty much two I can see - one is the MyModel.query attribute, and the other are the events. Neither are necessary, though you might want the usage of one or the other. Otherwise no conversion is needed. Using the two examples above, would I just take my User(Base) class and then assign it property out of the Flask db properties? Something like: User.session = db.session() User.engine = db.engine() I know those properties don't actually exist on User, but is that the kind of thing I should be looking to do? Move certain pieces into all of the models? or is there something higher level going on that will do this for me in some way? something else? I don't see why engine or session would be stuck onto the model class like that, I don't see that usage here: http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application. Just the query attribute. User.query.all(). This is equivalent to session.query(User).all(). The User.session / User.engine pattern suggests that a model class acts as a registry for how to get at a live database connection. That pattern is one that SQLAlchemy specifically discourages. In the Flask docs, you'll see that the handle to the state of a transaction is db.session. The objects like instances of User are then contained within that. This seems like it would be a really good way to accomplish what I'm looking for and to move things forward as well. Once I read up on the requisite knowledge I may end up making an attempt at making this over the next little while. I had a drink with Armin last week and I'm not sure if his current stuff points him in this direction (of course you'd have to ask him for the real answer on that), but I certainly have a vested interest, so maybe I can do some of the grunt work. its really quite a small amount of effort. I think for now the strategy on your end should be to forge ahead with what's there, and if there's some very specific thing that doesn't work as you expect, I can show you how to get the behavior you're looking for. 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) 2) Debug query tracking for use with Flask-DebugToolbar (Plus compatibility with other plug-ins that may expect Flask-SQLAlchemy) - This is really the difficult one. In this case I think it just needs an attribute on the Flask app called sqlalchemy_queries which contains a tuple of queries. This is where I was thinking it may be better to assist with updating the plugin to just play nice with SQLAlchemy's default behaviours. Maybe I'm wrong? 3) The Model.query behaviour (it's nice, but I could live without it, since its really just syntactic) I think it'll make my code simpler/better for two reasons: 1) My models will be SQLAlchemy, not a Flask specific dialect with db dot everywhere. This will make the SQLAlchemy documentation more obvious to programmers that use the code. 2) The db.Model method seems to make it very easy to create circular import errors (as seen in this issue: https://github.com/mitsuhiko/flask-sqlalchemy/issues/97 and experienced by myself). It would be really nice if I could isolate the models and model mapping behaviours from the rest of the application to avoid that kind of thing. The User.session / User.engine pattern suggests that a model class acts as a registry for how to get at a live database connection. That pattern is one that SQLAlchemy specifically discourages. In the Flask docs, you'll see that the handle to the state of a transaction is db.session. The objects like instances of User are then contained within that. This was really just my lack of understanding of how the database connection is connected to the models, rather than stating how I wanted it to be done. Thinking about it now, and reading your comment, I realize that sessions are connected to the models when they are passed in, like in session.query(User).first(), and Model.query is really just a class instance that holds onto the session
Re: [sqlalchemy] SQLAlchemy with Flask -- hybrid models?
On Wednesday, August 22, 2012 10:21:59 PM UTC+1, David McKeone wrote: On Wednesday, August 22, 2012 9:03:55 PM UTC+1, Michael Bayer wrote: On Aug 22, 2012, at 3:51 PM, David McKeone wrote: I was wondering if you could just clarify how I might convert a standard model object into a flask-sqlalchemy model object. why do you need to do this ?What flask-sqlalchemy-specific features would you hope for the model objects to have ? There's pretty much two I can see - one is the MyModel.query attribute, and the other are the events. Neither are necessary, though you might want the usage of one or the other. Otherwise no conversion is needed. Using the two examples above, would I just take my User(Base) class and then assign it property out of the Flask db properties? Something like: User.session = db.session() User.engine = db.engine() I know those properties don't actually exist on User, but is that the kind of thing I should be looking to do? Move certain pieces into all of the models? or is there something higher level going on that will do this for me in some way? something else? I don't see why engine or session would be stuck onto the model class like that, I don't see that usage here: http://packages.python.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application. Just the query attribute. User.query.all(). This is equivalent to session.query(User).all(). The User.session / User.engine pattern suggests that a model class acts as a registry for how to get at a live database connection. That pattern is one that SQLAlchemy specifically discourages. In the Flask docs, you'll see that the handle to the state of a transaction is db.session. The objects like instances of User are then contained within that. This seems like it would be a really good way to accomplish what I'm looking for and to move things forward as well. Once I read up on the requisite knowledge I may end up making an attempt at making this over the next little while. I had a drink with Armin last week and I'm not sure if his current stuff points him in this direction (of course you'd have to ask him for the real answer on that), but I certainly have a vested interest, so maybe I can do some of the grunt work. its really quite a small amount of effort. I think for now the strategy on your end should be to forge ahead with what's there, and if there's some very specific thing that doesn't work as you expect, I can show you how to get the behavior you're looking for. 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) 2) Debug query tracking for use with Flask-DebugToolbar (Plus compatibility with other plug-ins that may expect Flask-SQLAlchemy) - This is really the difficult one. In this case I think it just needs an attribute on the Flask app called sqlalchemy_queries which contains a tuple of queries. This is where I was thinking it may be better to assist with updating the plugin to just play nice with SQLAlchemy's default behaviours. Maybe I'm wrong? 3) The Model.query behaviour (it's nice, but I could live without it, since its really just syntactic) I think it'll make my code simpler/better for two reasons: 1) My models will be SQLAlchemy, not a Flask specific dialect with db dot everywhere. This will make the SQLAlchemy documentation more obvious to programmers that use the code. 2) The db.Model method seems to make it very easy to create circular import errors (as seen in this issue: https://github.com/mitsuhiko/flask-sqlalchemy/issues/97 and experienced by myself). It would be really nice if I could isolate the models and model mapping behaviours from the rest of the application to avoid that kind of thing. The User.session / User.engine pattern suggests that a model class acts as a registry for how to get at a live database connection. That pattern is one that SQLAlchemy specifically discourages. In the Flask docs, you'll see that the handle to the state of a transaction is db.session. The objects like instances of User are then contained within that. This was really just my lack of understanding of how the database connection is connected to the models, rather than stating how I wanted it to be done. Thinking about it now, and reading your comment, I realize that sessions are connected to the models when they are passed in, like
[sqlalchemy] Declarative and deferred
Short:
---
Is there a way to backfill multiple deferred columns in a declarative object
result instance in a dynamic way when groups can't be predicted in the model?
Long:
First, let me just say thanks for SQLAlchemy. This is my first post to this
list and after working with it for quite a while I've found it to be an
excellent tool for working with the database. My previous work (non-Python)
was done with a database abstraction layer that was more relational and less
object-oriented and I've found SQLAlchemy to be amazing for letting me have my
cake (objects) and eat it too (hand-crafted sql optimizations).
Alright, so a few caveats for background:
1) I'm fairly new to Python (~4 months), but not to programming (~10 years)
2) This is the first time I've used an ORM, so my question may be more about
the object-relational mismatch handling, rather than SQLAlchemy directly.
3) I'm using SQLAlchemy with Flask's plug-in flask-sqlalchemy. That may not
have much do with my question, but just in case there is some subtle difference
between declarative's base model and Flask's db.Model
4) The current project is to use Flask and SQLAlchemy to create a web site with
an existing database in a deployed client-server application (96 Tables, can be
anywhere between ~200MB and 30GB)
5) Assumptions abound... this is a fairly complicated/specific case (I think)
so there may be underlying assumptions about how I'm doing things that are
incorrect. If I'm wrong in those underlying assumptions, then feel free to
challenge them.
6) SQLAlchemy 0.7.8
Cool.
So, I see that using declarative objects has a quite a few advantages; you can
easily add attributes(columns, relationships, etc...), validators, and methods
-- all great stuff for keeping things logically grouped. Then when you get to
performance optimizations there is a significant benefit with larger models to
not fetch all the columns for every request (this is a web app after all, so
lower response times are a goal). Great, so deferred looks like the ticket to
be able to handle this particular mis-match in a good enough way. I can defer
any non-essential columns and if I need one or two other columns down the line
then they'll be lazy-loaded as required.
Contrived example:
class User(db.Model, HelperMixin):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
password = db.Column(db.String)
type = db.Column(db.Integer)
first_name = db.Column(db.String)
last_name = db.Column(db.String)
title = db.Column(db.String)
birthday = db.Column(db.Date)
height = db.Column(db.Numeric)
width = db.Column(db.Numeric)
# etc...
def is_valid(self, check_password):
# check password in a horribly insecure, but easy way
return True if check_password == self.password else False
So with this model I want to validate a users password on login, but not load
all the other unnecessary stuff, because login probably doesn't need all the
rest of those columns. Because I also want to keep things simple on the model,
I don't use deferred directly, but rather I created a couple helper methods in
a mixin. (Note that other parts of the application may need more columns or
less columns or different columns, depending on context, so putting deferreds
directly in the model would also be impractical)
The mixin looks like this:
from sqlalchemy.orm import defer
from sqlalchemy.orm.properties import ColumnProperty
from sqlalchemy.orm.util import class_mapper
class HelperMixin(object):
@classmethod
def itercolumns(cls):
for prop in class_mapper(cls).iterate_properties:
if isinstance(prop, ColumnProperty):
yield prop.key
@classmethod
def get_deferred_except(cls, *attributes):
attribute_set = set(attributes)
ret = list()
for name in cls.itercolumns():
if name not in attribute_set:
ret.append(defer(name))
return ret
so with this helper I can context sensitively build up a result object with
just the stuff I need (but without losing the benefits of the associated
methods):
deferred = User.get_deferred_except('id', 'password') # Get list of defer()
instances for all columns, but those specified
user = User.query.options(*deferred).first()
# SQL Emitted -- SELECT id, password FROM user
if user.is_valid(the_password):
# Valid stuff
else:
# Invalid stuff
Ok, well that worked great, but now I need to get the patrons name for some
runtime specific reason. So I do this:
full_name = .join([user.title, user.first_name, user.last_name])
I now emit:
SELECT title FROM user
SELECT first_name FROM user
SELECT last_name FROM user
When what I really want at this point, and can predictably know in this case,
is:
SELECT title, first_name, last_name FROM user
So, the question is, what is the best way to back-fill an object in a way that
you keep the number
Re: [sqlalchemy] Declarative and deferred
session.refresh(user, [title, first_name, last_name]) This was the part that I was missing. It's fairly readable and it does exactly what I'd need. also, if the columns you're actually using are along these lines, that is, they aren't 10K text files, I'd strongly encourage you to do some actual profiling to determine if all this complexity is necessary and not actually hurting performance much more.To pull over 3K of text over the wire in a single result is probably a lot less overhead than to pull 1000K of text in two or three queries. At this point I'm really just exploring the boundaries of the tool so that I can select a flexible design. I still haven't quite found the sweet spot between what can/should be lazy and what cannot/shouldn't be lazy. In the existing application (the non-ORM one) all of this is done with an abstracted form of direct SQL (kind of like SQLAlchemy core). I'd like to convert some of those sections to use declarative objects instead, so the point of of this is to know that if I do go down that path then I could still optimize the columns if I needed to (read: after I profiled it and determined that it was necessary) without having to drop all the way down to SQLAlchemy core and then change things from passing objects around to passing keys in some circumstances. Although it's very likely that you are correct and that the complexity induced from using this kind of system may outweigh the over-the-wire savings -- I guess we'll see when I get there. well this is what the group feature does, if any columns in the group are touched, the whole group is loaded. My advice would be to do some profiling, come up with groups that are tailored close enough to the groups of attributes that tend to be called together, and to not overthink it. I will certainly look into this some more, since there are certainly groups of columns that can be naturally grouped. Plus I imagine that session.refresh() would load the entire group if an attribute from a group was passed to it. So that could be an interesting way to chunk it. Thanks for responding with such a great post. Its certainly helped me think through the issues from different angle. -- 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/-/zQNUzzPhzFAJ. 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.
