On Jun 18, 2013, at 4:46 PM, Witold Greń <witold.g...@gmail.com> wrote:
> > But how resolved no 5? > I try: > > subq = session.query(Person).filter(Person.gender == False, > Person.name.like('M%')).subquery() > y = session.query(Projects).filter(Projects.students.any(subq)).count() any() does the subquery correlated to the related table for you: s.query(Projects).filter(Projects.students.any(and_(Person.gender == False, Person.name.like('x')))).count() > > ERROR: > OperationalError at /sqlalchemy/test_alchemy/ > (OperationalError) near "SELECT": syntax error u'SELECT count(*) AS count_1 > \nFROM (SELECT projects.id AS projects_id, projects.name AS projects_name, > projects.subject AS projects_subject, projects.supervisor_id AS > projects_supervisor_id \nFROM projects \nWHERE EXISTS (SELECT 1 \nFROM > association, person \nWHERE projects.id = association.projects_id AND > person.id = association.person_id AND SELECT person.id, person.name, > person.surname, person.gender, person.pesel, person.phone, person.email, > person.no_card, person.status, person.student, person.group_leader_id, > person.addressess_id, person.price_id \nFROM person \nWHERE person.gender = 0 > AND person.name LIKE ?)) AS anon_1' ('M%',) > > > W dniu poniedziałek, 17 czerwca 2013 21:09:18 UTC+2 użytkownik Witold Greń > napisał: > Hi, I'm Witold and learn Sqlalchemy :) > I have a little problem, here it is: > > This is my DB: > > class Addressess(Base): > > __tablename__ = 'addressess' > > id = Column('id', Integer, Sequence('address_id_seq'), primary_key=True, > index=True, unique=True, nullable=False) > street = Column('street', String(200)) > house_no = Column('house_no', Integer()) > place = Column('place', String(200)) > postal_code = Column('postal_code', String(200)) > post = Column('post', String(200)) > > def __init__(self, street, house_no): > self.street = street > self.house_no = house_no > > def __repr__(self): > return u'%s, %s, %s' % (self.place, self.street, self.house_no) > > > class Price(Base): > > __tablename__ = 'prices' > > id = Column('id', Integer, Sequence('price_id_seq'), primary_key=True, > index=True, unique=True, nullable=False) > price = Column('price', String(200)) > extra = Column('extra', String(200), nullable=True) > > def __init__(self, price, extra=None): > self.price = price > self.extra = extra > > def __repr__(self): > return u'%s (%s)' % (self.price, self.extra) > > > class Person(Base): > > __tablename__ = 'person' > > id = Column('id', Integer, Sequence('person_id_seq'), primary_key=True, > index=True, unique=True, nullable=False) > name = Column('name', String(200)) > surname = Column('surname', String(200)) > gender = Column('gender', Boolean, unique=False) > pesel = Column('pesel', Numeric(11, 0)) > phone = Column('phone', Numeric(9, 0)) > email = Column('email', String(200)) > no_card = Column('no_card', String(10)) > status = Column('status', Integer) > student = Column('student', Boolean, unique=False) > group_leader_id = Column(Integer, ForeignKey('person.id'), nullable=True) > group_leader = relationship("Person", remote_side=[id], > primaryjoin="Person.id==Person.group_leader_id") > addressess_id = Column(Integer, ForeignKey('addressess.id'), > nullable=True) > addressess = relationship("Addressess", backref=backref("person", > uselist=False)) > price_id = Column(Integer, ForeignKey('prices.id'), nullable=True) > price = relationship("Price", backref=backref("person", uselist=False)) > > @validates('email') > def validate_email(self, key, address): > assert '@' in address > return address > > def __init__(self, name, surname): > self.name = name > self.surname = surname > > def __repr__(self): > return u'%s %s, %s' % (self.name, self.surname, self.pesel) > > > class Group(Base): > > __tablename__ = 'group' > > id = Column('id', Integer, Sequence('person_id_seq'), primary_key=True, > index=True, unique=True, nullable=False) > name = Column('email', String(200)) > leader_id = Column(Integer, ForeignKey('person.id')) > leader = relationship("Person") > addressess_id = Column(Integer, ForeignKey('addressess.id')) > addressess = relationship("Addressess") > > def __init__(self, name): > self.name = name > > def __repr__(self): > return u'%s' % self.name > > > association_table = Table('association', Base.metadata, > Column('person_id', Integer, ForeignKey('person.id')), > Column('projects_id', Integer, ForeignKey('projects.id')) > ) > > class Projects(Base): > > __tablename__ = 'projects' > > id = Column('id', Integer, Sequence('person_id_seq'), primary_key=True, > index=True, unique=True, nullable=False) > name = Column('name', String(200)) > subject = Column('subject', String(200)) > supervisor_id = Column(Integer, ForeignKey('person.id')) > supervisor = relationship("Person") > students = relationship("Person", secondary=association_table, > backref="projects") > > def __init__(self, name, subject): > self.name = name > self.subject = subject > > def __repr__(self): > return u'%s' % self.subject > > > I have a problem with update record: > This is work: > 1. > session.query(Person).filter(Person.phone.like('%89%')).update({Person.no_card: > u'Usunięto'}, synchronize_session=False) > 2. session.query(Person).join(Person.group_leader, > aliased=True).filter(or_(Person.name == 'Jan', Person.surname == 'Nowak')) > .update({Person.name:u'Łukasz'}, synchronize_session=False) > > But this is not work: > 3. > session.query(Price).join(Person).filter(Person.name.like('J%')).update({Price.extra: > 20000}, synchronize_session=False) > ERROR: > OperationalError at /sqlalchemy/test_alchemy/ > (OperationalError) near "FROM": syntax error u'UPDATE prices SET extra=? FROM > person WHERE person.name LIKE ?' (20000, 'J%') > > 4. > session.query(Addressess).join(Person.addressess).filter(Person.surname.like('%k')).filter(sqlalchemy.not_(Person.surname.like('%o%'))).update({Addressess.house_no: > 10}, synchronize_session=False) > ERROR: > OperationalError at /sqlalchemy/test_alchemy/ > (OperationalError) near "FROM": syntax error u'UPDATE addressess SET > house_no=? FROM person WHERE person.surname LIKE ? AND person.surname NOT > LIKE ?' (10, '%k', '%o%') > > 5. session.query(Projects).join(Person, > Projects.students).filter(Person.gender==False).filter(Person.name.like('M%')).update({Projects.supervisor_id: > 1}, synchronize_session=False) > ERROR: > OperationalError at /sqlalchemy/test_alchemy/ > (OperationalError) near "FROM": syntax error u'UPDATE projects SET > supervisor_id=? FROM person WHERE person.gender = 0 AND person.name LIKE ?' > (1, 'M%') > > When i use only select and count this is work: > 3. session.query(Price).join(Person).filter(Person.name.like('J%')).count() > 4. > session.query(Addressess).join(Person.addressess).filter(Person.surname.like('%k')).filter(sqlalchemy.not_(Person.surname.like('%o%'))).count() > 5. session.query(Projects).join(Person, > Projects.students).filter(Person.gender==False).filter(Person.name.like('M%')).count() > > How to update this field? > > > -- > 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/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. For more options, visit https://groups.google.com/groups/opt_out.