[sqlalchemy] Recreating Access in LibreOffice Base
I used to find MS Access to be a very useful tool in the right hands. I've been doing some python script coding in LibreOffice Base and started looking at sqlalchemy it looks as if it wouldnt be too much of a job to add the components necessary to turn LO Base front end into something not far from the Access of old (I have not used MS for 20 years or so). I was wondering if anyone has ventured there or is interested in providing some input? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d979a062-2b51-4646-babf-95d08abb6138n%40googlegroups.com.
Re: [sqlalchemy] Using the same joinedload object in multiple options causes performance issue with baked queries
I've tested your patch against the problematic queries we had, and it completely solves the problem. Thanks! On Wed, 6 Jun 2018 at 21:52 Mike Bayer wrote: > There's a patch at > https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/771/ which if > you can review against your specific mappings would be helpful to > confirm this fixed the issue. > > On Wed, Jun 6, 2018 at 10:32 AM, Tom Flannaghan > wrote: > > Thanks Mike. I've attached a script that shows the difference in case > that > > helps. > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to sqlalchemy+unsubscr...@googlegroups.com. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/9hBwMGPneJM/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Using the same joinedload object in multiple options causes performance issue with baked queries
Thanks Mike. I've attached a script that shows the difference in case that
helps.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import joinedload, relationship
Base = declarative_base()
class Book(Base):
__tablename__ = 'book'
id = Column(Integer, primary_key=True)
pages = relationship('Page', backref='book')
class Font(Base):
__tablename__ = 'font'
id = Column(Integer, primary_key=True)
class Layout(Base):
__tablename__ = 'layout'
id = Column(Integer, primary_key=True)
class Page(Base):
__tablename__ = 'page'
id = Column(Integer, primary_key=True)
book_id = Column(ForeignKey('book.id'))
font_id = Column(ForeignKey('font.id'))
layout_id = Column(ForeignKey('layout.id'))
font = relationship(Font)
layout = relationship(Layout)
pages = joinedload(Book.pages)
option1 = pages.joinedload(Page.font)
option2 = pages.joinedload(Page.layout)
print([[str(i) for i in load.path] for load in option1._to_bind])
print([[str(i) for i in load.path] for load in option2._to_bind])
option3 = joinedload(Book.pages).joinedload(Page.font)
option4 = joinedload(Book.pages).joinedload(Page.layout)
print([[str(i) for i in load.path] for load in option3._to_bind])
print([[str(i) for i in load.path] for load in option4._to_bind])
[sqlalchemy] Using the same joinedload object in multiple options causes performance issue with baked queries
Hi all, We have just upgraded to sqlalchemy 1.2.7 (from 1.1.14), and had a performance issue with a query that uses a lot of joinedloads that was caused by the automatic baking of all relationship queries that was introduced in 1.2. Say we have a set of tables with relationships Book.pages, Page.font and Font.layout. We have a query of this form: pages = joinedload(Book.pages) option1 = pages.joinedload(Page.font) option2 = pages.joinedload(Page.layout) query = session().query(Book).options(option1, option2) The important point here is that the pages object defined on line 1 is reused in both option1 and option2. Now suppose we fetch another relationship that wasn't joined-loaded on the returned instances. This will case another query as it is not loaded already, and this query will be baked due to the change in 1.2 to bake all relationship loads. We found that the construction of the cache key for baking this query becomes very slow as the number of options of this form increases, and is in fact quadratic in the number of such options (we have ~25 such options in our problematic query). This is due to each option containing all of the joinedloads inside its _to_bind attribute, and _UnboundLoad._generate_cache_key has to process everything in the _to_bind list. E.g. in this example: print([[str(i) for i in load.path] for load in option1._to_bind]) print([[str(i) for i in load.path] for load in option2._to_bind]) [['Book.pages'], ['Book.pages', 'Page.font'], ['Book.pages', 'Page.layout']] [['Book.pages'], ['Book.pages', 'Page.font'], ['Book.pages', 'Page.layout']] Therefore, when generating the key for each option we are processing the joinedloads from all of the options, leading to the quadratic performance degradation. We fixed it by avoiding reusing the joinedload for Book.pages by doing this: option1 = joinedload(Book.pages).joinedload(Page.font) option2 = joinedload(Book.pages).joinedload(Page.layout) The resulting query is unchanged, but the cache key function is now just linear in the number of joinedloads as each option has only its relationships in its _to_bind attribute. In our case, this completely solved the performance issue. I'm not sure whether this behaviour is a bug or whether joinedloads aren't intended to be reused. If the latter, it would be great if they raised a warning if reused like this (and a mention of this issue in the docs). Thanks, Tom -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: KeyError when looping thru class objects to try and copy them
I moved `db.session.commit()` outside the loop. That seemed to fix the error. I'll post an update if I get more errors. On Wednesday, December 20, 2017 at 6:43:05 PM UTC-5, Lele Gaifax wrote: > > Tom Tanner <dontsende...@gmail.com > writes: > > > I want to query rows and copy them while changing an attribute of each. > > Here's my code. > > > > colObjs= db.session.query(Column).filter_by(chart_id=oldChartID).all() > > for colObj in colObjs: > >make_transient(colObj) > >print colObj.id > >del colObj.id > >colObj.chart_id= newChartID > >db.session.add(colObj) > >db.session.commit() > > > > In this example, `colObjs` has two objects. > > Not sure about what causes that, but maybe you just need to commit once, > at > the end of the loop, that is something like > > colObjs= db.session.query(Column).filter_by(chart_id=oldChartID).all() > for colObj in colObjs: > make_transient(colObj) > print colObj.id > del colObj.id > colObj.chart_id= newChartID > db.session.add(colObj) > # Single commit, when the loop above exits > db.session.commit() > > ciao, lele. > -- > nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri > real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. > le...@metapensiero.it | -- Fortunato > Depero, 1929. > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] KeyError when looping thru class objects to try and copy them
I want to query rows and copy them while changing an attribute of each. Here's my code. colObjs= db.session.query(Column).filter_by(chart_id=oldChartID).all() for colObj in colObjs: make_transient(colObj) print colObj.id del colObj.id colObj.chart_id= newChartID db.session.add(colObj) db.session.commit() In this example, `colObjs` has two objects. I loop thru `colObjs`, going over each `colObj`. The first item in the loop copies fine. But when I try copying the second one, I get this error. Traceback (most recent call last): File "/path/to/local/lib/python2.7/site-packages/flask/app.py", line 1997, in __call__ return self.wsgi_app(environ, start_response) File "/path/to/local/lib/python2.7/site-packages/flask/app.py", line 1985, in wsgi_app response = self.handle_exception(e) File "/path/to/local/lib/python2.7/site-packages/flask/app.py", line 1540, in handle_exception reraise(exc_type, exc_value, tb) File "/path/to/local/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app response = self.full_dispatch_request() File "/path/to/local/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request rv = self.handle_user_exception(e) File "/path/to/local/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception reraise(exc_type, exc_value, tb) File "/path/to/local/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request rv = self.dispatch_request() File "/path/to/local/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/path/to/myApp.py", line 859, in copyGraphic del colObj.id File "/path/to/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 227, in __delete__ self.impl.delete(instance_state(instance), instance_dict(instance)) File "/path/to/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 679, in delete del dict_[self.key] KeyError: 'id' Furthermore, the output of the `print` statement on the first item of the loop shows the `coloObj` id, but the second one outputs `None`. Why does this error happen? How do I fix it? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: How do I copy an object and save to database?
Thank you On Wednesday, December 20, 2017 at 8:37:57 AM UTC-5, Lele Gaifax wrote: > > Tom Tanner <dontsende...@gmail.com > writes: > > > Lele, could you write the code showing how that would look? Thanks. > > Extending Mike's answer: > > from sqlalchemy.orm import make_transient > make_transient(someObj) # someObj is no longer in the session > del someObj.id# assume this is the primary key > session.add(someObj) > session.commit() > assert someObj.id # here is the new PK on the cloned object > > ciao, lele. > -- > nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri > real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. > le...@metapensiero.it | -- Fortunato > Depero, 1929. > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: How do I copy an object and save to database?
Lele, could you write the code showing how that would look? Thanks. On Wednesday, December 20, 2017 at 2:22:49 AM UTC-5, Lele Gaifax wrote: > > Tom Tanner <dontsende...@gmail.com > writes: > > > Thanks, I used the first method. Follow up question: How do I get the > new > > primary key after running `session.commit()`? > > After a commit SA should have (re)populated the "id" field (or whatever > it's > PK field is named) of the cloned object. > > ciao, lele. > -- > nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri > real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. > le...@metapensiero.it | -- Fortunato > Depero, 1929. > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How do I copy an object and save to database?
Thanks, I used the first method. Follow up question: How do I get the new primary key after running `session.commit()`? On Tuesday, December 19, 2017 at 11:29:34 AM UTC-5, Mike Bayer wrote: > > On Tue, Dec 19, 2017 at 3:02 AM, Tom Tanner > <dontsende...@gmail.com > wrote: > > I'm using SQLAlchemy with the Python library Flask. > > > > How do I query an object from a database, make a copy of it, and save > that > > copy to the database? > > > > Example of querying an object. > > someObj = db.session.query(SomeDataTable).filter_by(id=someID).first() > > > > > > I want to save a copy of `someObj` to the table. How do I do this? > > OK so "copy" we will take to mean, a new row in the table, with all > the same data, *except* for the primary key, as that has to be > different. > > The kind of primary key your object has is important here. Let's say > it's a single integer "id" column that is generated from the database. > You'd do this: > > from sqlalchemy.orm import make_transient > make_transient(someObj) # someObj is no longer in the session > del someObj.id # assume this is the primary key > session.add(someObj) > session.commit() > > > if OTOH your object has a natural primary key (one that you set in > python), then instead of "del someObj.id" you would assign a new > primary key value to be used. > > > that's it ! > > > > All you do is this: > > > > > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google > Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an > > email to sqlalchemy+...@googlegroups.com . > > To post to this group, send email to sqlal...@googlegroups.com > . > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] How do I copy an object and save to database?
I'm using SQLAlchemy with the Python library Flask. How do I query an object from a database, make a copy of it, and save that copy to the database? Example of querying an object. someObj = db.session.query(SomeDataTable).filter_by(id=someID).first() I want to save a copy of `someObj` to the table. How do I do this? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] could not translate host name
I'm running sqlalchemy and have a cron job that runs hourly. Occasionally I get email from the cron daemon that contains a stack trace that ends with this: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "db3.labmed.uw.edu" to address: Name or service not known It has happened about 9 times so far this month. I have one of our network people look into this but there is no real answer. I could use the IP address but I'd rather not. This problem only happens on one host. I moved the data from db2 to db3 and was getting similar emails on db2. Any ideas on how to proceed? Tom -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: patterns for automated tests of migrations
hi chris, here’s how we do it in all our projects: https://github.com/pyfidelity/rest-seed/blob/master/backend/backrest/tests/test_migrations.py basically, our migrations start with an empty database, so we run them, dump the resulting SQL, then create a new database using the `metadata.create_all` feature and then normalize the results and compare. if there is a mismatch the test fails. if you want to write more elaborate tests involving actual data, this should be a good starting point, though HTH, tom > On 23 Nov 2016, at 10:15, Chris Withers <ch...@simplistix.co.uk> wrote: > > Hi All, > > How do you go about writing automated tests for a migration? > > I don't often do this, but when migrations involve data, I prefer to but now > I don't know how ;-) > There's nothing in the docs, right? (or am I missing something obvious) > > cheers, > > Chris > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy-alembic" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy-alembic+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. signature.asc Description: Message signed with OpenPGP using GPGMail
Is there a way to generate DDL of current state as of a revision?
I work in a team of people collaborating on database development and we'd like to start version controlling and deploy our changes with Alembic. I was wondering though if there is a way to generate the DDL for the current state of the whole schema as of a given revision... something like the 'offline' SQL generation, however rather than every intermediate revision, just output the CREATE statements of the final states of all the objects. You might ask why anyone would want to do this... the reason is that we generally like to do code reviews or pull requests when someone deploys a change. If the only artifacts stored in source control are alembic migrations, it can be difficult for a reviewer to get an idea of the overall context and impact of a given migration or set of migrations. If we could use alembic to update a file or set of files representing the current state of the entire DB, then that could be checked into version control along with the migrations. Granted it would be useless for migrating a given instance of a database from one state to another, but it would be a useful and more readable way for a dev to understand all the objects in a database, short of logging into a live instance of one and poking around. -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to define relationship in inherited declarative class?
I see. I think I can do okay without UserKeyword.user, as you suggested.
Seems to work.
Though is it really necessary to define user_keywords relationship both on
user and super_user? It seems to be working with only defining it in user.
On Sunday, August 21, 2016 at 9:33:01 PM UTC+3, Mike Bayer wrote:
>
>
>
> On 08/21/2016 10:22 AM, Tom Kedem wrote:
> > It seems I confused "concrete" with "joined" inheritance.
> >
> > What I want to achieve is /joined/ inheritance. I've modified the code
> > to reflect that (just removed all concrete references).
> > According to the documentation relationships on joined inheritance are
> > inherited, but I still get an error saying it doesn't recognize
> > super_user as a child of user.
>
>
> This is the error:
>
> sqlalchemy.orm.exc.FlushError: Attempting to flush an item of type
> as a member of collection
> "UserKeyword.user". Expected an object of type
> or a polymorphic subclass of this type. If
> is a subclass of , configure mapper
> "Mapper|User|user" to load this subtype polymorphically, or set
> enable_typechecks=False to allow any subtype to be accepted for flush.
>
>
> this error is more of a warning to stop you from proceeding as though
> things are "normal" - it has no problem persisting a SuperUser here,
> however when you go later to load some_user_keyword.user, it will query
> the user table only, and return a User object, not a SuperUser. It's
> not possible for it to detect a SuperUser because you aren't using a
> discriminator.
>
> Your options are:
>
> 1. make two separate user_keywords relationships, and get rid of
> UserKeyword.user totally, since it can't be relied upon to load a
> SuperUser.
>
> 2. same thing, but keep UserKeyword.user and set the above-mentioned
> enable_typechecks=False on it. Still risky to call upon it because it
> can't load a SuperUser. see attached.
>
> Unfortunately, it does not seem to be possible to have a "user" and a
> separate "super_user" relationship on UserKeyword that share the same
> UserKeyword.user_id parameter, and they appear to conflict on flush.
>
> 3. Use polymorphic loading without a discriminator column, by using a
> CASE expression that checks for the super_user table being present in a
> row. This is a lot like what the concrete polymorphic loading does,
> though we don't have the declarative helpers for this pattern. Setting
> it up would be a little more manual and it means all queries for User,
> or at least the ones from UserKeyword.user if we made a special mapper
> just for this operation, would query an outer join against both tables.
> I can try to work this out if you are interested. But I don't think you
> even need to have UserKeyword.user here.
>
>
>
>
>
> >
> >
> > On Sunday, August 21, 2016 at 6:37:19 AM UTC+3, Mike Bayer wrote:
> >
> >
> >
> > On 08/20/2016 08:27 PM, Tom Kedem wrote:
> > > I suppose I could have a discriminator value for "function type",
> > but I
> > > have no use for it now (so yeah, the base class is a single column
> > one).
> > >
> > > It's a simplified model. The real use-case is as such - the base
> > class
> > > is "function" and the inheriting classes are all sorts of
> > functions (all
> > > concrete classes). They all have a collection of "arguments" (many
> to
> > > many), which I define in the base class - since the "arguments"
> > can only
> > > point to a single table.
> > >
> > > Maybe it's not the best mapping, I'm open for suggestions. But is
> > there
> > > anything wrong in my configuration or understanding?
> >
> > Two things do not make sense, in terms of the use of ConcreteBase
> and
> > "concrete=True".
> >
> > One is:
> >
> >
> > id = Column(Integer, ForeignKey(User.id), primary_key=True)
> >
> > on SuperUser.
> >
> > The other is:
> >
> > class UserKeyword(Base):
> > __tablename__ = 'user_keyword'
> > user_id = Column(Integer, ForeignKey('user.id
> > <http://user.id>'), primary_key=True)
> >
> >
> > both of these imply that the fields of a SuperUser object are stored
> in
> > the "super_user" table *and* the "user" table.
> >
> > Also, when
Re: [sqlalchemy] How to define relationship in inherited declarative class?
It seems I confused "concrete" with "joined" inheritance.
What I want to achieve is *joined* inheritance. I've modified the code to
reflect that (just removed all concrete references).
According to the documentation relationships on joined inheritance are
inherited, but I still get an error saying it doesn't recognize super_user
as a child of user.
On Sunday, August 21, 2016 at 6:37:19 AM UTC+3, Mike Bayer wrote:
>
>
>
> On 08/20/2016 08:27 PM, Tom Kedem wrote:
> > I suppose I could have a discriminator value for "function type", but I
> > have no use for it now (so yeah, the base class is a single column one).
> >
> > It's a simplified model. The real use-case is as such - the base class
> > is "function" and the inheriting classes are all sorts of functions (all
> > concrete classes). They all have a collection of "arguments" (many to
> > many), which I define in the base class - since the "arguments" can only
> > point to a single table.
> >
> > Maybe it's not the best mapping, I'm open for suggestions. But is there
> > anything wrong in my configuration or understanding?
>
> Two things do not make sense, in terms of the use of ConcreteBase and
> "concrete=True".
>
> One is:
>
>
> id = Column(Integer, ForeignKey(User.id), primary_key=True)
>
> on SuperUser.
>
> The other is:
>
> class UserKeyword(Base):
> __tablename__ = 'user_keyword'
> user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
>
>
> both of these imply that the fields of a SuperUser object are stored in
> the "super_user" table *and* the "user" table.
>
> Also, when you say "I suppose I could have a discriminator..." in
> response to the question, "did you mean for this to be joined
> inheritance?", that suggests you might be under the impression that
> "don't have a discriminator" means you must use "concrete inheritance",
> which is not true.A "discriminator" is not necessary for any style
> of inheritance, as long as you don't need to load objects polymorphically.
>
> Same question as before, more specifically. When you load a row from
> "super_user" in order to get a SuperUser object, should the ORM also be
> loading a row from "user" that matches up to it? Or can you get every
> possible field in a SuperUser from the "super_user" table alone without
> ever looking at "user"? If the former, that would be joined
> inheritance. that's what this looks like.
>
>
>
>
>
>
>
>
>
> >
> > On Saturday, August 20, 2016 at 10:44:24 PM UTC+3, Mike Bayer wrote:
> >
> > This doesn't look like a concrete mapping, you have a foreign key
> > from SuperUser to User. Are you sure this isn't supposed to be an
> > ordinary joined inheritance model ?
> >
> > On Saturday, August 20, 2016, Tom Kedem <tomk...@gmail.com
> > > wrote:
> >
> > I have the following setup (attached python file).
> > I'm using an inheritance hierarchy without a discriminator
> > field, deriving from AbstractBase.
> > I want to be able to use the "keywords" attribute in the
> > "SuperUser" class, and from the documentation I understand I
> > need to redefine it, however that doesn't seem to work.
> > I assume I could manually use a primary join there (as the error
> > indicates), but as I understand that's exactly what
> > "AbstractBase" class should handle...
> >
> > --
> > You received this message because you are subscribed to the
> > Google Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from
> > it, send an email to sqlalchemy+...@googlegroups.com
> .
> > To post to this group, send email to sqlal...@googlegroups.com
> .
> > Visit this group at https://groups.google.com/group/sqlalchemy
> > <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> > <https://groups.google.com/d/optout>.
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to sqlalchemy+...@googlegroups.com
> > <
Re: [sqlalchemy] How to define relationship in inherited declarative class?
I suppose I could have a discriminator value for "function type", but I have no use for it now (so yeah, the base class is a single column one). It's a simplified model. The real use-case is as such - the base class is "function" and the inheriting classes are all sorts of functions (all concrete classes). They all have a collection of "arguments" (many to many), which I define in the base class - since the "arguments" can only point to a single table. Maybe it's not the best mapping, I'm open for suggestions. But is there anything wrong in my configuration or understanding? On Saturday, August 20, 2016 at 10:44:24 PM UTC+3, Mike Bayer wrote: > > This doesn't look like a concrete mapping, you have a foreign key from > SuperUser to User. Are you sure this isn't supposed to be an ordinary > joined inheritance model ? > > On Saturday, August 20, 2016, Tom Kedem <tomk...@gmail.com > > wrote: > >> I have the following setup (attached python file). >> I'm using an inheritance hierarchy without a discriminator field, >> deriving from AbstractBase. >> I want to be able to use the "keywords" attribute in the "SuperUser" >> class, and from the documentation I understand I need to redefine it, >> however that doesn't seem to work. >> I assume I could manually use a primary join there (as the error >> indicates), but as I understand that's exactly what "AbstractBase" class >> should handle... >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] How to define relationship in inherited declarative class?
I have the following setup (attached python file).
I'm using an inheritance hierarchy without a discriminator field, deriving
from AbstractBase.
I want to be able to use the "keywords" attribute in the "SuperUser" class,
and from the documentation I understand I need to redefine it, however that
doesn't seem to work.
I assume I could manually use a primary join there (as the error
indicates), but as I understand that's exactly what "AbstractBase" class
should handle...
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import Column
from sqlalchemy import String
from sqlalchemy import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import ConcreteBase
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
# Inheritance without discriminator
class User(ConcreteBase, Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
keywords = association_proxy('user_keywords', 'keyword')
__mapper_args__ = {'polymorphic_identity': 'user', 'concrete': True}
class UserKeyword(Base):
__tablename__ = 'user_keyword'
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)
special_key = Column(String(50))
user = relationship(User,
backref=backref("user_keywords",
cascade="all, delete-orphan")
)
keyword = relationship("Keyword")
def __init__(self, keyword=None, user=None, special_key=None):
self.user = user
self.keyword = keyword
self.special_key = special_key
class Keyword(Base):
__tablename__ = 'keyword'
id = Column(Integer, primary_key=True)
keyword = Column('keyword', String(64))
def __init__(self, keyword):
self.keyword = keyword
def __repr__(self):
return 'Keyword(%s)' % repr(self.keyword)
class SuperUser(User):
__tablename__ = 'super_user'
id = Column(Integer, ForeignKey(User.id), primary_key=True)
role = Column(String(64))
__mapper_args__ = {'polymorphic_identity': 'super_user', 'concrete': True}
#
user_keywords = relationship(UserKeyword)
keywords = association_proxy('user_keywords', 'keyword')
engine = create_engine('sqlite:///:memory:', echo=False)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
user = SuperUser()
for kw in (Keyword('new_from_blammo'), Keyword('its_big')):
user.keywords.append(kw)
session.add(user)
session.commit()
[sqlalchemy] Re: Creating declared class in __declare_first__ causes RuntimeError: deque mutated during iteration
Figured it out. It wasn't FlaskSqlalchemy, it was me. The decalrative base class inherited from References which had the __declarefirst__ in it. Trying to instantiate a declarative class (which inherited from References) inside __declarefirst__ fired that event, which caused the error. I removed the References class from the declarative base up the hierarchy (created a base class inheriting from declarative base, which is marked with __abstract__=true). Solved the problem. Thanks for the support :) I hope to release my results, once I finish, as part of sqlalchemy utils. On Monday, August 15, 2016 at 4:38:34 PM UTC+3, Tom Kedem wrote: > > I'm trying to create a dynamic many_to_many relationship, inspired by: > http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/ > > https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/f50cbe745a19/atmcraft/model/meta/?at=master > > (orm.py and schema.py) > > I've got it working with just creating a secondary table in the > __declare_first__ hook. > > Now I want it to be a "secondary" class and not just a table - and I'm > having trouble. > I've created a declared class in the __declare_first__ hook. Creating that > class causes (Full stacktrace at the bottom): > RuntimeError: deque mutated during iteration > > I suppose new events are being added when I do so. Searching google I came > upon these: > > https://bitbucket.org/zzzeek/sqlalchemy/issues/3163/use-deque-for-event-lists-so-that-remove > > https://bitbucket.org/zzzeek/sqlalchemy/commits/4a4cccfee5a2#Llib/sqlalchemy/event/api.pyT61 > > But since I'm not creating those events explicitly (I suspect the declared > class creates them), I'm not sure about how to proceed. > > Full stacktrace if it's helpful: > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\instrumentation.py", > > line 347, in _new_state_if_none > state = self._state_constructor(instance, self) > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\util\langhelpers.py", > > line 754, in __get__ > obj.__dict__[self.__name__] = result = self.fget(obj) > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\instrumentation.py", > > line 177, in _state_constructor > self.dispatch.first_init(self, self.class_) > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\event\attr.py", > line 256, in __call__ > fn(*args, **kw) > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\mapper.py", > line 2943, in _event_on_first_init > configure_mappers() > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\mapper.py", > line 2822, in configure_mappers > Mapper.dispatch._for_class(Mapper).before_configured() > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\event\attr.py", > line 217, in __call__ > for fn in self.parent_listeners: > RuntimeError: deque mutated during iteration > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Creating declared class in __declare_first__ causes RuntimeError: deque mutated during iteration
I trimmed the business-logic part. Here's a really full stacktrace for the sandbox.py file I attached: Traceback (most recent call last): File "C:/Users/vToMy/PycharmProjects/sandbox/sandbox.py", line 197, in user = User() File "", line 2, in __init__ File "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\instrumentation.py", line 347, in _new_state_if_none state = self._state_constructor(instance, self) File "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\util\langhelpers.py", line 754, in __get__ obj.__dict__[self.__name__] = result = self.fget(obj) File "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\instrumentation.py", line 177, in _state_constructor self.dispatch.first_init(self, self.class_) File "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\event\attr.py", line 256, in __call__ fn(*args, **kw) File "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\mapper.py", line 2941, in _event_on_first_init configure_mappers() File "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\mapper.py", line 2820, in configure_mappers Mapper.dispatch._for_class(Mapper).before_configured() File "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\event\attr.py", line 217, in __call__ for fn in self.parent_listeners: RuntimeError: deque mutated during iteration I'm not sure it's flask-sqlalchemy's issue, but it's related to it. Looking at their base class creation I don't notice anything "funny", but I'm probably overlooking something. On Monday, August 15, 2016 at 4:38:34 PM UTC+3, Tom Kedem wrote: > > I'm trying to create a dynamic many_to_many relationship, inspired by: > http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/ > > https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/f50cbe745a19/atmcraft/model/meta/?at=master > > (orm.py and schema.py) > > I've got it working with just creating a secondary table in the > __declare_first__ hook. > > Now I want it to be a "secondary" class and not just a table - and I'm > having trouble. > I've created a declared class in the __declare_first__ hook. Creating that > class causes (Full stacktrace at the bottom): > RuntimeError: deque mutated during iteration > > I suppose new events are being added when I do so. Searching google I came > upon these: > > https://bitbucket.org/zzzeek/sqlalchemy/issues/3163/use-deque-for-event-lists-so-that-remove > > https://bitbucket.org/zzzeek/sqlalchemy/commits/4a4cccfee5a2#Llib/sqlalchemy/event/api.pyT61 > > But since I'm not creating those events explicitly (I suspect the declared > class creates them), I'm not sure about how to proceed. > > Full stacktrace if it's helpful: > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\instrumentation.py", > > line 347, in _new_state_if_none > state = self._state_constructor(instance, self) > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\util\langhelpers.py", > > line 754, in __get__ > obj.__dict__[self.__name__] = result = self.fget(obj) > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\instrumentation.py", > > line 177, in _state_constructor > self.dispatch.first_init(self, self.class_) > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\event\attr.py", > line 256, in __call__ > fn(*args, **kw) > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\mapper.py", > line 2943, in _event_on_first_init > configure_mappers() > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\mapper.py", > line 2822, in configure_mappers > Mapper.dispatch._for_class(Mapper).before_configured() > File > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\event\attr.py", > line 217, in __call__ > for fn in self.parent_listeners: > RuntimeError: deque mutated during iteration > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Creating declared class in __declare_first__ causes RuntimeError: deque mutated during iteration
Thanks the for quick reply. Trying to mush it to a single file I accidentally found the source (not the solution unfortunately) of the problem. It only happens when I use FlaskSqlalchemy as the base class... so maybe it's something on their part? I attached a file (python 3). You must use flask-sqlalchemy master to run it, as it uses a custom base class which I think isn't released yet. I suppose their base class generates those events. I commented out the declarative_base usage that works (see attached file). On Monday, August 15, 2016 at 4:57:19 PM UTC+3, Mike Bayer wrote: > > can you attach a quick single-file .py script that I can run, and will > show how you're getting this error? you might be in an "impossible" > block there, in that you are trying to add an event listener within that > same event, but I need to see if what you're doing is really something > that should be made to work. That is, perhaps declarative should use a > different system internally for the __declare_first__ in order to get > around this. > > > > On 08/15/2016 09:38 AM, Tom Kedem wrote: > > I'm trying to create a dynamic many_to_many relationship, inspired by: > > http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/ > > > https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/f50cbe745a19/atmcraft/model/meta/?at=master > > > (orm.py and schema.py) > > > > I've got it working with just creating a secondary table in the > > __declare_first__ hook. > > > > Now I want it to be a "secondary" class and not just a table - and I'm > > having trouble. > > I've created a declared class in the __declare_first__ hook. Creating > > that class causes (Full stacktrace at the bottom): > > RuntimeError: deque mutated during iteration > > > > I suppose new events are being added when I do so. Searching google I > > came upon these: > > > https://bitbucket.org/zzzeek/sqlalchemy/issues/3163/use-deque-for-event-lists-so-that-remove > > > > https://bitbucket.org/zzzeek/sqlalchemy/commits/4a4cccfee5a2#Llib/sqlalchemy/event/api.pyT61 > > > > > But since I'm not creating those events explicitly (I suspect the > > declared class creates them), I'm not sure about how to proceed. > > > > Full stacktrace if it's helpful: > > File > > > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\instrumentation.py", > > > > line 347, in _new_state_if_none > > state = self._state_constructor(instance, self) > > File > > > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\util\langhelpers.py", > > > > line 754, in __get__ > > obj.__dict__[self.__name__] = result = self.fget(obj) > > File > > > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\instrumentation.py", > > > > line 177, in _state_constructor > > self.dispatch.first_init(self, self.class_) > > File > > > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\event\attr.py", > line > > 256, in __call__ > > fn(*args, **kw) > > File > > > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\mapper.py", > line > > 2943, in _event_on_first_init > > configure_mappers() > > File > > > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\orm\mapper.py", > line > > 2822, in configure_mappers > > Mapper.dispatch._for_class(Mapper).before_configured() > > File > > > "C:\Users\vToMy\PycharmProjects\sqlalchemy\lib\sqlalchemy\event\attr.py", > line > > 217, in __call__ > > for fn in self.parent_listeners: > > RuntimeError: deque mutated during iteration > > > > > > -- > > You received this message because you are subscribed to the Google > > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > > an email to sqlalchemy+...@googlegroups.com > > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. > > To post to this group, send email to sqlal...@googlegroups.com > > > <mailto:sqlal...@googlegroups.com >. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this grou
Re: [sqlalchemy] Re: executemany() incorrectly used for updates to a table with row versioning with psycopg2
Hi Mike, Your fix worked - the example I gave and our other tests pass now. Many thanks, Tom On Mon, 19 Oct 2015 at 17:19 Mike Bayer <mike...@zzzcomputing.com> wrote: > > > On 10/19/15 11:57 AM, Mike Bayer wrote: > > > > > > On 10/19/15 11:44 AM, Steven Winfield wrote: > >> The problem seems to be that the result set from an executemany() call > >> in psycopg2 is discarded - here is a good stackoverflow article on this > >> subject: > >> > http://stackoverflow.com/questions/21624844/getting-ids-of-multiple-rows-inserted-in-psycopg2 > >> > >> In _emit_update_statements in orm/persistence.py, and here > >> < > http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html?highlight=version_id_col#update-statements-are-now-batched-with-executemany-in-a-flush > > > >> in the v1.0 migration docs, one of the conditions for using executemany > is: > >> "The mapping does not use a |version_id_col| > >> < > http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html#sqlalchemy.orm.mapper.params.version_id_col > >, > >> or the backend dialect supports a “sane” rowcount for an executemany() > >> operation;" > >> > >> ...but I'm not sure that is enough - psycopg2 does support sane > >> rowcounts, and its dialect's supports_sane_rowcount and > >> supports_sane_multi_rowcount are both rightly True (I work with Tom, and > >> we're using psycopg2 v2.6 by the way - I know those values are > >> predicated on the psycopg2 version). > >> > >> I'm working on a patch that adds another member to the default dialect > >> called "supports_executemany_results" that defaults to True > > > > I wouldn't do that, no DBAPI has results for executemany. The flag is > > not needed. > > > > This is very likely yet another 1.0 regression caused by > > > http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#update-statements-are-now-batched-with-executemany-in-a-flush > > and not checking closely enough for the right conditions. I'll add a > > new ticket soon. > > issue > > https://bitbucket.org/zzzeek/sqlalchemy/issues/3556/update-executemany-enhancement-breaks > which is now resolved. I will try to release 1.0.9 this week; please > confirm the fix in the latest rel_1_0 branch fixes this issue for you as > well, thanks! > > > > > > > > > > > > > > > but is set > >> to False in PGDialect_psycopg2 in dialects/postgres/psycopg2.py. This > >> can then be used in the determination of allow_multirow (line 646 in > >> orm/persistence.py). This will preserve the current behaviour of other > >> dialects, but of course there could be others with the same problem. > >> > >> Steve > >> > >> On Monday, October 19, 2015 at 4:22:36 PM UTC+1, Tom Flannaghan wrote: > >> > >> Hi, > >> > >> We are currently trying to upgrade to sqlalchemy 1.0.8 and have hit > >> a problem with executemany() and row versioning. We are using > >> postgres and psycopg2 as a backend. We have tables that use server > >> side versioning (as discussed at > >> > http://docs.sqlalchemy.org/en/rel_0_9/orm/versioning.html#server-side-version-counters > >> < > http://docs.sqlalchemy.org/en/rel_0_9/orm/versioning.html#server-side-version-counters > >), > >> and when multiple rows are updated, the UPDATE statements are > >> batched together using executemany() in the new version of > >> sqlalchemy. Unfortunately, psycopg2 does not support iterating over > >> the results of an executemany() query (see > >> http://initd.org/psycopg/docs/cursor.html#cursor.executemany > >> <http://initd.org/psycopg/docs/cursor.html#cursor.executemany>) so > >> the new row versions returned by the update statement cannot be > read. > >> > >> Here is an example that illustrates the problem: > >> > >> from sqlalchemy import Column, FetchedValue, Integer, String > >> from sqlalchemy.ext.declarative import declarative_base > >> from sqlalchemy.orm import sessionmaker > >> > >> Base = declarative_base() > >> > >> class Port(Base): > >> ''' Table for storing ports. ''' > >> __tablename__ = "port" > >> > >> name = Column(String, primary_key=True) > >> port = Column(Integer) > >> > >> xmin = Column(Integ
[sqlalchemy] executemany() incorrectly used for updates to a table with row versioning with psycopg2
Hi,
We are currently trying to upgrade to sqlalchemy 1.0.8 and have hit a
problem with executemany() and row versioning. We are using postgres and
psycopg2 as a backend. We have tables that use server side versioning (as
discussed at
http://docs.sqlalchemy.org/en/rel_0_9/orm/versioning.html#server-side-version-counters),
and when multiple rows are updated, the UPDATE statements are batched
together using executemany() in the new version of sqlalchemy.
Unfortunately, psycopg2 does not support iterating over the results of an
executemany() query (see
http://initd.org/psycopg/docs/cursor.html#cursor.executemany) so the new
row versions returned by the update statement cannot be read.
Here is an example that illustrates the problem:
from sqlalchemy import Column, FetchedValue, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Port(Base):
''' Table for storing ports. '''
__tablename__ = "port"
name = Column(String, primary_key=True)
port = Column(Integer)
xmin = Column(Integer, server_default=FetchedValue(),
server_onupdate=FetchedValue(), system=True)
__mapper_args__ = {"version_id_col": xmin,
"version_id_generator": False}
engine = create_engine(...)
Session = sessionmaker(bind=engine)
session = Session()
session.bind.echo = True
p1 = Port(name='a', port=1)
p2 = Port(name='b', port=2)
session.add(p1)
session.add(p2)
session.flush()
# this update fails
p1.port = 3
p2.port = 4
session.flush()
session.rollback()
The SQL generated for the update looks like this:
2015-10-19 14:46:30,289 INFO sqlalchemy.engine.base.Engine UPDATE comm.port
SET port=%(port)s WHERE comm.port.name = %(comm_port_name)s AND
comm.port.xmin = %(comm_port_xmin)s RETURNING comm.port.xmin
2015-10-19 14:46:30,289 INFO sqlalchemy.engine.base.Engine
({'comm_port_xmin': '536394944', 'comm_port_name': 'a', 'port': 3},
{'comm_port_xmin': '536394944', 'comm_port_name': 'b', 'port': 4})
This code raises the following exception:
File user!flannt!untitled6.py, line 52, in : session.flush()
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py,
line 2004, in flush : self._flush(objects)
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py,
line 2122, in _flush : transaction.rollback(_capture_exception=True)
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\util\langhelpers.py,
line 60, in __exit__ : compat.reraise(exc_type, exc_value, exc_tb)
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py,
line 2086, in _flush : flush_context.execute()
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\unitofwork.py,
line 373, in execute : rec.execute(self)
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\unitofwork.py,
line 532, in execute : uow
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\persistence.py,
line 170, in save_obj : mapper, table, update)
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\persistence.py,
line 692, in _emit_update_statements : execute(statement, multiparams)
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py,
line 914, in execute : return meth(self, multiparams, params)
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\sql\elements.py,
line 323, in _execute_on_connection : return
connection._execute_clauseelement(self, multiparams, params)
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py,
line 1010, in _execute_clauseelement : compiled_sql, distilled_params
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py,
line 1159, in _execute_context : result =
context._setup_crud_result_proxy()
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\default.py,
line 832, in _setup_crud_result_proxy : row = result.fetchone()
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\result.py,
line 1026, in fetchone : self.cursor, self.context)
File
I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py,
line 1341, in _handle_dbapi_exception : exc_info
File
Re: [sqlalchemy] Columns not deferred when an object is merged into a session with load=False
Thanks a lot! This works well. Tom On Thu, 16 Jul 2015 at 21:23 Mike Bayer mike...@zzzcomputing.com wrote: On 7/16/15 2:28 PM, Tom Flannaghan wrote: Thanks for your reply. Our exact problem is that we are creating empty detached objects from the primary key alone, and then merging them in to a session, so we can't do this: On Thursday, 16 July 2015 18:44:26 UTC+1, Michael Bayer wrote: For now, I'd recommend either not using expire() or specifying specific attribute names to expire(). I just included the expire() in the example as it was a more succinct way to reproduce the same bug. Our code looks more like this: detached_port = Port(name='test') make_transient_to_detached(detached_port) new_port = session.merge(detached_port, load=False) ... In my example, Port only has two columns so this won't demonstrate the bug as the only non-deferred column is filled in already, but more complicated objects that are merged in this way will not defer columns. Do you think there a work around in this case? try this recipe which should reset the expired state of the target attributes individually: from sqlalchemy.orm import attributes def merge_load_false(session, obj): obj = session.merge(obj, load=False) obj_state = attributes.instance_state(obj) obj_dict = obj_state.dict deferred_keys = [ attr.key for attr in obj_state.mapper.column_attrs if attr.deferred] for k in deferred_keys: if k not in obj_dict: obj_state._reset(obj_dict, k) return obj a1 = merge_load_false(s, a1) Thanks, Tom -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/gRV7mSHFJiE/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Columns not deferred when an object is merged into a session with load=False
We are using sqlalchemy and postgres extensively, and have come across a problem with deferred columns when an object is merged into a session with load=False. Under these conditions, we find that the deferred columns are not deferred - i.e. they loaded when any attribute is accessed, rather than being deferred until explicitly accessed. We are using sqlalchemy 0.9.7 and postgres 9.4.1. Here's a simple example that illustrates the problem using the following table: class Port(Base): ''' Table port in schema comm. ''' __tablename__ = port name = Column(String, primary_key=True) port = deferred(Column(Integer, unique=True)) First we try querying Port on name: my_port = session.query(Port).filter_by(name='test_thing').one() my_port.name This code produces the following query, showing that the port column is correctly deferred: 2015-07-16 15:44:08.539 : sqlalchemy.engine.base.Engine : base.py:912(_execute_context) : INFO : SELECT comm.port.name AS comm_port_name FROM comm.port WHERE comm.port.name = %(name_1)s Now I expire and expunge the my_port object, and then merge it back into the session with load=False, and access name like this: session.expire(my_port) session.expunge(my_port) new_port = session.merge(my_port, load=False) new_port.name Which produces the following query, showing that the port column has not been deferred, and has instead been included in the query that fetches name: 2015-07-16 15:44:08.539 : sqlalchemy.engine.base.Engine : base.py:912(_execute_context) : INFO : SELECT comm.port.port AS comm_port_port, comm.port.name AS comm_port_name FROM comm.port WHERE comm.port.name = %(param_1)s If I do not set load=False, the column is correctly deferred. I've also tried creating a deferred object directly rather than expunging one from the session and the problem still occurs, so it seems to be caused by the load=False option. Thanks, Tom -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Columns not deferred when an object is merged into a session with load=False
Thanks for your reply. Our exact problem is that we are creating empty detached objects from the primary key alone, and then merging them in to a session, so we can't do this: On Thursday, 16 July 2015 18:44:26 UTC+1, Michael Bayer wrote: For now, I'd recommend either not using expire() or specifying specific attribute names to expire(). I just included the expire() in the example as it was a more succinct way to reproduce the same bug. Our code looks more like this: detached_port = Port(name='test') make_transient_to_detached(detached_port) new_port = session.merge(detached_port, load=False) ... In my example, Port only has two columns so this won't demonstrate the bug as the only non-deferred column is filled in already, but more complicated objects that are merged in this way will not defer columns. Do you think there a work around in this case? Thanks, Tom -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] How to determine if a session has any uncommitted changes
Using SQLAlchemy 0.9.
I am writing a server, that's running in gevent. As part of the library
code, I am trying to write a session context manager that checks if the
session has uncommitted changes when it's exited (this situation represents
a logic/programming error, as I expect every server 'request' to finish
with a committed or rolled back transaction. Each request has its own
session but there may be multiple transactions within that request/session.
Based on the docs for Session and this question at stackoverflow
(http://stackoverflow.com/questions/13910576/find-out-how-many-uncommitted-items-are-in-the-session)
I wrote a context manager like so:
@contextmanager
def checked_scoped_session():
session = get_session()
try:
yield session
except:
session.rollback()
raise
finally:
if session.new or session.dirty or session.deleted:
new = len(session.new)
dirty = len(session.dirty)
deleted = len(session.deleted)
msg = Session left with ({} new, {} dirty, {} deleted)
instances\
uncommitted.format(new, dirty, deleted)
raise SessionLeftUnclean(msg)
session.close()
While testing, I discovered that session.new, .dirty and .deleted appear to
actually be UNFLUSHED instances, and not uncommitted. This is a problem
since I am using (and want to keep using) autoflush. I have been reading up
on session.transaction, however, it's still not clear to me how I can tell
if the transaction has (uncommitted) changes, and this seems to be further
complicated by the possibility of sub-transactions.
Is there an easy way to do what I want? E.g. Ideally I want to do:
finally:
if session.has_uncommitted_changes():
raise SessionLeftUnclean()
Any and all help gratefully appreciated!
Regards,
Tom
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to determine if a session has any uncommitted changes
On Thursday, 9 October 2014 16:51:49 UTC+1, Michael Bayer wrote:
On Oct 9, 2014, at 6:09 AM, Tom Dalton tom.d...@fanduel.com javascript:
wrote:
Using SQLAlchemy 0.9.
I am writing a server, that's running in gevent. As part of the library
code, I am trying to write a session context manager that checks if the
session has uncommitted changes when it's exited (this situation represents
a logic/programming error, as I expect every server 'request' to finish
with a committed or rolled back transaction. Each request has its own
session but there may be multiple transactions within that request/session.
Based on the docs for Session and this question at stackoverflow (
http://stackoverflow.com/questions/13910576/find-out-how-many-uncommitted-items-are-in-the-session)
I wrote a context manager like so:
@contextmanager
def checked_scoped_session():
session = get_session()
try:
yield session
except:
session.rollback()
raise
finally:
if session.new or session.dirty or session.deleted:
new = len(session.new)
dirty = len(session.dirty)
deleted = len(session.deleted)
msg = Session left with ({} new, {} dirty, {} deleted)
instances\
uncommitted.format(new, dirty, deleted)
raise SessionLeftUnclean(msg)
session.close()
While testing, I discovered that session.new, .dirty and .deleted appear
to actually be UNFLUSHED instances, and not uncommitted. This is a problem
since I am using (and want to keep using) autoflush. I have been reading up
on session.transaction, however, it's still not clear to me how I can tell
if the transaction has (uncommitted) changes, and this seems to be further
complicated by the possibility of sub-transactions.
Is there an easy way to do what I want? E.g. Ideally I want to do:
Checking .new, .dirty, and .deleted is not a bad idea because they
indicate activity that has occurred on the session subsequent to the last
commit().But if these changes have been flushed, then it’s “clean”, but
the transaction may not have been committed yet. Right now the
documentation encourages just using events (eg. after_begin, after_commit,
after_rollback) to track the state of the Session regarding connections as
you see fit, there’s not a public API method of checking this.
If you really want to see if the session is linked to a transaction in
progress, you could check len(session.transaction._connections), if that’s
nonzero, then there’s a DBAPI-level transaction in progress - it means
there’s one or more connections that the Session is linked to. But you
could achieve this same information using after_begin as well.
Thanks for the reply. I saw the session transaction events stuff but I'm
not sure that they help me (correct me if I'm wrong). I believe a
transaction will exist if I run *any* query, not just one that modifies
data. Also, the docs imply that a session will effectively always have a
transaction when using autoflush mode. Finally, the after_begin event only
fires once. So I'd have no way to tell the difference between the session
state after the following 3 scenarios:
1. (implicit) begin, select, update, select, (autoflush)
2. (implicit) begin, select, (autoflush)
3. (implicit) begin, select
In my use case, I want to detect the uncommitted (but flushed) changes and
throw an error, but cases 2 and 3 are 'ok'.
Even disabling autoflush doesn't help, since I'd still be unable to tell if
the (erroneous/buggy) code had done an explicit flush without a subsequent
rollback or commit.
I'm feeling a bit stuck, but I assume this information must exist in
SQLAlchemy somewhere, since if you do:
4. (implicit) begin, select X, update X, flush, rollback
then SQLAlchemy must 'know' which instance's (flushed) changes have been
rolled back in order to change those instances' state (I think in the above
example, it would mark X as detached?). The problem is I don't know where
or how it's maintaining that info...
Any more ideas?
Tom
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to determine if a session has any uncommitted changes
Brilliant, the after_flush event sounds like the way to go. I guess my event handlers would be: after_begin: session.info.clean = True after_flush: if new, dirty, deleted: session.info.clean = False after_commit: session.info.clean = True after_rollback: session.info.clean = True Then in my context manager I just need to check both (current session.new .dirty .deleted) and (my new session.info.clean attribute) to determine if the session has been left with uncommitted changes. Does that sound like what you'd expect? Are there any other events you can think of that I'd need to handle/be careful of? Thanks a lot for your help! Tom On 9 October 2014 17:42, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 9, 2014, at 12:16 PM, Tom Dalton tom.dal...@fanduel.com wrote: Thanks for the reply. I saw the session transaction events stuff but I'm not sure that they help me (correct me if I'm wrong). I believe a transaction will exist if I run *any* query, not just one that modifies data. Also, the docs imply that a session will effectively always have a transaction when using autoflush mode. Finally, the after_begin event only fires once. So I'd have no way to tell the difference between the session state after the following 3 scenarios: 1. (implicit) begin, select, update, select, (autoflush) 2. (implicit) begin, select, (autoflush) 3. (implicit) begin, select In my use case, I want to detect the uncommitted (but flushed) changes and throw an error, but cases 2 and 3 are 'ok’. OK, so catch after_begin, as well as after_flush - inside of after_flush, check a boolean for .new, .dirty, .deleted, that will tell you if the flush actually rendered any changes (which usually it has, you can just record after_flush() happening at all as “changes were probably emitted”). Even disabling autoflush doesn't help, since I'd still be unable to tell if the (erroneous/buggy) code had done an explicit flush without a subsequent rollback or commit. I'm feeling a bit stuck, but I assume this information must exist in SQLAlchemy somewhere, since if you do: 4. (implicit) begin, select X, update X, flush, rollback then SQLAlchemy must 'know' which instance's (flushed) changes have been rolled back in order to change those instances' state (I think in the above example, it would mark X as detached?). The problem is I don't know where or how it's maintaining that info… it maintains that in session.transaction._new, session.transaction._dirty, session.transaction._deleted, but these are weak referencing (because if references to the object are lost on the outside, we forget about it) so aren’t guaranteed to show you that something definitely didn’t happen. (hence events the best way to go) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: Delete class in model and downgrade, bulk_insert
So, create a Table, use that, make sure you’re sending the right kinds of
objects to bulk_insert().
Thank you very much for that very quick reply, this did the trick. The
try/except blocks in my post above were just to show what I've tried so far.
I'll include my working code so that it may help someone else:
conn = op.get_bind()
metadata = sa.MetaData()
dog_table = sa.Table('dog', metadata,
sa.Column('id', sa.INTEGER(), primary_key=True,
autoincrement=True),
sa.Column('mammal_id', sa.INTEGER(),
nullable=True),
sa.ForeignKeyConstraint(['mammal_id'],
[Mammal.id],
name='dog_mammal_id_fkey'),
sa.PrimaryKeyConstraint('id', name='dog_pkey'))
dog_table.create(conn)
new_dogs = reconstruct_dogs()
op.bulk_insert(dog_table, new_dogs)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy-alembic group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: postgresql emulating app engines list property
I went ahead and asked this on stackoverflow if anyone would like to have a
stab at it.
http://stackoverflow.com/questions/11021020/emulating-appengine-list-property-with-postgresql-array-and-sqlalchemy
On Tuesday, June 12, 2012 7:34:42 AM UTC-4, Tom Willis wrote:
Hello,
I'm hoping that some of the functionality I actually like in appengine
datastore can be duplicated in postgresql via sqlalchemy. However I'm not
quite grokking how all the moving pieces for a dialect(if that's the right
term) fit together.
On appengine there are list properties or repeated properties. This is
simply an array of values that has a custom behavior for the = in a
query. In that an object with a property named my_list who's value is
[1,2,3] will be true in the following where clauses...
where my_list=1
where my_list=2
where my_list=3
I feel like it is possible to get the same kind of behavior on postgresql
with the ARRAY column type, but I'm having trouble finding examples of how
one might do this.
Here's the code I have so far.
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.orm.properties import ColumnProperty
from sqlalchemy.orm import column_property
class ListComparator(ColumnProperty.ColumnComparator):
other operators as they make sense
optimization: override set operators for one filter instead of
multiples and'd
todo: non-string types?
def __eq__(self, other):
return self.__clause_element__().op()(u{%s} % other)
def ListColumn(*args, **kw):
makes a column of an array of types args[0]
and uses ListComparator to emulate appengine list property
if not isinstance(args[0], ARRAY):
largs = list(args)
largs[0] = ARRAY(args[0])
args = tuple(largs)
else:
raise ValueError(%s is an array which is not allowed % args[0])
return column_property(Column(*args, **kw),
comparator_factory=ListComparator)
example usage
class T(B):
__tablename__ = t
id = Column(Integer, primary_key=True)
v = ListColumn(Integer)
t = T()
t.v = [1, 2]
S.add(t)
S.commit()
S.query(T).count()
S.query(T).filter(T.v==1).count()
S.query(T).filter_by(v =1).count()
An array of strings or ints works just fine for this, but any other data
types I would have to convert to strings and in some cases escape for the
array literal syntax in postgresql. It seems like I'm missing some piece of
the puzzle in regards to how all the types in sqla relate to each other for
queries. Is it the dialect the drives that, is there behavior there for
ARRAY that I'm somehow missing or trying to duplicate?
Thanks in advance for any information you have.
--
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/-/wLflA-VmBmEJ.
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] Re: postgresql emulating app engines list property
mechanics which may
or may not need to be involved.
What specifically is the type that is failing ?
On Jun 13, 2012, at 2:24 PM, Tom Willis wrote:
I went ahead and asked this on stackoverflow if anyone would like to have
a stab at it.
http://stackoverflow.com/questions/11021020/emulating-appengine-list-property-with-postgresql-array-and-sqlalchemy
On Tuesday, June 12, 2012 7:34:42 AM UTC-4, Tom Willis wrote:
Hello,
I'm hoping that some of the functionality I actually like in appengine
datastore can be duplicated in postgresql via sqlalchemy. However I'm not
quite grokking how all the moving pieces for a dialect(if that's the right
term) fit together.
On appengine there are list properties or repeated properties. This is
simply an array of values that has a custom behavior for the = in a
query. In that an object with a property named my_list who's value is
[1,2,3] will be true in the following where clauses...
where my_list=1
where my_list=2
where my_list=3
I feel like it is possible to get the same kind of behavior on postgresql
with the ARRAY column type, but I'm having trouble finding examples of how
one might do this.
Here's the code I have so far.
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.orm.properties import ColumnProperty
from sqlalchemy.orm import column_property
class ListComparator(ColumnProperty.ColumnComparator):
other operators as they make sense
optimization: override set operators for one filter instead of
multiples and'd
todo: non-string types?
def __eq__(self, other):
return self.__clause_element__().op()(u{%s} % other)
def ListColumn(*args, **kw):
makes a column of an array of types args[0]
and uses ListComparator to emulate appengine list property
if not isinstance(args[0], ARRAY):
largs = list(args)
largs[0] = ARRAY(args[0])
args = tuple(largs)
else:
raise ValueError(%s is an array which is not allowed % args[0])
return column_property(Column(*args, **kw),
comparator_factory=ListComparator)
example usage
class T(B):
__tablename__ = t
id = Column(Integer, primary_key=True)
v = ListColumn(Integer)
t = T()
t.v = [1, 2]
S.add(t)
S.commit()
S.query(T).count()
S.query(T).filter(T.v==1).count()
S.query(T).filter_by(v =1).count()
An array of strings or ints works just fine for this, but any other data
types I would have to convert to strings and in some cases escape for the
array literal syntax in postgresql. It seems like I'm missing some piece of
the puzzle in regards to how all the types in sqla relate to each other for
queries. Is it the dialect the drives that, is there behavior there for
ARRAY that I'm somehow missing or trying to duplicate?
Thanks in advance for any information you have.
--
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/-/wLflA-VmBmEJ.
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 view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/qX1bnfLFB5MJ.
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] Re: postgresql emulating app engines list property
AH ok thanks for the tip. I'll poke around with that and see what I can
come up with.
Thanks for your help
On Wednesday, June 13, 2012 3:11:56 PM UTC-4, Michael Bayer wrote:
there's not a lot of moving parts in SQLA here. the first step would be,
figure out how this needs to render with psycopg2 directly. If you can get
it to work with psycopg2 alone, passing the desired value as a bound
parameter, then we can work out what the type engine needs to do or not.
On Jun 13, 2012, at 3:07 PM, Tom Willis wrote:
Well I had written another type to emulate appengines Key, basically an
object that will serialize/deserialize itself to json when asked and what I
thought were all the mechanics needed for type coercion in sa.
from sqlalchemy import types as sqltypes
from sqlalchemy import UnicodeText
import json
class Key(object):
identifier very similar to appengines key
serialization via json
_provider is set when session is created and we're assuming it's
done once
_provider = None
def __init__(self, kind, id, ns=None):
self._identity = dict(kind=str(kind),
id=int(id),
ns=ns)
@property
def kind(self):
return self._identity[kind]
@property
def id(self):
return self._identity[id]
@property
def ns(self):
return self._identity[ns]
def __repr__(self):
return Key(%s) % self.serialize()
def serialize(self):
return json.dumps(self._identity)
@classmethod
def deserialize(cls, k):
value = json.loads(k)
return cls(**value)
def get(self):
assert self._provider, _provider not set
return self._provider.get_for_key(self)
class KEY(sqltypes.TypeEngine):
Key Ref type for use in columns
__visit_name__ = UnicodeText.__visit_name__
def __init__(self, kind=None):
self._kind = kind
def _assert_kind(self, value):
if value and self._kind:
assert value.kind == self._kind,\
%s != %s % (value.kind, self._kind)
def bind_processor(self, dialect):
def x(value):
self._assert_kind(value)
if value is not None:
return value.serialize()
else:
return value
return x
def result_processor(self, dialect, coltype):
def x(value):
self._assert_kind(value)
if value is not None:
return Key.deserialize(value)
else:
return None
return x
So that + the code I wrote previously I figured I could do something like
this...
class T(B):
__tablename__ = t
id = Column(Integer, primary_key=True)
v = ListColumn(KEY)
kv = Key(**dict(kind=B.__name__, id=1, ns=1))
kv2 = Key(**dict(kind=B.__name__, id=2, ns=2))
t = T()
t.v = [kv, kv2]
S.add(t)
S.commit()
self.assert_(S.query(T).filter_by(v=kv).count())
self.assert_(S.query(T).filter_by(v=kv2).count())
Running this I get a statement error because whats passed in to the
ListComparator is str(Key) not Key.serialize() which I thought would have
happened due to the KEY class.
E DataError: (DataError) malformed array literal: {Key({kind:
Base, ns: 1, id: 1})}
E LINE 4: WHERE t.v '{Key({kind: Base, ns: 1, id: 1})}')
AS...
E^
E'SELECT count(*) AS count_1 \nFROM (SELECT t.v AS t_v, t.id AS
t_id \nFROM t \nWHERE t.v %(v_1)s) AS anon_1' {'v_1': u'{Key({kind:
Base, ns: 1, id: 1})}'}
if I temporarily make the comparator call serialize() on what is passed in
I get a different DataError
def do_execute(self, cursor, statement, parameters, context=None):
cursor.execute(statement, parameters)
E DataError: (DataError) malformed array literal: {{kind: Base,
ns: 1, id: 1}}
E LINE 4: WHERE t.v '{{kind: Base, ns: 1, id: 1}}') AS
anon...
E^
E'SELECT count(*) AS count_1 \nFROM (SELECT t.v AS t_v, t.id AS
t_id \nFROM t \nWHERE t.v %(v_1)s) AS anon_1' {'v_1': u'{{kind:
Base, ns: 1, id: 1}}'}
So now it's not escaped properly which I guess I can accept but it seems
like I'm doing it wrong and really there's some other piece that should
know how to make an array literal out of what is passed to it somewhere.
But as I said in my original email, I don't think I fully grok all the
moving parts in sqla
[sqlalchemy] postgresql emulating app engines list property
Hello,
I'm hoping that some of the functionality I actually like in appengine
datastore can be duplicated in postgresql via sqlalchemy. However I'm not
quite grokking how all the moving pieces for a dialect(if that's the right
term) fit together.
On appengine there are list properties or repeated properties. This is
simply an array of values that has a custom behavior for the = in a
query. In that an object with a property named my_list who's value is
[1,2,3] will be true in the following where clauses...
where my_list=1
where my_list=2
where my_list=3
I feel like it is possible to get the same kind of behavior on postgresql
with the ARRAY column type, but I'm having trouble finding examples of how
one might do this.
Here's the code I have so far.
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.orm.properties import ColumnProperty
from sqlalchemy.orm import column_property
class ListComparator(ColumnProperty.ColumnComparator):
other operators as they make sense
optimization: override set operators for one filter instead of
multiples and'd
todo: non-string types?
def __eq__(self, other):
return self.__clause_element__().op()(u{%s} % other)
def ListColumn(*args, **kw):
makes a column of an array of types args[0]
and uses ListComparator to emulate appengine list property
if not isinstance(args[0], ARRAY):
largs = list(args)
largs[0] = ARRAY(args[0])
args = tuple(largs)
else:
raise ValueError(%s is an array which is not allowed % args[0])
return column_property(Column(*args, **kw),
comparator_factory=ListComparator)
example usage
class T(B):
__tablename__ = t
id = Column(Integer, primary_key=True)
v = ListColumn(Integer)
t = T()
t.v = [1, 2]
S.add(t)
S.commit()
S.query(T).count()
S.query(T).filter(T.v==1).count()
S.query(T).filter_by(v =1).count()
An array of strings or ints works just fine for this, but any other data
types I would have to convert to strings and in some cases escape for the
array literal syntax in postgresql. It seems like I'm missing some piece of
the puzzle in regards to how all the types in sqla relate to each other for
queries. Is it the dialect the drives that, is there behavior there for
ARRAY that I'm somehow missing or trying to duplicate?
Thanks in advance for any information you have.
--
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/-/9ZJ_qS0UuQkJ.
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] sqlite custom queries failing in 0.7.6
We have a webapp that makes use of sqlite3 create_function type
queries. Using 0.6, this has worked well for us.
Using 0.7 our approach breaks. I've tried to distill a minimal test
case to demonstrate the issue:
#!/usr/bin/python
from sqlalchemy import create_engine, __version__
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import create_session
def foo(s):
return s
print __version__
engine = create_engine('sqlite:///foo.db', echo=False)
base = declarative_base(bind=engine)
dataset = type('dataset', (base,),
dict(__tablename__='records',__table_args__={'autoload': True}))
dbtype = engine.name
session = create_session(engine)
connection = engine.raw_connection()
connection.create_function('foo', 1, foo)
query =
session.query(dataset).filter('foo(TESTVALUE)=TESTVALUE').all()
Using 0.6, this works. Using 0.7, we get an OperationalError: no such
function as per below:
0.7.6
Traceback (most recent call last):
File ./test.py, line 20, in module
query =
session.query(dataset).filter('foo(TESTVALUE)=TESTVALUE').all()
File /home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/orm/query.py, line 2066, in all
return list(self)
File /home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/orm/query.py, line 2176, in __iter__
return self._execute_and_instances(context)
File /home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/orm/query.py, line 2191, in
_execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File /home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/engine/base.py, line 1450, in execute
params)
File /home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/engine/base.py, line 1583, in
_execute_clauseelement
compiled_sql, distilled_params
File /home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/engine/base.py, line 1697, in
_execute_context
context)
File /home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/engine/base.py, line 1690, in
_execute_context
context)
File /home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/engine/default.py, line 335, in
do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) no such function:
foo u'SELECT records.id AS records_id, records.title AS records_title
\nFROM records \nWHERE foo(TESTVALUE)=TESTVALUE' ()
For reference, the table structure is as follows:
$ sqlite3 foo.db
SQLite version 3.7.3
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE records(id int primary key, title text);
INSERT INTO records VALUES(1,'foo');
INSERT INTO records VALUES(2,'bar');
COMMIT;
sqlite
Any idea on how to support this approach in both 0.6 and 0.7? Thanks
for any advice.
..Tom
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] custom Python functions as part of SQL queries
Hi,
I'm using SQLAlchemy 0.6.6 for a web application. At this point, the
app uses SQLite3 as the underlying database; I'm currently working on
abstracting this out to work with others (PostgreSQL, MySQL).
At a high level, the application receives CGI queries which are parsed
and sent to SQLAlchemy to query and filter. Much of the filtering is
done against XML data (say dataset.xml) stored in a column (type text)
in SQLite.
We use SQLite's create_function approach to pass the XML filter
queries (basically lxml.etree xpath function wrapper) via (snippet):
self.session = create_session(engine)
self.connection = engine.raw_connection()
self.connection.create_function('query_xpath', 2,
util.query_xpath)
Sample use case: a user provides a given XPath in their query which is
passed to query_xpath(dataset.xml, '/some/xpath') and returns either
None or the value of the XPath as a string, which would end up being
the following in SQL:
select * from records where query_xpath(xml, '/some/xpath') = 'foo';
This has served us very well so far.
Quite simply, we're looking for a similar way to 'register' Python
functions so that they can be used in the same manner as above, with
any database.
http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=func#sqlalchemy.sql.expression.func
seems to suggest that custom Python functions can be called in the way
that we are looking for (i.e. dot separated packages). Testing this
out like:
def foo()
return 'bar'
...
query = session.query(dataset.xml).filter(func.foo()=='bar')
sqlalchemy.exc.ProgrammingError: (ProgrammingError) function foo()
does not exist
LINE 3: WHERE foo() = E'bar'
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
'SELECT records.xml AS records_xml \nFROM records \nWHERE foo() = %
(foo_1)s' {'foo_1': 'bar'}
Does this approach only apply to db queries which are set in the
backend db?
Alternatively, I'm wondering whether
http://www.sqlalchemy.org/docs/core/compiler.html#greatest-function
serves as a better example to what I want to do. Testing this out
like:
class query_xpath(expression.FunctionElement):
type = String()
name = 'query_xpath'
@compiles(query_xpath)
def default_query_xpath(element, compiler, **kw):
return compiler.visit_function(element)
@compiles(query_xpath, 'sqlite')
@compiles(query_xpath, 'mysql')
@compiles(query_xpath, 'postgresql')
def case_query_xpath(element, compiler, **kw):
arg1, arg2 = list(element.clauses)
from server import util
return util.query_xpath(compiler.process(arg1),
compiler.process(arg2))
...
query = session.query(query_xpath(dataset.xml, xpath_string))
print query
...which gives me an lxml.etree error saying the XML is not well-
formed. Tracing through this, it looks like the column name is passed
to the function verbatim, instead of being evaluated to the column
value (I though compiler.process() would do this?)
I hope I have explained this well enough for some suggestions and / or
workarounds.
Thanks
..Tom
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] InternalError: current transaction is aborted, commands ignored until end of transaction block...
Hey,
I'm applying finishing touches to my web app before rolling it out,
when this strange error occurs:
sqlalchemy.exc.InternalError
InternalError: (InternalError) current transaction is aborted,
commands ignored until end of transaction block 'SELECT count(1) AS
count_1 \nFROM personal_message \nWHERE personal_message.receiver_id =
%(receiver_id_1)s AND personal_message.read = %
(read_1)s' {'receiver_id_1': 1, 'read_1': False}
Now, this hasn't happened before, and as far as I can tell I haven't
touched the concerning code recently. Googling the error doesn't turn
up anything useful, I'm not even sure it has to do with Sqlalchemy
(I'm using version 0.5.8, with Postgres).
Any pointers in the right direction would be greatly appreciated.
- Tom
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: InternalError: current transaction is aborted, commands ignored until end of transaction block...
First, thanks for that quick answer, that explains it. I turned
autocommit on, and it works again.
That leads me to this question: I do have session.commit() sprinkled
throughout my code without any except: rollback() blocks, can that
lead to problems down the line? I had the impression that rollback is
called automatically anytime a commit fails, but I'm not so sure
anymore.
On Sep 29, 4:07 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Sep 29, 2010, at 7:13 AM, tom wrote:
Hey,
I'm applying finishing touches to my web app before rolling it out,
when this strange error occurs:
sqlalchemy.exc.InternalError
InternalError: (InternalError) current transaction is aborted,
commands ignored until end of transaction block 'SELECT count(1) AS
count_1 \nFROM personal_message \nWHERE personal_message.receiver_id =
%(receiver_id_1)s AND personal_message.read = %
(read_1)s' {'receiver_id_1': 1, 'read_1': False}
Now, this hasn't happened before, and as far as I can tell I haven't
touched the concerning code recently. Googling the error doesn't turn
up anything useful, I'm not even sure it has to do with Sqlalchemy
(I'm using version 0.5.8, with Postgres).
Any pointers in the right direction would be greatly appreciated.
when an integrity error is issued by Postgresql, such as attempting to insert
a primary key that already exists, the ongoing transaction is then marked as
invalid. If you try to do anything else in the transaction, you get that
error.
The underlying reason from a Python interaction perspective is that the
session or connection you're using has encountered an error, but the
transaction was not rolled back, and the application proceeded with that same
connection. In a web applciation, the simplest cause of this is that one
web request is re-using the session or connection from a previous request
that raised an error. Theres myriad other variants of that sequence but
that is the most ordinary one.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: InternalError: current transaction is aborted, commands ignored until end of transaction block...
Thank you very much, that was very helpful!
On Sep 29, 5:27 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Sep 29, 2010, at 11:03 AM, tom wrote:
First, thanks for that quick answer, that explains it. I turned
autocommit on, and it works again.
That leads me to this question: I do have session.commit() sprinkled
throughout my code without any except: rollback() blocks, can that
lead to problems down the line? I had the impression that rollback is
called automatically anytime a commit fails, but I'm not so sure
anymore.
If the session has autocommit on, then the commit() calls are moot (and
slightly wasteful since it opens a no-op tranasction then closes it). The
rollback is also always implicit, since in fact every operation, not just
errors, results in connection resources being restored to the connection pool
when the operation is complete, which unconditionally performs a rollback.
autocommit isn't the current mainstream way to do things, usually for web
apps we recommend one session per request, which maintains a transaction
until commit() or rollback() is called, using an enclosure scheme like that
described
athttp://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-context
On Sep 29, 4:07 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Sep 29, 2010, at 7:13 AM, tom wrote:
Hey,
I'm applying finishing touches to my web app before rolling it out,
when this strange error occurs:
sqlalchemy.exc.InternalError
InternalError: (InternalError) current transaction is aborted,
commands ignored until end of transaction block 'SELECT count(1) AS
count_1 \nFROM personal_message \nWHERE personal_message.receiver_id =
%(receiver_id_1)s AND personal_message.read = %
(read_1)s' {'receiver_id_1': 1, 'read_1': False}
Now, this hasn't happened before, and as far as I can tell I haven't
touched the concerning code recently. Googling the error doesn't turn
up anything useful, I'm not even sure it has to do with Sqlalchemy
(I'm using version 0.5.8, with Postgres).
Any pointers in the right direction would be greatly appreciated.
when an integrity error is issued by Postgresql, such as attempting to
insert a primary key that already exists, the ongoing transaction is then
marked as invalid. If you try to do anything else in the transaction, you
get that error.
The underlying reason from a Python interaction perspective is that the
session or connection you're using has encountered an error, but the
transaction was not rolled back, and the application proceeded with that
same connection. In a web applciation, the simplest cause of this is
that one web request is re-using the session or connection from a previous
request that raised an error. Theres myriad other variants of that
sequence but that is the most ordinary one.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: cascading a delete in a many-to-many relation
That was indeed my problem, thank you very much! But I still cannot
wrap my brain around cascading, if anyone has a link to a good writeup
I'd be glad.
--tom
On Jul 13, 12:51 am, Conor conor.edward.da...@gmail.com wrote:
On 07/12/2010 02:38 PM, tom wrote:
Hi,
I have a problem with cascading a delete. I have two tables, and they
are mapped many-to-many:
class File(object): pass
file_table = Table('file', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('filename', String(255)),
}
class FileHost(object): pass
file_host = Table('host', metadata,
Column('id', Integer, primary_key=True, autoincrement=True ),
Column('name', String(255)),
)
file_hosted = Table('file_hosted', metadata,
Column('id_host', Integer, ForeignKey('host.id')),
Column('id_file', Integer, ForeignKey('file.id'))
)
session.mapper(File, file_table, properties={
'host': relation(FileHost, secondary=file_hosted, backref='files',
cascade='all,delete-orphan', single_parent=True)
})
session.mapper(FileHost, file_host)
This is the error I get:
sqlalchemy.exc.IntegrityError: (IntegrityError) update or delete on
table file violates foreign key constraint
file_hosted_id_file_fkey on table file_hosted
DETAIL: Key (id)=(50905) is still referenced from table
file_hosted.
Can somebody please tell me what I'm doing wrong because I tried to
find an answer and couldn't. This was the only somewhat related thing
I
found:http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg13198.html
You are telling SQLAlchemy to cascade File deletes to FileHost, but you
want it the other way around. You can fix this by moving the
cascade='all,delete-orphan' and single_parent=True clauses into the
backref. You also probably want use_list=False.
session.mapper(File, file_table, properties={
'host': relation(FileHost,
backref=backref('files',
cascade='all,delete-orphan',
single_parent=True),
secondary=file_hosted,
use_list=False)
})
-Conor
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] predefined,dynamic query
hi all! i'm creating user interface for search in database, and i wondered of any of you knows how to generate query dynamically, without using 'eval ()' (slow) or 'engine.execute(my query)' (loosing flexability and efficiency - am i right?) the aim is to manage complex query (with or, and, join,multiple tables, private-user's tables) efficiently and without letting the user be aware of implementaion. anyone? :) thanks tom -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] String to Column Type Conversion
I would like to present my users a simple search box that searches a table. I have issues getting DataError when using the text input and trying to do filter against a column that is an integer only column. I want to have some code that takes a particular sqlalchemy column object and attempts to convert a string to the column type so that it can be used for filtering. Is there some way of doing this? I tried something like... (key word is like here, I know it probably isn't exactly correct) myval = '10' myval = mytable.c.some_id.type(myval) I get an exception DataError. I don't really understand how I could do what I want with pure introspection like I do now. -Tom -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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=.
[sqlalchemy] Re: String to Column Type Conversion
Ok, here's a small test. It actually works fine when using sqlite, but not when using postgresql. I want it to work with postgresql! This probably helps more than the description above. http://www.pylonshq.com/pasties/fe6a2857f3f72d499914caabce0d41bc -Tom On Nov 23, 10:33 am, Tom Burdick thomas.burd...@gmail.com wrote: I would like to present my users a simple search box that searches a table. I have issues getting DataError when using the text input and trying to do filter against a column that is an integer only column. I want to have some code that takes a particular sqlalchemy column object and attempts to convert a string to the column type so that it can be used for filtering. Is there some way of doing this? I tried something like... (key word is like here, I know it probably isn't exactly correct) myval = '10' myval = mytable.c.some_id.type(myval) I get an exception DataError. I don't really understand how I could do what I want with pure introspection like I do now. -Tom -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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=.
[sqlalchemy] Adding new columns to a table
Hello,
my question is quite a basic one, but I couldn't find an answer, maybe
because it's too basic.
I have a table User and want to add a new Column('donated',
Boolean). Then I initialize the database with metadata.create_all
(self.database_engine) but in phpPgAdmin there are no changes. Also,
if I modify an existing column, eg. from Column('subject', String
(128)) to Column('subject', String(128), default='(no subject)'), and
then do create_all, nothing changes.
Is create_all the wrong thing to do there, or where am I going wrong?
Regards,
Tom
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Adding method to ORM mapped object
From what I see, you forgot the self argument in the getRendered function definition. def getRendered(self): return '%s\n%s' % (self.title, self.content) On Aug 10, 5:00 am, Andreas andr...@flausch.at wrote: Arghh... I got it. The problem was that I wasn't querying through a session, but doing a selection without one, so the object wasn't bound to the class ... Some old code fragment. Quite as you said. Thanks for your help :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
Mike ... thanks for picking this up! Just so you know, I haven't done any other testing of my change to see how it interacts with, say, db triggers that fire on the insert. I can add an another small bit of info: I also see the dialect unit test failures (test_fetchid_trigger and test_slice_mssql) against SQLAlchemy 0.5.3, otherwise same configuration as above. Just curious: were you able to reproduce the invalid cursor state exception using FreeTDS? I'm frankly nervous that there is something funny about our stack, although another developer here has been able to reproduce the problem on a separate system (Debian again.) -Tom On Apr 30, 11:06 pm, mtrier mtr...@gmail.com wrote: On Apr 30, 11:04 pm, mtrier mtr...@gmail.com wrote: Some additional info, and a possible fix: === --- lib/sqlalchemy/databases/mssql.py (revision 5930) +++ lib/sqlalchemy/databases/mssql.py (working copy) @@ -991,7 +991,7 @@ # We may have to skip over a number of result sets with no data (due to triggers, etc.) while True: try: - row = self.cursor.fetchone() + row = self.cursor.fetchall()[0] break except pyodbc.Error, e: self.cursor.nextset() I.e., calling fetchall() instead of fetchone() seems to clean up the cursor state. Also, FWIW, the original test passes just fine on Windows and pyodbc. So it's definitely a FreeTDS issue. Michael --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
Hi Lucas, I don't think #1350 applies here, but just in case, I pass-ed out the mssql dialect do_begin per the suggestion in the discussion thread referenced by that ticket: no impact on the invalid cursor state exception. -Tom Can you read over this ticket and see if maybe you are affected.http://www.sqlalchemy.org/trac/ticket/1350 If not then somebody more familiar with sa would need to look into why these tests are failing. Thanks, Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
Hi all,
Am having a problem with SQLAlchemy 0.5.3 and MSSQL. Running on a
Debian stack, using FreeTDS 0.82, pyodbc 2.1.4, Python 2.5 and
(separately) SQL Server 2000 and SQL Server 2005.
The (nose) test below fails with the exception:
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO
activities (institution, application_id) VALUES (?, ?); select
scope_identity()' ['UMass', 1]
Complete stack can be found below.
I'd love to hear from anyone running under a comparable configuration--
whether you see the same results or not! :-)
FYI: The test succeeds using SQLAlchemy 0.5.0rc3, but fails with every
subsequent release. It also passes running against a sqllite db.
Thanks very much.
Tom Wood
University of Connecticut
# begin test code
import sqlalchemy as sa
from sqlalchemy import orm
conn = 'mssql://insert your string here'
engine = sa.create_engine(conn)
metadata = sa.MetaData(bind=engine)
applications_table = sa.Table('applications', metadata,
sa.Column('id', sa.Integer,
primary_key=True),
sa.Column('last_name', sa.types.String
(20)))
activities_table = sa.Table('activities', metadata,
sa.Column('id', sa.Integer,
primary_key=True),
sa.Column('institution', sa.types.String
(20)),
sa.Column('application_id', sa.Integer,
sa.ForeignKey('applications.id')))
Session = orm.sessionmaker()
class Application(object):
def __init__(self, last_name):
self.last_name = last_name
class Activity(object):
def __init__(self, institution):
self.institution = institution
orm.mapper(Application, applications_table, properties={'activities':
orm.relation(Activity, backref='application')})
orm.mapper(Activity, activities_table)
class Tester(object):
def setup(self):
metadata.create_all()
self.session = Session()
def teardown(self):
self.session.close()
metadata.drop_all()
def test_orm_relation(self):
app = Application(last_name='Wood')
act = Activity(institution='UConn')
act2 = Activity(institution='UMass')
app.activities.append(act)
app.activities.append(act2)
self.session.add(app)
self.session.commit()
assert act.id is not None
assert app.id is not None
assert act2.id is not None
assert act.application_id == app.id
assert act2.application_id == app.id
# begin stack crawl
ERROR: simple_test.Tester.test_orm_relation
--
Traceback (most recent call last):
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
nose-0.10.4-py2.5.egg/nose/case.py, line 182, in runTest
self.test(*self.arg)
File /home/XXX/unicode_tests/simple_test.py, line 45, in
test_orm_relation
self.session.commit()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 673, in
commit
self.transaction.commit()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 378, in
commit
self._prepare_impl()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 362, in
_prepare_impl
self.session.flush()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 1351, in
flush
self._flush(objects)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 1422, in
_flush
flush_context.execute()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 244, in
execute
UOWExecutor().execute(self, tasks)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 707, in
execute
self.execute_save_steps(trans, task)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 722, in
execute_save_steps
self.save_objects(trans, task)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 713, in
save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/mapper.py, line 1347, in
_save_obj
c = connection.execute(statement.values(value_params), params)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
Lucas,
Thanks for taking a peek at this!
My connection string looks like: 'mssql://user:p...@server/db?
driver=FreeTDS_82Servername=MyServer'
where FreeTDS_82 is defined in my odbcinst.ini, and MyServer is
defined in my freetds.conf.
I've tried 'tds version = 8.0' and 'tds version = 7.0' in my
freetds.conf, but still see the same behavior. I'm fairly certain
both config files are being read correctly.
Using FreeTDS 0.82, I see the exception as reported. Using FreeTDS
0.63, I see a slightly different exception:
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
DBAPIError: (Error) ('HY000', 'The driver did not supply an
error!') 'INSERT INTO activities (institution, application_id) VALUES
(?, ?); select scope_identity()' ['UMass', 1]
and the FreeTDS log includes:
17:28:26.864001 tds_submit_query(): state is PENDING
17:28:26.864015 tds_client_msg: #20019: Attempt to initiate a new
SQL Server operation with results pending.. Connection state is now
1.
which suggests to me the same problem, just being caught in a
different place.
I should also mention that simpler tests (e.g., using the ORM to save
an object with no relations) do succeed.
-Tom
On Apr 27, 12:18 pm, Lukasz Szybalski szybal...@gmail.com wrote:
On Mon, Apr 27, 2009 at 10:01 AM, Tom Wood thomas.a.w...@gmail.com wrote:
Hi all,
Am having a problem with SQLAlchemy 0.5.3 and MSSQL. Running on a
Debian stack, using FreeTDS 0.82, pyodbc 2.1.4, Python 2.5 and
(separately) SQL Server 2000 and SQL Server 2005.
The (nose) test below fails with the exception:
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO
activities (institution, application_id) VALUES (?, ?); select
scope_identity()' ['UMass', 1]
Complete stack can be found below.
I'd love to hear from anyone running under a comparable configuration--
whether you see the same results or not! :-)
FYI: The test succeeds using SQLAlchemy 0.5.0rc3, but fails with every
subsequent release. It also passes running against a sqllite db.
Thanks very much.
Tom Wood
University of Connecticut
# begin test code
import sqlalchemy as sa
from sqlalchemy import orm
conn = 'mssql://insert your string here'
engine = sa.create_engine(conn)
I'm not sure if I can help but if we could start with the basics and
find out what version of tds are you using and how are you connecting?
1. Are you using dsn-less or dsn connection string? �...@dsn ?
2. What tds version have you set in
/etc/freetds/tds.dsn.templatehttp://lucasmanual.com/mywiki/unixODBC
Have you tried setting it to tds version 8.0?
Thanks,
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: column name capitalization in mysql
Corrected in r5204. http://www.sqlalchemy.org/trac/changeset/5204
Ticket details: http://www.sqlalchemy.org/trac/ticket/1206
-Tom H
On Oct 27, 4:35 pm, Tom H [EMAIL PROTECTED] wrote:
Thanks Michael,
I posted a new ticket at:http://www.sqlalchemy.org/trac/ticket/1206
Best Regards,
-Tom H
On Oct 27, 4:13 pm, Empty [EMAIL PROTECTED] wrote:
Tom,
On Mon, Oct 27, 2008 at 4:03 PM, Tom H [EMAIL PROTECTED] wrote:
I'm experiencing the following problem with SA 0.4.7
The table has the following columns:
id - int primary key
DOB - varchar(25)
... extra columns not needed for report
It's a table from legacy app that I'm accessing from SA to generate
reports, etc.
I'm having the table definition auto load (reflected from the
database) like this:
myTable = Table('sample_table', metadata, autoload=True,
include_columns=['id', 'DOB'])
When I later retreive data, the DOB column is not reflected. If I
change 'DOB' to 'dob' for the include_columns list, the column is
reflected as 'DOB' ... strange that the reflected name is capitalized,
but when I specify all caps for include_columns the field is not
reflected.
Any recommendations or suggestions for dealing with this issue?
The problem is related to this bit of code.:
if only and name.lower() not in only:
self.logger.info(Omitting reflected column %s.%s %
(table.name, name))
return
If you would please submit a ticket we can get it fixed.
Thanks,
Michael
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: column name capitalization in mysql
Thanks Michael,
I posted a new ticket at:
http://www.sqlalchemy.org/trac/ticket/1206
Best Regards,
-Tom H
On Oct 27, 4:13 pm, Empty [EMAIL PROTECTED] wrote:
Tom,
On Mon, Oct 27, 2008 at 4:03 PM, Tom H [EMAIL PROTECTED] wrote:
I'm experiencing the following problem with SA 0.4.7
The table has the following columns:
id - int primary key
DOB - varchar(25)
... extra columns not needed for report
It's a table from legacy app that I'm accessing from SA to generate
reports, etc.
I'm having the table definition auto load (reflected from the
database) like this:
myTable = Table('sample_table', metadata, autoload=True,
include_columns=['id', 'DOB'])
When I later retreive data, the DOB column is not reflected. If I
change 'DOB' to 'dob' for the include_columns list, the column is
reflected as 'DOB' ... strange that the reflected name is capitalized,
but when I specify all caps for include_columns the field is not
reflected.
Any recommendations or suggestions for dealing with this issue?
The problem is related to this bit of code.:
if only and name.lower() not in only:
self.logger.info(Omitting reflected column %s.%s %
(table.name, name))
return
If you would please submit a ticket we can get it fixed.
Thanks,
Michael
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] mysql, use password from my.cnf file
Depending on security preferences, it may be desirable to keep
database passwords out of code files.
One of the recommendations for MySQL is to keep the password in
a .my.cnf file accessible only to the user.
http://dev.mysql.com/doc/refman/5.0/en/password-security.html
To use the .my.cnf password in sqlalchemy, do something like the
following:
from sqlalchemy.engine.url import URL
myDB = URL(drivername='mysql', host='localhost',
database='my_database_name',
query={ 'read_default_file' : '/path/to/.my.cnf' } )
engine = create_engine(name_or_url=myDB)
# use the engine as usual, no password needed in your code file :)
This has been very useful to me, hopefully others find it helpful as
well.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] SA4: Why the automatic rollback on exception during SQL-Expression execution?
Hi! Let's say I have a simple table and want to create a function to ensure that a given row exists in that table. That is, the desired semantics is that after I call this function, the given row will exist: if it doesn't exist before the call, it will be created. I'm using SA 0.4.7 and the SQL Expression Language. My initial attempt follows: def make_sure_row_exists(row_attr_dict): s = MyTable.insert(row_attr_dict) try: s.execute() except IntegrityError: pass # row already exists, so we need do nothing more In sum: try to create the row and if it's already there, great. The trouble is, if the row already exists, SA will issue a ROLLBACK on the connection, wiping out any as-of-yet-uncommitted work that may have built up previously: sqlalchemy.engine.base.Engine.0x..d0 INSERT INTO mytable (x, y) VALUES (?, ?) sqlalchemy.engine.base.Engine.0x..d0 ['1', '1'] # already exists sqlalchemy.engine.base.Engine.0x..d0 ROLLBACK This ROLLBACK makes it hard to use this function as a building block in larger transactions. (Unless I somehow isolate this function in its own transaction, e.g., by using nested transactions.) What that background, I have some questions: 1. Is there a better way to use SA's SQL Expression Language to implement a function with the desired semantics? 2. How can I tell SA that it's fine if the row already exists and not to issue the ROLLBACK? 3. Is there any documentation on the interaction of transactions between ORM sessions and SQL Expression Language connections? (I ask this because I first noticed the ROLLBACK-on-exception issue when work on an ORM session failed to be committed as usual. Since the ORM work was done via a scoped session object and the SQL-EL work was done as shown in the code snippet above on an engine that does not use threadlocal execution strategy, I would have expected the SQL-EL-issued ROLLBACK not to affect the ORM work, they presumably being on different connections, albeit to the same database.) If anybody can shed some light, I would be grateful. Thanks for your help! Cheers, Tom --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SA4: Why the automatic rollback on exception during SQL-Expression execution?
Michael, thanks for your quick and helpful response. For any new project, I would strongly urge the usage of 0.5... Our project isn't new, but I think it's time to move to SA 0.5. :-) Cheers, Tom --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: relation with same class/table on both sides
The manager and direct reports example was just what my project needed. I'm looking forward to upgrading to 0.5 in the future so that I don't have to enter redundant primaryjoin and secondaryjoin on the backref. Thank you Mike! On Jun 28, 1:10 am, Tom Hogarty [EMAIL PROTECTED] wrote: Wow, thank you very much for the detailed example. It looks like just what I need. I look forward to trying it out very soon. -Tom On Jun 27, 6:12 pm, Michael Bayer [EMAIL PROTECTED] wrote: this relation will require you to configure primaryjoin and secondaryjoin (it should be raising an error without them). an example is attached. test.py 1KDownload On Jun 27, 2008, at 5:30 PM, Tom Hogarty wrote: Hello, I have the following scenario where I want the same class/table on both sides of a relation. person table - id - name manager table - person_id - manager_id I define both tables and a Person class, then create a relation in the person mapper like: 'manager' : relation(Person, secondary=managers, backref='direct_reports') When I do this, the 'manager' attribute doesn't show up in Person objects when I query on people. The error I get is: AttributeError: 'Person' object has no attribute 'manager' # query is something like session.query(Person).filter_by(name='Joe').one() Any hints on how I can do this. I have other basic relations working (1-1, 1-M, M-1) but they all have different classes/tables on each end of the relation. Regards, -Tom- Hide quoted text - - Show quoted text - --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] do not load large binary column but make available on demand
Hello, I have a table that stores binary file data in one column and information about the file (file name, mime type, sha1 sum, etc) in the other columns. Currently when I use the mapped class, it loads the file data (adds to network and memory load). What I would like to do is check the sha1 sum first and then only if needed load the file data. I have been looking into the 'lazy' and 'dynamic' loading options, but they seem to be designed for loading a separate class and not just a single heavy or large property (column) like my file data. Any advice for this situation? I was considering mapping two separate classes, one to the light columns, and another to the heavier data column. I have not mapped separate classes to the same table before though, so any hints on this would be great. Ideally I would like just that data property to lazy-load when accessed, but not sure if there is a 'lazy load this column' setting somewhere. My table is something like: files: - file_name - mime_type - sha1_sum - file_data --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: do not load large binary column but make available on demand
Deferred column loading is exactly what I needed, thanks Rick! Coming from a pure SQL background I'm starting to get familiar with all this new ORM and SQLAlchemy terminology. It's worth it though, the code is so much cleaner and more maintainable than stringing together huge complicated SQL queries. On Jul 9, 4:53 pm, Rick Morrison [EMAIL PROTECTED] wrote: Sounds like you want deferred loading for the column: http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_... --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] relation with same class/table on both sides
Hello, I have the following scenario where I want the same class/table on both sides of a relation. person table - id - name manager table - person_id - manager_id I define both tables and a Person class, then create a relation in the person mapper like: 'manager' : relation(Person, secondary=managers, backref='direct_reports') When I do this, the 'manager' attribute doesn't show up in Person objects when I query on people. The error I get is: AttributeError: 'Person' object has no attribute 'manager' # query is something like session.query(Person).filter_by(name='Joe').one() Any hints on how I can do this. I have other basic relations working (1-1, 1-M, M-1) but they all have different classes/tables on each end of the relation. Regards, -Tom --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: relation with same class/table on both sides
Wow, thank you very much for the detailed example. It looks like just what I need. I look forward to trying it out very soon. -Tom On Jun 27, 6:12 pm, Michael Bayer [EMAIL PROTECTED] wrote: this relation will require you to configure primaryjoin and secondaryjoin (it should be raising an error without them). an example is attached. test.py 1KDownload On Jun 27, 2008, at 5:30 PM, Tom Hogarty wrote: Hello, I have the following scenario where I want the same class/table on both sides of a relation. person table - id - name manager table - person_id - manager_id I define both tables and a Person class, then create a relation in the person mapper like: 'manager' : relation(Person, secondary=managers, backref='direct_reports') When I do this, the 'manager' attribute doesn't show up in Person objects when I query on people. The error I get is: AttributeError: 'Person' object has no attribute 'manager' # query is something like session.query(Person).filter_by(name='Joe').one() Any hints on how I can do this. I have other basic relations working (1-1, 1-M, M-1) but they all have different classes/tables on each end of the relation. Regards, -Tom --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: allow_column_override
Thanks for the info, I had the same problem and was able to fix it by
renaming my relation to not override the other one and turning off
allow_override. Removing allow_column_override would help eliminate
the confusion since replacing the column makes the relation not work
without the undocumented _actual_foreign_key_column you mentioned.
Regards,
-TH
allow_column_override is not used for this, its used to entirely
obliterate the knowledge of the underlying Column so that you can
place a relation() there instead. Its also deprecated since the same
effect can be acheived with exclude_columns (thanks for reminding me
so i can remove it from 0.5).
Since you dont want to obliterate the column and you actually need it,
do this:
mapper(Class, mytable, properties={
'_actual_foreign_key_column' : mytable.c.sensor,
'sensor':relation(Sensor)
})
mapper(Sensor, sensor)
detectionmapper = mapper(Detection, detection,
allow_column_override= True , properties={
'sensor' : relation(Sensor),
})
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Oracle, limit, and polymorphism
-Original Message-
From: sqlalchemy@googlegroups.com sqlalchemy@googlegroups.com
To: sqlalchemy sqlalchemy@googlegroups.com
Sent: Mon Feb 04 16:09:49 2008
Subject: [sqlalchemy] Oracle, limit, and polymorphism
Hey all,
I'm rather new to sqlalchemy, I've been playing with it for about a
week. I'm using version 4.2p3 on python 2.4
Anyways, I've run into a problem when I use oracle with joined
inheritance with a limit clause, specifically, it can't seem to find
the polymorphic_on column.
If I switch the database to sqlite, it works fine.
Here's the psuedo-code description:
tickets = Table('hpd_helpdesk', metadata,
Column('case_id_', String(100), primary_key=True),
Column('requester_login_name_', String(100)),
Column('ticket_type', String(100))
schema=aradmin)
class Ticket(object): pass
tickets_mapper = mapper(Ticket, tickets,
polymorphic_on=tickets.c.ticket_type,
polymorphic_identity=Default)
broken_query =
session.query(Ticket).filter(tickets.ticket_type==Default).limit(10)
working_query =
session.query(Ticket).filter(tickets.ticket_type==Default)
On oracle, if I comment out the polymorphic_* args it works with a
limit() specified.
Here's the last line of the traceback
sqlalchemy/engine.base.py, line 1479, in lookup_key
raise exceptions.NoSuchColumnError(Could not locate column in row
for column '%s' % str(key))
exceptions.NoSuchColumnError: Could not locate column in row for
column 'tickets.ticket_type'
The sql its generating is (note i've sanitized the actual name of the
ticket_type column, it had a company's name in it)
SELECT hpd_helpdesk_case_id_, hpd_helpdesk_requester_l_1,
hpd_helpdesk_ticket_type__2
FROM (SELECT hpd_helpdesk.case_id_ AS hpd_helpdesk_case_id_,
hpd_helpdesk.requester_login_name_ AS hpd_helpdesk_requester_l_1,
hpd_helpdesk.ticket_type AS hpd_helpdesk_ticket_type__2, ROW_NUMBER()
OVER (ORDER BY hpd_helpdesk.rowid) AS ora_rn
FROM aradmin.hpd_helpdesk
hpd_helpdesk.ticket_type = :hpd_helpdesk_ticket_type__2)
WHERE ora_rn=10
How can I fix this? I'm about to start hacking into the source to see
whats going on, but I figured I'd ask here first.
-Richard
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Oracle, limit, and polymorphism
-Original Message-
From: sqlalchemy@googlegroups.com sqlalchemy@googlegroups.com
To: sqlalchemy sqlalchemy@googlegroups.com
Sent: Mon Feb 04 16:09:49 2008
Subject: [sqlalchemy] Oracle, limit, and polymorphism
Hey all,
I'm rather new to sqlalchemy, I've been playing with it for about a
week. I'm using version 4.2p3 on python 2.4
Anyways, I've run into a problem when I use oracle with joined
inheritance with a limit clause, specifically, it can't seem to find
the polymorphic_on column.
If I switch the database to sqlite, it works fine.
Here's the psuedo-code description:
tickets = Table('hpd_helpdesk', metadata,
Column('case_id_', String(100), primary_key=True),
Column('requester_login_name_', String(100)),
Column('ticket_type', String(100))
schema=aradmin)
class Ticket(object): pass
tickets_mapper = mapper(Ticket, tickets,
polymorphic_on=tickets.c.ticket_type,
polymorphic_identity=Default)
broken_query =
session.query(Ticket).filter(tickets.ticket_type==Default).limit(10)
working_query =
session.query(Ticket).filter(tickets.ticket_type==Default)
On oracle, if I comment out the polymorphic_* args it works with a
limit() specified.
Here's the last line of the traceback
sqlalchemy/engine.base.py, line 1479, in lookup_key
raise exceptions.NoSuchColumnError(Could not locate column in row
for column '%s' % str(key))
exceptions.NoSuchColumnError: Could not locate column in row for
column 'tickets.ticket_type'
The sql its generating is (note i've sanitized the actual name of the
ticket_type column, it had a company's name in it)
SELECT hpd_helpdesk_case_id_, hpd_helpdesk_requester_l_1,
hpd_helpdesk_ticket_type__2
FROM (SELECT hpd_helpdesk.case_id_ AS hpd_helpdesk_case_id_,
hpd_helpdesk.requester_login_name_ AS hpd_helpdesk_requester_l_1,
hpd_helpdesk.ticket_type AS hpd_helpdesk_ticket_type__2, ROW_NUMBER()
OVER (ORDER BY hpd_helpdesk.rowid) AS ora_rn
FROM aradmin.hpd_helpdesk
hpd_helpdesk.ticket_type = :hpd_helpdesk_ticket_type__2)
WHERE ora_rn=10
How can I fix this? I'm about to start hacking into the source to see
whats going on, but I figured I'd ask here first.
-Richard
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Oracle, limit, and polymorphism
-Original Message-
From: sqlalchemy@googlegroups.com sqlalchemy@googlegroups.com
To: sqlalchemy sqlalchemy@googlegroups.com
Sent: Mon Feb 04 16:09:49 2008
Subject: [sqlalchemy] Oracle, limit, and polymorphism
Hey all,
I'm rather new to sqlalchemy, I've been playing with it for about a
week. I'm using version 4.2p3 on python 2.4
Anyways, I've run into a problem when I use oracle with joined
inheritance with a limit clause, specifically, it can't seem to find
the polymorphic_on column.
If I switch the database to sqlite, it works fine.
Here's the psuedo-code description:
tickets = Table('hpd_helpdesk', metadata,
Column('case_id_', String(100), primary_key=True),
Column('requester_login_name_', String(100)),
Column('ticket_type', String(100))
schema=aradmin)
class Ticket(object): pass
tickets_mapper = mapper(Ticket, tickets,
polymorphic_on=tickets.c.ticket_type,
polymorphic_identity=Default)
broken_query =
session.query(Ticket).filter(tickets.ticket_type==Default).limit(10)
working_query =
session.query(Ticket).filter(tickets.ticket_type==Default)
On oracle, if I comment out the polymorphic_* args it works with a
limit() specified.
Here's the last line of the traceback
sqlalchemy/engine.base.py, line 1479, in lookup_key
raise exceptions.NoSuchColumnError(Could not locate column in row
for column '%s' % str(key))
exceptions.NoSuchColumnError: Could not locate column in row for
column 'tickets.ticket_type'
The sql its generating is (note i've sanitized the actual name of the
ticket_type column, it had a company's name in it)
SELECT hpd_helpdesk_case_id_, hpd_helpdesk_requester_l_1,
hpd_helpdesk_ticket_type__2
FROM (SELECT hpd_helpdesk.case_id_ AS hpd_helpdesk_case_id_,
hpd_helpdesk.requester_login_name_ AS hpd_helpdesk_requester_l_1,
hpd_helpdesk.ticket_type AS hpd_helpdesk_ticket_type__2, ROW_NUMBER()
OVER (ORDER BY hpd_helpdesk.rowid) AS ora_rn
FROM aradmin.hpd_helpdesk
hpd_helpdesk.ticket_type = :hpd_helpdesk_ticket_type__2)
WHERE ora_rn=10
How can I fix this? I'm about to start hacking into the source to see
whats going on, but I figured I'd ask here first.
-Richard
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
