Re: [sqlalchemy] inheritance with multiple foreign keys to the base table

2015-02-24 Thread Christopher Singley 
Thanks!  Working for me now.

On Tue, Feb 24, 2015 at 2:31 PM, Michael Bayer 
wrote:

>
>
> Christopher Singley  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.

Re: [sqlalchemy] inheritance with multiple foreign keys to the base table

2015-02-24 Thread Michael Bayer 


Christopher Singley  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] relationship problem

2015-02-24 Thread Julien Cigar 
On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote:
> 
> 
> Julien Cigar  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

2015-02-24 Thread Simon King 
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  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  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
>> > "" % (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] How do we define a relationship in sqlalchemy for a innodb file without actually creating the FK

2015-02-24 Thread eli rashlin 
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  > 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 
> > "" % (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.

RE: [sqlalchemy] "How can I know which fields cause IntegrityError when inserting into a table with multiple unique fields?"

2015-02-24 Thread Gombas, Gabor 
There may be better ways if your backend supports it. E.g. if you’re using a 
recent version of PostgreSQL, then diagnostics information is attached to the 
exception being thrown by psycopg2 (which is available under the .orig property 
of the SQLAlchemy exception). See the documentation of 
psycopg2.extensions.Diagnostics and the documentation of libpq. I don’t know if 
there are enough details in the diagnostics to support your use case.

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On 
Behalf Of Maurice Schleußinger
Sent: 24 February 2015 11:42
To: sqlalchemy@googlegroups.com
Subject: 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 
> 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.
 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 mailto: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.
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.




NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or 
views contained herein are not intended to be, and do not constitute, advice 
within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and 
Consumer Protection Act. If you have received t

[sqlalchemy] inheritance with multiple foreign keys to the base table

2015-02-24 Thread Christopher Singley 
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

2015-02-24 Thread Simon King 
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  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
> "" % (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?"

2015-02-24 Thread Maurice Schleußinger 
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  > 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 
> .
>  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  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 
> .
> 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.