Re: [sqlalchemy] inheritance with multiple foreign keys to the base table
Christopher Singley ch...@singleys.com wrote: I'm using declared_attr.cascading from sqlalchemy-1.0.0dev to declare a foreign key on a subclass that also needs another separate foreign key reference to the parent table. To let the Mapper know which column to join for the polymorphic inheritance relationship, I'm trying to pass an inherit_condition argument. Relevant code snippet can be seen here: https://gist.github.com/anonymous/1b24768cb714fb9c7de7 this is fixed in 3a56c4f019052c5d and your example case now succeeds. Importing this code generates this warning: SAWarning: Implicitly combining column secinfo.id with column optinfo.id under attribute 'id'. Please configure one or more attributes for these same-named columns explicitly. ...and then fails with this error: sqlalchemy.exc.ArgumentError: Mapper Mapper|OPTINFO|optinfo could not assemble any primary key columns for mapped table 'Join object on secinfo(23263120) and optinfo(19756496)' How can I configure this correctly? -- 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: [sqlalchemy] How do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
I'm not sure I understand - the line you've shown doesn't include any
joins between Signals and Clips, so I don't know why that error would
occur.
In general, if you are seeing that error message, then you need to
explicitly tell SQLAlchemy the join condition between the 2 tables.
This is normally done using the primaryjoin parameter when defining
the relationships.
If you are getting the error at query time and you really *are*
performing a join, you can specify the join condition at that time as
well:
http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#querying-with-joins
Hope that helps,
Simon
On Tue, Feb 24, 2015 at 7:56 AM, eli rashlin eli.rash...@gmail.com wrote:
Thanks Simon for your replay.
when I'm removing the FK definition from the Column - The tables are being
built as they should - the ptoblem is when i try to perform a join I'm
getting the following error:
Can't find any foreign key relationships between 'Signals' and 'Clips'.
and this is the code:
return engine.query(func.count(signals_table.Signals.sig_value)).\
filter(signals_table.Signals.message_id == msg_id).\
filter(signals_table.Signals.signal_id == sig_id).\
filter(func.format(signals_table.Signals.sig_value, 2) ==
func.format(sig_val, 2)).\ first()[0]
and this is the definition of the class Clips:
from datetime import datetime from sqlalchemy import * from sqlalchemy.orm
import relationship from BaseCoverRuns import Base class Clips(Base):
__table_args__ = { 'mysql_engine': 'MyISAM', 'mysql_charset': 'utf8' }
__tablename__ = 'Clips' id = Column(Integer, autoincrement=True,
primary_key=True) clip_name = Column(String(255)) def
get_table_orm_def(self): return self.__table__ def __init__(self,
clip_name=None): self.clip_name = clip_name def __repr__(self): return
Clips ('%s') % (self.clip_name)
--
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: [sqlalchemy] How can I know which fields cause IntegrityError when inserting into a table with multiple unique fields?
Merge wont work in my case, because I commit in bulk and also use multiple processes. By the time the commit is triggered (e.g. as soon as there are 1000 new objects to commit) an other process could have committed an object already which triggers an IntegrityError. The same applies for the Unique Object recipes if I'm not mistaken. So I basically have to check every object as I commit it which defies the idea of an bulk commit. Thats why I want to get the key which triggers the IntegrityError, update that object and commit all other elements again in bulk (and do this recursively if there are still other conflicts). *To archive this I have no other way but to parse the exception or am I missing something?* Alternatively I thought about using raw SQL statements (INSERT IGNORE), but I would have to disable relationships for that to work and possibly create many new problems... On Monday, February 23, 2015 at 9:11:28 PM UTC+1, Michael Bayer wrote: On Feb 23, 2015, at 8:49 AM, Maurice Schleußinger m.schle...@gmail.com javascript: wrote: Thanks for the reply! Since I create objects concurrently I can not predict and therefore not pre-select all rows which could possibly provoke IntegrityErrors. On the other hand Session.merge() seams to provide the functionality which could resolve my problem. In my setup many processes create a number of objects which could occur multiple times (in other words throw an IntegrityError on commit). That can happen in one process (which I can handle otherwise), in between multiple processes and between a process and the consistent state in the database (which is my main problem ATM). I just read the corresponding part in the SQLAlchemy docs http://docs.sqlalchemy.org/en/rel_0_9/orm/session_state_management.html#merging. So if I use Session.merge() with the load=True argument (instead of Session.add()) the session should create the corresponding object if it does not exists, avoid duplicates within one session and also update an existing entry in the database? Also it seems that merging only works for primary keys. So if I had the problem with an unique key, I still would have to parse the exception, right? Ah ok, yes session.merge() works this way, but it does emit a SELECT. If you were looking to emit less SQL I was referring to an operation like MySQL's REPLACE statement. But if merge works for you then stick with that. You can write your own function that does something similar for other fields that are not the PK. The recipe below is one way to do this. https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject On Thursday, February 19, 2015 at 5:29:56 PM UTC+1, Michael Bayer wrote: Maurice Schleußinger m.schle...@gmail.com wrote: Is there no other way? http://stackoverflow.com/questions/27635933/how-can-i-know-which-fiels-cause-integrityerror-when-inserting-into-a-table-with/27884632#27884632 Parsing an exception with regex just doesn't feel right… The two other ways are that you can pre-select the rows, or use a MERGE / upsert approach (you’d have to roll that yourself). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe Sent from my iPhone To post to this group, send email to sqlal...@googlegroups.com javascript:. 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] inheritance with multiple foreign keys to the base table
I'm using declared_attr.cascading from sqlalchemy-1.0.0dev to declare a foreign key on a subclass that also needs another separate foreign key reference to the parent table. To let the Mapper know which column to join for the polymorphic inheritance relationship, I'm trying to pass an inherit_condition argument. Relevant code snippet can be seen here: https://gist.github.com/anonymous/1b24768cb714fb9c7de7 Importing this code generates this warning: SAWarning: Implicitly combining column secinfo.id with column optinfo.id under attribute 'id'. Please configure one or more attributes for these same-named columns explicitly. ...and then fails with this error: sqlalchemy.exc.ArgumentError: Mapper Mapper|OPTINFO|optinfo could not assemble any primary key columns for mapped table 'Join object on secinfo(23263120) and optinfo(19756496)' How can I configure this correctly? -- 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 do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
I'm Sorry Simon - wrong query...
This is the query that gives me the error
query_obj = engine.query(signals_table.Signals.sig_value,
signals_table.Signals.exist_in_frames,
signals_table.Signals.local_frames,
clips_table.Clips.clip_name).\
join(clips_table.Clips).\
filter(signals_table.Signals.message_id == msg_id).\
filter(signals_table.Signals.signal_id == sig_id).\
filter(func.format(signals_table.Signals.sig_value, 2) ==
func.format(sig_val, 2)).\
order_by(signals_table.Signals.sig_value)
On Tuesday, February 24, 2015 at 1:09:12 PM UTC+2, Simon King wrote:
I'm not sure I understand - the line you've shown doesn't include any
joins between Signals and Clips, so I don't know why that error would
occur.
In general, if you are seeing that error message, then you need to
explicitly tell SQLAlchemy the join condition between the 2 tables.
This is normally done using the primaryjoin parameter when defining
the relationships.
If you are getting the error at query time and you really *are*
performing a join, you can specify the join condition at that time as
well:
http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#querying-with-joins
Hope that helps,
Simon
On Tue, Feb 24, 2015 at 7:56 AM, eli rashlin eli.r...@gmail.com
javascript: wrote:
Thanks Simon for your replay.
when I'm removing the FK definition from the Column - The tables are
being
built as they should - the ptoblem is when i try to perform a join I'm
getting the following error:
Can't find any foreign key relationships between 'Signals' and 'Clips'.
and this is the code:
return engine.query(func.count(signals_table.Signals.sig_value)).\
filter(signals_table.Signals.message_id == msg_id).\
filter(signals_table.Signals.signal_id == sig_id).\
filter(func.format(signals_table.Signals.sig_value, 2) ==
func.format(sig_val, 2)).\ first()[0]
and this is the definition of the class Clips:
from datetime import datetime from sqlalchemy import * from
sqlalchemy.orm
import relationship from BaseCoverRuns import Base class Clips(Base):
__table_args__ = { 'mysql_engine': 'MyISAM', 'mysql_charset': 'utf8' }
__tablename__ = 'Clips' id = Column(Integer, autoincrement=True,
primary_key=True) clip_name = Column(String(255)) def
get_table_orm_def(self): return self.__table__ def __init__(self,
clip_name=None): self.clip_name = clip_name def __repr__(self): return
Clips ('%s') % (self.clip_name)
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
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: [sqlalchemy] relationship problem
On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: Hello, I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of SQLAlchemy. I have the following tables (only relevant parts are show): https://gist.github.com/silenius/390bb9937490730741f2 and the problematic mapper is the one of my association object: https://gist.github.com/silenius/1559a7db65ed30a1b079 SQLAlchemy complains with the following error: sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. The problem is that in the PoolAccountResult mapper I want a relationship to the Pool but the link is made through an intermediate table (pool_invite) .. Any idea how to handle this with SQLAlchemy ? Thanks :) Julien ... and I'm answering to myself: it seems to work with https://gist.github.com/silenius/e7e59c96a7277fb5879f does it sound right ? Sure. Also, you could use automap which does figure these out in simple cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html always with this, any idea why SQLAlchemy inserts NULL and NULL for my two relationship (line 51-79) instead of the pool_obj and dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? Thanks, Julien -- Julien Cigar Belgian Biodiversity Platform (http://www.biodiversity.be) PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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. -- Julien Cigar Belgian Biodiversity Platform (http://www.biodiversity.be) PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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. -- Julien Cigar Belgian Biodiversity Platform (http://www.biodiversity.be) PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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. pgp9ParhCgn47.pgp Description: PGP signature
Re: [sqlalchemy] How do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
What happens if you replace
join(clips_table.Clips)
with
join(signals_table.Signals.clips)
ie. telling SQLAlchemy to use the predefined relationship between
Signals and Clips as the join condition.
On Tue, Feb 24, 2015 at 12:35 PM, eli rashlin eli.rash...@gmail.com wrote:
I'm Sorry Simon - wrong query...
This is the query that gives me the error
query_obj = engine.query(signals_table.Signals.sig_value,
signals_table.Signals.exist_in_frames,
signals_table.Signals.local_frames,
clips_table.Clips.clip_name).\
join(clips_table.Clips).\
filter(signals_table.Signals.message_id == msg_id).\
filter(signals_table.Signals.signal_id
== sig_id).\
filter(func.format(signals_table.Signals.sig_value, 2) ==
func.format(sig_val, 2)).\
order_by(signals_table.Signals.sig_value)
On Tuesday, February 24, 2015 at 1:09:12 PM UTC+2, Simon King wrote:
I'm not sure I understand - the line you've shown doesn't include any
joins between Signals and Clips, so I don't know why that error would
occur.
In general, if you are seeing that error message, then you need to
explicitly tell SQLAlchemy the join condition between the 2 tables.
This is normally done using the primaryjoin parameter when defining
the relationships.
If you are getting the error at query time and you really *are*
performing a join, you can specify the join condition at that time as
well:
http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#querying-with-joins
Hope that helps,
Simon
On Tue, Feb 24, 2015 at 7:56 AM, eli rashlin eli.r...@gmail.com wrote:
Thanks Simon for your replay.
when I'm removing the FK definition from the Column - The tables are
being
built as they should - the ptoblem is when i try to perform a join I'm
getting the following error:
Can't find any foreign key relationships between 'Signals' and 'Clips'.
and this is the code:
return engine.query(func.count(signals_table.Signals.sig_value)).\
filter(signals_table.Signals.message_id == msg_id).\
filter(signals_table.Signals.signal_id == sig_id).\
filter(func.format(signals_table.Signals.sig_value, 2) ==
func.format(sig_val, 2)).\ first()[0]
and this is the definition of the class Clips:
from datetime import datetime from sqlalchemy import * from
sqlalchemy.orm
import relationship from BaseCoverRuns import Base class Clips(Base):
__table_args__ = { 'mysql_engine': 'MyISAM', 'mysql_charset': 'utf8' }
__tablename__ = 'Clips' id = Column(Integer, autoincrement=True,
primary_key=True) clip_name = Column(String(255)) def
get_table_orm_def(self): return self.__table__ def __init__(self,
clip_name=None): self.clip_name = clip_name def __repr__(self): return
Clips ('%s') % (self.clip_name)
--
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 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.
--
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] inheritance with multiple foreign keys to the base table
Thanks! Working for me now. On Tue, Feb 24, 2015 at 2:31 PM, Michael Bayer mike...@zzzcomputing.com wrote: Christopher Singley ch...@singleys.com wrote: I'm using declared_attr.cascading from sqlalchemy-1.0.0dev to declare a foreign key on a subclass that also needs another separate foreign key reference to the parent table. To let the Mapper know which column to join for the polymorphic inheritance relationship, I'm trying to pass an inherit_condition argument. Relevant code snippet can be seen here: https://gist.github.com/anonymous/1b24768cb714fb9c7de7 this is fixed in 3a56c4f019052c5d and your example case now succeeds. Importing this code generates this warning: SAWarning: Implicitly combining column secinfo.id with column optinfo.id under attribute 'id'. Please configure one or more attributes for these same-named columns explicitly. ...and then fails with this error: sqlalchemy.exc.ArgumentError: Mapper Mapper|OPTINFO|optinfo could not assemble any primary key columns for mapped table 'Join object on secinfo(23263120) and optinfo(19756496)' How can I configure this correctly? -- 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/a9IyAGKzr7M/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.
