Re: [sqlalchemy] add/update causing InvalidRequest for object with association_proxy lookup

Hi Mike Thanks very much for all the feedback, 'expunging the expunges' and setting expire_on_commit=False worked a treat. Had to add commits for read operations too, which felt a little awkward, but works nicely. Nathan On Monday, 16 May 2022 at 19:19:56 UTC+1 Mike Bayer wrote

Re: [sqlalchemy] add/update causing InvalidRequest for object with association_proxy lookup

orm.exc.DetachedInstanceError: Parent instance is not bound to a Session; lazy load operation of attribute '_lookup' cannot proceed (Background on this error at: http://sqlalche.me/e/14/bhk3) ``` Thanks Nathan On Wednesday, 11 May 2022 at 15:25:11 UTC+1 Mike Bayer wrote: > the pattern y

[sqlalchemy] Re: add/update causing InvalidRequest for object with association_proxy lookup

Oh, forgot to clarify, if it wasn't obvious... The LookUp is of course in the session already for the initial object creation, it's only on updating the created object that this error manifests. On Wednesday, 11 May 2022 at 11:24:41 UTC+1 Nathan Johnson wrote: > Hi > > I

[sqlalchemy] add/update causing InvalidRequest for object with association_proxy lookup

e cascade and adding the obj._lookup to the session directly, but this results in: /usr/local/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py:835: SAWarning: Object of type not in session, add operation along 'Dave._lookup' won't proceed Would really appreciate

Re: [sqlalchemy] Representing Tree Structures with sqlAlchemy

Thanks for the example, it was exactly what I was looking for. On Friday, July 21, 2017 at 10:47:16 AM UTC-6, Mike Bayer wrote: > > On Fri, Jul 21, 2017 at 11:40 AM, Nathan Mooth > wrote: > > So I have a tree-like data structure that I need to store in a way so > that

[sqlalchemy] Representing Tree Structures with sqlAlchemy

So I have a tree-like data structure that I need to store in a way so that I can see all children or all parents of a query as well as their relative depth from the query item. In the past I have been using the closure table method demonstrated in this blog post

[sqlalchemy] Web API Wrapper for Informix Database

{sql_query} ''' xml_request = wrapper.format(sql_query=sql_query) r = sess.post(url, headers=req_headers, data=xml_request, verify=False) xml_root = _parse_unicode_xml(r.text) result = [{y.tag: y.text for y in

Re: [sqlalchemy] Join on subquery

Thanks Michael, I really appreciate your help. On Wednesday, 27 May 2015 14:32:48 UTC+1, Michael Bayer wrote: > > > > On 5/27/15 2:43 AM, Nathan wrote: > > Hi, > > I'm trying to convert the following query to sqlalchemy: > > SELECT cg.id

[sqlalchemy] Join on subquery

Hi, I'm trying to convert the following query to sqlalchemy: SELECT cg.id, cg.name cg.status, history.action, history.timestamp FROM customergame cg JOIN history ON history.id = (SELECT id FROM history h WHERE cg.id = h.customergame_id ORDER BY timestamp DESC LIMIT 1) ORDER BY gamename ASC Ess

[sqlalchemy] how to model a collection of different types/classes using declarative

Sorry if I’m missing something basic and/or pretty obvious but I don’t know how to model collections of different types/classes. For example, let’s say I have these base types/classes: class Chicken(Base): __tablename__ = ‘chicken' id = Column(Integer, primary_key=True) name = Colu

[sqlalchemy] simple join to parent with group_by

I’m trying to get a simple join working to a parent table with group_by. I’ve tried some variations working from the ORM tutorial but keep running into exceptions or I get multiple queries. I’m trying to get a single query that results in a collection of Client instances that’s joined to and gr

Re: [sqlalchemy] foreign key relations

onfiguration. I really appreciate your time. On Aug 3, 2014, at 5:14 PM, Rich Shepard wrote: > On Sun, 3 Aug 2014, nathan wrote: > >> A player and a song are both created separately and associated with a >> client. >> >> class Client(Base): >> __tablename__ =

[sqlalchemy] foreign key relations

Hi, I have 3 tables and I'm concerned about possible issues with foreign key relations and how they might impact my code and its behavior. Maybe there's a circular reference issue or other concern I should know about with the tables below? A player and a song are both created separately and as

[sqlalchemy] pycon 2014

Hi Mike, I really wish I could be there and see your presentations. They're usually recorded right? Just checking and hoping it's recorded... and if so, whoever records it does a great job. :) https://us.pycon.org/2014/speaker/profile/455/ Introduction to SQLAlchemy https://us.pycon.org/2014

[sqlalchemy] instance is not bound to a session

Hi, I'm looping writing "Media" objects with SA, which are just rows in the db that represent media files. The same "Account" object/instance is used during each iteration of the loop and contains the user's username, among other things. account = DBSession.query(Account).filter(Account.usernam

Re: [sqlalchemy] using WITH query AS, cte() with postgresql

e rows it finds on the way back. > > > On Oct 3, 2013, at 2:35 PM, Nathan Mailg wrote: > >> Sorry for this follow-up, but I'm really at a loss as to what to check next… >> >> What should I do if after I insert a new "Appl" row, the query we've

Re: [sqlalchemy] using WITH query AS, cte() with postgresql

me, firstname; Another odd thing is in my application, I have another search feature, and it's using an SA query that's not using the cte/distinct query below, and it works! Sorry to have to ask this, but I'm stuck and need to get this working. Thanks again for all the help! On Oct

Re: [sqlalchemy] using WITH query AS, cte() with postgresql

Thanks Mike, that works! I really appreciate you taking the time to show me how to get this working. Do you have an amazon wish list or something like that somewhere? On Sep 30, 2013, at 2:09 PM, Michael Bayer wrote: > qlast, qfirst = 'a', 'b' > d = DBSession.query(Appl).\ > distinct(Appl.r

Re: [sqlalchemy] using WITH query AS, cte() with postgresql

On Sep 20, 2013, at 11:59 AM, Michael Bayer wrote: > > Ok now I'm still not following - in this case, "row" is a NamedTuple, or a > mapped instance? if its a NamedTuple then you don't have the service of > traversing along object relationships available since the NamedTuple isn't a > mapped

Re: [sqlalchemy] using WITH query AS, cte() with postgresql

On Sep 19, 2013, at 3:07 PM, Michael Bayer wrote: > > On Sep 19, 2013, at 2:46 PM, Nathan Mailg wrote: > >> c = aliased(City) >> q2 = s.query(q1).\ >> join(Appl.city).\ >> join(c, Appl.city).\ >> order_by(q1.c.lastname, q1.c.firstname

Re: [sqlalchemy] using WITH query AS, cte() with postgresql

refid, distinct_query.appldate AS > distinct_query_appldate > FROM distinct_query ORDER BY distinct_query.lastname, distinct_query.firstname > > > I know this is not exactly the same, but the query itself is a SELECT > DISTINCT ON(x), which seems to be what they're

Re: [sqlalchemy] using WITH query AS, cte() with postgresql

==Appl.cityid') I've tried: c = aliased(City) q2 = s.query(q1).\ join(Appl.city).\ join(c, Appl.city).\ order_by(q1.c.lastname, q1.c.firstname) but that returns: […] File "/Users/nathan/projects/env/lib/python2.7/site-packages/SQLAlchemy-0.8.2-py2.7-mac

Re: [sqlalchemy] using WITH query AS, cte() with postgresql

_query_firstname, > distinct_query.lastname AS distinct_query_lastname, distinct_query.refid AS > distinct_query_refid, distinct_query.appldate AS distinct_query_appldate > FROM distinct_query ORDER BY distinct_query.lastname, distinct_query.firstname > > turning it into aliase

[sqlalchemy] using WITH query AS, cte() with postgresql

I'm using SA 0.8.2 and trying to port this query that works with PostgreSQL 9.2.4: WITH distinct_query AS ( SELECT DISTINCT ON (refid) * FROM appl WHERE lastname ILIKE 'Williamson%' AND firstname ILIKE 'd%' GROUP BY refid, id, lastname, firstname, appldate ORDE

[sqlalchemy] multiple many-to-one to same class

"teacher" has two many-to-one relationships with "student". A teacher can have a favorite student and a worst student. A student can be the favorite (or worst) of many teachers 1) Error is not surprising, since there is nothing to indicate whether "favorite" relationship uses "favorite_id" Foreign

[sqlalchemy] Mutating behavior of InstrumentedAttributes

I need to add some processing to mapped attributes. For instance, some extra client side validation, returning attribute values embedded in markup, etc. Is there a better way to do this than to change the column definition (i.e. Column(name, type_, key="_name")) and adding a descriptor to the cla

Re: [sqlalchemy] Incorrect SQL generated for INSERT into PostgreSQL

On Mon, Sep 26, 2011 at 11:05 PM, Michael Bayer wrote: > On Sep 26, 2011, at 2:50 AM, Nathan Robertson wrote: > >>       Column('custid', Integer, Sequence('test.customer_custid_seq'), >> primary_key=True), > > for the Sequence, as with all s

Re: [sqlalchemy] Incorrect SQL generated for INSERT into PostgreSQL

On Mon, Sep 26, 2011 at 11:05 PM, Michael Bayer wrote: > On Sep 26, 2011, at 2:50 AM, Nathan Robertson wrote: > >>       Column('custid', Integer, Sequence('test.customer_custid_seq'), >> primary_key=True), > > for the Sequence, as with all s

[sqlalchemy] Incorrect SQL generated for INSERT into PostgreSQL

Hi, I've come across a bug (hopefully in my configuration) where SQLAlchemy will generate an INSERT statement for a table with a SERIAL primary key which PostgreSQL errors on. I'm running EnterpriseDB's Postgres Plus 8.4 on openSUSE 11.4 x64, with Python 2.7, SQLAlchemy 0.7.2 and psycopg2 2.4.2. F

Re: [sqlalchemy] Deferred properties are sometimes compiled unnecessarily?

On Thursday, 26 May 2011 06:42:55 UTC-7, Michael Bayer wrote: > > > On May 25, 2011, at 10:52 PM, Nathan Wright wrote: > > > Hi all, > > > > I've run into an issue when mapping a deferred column_property that uses > a custom SQL construct. I get a K

[sqlalchemy] Deferred properties are sometimes compiled unnecessarily?

efault dialect, but I think this could be avoided entirely by changing the NoSuchColumnError message to not cast key (ie, the select) to a string. Perhaps the repr could be used instead? If I'm way off base feel free to let me know, heh, but hopefully this helps! :) Cheers, Natha

[sqlalchemy] Struqtural for SQL Alchemy

Wanted to drop by and mention that I just released the first public version of Struqtural. In a nutshell, Struqtural tries to make it as easy as possible to get data into a database, out of a database (as python objects) and to build some of the more common complex table arrangements. Struqtural

[sqlalchemy] Re: Locking tables

/speeding-up-inserts-on-mysql/ Thanks for the advice, Nathan 2009/10/31 Michael Bayer : > > > On Oct 30, 2009, at 9:45 AM, Nathan Harmston wrote: > >> >> Hi, >> >> I have a script which will process a large amount of data and save >> some of those results

[sqlalchemy] Locking tables

, %s)' [29046L, 'FOO'] What I am doing wrong? Is there something with the way conn.execute and insert operate? And can I find a way around this? Nathan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Gr

[sqlalchemy] Re: Vertical partitioning, mappers and foreign keys

.cellline_id AND taxonomy_species.taxa_id = cll2spe_table.taxa_id 2009-08-18 13:49:51,249 INFO sqlalchemy.engine.base.Engine.0x...7c10 ['CellLine_5162'] [] With nothing in the species..I can't understand whats going on ... my only thought is that cll2spe_table isn't being fo

[sqlalchemy] Vertical partitioning, mappers and foreign keys

n. When I had all the tables in the same database I had no problems involving having to specify foreign keys. What does SQLalchemy do it the background here? Is my problem that it doesnt know that table3 is accessed via the t_engine. How can I make it explicitly know this here? I have tried using

[sqlalchemy] Accessing attributes hidden by a join

llow this? If I then wanted to extend this so the Interval class has a derived attribute deleted ( the or of interval.deleted and entity.deleted ) is this possible to do aswell? Many thanks in advance Nathan --~--~-~--~~~---~--~~ You received this message becaus

[sqlalchemy] Unique parent class and various subclasses

e intervals table and then setting e's interval_id. However this still doesnt work. Has anybody done anything like this before? Is there a simple way of doing this, maybe at the level of the mapper? Many thanks in advance, Nathan --~--~-~--~~~---~--~~ You receive

[sqlalchemy] Accessing classes a table away in a mapper - why does this work?

ny thanks in advance, Nathan --~--~-~--~~~---~--~~ 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

[sqlalchemy] Re: Accessing classes a table away in a mapper

can't see the final step to finish it off. Many thanks in advance Nathan 2009/5/20 Nathan Harmston : > The problem was solved by using a join query and then specifying both > foreign keys and the secondary argument to the relation. > > Many thanks, > > Na

[sqlalchemy] Re: Accessing classes a table away in a mapper

The problem was solved by using a join query and then specifying both foreign keys and the secondary argument to the relation. Many thanks, Nathan secondary = sentences_table.join(tag_table.join(processed_tags_table, processed_tags_table.c.tag_id == tag_table.c.tag_id

[sqlalchemy] Joined table inheritance mapping issues

ding of the inheritance mapping until later? Many thanks in advance, yet again, Nathan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@goog

[sqlalchemy] Accessing classes a table away in a mapper

able.c.deleted==0 ), secondary=processed_tag_table) UnmappedColumnError: No column sentences.sentence_id is configured on mapper Mapper|Zone|zones Any pointers greatly appreciated! Many thanks Nathan --~--~-~--~~~---~--~~ You received this message because you are subscribed to

[sqlalchemy] Re: Using column_property getting no results

Yes that whats I meant. I was hoping to not have to create a class to do this, but its not going to be take long. I think was just reading more into what column_property could do. Thanks a lot, Nathan BTW SQLAlchemy is brilliant! 2009/5/18 Michael Bayer : > > > On May 17, 2009, a

[sqlalchemy] Re: Using column_property getting no results

.0x...2e70 BEGIN 2009-05-17 19:06:42,033 INFO sqlalchemy.engine.base.Engine.0x...2e70 SELECT (SELECT bar.name FROM bar WHERE foo.foo_id = bar.foo_id) AS names, foo.foo_id AS foo_foo_id, foo.data AS foo_data FROM foo 2009-05-17 19:06:42,033 INFO sqlalchemy.engine.base.Engine.0x...2e70 [] Many thanks

[sqlalchemy] Using column_property getting no results

I get something back. Am I just doing something incredibly stupid wrong? Many thanks in advance, Nathan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email

[sqlalchemy] Using orderedlist with a secondary table

1, in p = Paper(title, date, authors, journal, j_volume, j_issue, keywords, abstract, None, body, pmid, institution) File "/cluster/soft/Linux_2.6_64/lib/python2.5/site-packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/orm/attributes.py", line 1218, in init oldinit(instance, *args, **k

[sqlalchemy] Re: SQL Expressions as Mapped Attributes

ngle instance of the Species object with a list of names in an attribute called "names". How do I accomplish this? Also what do you mean by the correlated criterion of the column_property I have looked for this and have been unable to find it. Many thanks in advance, Nathan 2009/1/26 Micha

[sqlalchemy] SQL Expressions as Mapped Attributes

er with no column_property: mapper(Species, select([species_table], species_table.c.rank=='species').alias('speciesfooalias')) I get the correct output. I am using SQLAlchemy 0.4.8 I am pretty confused by this. Any help is very much appreciated. Many thanks in advance, Nathan --~--~

[sqlalchemy] Looking for a pattern/simple way to simplify parsing and storing files in a database

he database? The problem is that some of the files I am going to be parsing are quite large (Gb) and I dont want to saturate my database server with requests and likewise I only have limited memory on my machines. Have I missed something simple in the documentation? My code is be

[sqlalchemy] exceptions.ArgumentError when trying to map class to a table

for destination column 'ppi.evidence'; mapper 'Mapper|Paper|documents' does not map this column. Try using an explicit `foreign_keys` collection which does not include this column (or use a viewonly=True relation). Any help greatly appreciated, Many thanks in advance, Nathan --~--

[sqlalchemy] Problem Mapping table/relationship to a dictionary.

dge has has node1 and 1 node 2. IS there a pattern in sqlalchemy for doing this sort of thing, I noticed in the new version of SQLalchemy you can map to sets, but I m a little clueless where to start. Any help muchly appreciated. Nathan --~--~-~--~~~---~--~~

[sqlalchemy] Problems trying to run a query - typeerror

e(context.session) File "/usr/lib/python2.4/site-packages/sqlalchemy/orm/mapper.py", line 1234, in _create_instance obj = self.class_.__new__(self.class_) TypeError: __new__() takes exactly 4 arguments (1 given) Does anyone know what the problem is? Is it the way I ve programmed this

[sqlalchemy] Re: Mapping existing structure of a database

What kind of overhead is associated with using the autoload flag? What kind of overhead would be associated with this over a network? (with a remote database). Is there a way to "dump" the structure of a database to a file and import this as a kind of module? Than

[sqlalchemy] Mapping existing structure of a database

if so how? If not is there any advice you have to actually use SQLAlchemy in such a way. I m trying to create an API to my database and dont really want to have to declare all the tables again. Many Thanks in advance, Nathan --~--~-~--~~~---~--~~ You received th