Re: [sqlalchemy] How do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
Thank you so much Simon, your last suggestion solved it - You helped me so
much - Its working :)
On Tue, Feb 24, 2015 at 4:24 PM, Simon King wrote:
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/Qp5j77Tz4wU/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:
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 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
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 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 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 do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
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.
Re: [sqlalchemy] How do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
On Mon, Feb 23, 2015 at 12:02 PM, eli rashlin wrote:
> I have a table which i have changed the Engine from Myisam to InnoDB, This
> is the only table that has been changed, there are other tables that are in
> relation with this table of 1:N
>
> class Signals(Base, sql_functions):
>
> __tablename__ = 'Signals'
>
>
> def __init__(self, message_id=None, clip_id=None, signal_id=None):
> self.message_id = message_id
> self.clip_id= clip_id
> self.signal_id = signal_id
>
> __table_args__ = {
> 'mysql_engine': 'InnoDB',
> 'mysql_charset': 'utf8'
> }
>
> __mapper_args__= {'always_refresh': True}
>
>
> id = Column(Integer(10), primary_key=True)
> message_id = Column(Integer(10), ForeignKey('Messages.id'))
> clip_id = Column(Integer(10), ForeignKey('Clips.id'))
> signal_id = Column(Integer(10), ForeignKey('Signal_names.id'),
> primary_key=True)
>
>
> sig_name = relationship("SignalNames", backref=backref('Signals',
> order_by=id), primaryjoin = 'Signals.signal_id == SignalNames.id',
> foreign_keys = 'SignalNames.id')
> clips = relationship("Clips", backref=backref('Signals', order_by=id),
> primaryjoin = 'Signals.clip_id == Clips.id', foreign_keys = 'Clips.id')
> messages = relationship("Messages", backref=backref('Signals', order_by=id),
> primaryjoin = 'Signals.message_id == Messages.id', foreign_keys =
> 'Messages.id')
>
> def __repr__(self):
> return "" % (self.message_id, self.clip_id)
>
>
> Index('idx_msg_id', Signals.message_id)
>
> The problem is that since I have changed the Engine to InnoDB the foreignKey
> are being created which is undesired, the problem is that Sqlalchemy is
> using the foreignKey definition in the Column for defining the Join queries
> and this definition cannot be dropped.
>
> Is there a way to prevent the creation of the FK? or defining the
> relationships without the adding the foreignKey?
>
Yes, simply remove the unwanted ForeignKey lines from the column
definitions. SQLAlchemy uses those as a hint to figure out the join
conditions when you create a relationship(), but since you are already
explicitly specifying the "primaryjoin" conditions, the ForeignKey()
calls are unnecessary.
Hope that helps,
Simon
--
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 do we define a relationship in sqlalchemy for a innodb file without actually creating the FK
I have a table which i have changed the Engine from Myisam to InnoDB, This
is the only table that has been changed, there are other tables that are in
relation with this table of 1:N
class Signals(Base, sql_functions):
__tablename__ = 'Signals'
def __init__(self, message_id=None, clip_id=None, signal_id=None):
self.message_id = message_id
self.clip_id= clip_id
self.signal_id = signal_id
__table_args__ = {
'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8'
}
__mapper_args__= {'always_refresh': True}
id = Column(Integer(10), primary_key=True)
message_id = Column(Integer(10), ForeignKey('Messages.id'))
clip_id = Column(Integer(10), ForeignKey('Clips.id'))
signal_id = Column(Integer(10), ForeignKey('Signal_names.id'),
primary_key=True)
sig_name = relationship("SignalNames", backref=backref('Signals', order_by=id),
primaryjoin = 'Signals.signal_id == SignalNames.id', foreign_keys =
'SignalNames.id')
clips = relationship("Clips", backref=backref('Signals', order_by=id),
primaryjoin = 'Signals.clip_id == Clips.id', foreign_keys = 'Clips.id')
messages = relationship("Messages", backref=backref('Signals', order_by=id),
primaryjoin = 'Signals.message_id == Messages.id', foreign_keys = 'Messages.id')
def __repr__(self):
return "" % (self.message_id, self.clip_id)
Index('idx_msg_id', Signals.message_id)
The problem is that since I have changed the Engine to InnoDB the
foreignKey are being created which is undesired, the problem is that
Sqlalchemy is using the foreignKey definition in the Column for defining
the Join queries and this definition cannot be dropped.
Is there a way to prevent the creation of the FK? or defining the
relationships without the adding the foreignKey?
--
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.
