[sqlalchemy] OperationalError: (OperationalError) no such column:
Hi All,
using 7.10 but falls over also in 8.0.
User has a One2One UserPerson.
UserPerson inherits from Person.
Person has a One2Many PersonAddress.
PersonAddress inherits from Address.
Address has a One2Many Phone and One2Many Email.
The following query falls over with an (OperationalError) no such column:
Address.Id.
session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()
What am I doing wrong ?
Thanks in advance for your help.
sqlalchemy.exc.OperationalError: (OperationalError) no such column:
Address.Id u'SELECT User.Id AS User_Id, anon_1.UserPerson_Id AS
anon_1_UserPerson_Id, anon_1.Person_Id AS anon_1_Person_Id,
anon_1.Person_PersonType AS anon_1_Person_PersonType,
anon_1.UserPerson_ItemUserPerson_Id AS
anon_1_UserPerson_ItemUserPerson_Id, anon_2.PersonAddress_Id AS
anon_2_PersonAddress_Id, anon_2.Address_Id AS anon_2_Address_Id,
anon_2.Address_AddressType AS anon_2_Address_AddressType,
anon_2.PersonAddress_ItemPerson_Id AS
anon_2_PersonAddress_ItemPerson_Id \nFROM User LEFT OUTER JOIN (SELECT
Person.Id AS Person_Id, Person.PersonType AS Person_PersonType,
UserPerson.Id AS UserPerson_Id, UserPerson.ItemUserPerson_Id AS
UserPerson_ItemUserPerson_Id \nFROM Person JOIN UserPerson ON
UserPerson.Id = Person.Id) AS anon_1 ON
anon_1.UserPerson_ItemUserPerson_Id = User.Id LEFT OUTER JOIN (SELECT
Address.Id AS Address_Id, Address.AddressType AS
Address_AddressType, PersonAddress.Id AS PersonAddress_Id,
PersonAddress.ItemPerson_Id AS PersonAddress_ItemPerson_Id \nFROM
Address JOIN PersonAddress ON PersonAddress.Id = Address.Id) AS
anon_2 ON anon_1.Person_Id = anon_2.PersonAddress_ItemPerson_Id LEFT
OUTER JOIN Phone ON anon_2.Address_Id = Phone.ItemPhone_Id LEFT
OUTER JOIN Email ON Address.Id = Email.ItemEmail_Id \nWHERE
User.Id = ?\n LIMIT ? OFFSET ?' (1, 1, 0)
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
from sqlalchemy import __version__
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.orm import relationship, Session
from decl_enum import DeclEnum
Base = declarative_base()
class Email(Base):
__tablename__ = 'Email'
Id = Column(Integer, primary_key=True)
# Many2One side of Address One2Many - backref Email.ItemEmail
ItemEmail_Id = Column(Integer,
ForeignKey('Address.Id', use_alter=True, name=FK_Email_ItemEmail_Id))
class Phone(Base):
__tablename__ = 'Phone'
Id = Column(Integer, primary_key=True)
# Many2One side of Address One2Many - backref Phone.ItemPhone
ItemPhone_Id = Column(Integer,
ForeignKey('Address.Id', use_alter=True, name=FK_Phone_ItemPhone_Id))
class AddressItemType(DeclEnum):
person = PersonAddress, Personal Address
class Address(Base):
__tablename__ = 'Address'
Id = Column(Integer, primary_key=True)
AddressType = Column(AddressItemType.db_type(), nullable=False)
# One2Many side of Phone Many2One - backref Phone.ItemPhone
Phones = relationship(Phone, uselist=True, cascade=delete, backref=ItemPhone)
# One2Many side of Email Many2One - backref Email.ItemEmail
Emails = relationship(Email, uselist=True, cascade=delete, backref=ItemEmail)
__mapper_args__ = {'polymorphic_on': AddressType,
'with_polymorphic': '*'
}
class PersonAddress(Address):
__tablename__ = 'PersonAddress'
Id = Column(Integer, ForeignKey('Address.Id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity': AddressItemType.person,
'inherit_condition': (Id == Address.Id)
}
# ManyToOne side of Person OneTomany - backref PersonAddress.ItemPerson
ItemPerson_Id = Column(Integer,
ForeignKey('Person.Id', use_alter=True, name=FK_PersonAddress_ItemPerson_Id))
class PersonItemType(DeclEnum):
user = UserPerson, User Person
class Person(Base):
__tablename__ = 'Person'
Id = Column(Integer, primary_key=True)
PersonType = Column(PersonItemType.db_type(), nullable=False)
#
[sqlalchemy] Alembic: adding a partial index to a Postgres table
This will probably be an obvious answer, but I'm struggling to figure out how to add a partial index using an Alembic (0.5.0) revision. from alembic import op import sqlalchemy as sa def upgrade(): op.create_index( 'geocoded', 'locations', ['coordinates'], postgresql_where=locations.coordinates != Null) def downgrade(): op.drop_index(geocoded) Isn't working – is it valid to pass that keyword to create_index, and if so, what string should I be passing? -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Alembic: adding a partial index to a Postgres table
are you getting a stack trace (I am) ? that would be a bug which needs repair. On Apr 24, 2013, at 10:24 AM, Stephan Hügel ursch...@gmail.com wrote: This will probably be an obvious answer, but I'm struggling to figure out how to add a partial index using an Alembic (0.5.0) revision. from alembic import op import sqlalchemy as sa def upgrade(): op.create_index( 'geocoded', 'locations', ['coordinates'], postgresql_where=locations.coordinates != Null) def downgrade(): op.drop_index(geocoded) Isn't working – is it valid to pass that keyword to create_index, and if so, what string should I be passing? -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Alembic: adding a partial index to a Postgres table
actually, the issue is that it requires a SQL expression, this should be improved, but for now do this: from sqlalchemy.sql import text op.create_index( 'geocoded', 'locations', ['coordinates'], postgresql_where=text(locations.coordinates != Null)) On Apr 24, 2013, at 10:35 AM, Michael Bayer mike...@zzzcomputing.com wrote: are you getting a stack trace (I am) ? that would be a bug which needs repair. On Apr 24, 2013, at 10:24 AM, Stephan Hügel ursch...@gmail.com wrote: This will probably be an obvious answer, but I'm struggling to figure out how to add a partial index using an Alembic (0.5.0) revision. from alembic import op import sqlalchemy as sa def upgrade(): op.create_index( 'geocoded', 'locations', ['coordinates'], postgresql_where=locations.coordinates != Null) def downgrade(): op.drop_index(geocoded) Isn't working – is it valid to pass that keyword to create_index, and if so, what string should I be passing? -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Alembic: adding a partial index to a Postgres table
Yep, that worked. Thanks! On Wednesday, 24 April 2013 15:37:26 UTC+1, Michael Bayer wrote: actually, the issue is that it requires a SQL expression, this should be improved, but for now do this: from sqlalchemy.sql import text op.create_index( 'geocoded', 'locations', ['coordinates'], postgresql_where=text(locations.coordinates != Null)) On Apr 24, 2013, at 10:35 AM, Michael Bayer mik...@zzzcomputing.comjavascript: wrote: are you getting a stack trace (I am) ? that would be a bug which needs repair. On Apr 24, 2013, at 10:24 AM, Stephan Hügel ursc...@gmail.comjavascript: wrote: This will probably be an obvious answer, but I'm struggling to figure out how to add a partial index using an Alembic (0.5.0) revision. from alembic import op import sqlalchemy as sa def upgrade(): op.create_index( 'geocoded', 'locations', ['coordinates'], postgresql_where=locations.coordinates != Null) def downgrade(): op.drop_index(geocoded) Isn't working – is it valid to pass that keyword to create_index, and if so, what string should I be passing? -- 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.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] OperationalError: (OperationalError) no such column:
this is logged as http://www.sqlalchemy.org/trac/ticket/2714 for further study, this is an extremely complicated use case and I may have insight on it today, or I might not have insight on it until next week.I will have to find the time to look into it more deeply. On Apr 24, 2013, at 8:35 AM, gvv gvver...@gmail.com wrote: Hi All, using 7.10 but falls over also in 8.0. User has a One2One UserPerson. UserPerson inherits from Person. Person has a One2Many PersonAddress. PersonAddress inherits from Address. Address has a One2Many Phone and One2Many Email. The following query falls over with an (OperationalError) no such column: Address.Id. session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\ outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first() What am I doing wrong ? Thanks in advance for your help. sqlalchemy.exc.OperationalError: (OperationalError) no such column: Address.Id u'SELECT User.Id AS User_Id, anon_1.UserPerson_Id AS anon_1_UserPerson_Id, anon_1.Person_Id AS anon_1_Person_Id, anon_1.Person_PersonType AS anon_1_Person_PersonType, anon_1.UserPerson_ItemUserPerson_Id AS anon_1_UserPerson_ItemUserPerson_Id, anon_2.PersonAddress_Id AS anon_2_PersonAddress_Id, anon_2.Address_Id AS anon_2_Address_Id, anon_2.Address_AddressType AS anon_2_Address_AddressType, anon_2.PersonAddress_ItemPerson_Id AS anon_2_PersonAddress_ItemPerson_Id \nFROM User LEFT OUTER JOIN (SELECT Person.Id AS Person_Id, Person.PersonType AS Person_PersonType, UserPerson.Id AS UserPerson_Id, UserPerson.ItemUserPerson_Id AS UserPerson_ItemUserPerson_Id \nFROM Person JOIN UserPerson ON UserPerson.Id = Person.Id) AS anon_1 ON anon_1.UserPerson_ItemUserPerson_Id = User.Id LEFT OUTER JOIN (SELECT Address.Id AS Address_Id, Address.AddressType AS Address_AddressType, PersonAddress.Id AS PersonAddress_Id, PersonAddress.ItemPerson_Id AS PersonAddress_ItemPerson_Id \nFROM Address JOIN PersonAddress ON PersonAddress.Id = Address.Id) AS anon_2 ON anon_1.Person_Id = anon_2.PersonAddress_ItemPerson_Id LEFT OUTER JOIN Phone ON anon_2.Address_Id = Phone.ItemPhone_Id LEFT OUTER JOIN Email ON Address.Id = Email.ItemEmail_Id \nWHERE User.Id = ?\n LIMIT ? OFFSET ?' (1, 1, 0) -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. test.pydecl_enum.py -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Many to Many and Unique Object question
I've got a many to many relationship with the association table. The issue
I am running into is that a given pesticide entry may have a pest or pests
already in the pest table. I don't want to add the pest since it will be a
duplicate, however I still want the association of that pest to the
pesticide done.
I've looked at the Unique Object wiki, however I am not sure that solves my
issue. Is there a way that SQLAlchemy can handle this, or do I need to
manually build the entries in the PestToPesticide association table?
Thanks!
Dan
PestToPesticide = Table('pest_to_pesticide', Base.metadata,
Column('pesticide_id', Integer,
ForeignKey('pesticide.row_id')),
Column('pest_id', Integer,
ForeignKey('pest.row_id'))
)
class Pesticide(Base):
__tablename__ = 'pesticide'
row_id =
Column(Integer,primary_key=True)
name = Column(String(64), unique=True)
pestList =relationship(Pest,
secondary=PestToPesticide, backref=pesticide)
class Pest(object):
row_id =
Column(Integer,primary_key=True)
name = Column(String(), unique=True)
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Many to Many and Unique Object question
pesttopesticide is maintained by relationship() as the secondary table, but
the expectation is that rows in this table will be unique. if you are
appending duplicate entries to pestList or pesticide you may want to use
collection_class=set to maintain uniqueness in the collection.
On Apr 24, 2013, at 12:51 PM, dan d...@inlet.geol.sc.edu wrote:
I've got a many to many relationship with the association table. The issue I
am running into is that a given pesticide entry may have a pest or pests
already in the pest table. I don't want to add the pest since it will be a
duplicate, however I still want the association of that pest to the pesticide
done.
I've looked at the Unique Object wiki, however I am not sure that solves my
issue. Is there a way that SQLAlchemy can handle this, or do I need to
manually build the entries in the PestToPesticide association table?
Thanks!
Dan
PestToPesticide = Table('pest_to_pesticide', Base.metadata,
Column('pesticide_id', Integer,
ForeignKey('pesticide.row_id')),
Column('pest_id', Integer,
ForeignKey('pest.row_id'))
)
class Pesticide(Base):
__tablename__ = 'pesticide'
row_id = Column(Integer,primary_key=True)
name = Column(String(64), unique=True)
pestList =relationship(Pest, secondary=PestToPesticide,
backref=pesticide)
class Pest(object):
row_id = Column(Integer,primary_key=True)
name = Column(String(), unique=True)
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] OperationalError: (OperationalError) no such column:
oh. this *fails in 0.7 also*. I thought this was a regression.Yeah, this query is a little crazier than we've planned for, I can see the general thing it's failing to do but will have to see what's involved to get it going. But not a regression is good news at least. On Apr 24, 2013, at 8:35 AM, gvv gvver...@gmail.com wrote: Hi All, using 7.10 but falls over also in 8.0. User has a One2One UserPerson. UserPerson inherits from Person. Person has a One2Many PersonAddress. PersonAddress inherits from Address. Address has a One2Many Phone and One2Many Email. The following query falls over with an (OperationalError) no such column: Address.Id. session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\ outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first() What am I doing wrong ? Thanks in advance for your help. sqlalchemy.exc.OperationalError: (OperationalError) no such column: Address.Id u'SELECT User.Id AS User_Id, anon_1.UserPerson_Id AS anon_1_UserPerson_Id, anon_1.Person_Id AS anon_1_Person_Id, anon_1.Person_PersonType AS anon_1_Person_PersonType, anon_1.UserPerson_ItemUserPerson_Id AS anon_1_UserPerson_ItemUserPerson_Id, anon_2.PersonAddress_Id AS anon_2_PersonAddress_Id, anon_2.Address_Id AS anon_2_Address_Id, anon_2.Address_AddressType AS anon_2_Address_AddressType, anon_2.PersonAddress_ItemPerson_Id AS anon_2_PersonAddress_ItemPerson_Id \nFROM User LEFT OUTER JOIN (SELECT Person.Id AS Person_Id, Person.PersonType AS Person_PersonType, UserPerson.Id AS UserPerson_Id, UserPerson.ItemUserPerson_Id AS UserPerson_ItemUserPerson_Id \nFROM Person JOIN UserPerson ON UserPerson.Id = Person.Id) AS anon_1 ON anon_1.UserPerson_ItemUserPerson_Id = User.Id LEFT OUTER JOIN (SELECT Address.Id AS Address_Id, Address.AddressType AS Address_AddressType, PersonAddress.Id AS PersonAddress_Id, PersonAddress.ItemPerson_Id AS PersonAddress_ItemPerson_Id \nFROM Address JOIN PersonAddress ON PersonAddress.Id = Address.Id) AS anon_2 ON anon_1.Person_Id = anon_2.PersonAddress_ItemPerson_Id LEFT OUTER JOIN Phone ON anon_2.Address_Id = Phone.ItemPhone_Id LEFT OUTER JOIN Email ON Address.Id = Email.ItemEmail_Id \nWHERE User.Id = ?\n LIMIT ? OFFSET ?' (1, 1, 0) -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. test.pydecl_enum.py -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Many to Many and Unique Object question
Using the collection_class=set is only going to work if I try to add the
same pest multiple times into pestList isn't it?
My situation is more along the lines that the pest table already has the
pest, and the pesticide I want to add also will have that pest, so I want
the entries in the pest_to_pesticide created. What currently happens is an
IntegrityError is thrown complaining that there is a duplicate pest entry.
On Wednesday, April 24, 2013 1:24:59 PM UTC-4, Michael Bayer wrote:
pesttopesticide is maintained by relationship() as the secondary table,
but the expectation is that rows in this table will be unique. if you are
appending duplicate entries to pestList or pesticide you may want to
use collection_class=set to maintain uniqueness in the collection.
On Apr 24, 2013, at 12:51 PM, dan d...@inlet.geol.sc.edu javascript:
wrote:
I've got a many to many relationship with the association table. The issue
I am running into is that a given pesticide entry may have a pest or pests
already in the pest table. I don't want to add the pest since it will be a
duplicate, however I still want the association of that pest to the
pesticide done.
I've looked at the Unique Object wiki, however I am not sure that solves
my issue. Is there a way that SQLAlchemy can handle this, or do I need to
manually build the entries in the PestToPesticide association table?
Thanks!
Dan
PestToPesticide = Table('pest_to_pesticide', Base.metadata,
Column('pesticide_id', Integer,
ForeignKey('pesticide.row_id')),
Column('pest_id', Integer,
ForeignKey('pest.row_id'))
)
class Pesticide(Base):
__tablename__ = 'pesticide'
row_id =
Column(Integer,primary_key=True)
name = Column(String(64), unique=True)
pestList =relationship(Pest,
secondary=PestToPesticide, backref=pesticide)
class Pest(object):
row_id =
Column(Integer,primary_key=True)
name = Column(String(), unique=True)
--
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.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Many to Many and Unique Object question
On Apr 24, 2013, at 1:38 PM, dan d...@inlet.geol.sc.edu wrote:
Using the collection_class=set is only going to work if I try to add the same
pest multiple times into pestList isn't it?
My situation is more along the lines that the pest table already has the
pest, and the pesticide I want to add also will have that pest, so I want the
entries in the pest_to_pesticide created. What currently happens is an
IntegrityError is thrown complaining that there is a duplicate pest entry.
well the issue of the pest_to_pesticide table and the unique entries in Pest
are two separate things. since this is many to many, sure any number of Pests
can be associated with any number of Pesticides, so to achieve this you of
course need to work with an existing Pest entry and associate it as needed.
Typically, if you know what Pest you want to work with, you look it up in the
database, if its not there, you create it. The UniqueObject recipe is one way
to make this more invisible but it isn't a requirement.Just to get things
to work you might want to keep it simple and just do a get_or_create_pest()
function, before turning it into something more transparent.
On Wednesday, April 24, 2013 1:24:59 PM UTC-4, Michael Bayer wrote:
pesttopesticide is maintained by relationship() as the secondary table, but
the expectation is that rows in this table will be unique. if you are
appending duplicate entries to pestList or pesticide you may want to use
collection_class=set to maintain uniqueness in the collection.
On Apr 24, 2013, at 12:51 PM, dan d...@inlet.geol.sc.edu wrote:
I've got a many to many relationship with the association table. The issue I
am running into is that a given pesticide entry may have a pest or pests
already in the pest table. I don't want to add the pest since it will be a
duplicate, however I still want the association of that pest to the
pesticide done.
I've looked at the Unique Object wiki, however I am not sure that solves my
issue. Is there a way that SQLAlchemy can handle this, or do I need to
manually build the entries in the PestToPesticide association table?
Thanks!
Dan
PestToPesticide = Table('pest_to_pesticide', Base.metadata,
Column('pesticide_id', Integer,
ForeignKey('pesticide.row_id')),
Column('pest_id', Integer,
ForeignKey('pest.row_id'))
)
class Pesticide(Base):
__tablename__ = 'pesticide'
row_id = Column(Integer,primary_key=True)
name = Column(String(64), unique=True)
pestList =relationship(Pest, secondary=PestToPesticide,
backref=pesticide)
class Pest(object):
row_id = Column(Integer,primary_key=True)
name = Column(String(), unique=True)
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Many to Many and Unique Object question
Cool, that's what I wanted to know. I didn't want to miss some function
that did this for me if it was there.
Thanks alot for the help!
Dan
On Wednesday, April 24, 2013 1:42:39 PM UTC-4, Michael Bayer wrote:
On Apr 24, 2013, at 1:38 PM, dan d...@inlet.geol.sc.edu javascript:
wrote:
Using the collection_class=set is only going to work if I try to add the
same pest multiple times into pestList isn't it?
My situation is more along the lines that the pest table already has the
pest, and the pesticide I want to add also will have that pest, so I want
the entries in the pest_to_pesticide created. What currently happens is an
IntegrityError is thrown complaining that there is a duplicate pest entry.
well the issue of the pest_to_pesticide table and the unique entries in
Pest are two separate things. since this is many to many, sure any number
of Pests can be associated with any number of Pesticides, so to achieve
this you of course need to work with an existing Pest entry and associate
it as needed. Typically, if you know what Pest you want to work with, you
look it up in the database, if its not there, you create it. The
UniqueObject recipe is one way to make this more invisible but it isn't a
requirement.Just to get things to work you might want to keep it simple
and just do a get_or_create_pest() function, before turning it into
something more transparent.
On Wednesday, April 24, 2013 1:24:59 PM UTC-4, Michael Bayer wrote:
pesttopesticide is maintained by relationship() as the secondary table,
but the expectation is that rows in this table will be unique. if you are
appending duplicate entries to pestList or pesticide you may want to
use collection_class=set to maintain uniqueness in the collection.
On Apr 24, 2013, at 12:51 PM, dan d...@inlet.geol.sc.edu wrote:
I've got a many to many relationship with the association table. The
issue I am running into is that a given pesticide entry may have a pest or
pests already in the pest table. I don't want to add the pest since it will
be a duplicate, however I still want the association of that pest to the
pesticide done.
I've looked at the Unique Object wiki, however I am not sure that solves
my issue. Is there a way that SQLAlchemy can handle this, or do I need to
manually build the entries in the PestToPesticide association table?
Thanks!
Dan
PestToPesticide = Table('pest_to_pesticide', Base.metadata,
Column('pesticide_id', Integer,
ForeignKey('pesticide.row_id')),
Column('pest_id', Integer,
ForeignKey('pest.row_id'))
)
class Pesticide(Base):
__tablename__ = 'pesticide'
row_id =
Column(Integer,primary_key=True)
name = Column(String(64), unique=True)
pestList =relationship(Pest,
secondary=PestToPesticide, backref=pesticide)
class Pest(object):
row_id =
Column(Integer,primary_key=True)
name = Column(String(), unique=True)
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] mysql+gaerdbms: metadata.create_all() calls DESCRIBE
Guys, I worked on an improvement to the `mysql+gaerdbms` dialect that allows it to connect to a local database when using the development server. The new dialect is here: http://stackoverflow.com/a/16198395/125967 However, something very odd happens when calling `metadata.create_all()`: at some point it calls `DESCRIBE` on the yet- to-be-created table. Using the following dummy example: https://gist.github.com/moraes/dfe12eb44c2bcb47c166 When I call `table.create()` (or `for table in metadata.sorted_tables: table.create()`) it works. Then I drop all tables and try again calling `metadata.create_all()`. And for some reason it executes a `DESCRIBE` for a table that doesn't exist, and an exception occurs. This is the echo: https://gist.github.com/moraes/ac3cb12c9e6a6d70c6dd I'm very new to SqlAlchemy, so for those familiar with dialects: any clues why this is happening? :) Thank you. -- rodrigo -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] mysql+gaerdbms: metadata.create_all() calls DESCRIBE
that's the only way we can tell if a table exists. We catch the exception for DESCRIBE, determine that its the exception we expect, and return True or False. There have already been issues in the gaerdbms dialect regarding Google's exception formatting here being inconsistent. you should emulate the approach here: http://hg.sqlalchemy.org/sqlalchemy/file/21043b66fe2e/lib/sqlalchemy/dialects/mysql/gaerdbms.py#l67 On Apr 24, 2013, at 4:06 PM, Rodrigo Moraes rodrigo.mor...@gmail.com wrote: Guys, I worked on an improvement to the `mysql+gaerdbms` dialect that allows it to connect to a local database when using the development server. The new dialect is here: http://stackoverflow.com/a/16198395/125967 However, something very odd happens when calling `metadata.create_all()`: at some point it calls `DESCRIBE` on the yet- to-be-created table. Using the following dummy example: https://gist.github.com/moraes/dfe12eb44c2bcb47c166 When I call `table.create()` (or `for table in metadata.sorted_tables: table.create()`) it works. Then I drop all tables and try again calling `metadata.create_all()`. And for some reason it executes a `DESCRIBE` for a table that doesn't exist, and an exception occurs. This is the echo: https://gist.github.com/moraes/ac3cb12c9e6a6d70c6dd I'm very new to SqlAlchemy, so for those familiar with dialects: any clues why this is happening? :) Thank you. -- rodrigo -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: mysql+gaerdbms: metadata.create_all() calls DESCRIBE
On Apr 24, 5:28 pm, Michael Bayer wrote: There have already been issues in the gaerdbms dialect regarding Google's exception formatting here being inconsistent. you should emulate the approach here:http://hg.sqlalchemy.org/sqlalchemy/file/21043b66fe2e/lib/sqlalchemy/... Thank you very much, that regexp did the trick: metadata.create_all() now works. I'll open a ticket to propose the local-only mode to gaerdbms. -- rodrigo -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] OperationalError: (OperationalError) no such column:
Here's a technique you should be able to use as a workaround: 1. for every mapper that you're joining *to* which is also a joined inheritance subclass, transform it into an aliased() construct 2. any join involving an aliased() construct, construct the ON criterion manually. These two techniques should bypass all the automation in query.join() that's failing, such as: from sqlalchemy.orm import aliased up = aliased(UserPerson) pa = aliased(PersonAddress) session.query(User).\ outerjoin(up, up.ItemUserPerson_Id == User.Id).\ outerjoin(pa, pa.ItemPerson_Id == up.Id).\ outerjoin(pa.Emails).\ outerjoin(pa.Phones).\ first() On Apr 24, 2013, at 1:38 PM, Michael Bayer mike...@zzzcomputing.com wrote: oh. this *fails in 0.7 also*. I thought this was a regression.Yeah, this query is a little crazier than we've planned for, I can see the general thing it's failing to do but will have to see what's involved to get it going. But not a regression is good news at least. On Apr 24, 2013, at 8:35 AM, gvv gvver...@gmail.com wrote: Hi All, using 7.10 but falls over also in 8.0. User has a One2One UserPerson. UserPerson inherits from Person. Person has a One2Many PersonAddress. PersonAddress inherits from Address. Address has a One2Many Phone and One2Many Email. The following query falls over with an (OperationalError) no such column: Address.Id. session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\ outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first() What am I doing wrong ? Thanks in advance for your help. sqlalchemy.exc.OperationalError: (OperationalError) no such column: Address.Id u'SELECT User.Id AS User_Id, anon_1.UserPerson_Id AS anon_1_UserPerson_Id, anon_1.Person_Id AS anon_1_Person_Id, anon_1.Person_PersonType AS anon_1_Person_PersonType, anon_1.UserPerson_ItemUserPerson_Id AS anon_1_UserPerson_ItemUserPerson_Id, anon_2.PersonAddress_Id AS anon_2_PersonAddress_Id, anon_2.Address_Id AS anon_2_Address_Id, anon_2.Address_AddressType AS anon_2_Address_AddressType, anon_2.PersonAddress_ItemPerson_Id AS anon_2_PersonAddress_ItemPerson_Id \nFROM User LEFT OUTER JOIN (SELECT Person.Id AS Person_Id, Person.PersonType AS Person_PersonType, UserPerson.Id AS UserPerson_Id, UserPerson.ItemUserPerson_Id AS UserPerson_ItemUserPerson_Id \nFROM Person JOIN UserPerson ON UserPerson.Id = Person.Id) AS anon_1 ON anon_1.UserPerson_ItemUserPerson_Id = User.Id LEFT OUTER JOIN (SELECT Address.Id AS Address_Id, Address.AddressType AS Address_AddressType, PersonAddress.Id AS PersonAddress_Id, PersonAddress.ItemPerson_Id AS PersonAddress_ItemPerson_Id \nFROM Address JOIN PersonAddress ON PersonAddress.Id = Address.Id) AS anon_2 ON anon_1.Person_Id = anon_2.PersonAddress_ItemPerson_Id LEFT OUTER JOIN Phone ON anon_2.Address_Id = Phone.ItemPhone_Id LEFT OUTER JOIN Email ON Address.Id = Email.ItemEmail_Id \nWHERE User.Id = ?\n LIMIT ? OFFSET ?' (1, 1, 0) -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. test.pydecl_enum.py -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] OperationalError: (OperationalError) no such column:
Hi Michael, Thank you very much for your help and quick response. The workaround worked. I really like the simplicity of the query api that i automate the generation of it. I will try to incorporate the workaround. Thanks again On Thursday, April 25, 2013 7:31:49 AM UTC+10, Michael Bayer wrote: Here's a technique you should be able to use as a workaround: 1. for every mapper that you're joining *to* which is also a joined inheritance subclass, transform it into an aliased() construct 2. any join involving an aliased() construct, construct the ON criterion manually. These two techniques should bypass all the automation in query.join() that's failing, such as: from sqlalchemy.orm import aliased up = aliased(UserPerson) pa = aliased(PersonAddress) session.query(User).\ outerjoin(up, up.ItemUserPerson_Id == User.Id).\ outerjoin(pa, pa.ItemPerson_Id == up.Id).\ outerjoin(pa.Emails).\ outerjoin(pa.Phones).\ first() On Apr 24, 2013, at 1:38 PM, Michael Bayer mik...@zzzcomputing.comjavascript: wrote: oh. this *fails in 0.7 also*. I thought this was a regression.Yeah, this query is a little crazier than we've planned for, I can see the general thing it's failing to do but will have to see what's involved to get it going. But not a regression is good news at least. On Apr 24, 2013, at 8:35 AM, gvv gvve...@gmail.com javascript: wrote: Hi All, using 7.10 but falls over also in 8.0. User has a One2One UserPerson. UserPerson inherits from Person. Person has a One2Many PersonAddress. PersonAddress inherits from Address. Address has a One2Many Phone and One2Many Email. The following query falls over with an (OperationalError) no such column: Address.Id. session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\ outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first() What am I doing wrong ? Thanks in advance for your help. sqlalchemy.exc.OperationalError: (OperationalError) no such column: Address.Id u'SELECT User.Id AS User_Id, anon_1.UserPerson_Id AS anon_1_UserPerson_Id, anon_1.Person_Id AS anon_1_Person_Id, anon_1.Person_PersonType AS anon_1_Person_PersonType, anon_1.UserPerson_ItemUserPerson_Id AS anon_1_UserPerson_ItemUserPerson_Id, anon_2.PersonAddress_Id AS anon_2_PersonAddress_Id, anon_2.Address_Id AS anon_2_Address_Id, anon_2.Address_AddressType AS anon_2_Address_AddressType, anon_2.PersonAddress_ItemPerson_Id AS anon_2_PersonAddress_ItemPerson_Id \nFROM User LEFT OUTER JOIN (SELECT Person.Id AS Person_Id, Person.PersonType AS Person_PersonType, UserPerson.Id AS UserPerson_Id, UserPerson.ItemUserPerson_Id AS UserPerson_ItemUserPerson_Id \nFROM Person JOIN UserPerson ON UserPerson.Id = Person.Id) AS anon_1 ON anon_1.UserPerson_ItemUserPerson_Id = User.Id LEFT OUTER JOIN (SELECT Address.Id AS Address_Id, Address.AddressType AS Address_AddressType, PersonAddress.Id AS PersonAddress_Id, PersonAddress.ItemPerson_Id AS PersonAddress_ItemPerson_Id \nFROM Address JOIN PersonAddress ON PersonAddress.Id = Address.Id) AS anon_2 ON anon_1.Person_Id = anon_2.PersonAddress_ItemPerson_Id LEFT OUTER JOIN Phone ON anon_2.Address_Id = Phone.ItemPhone_Id LEFT OUTER JOIN Email ON Address.Id = Email.ItemEmail_Id \nWHERE User.Id = ?\n LIMIT ? OFFSET ?' (1, 1, 0) -- 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.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. test.pydecl_enum.py -- 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.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
