if the database you're using doesn't support UPDATE..FROM (which is likely), 
the normal way is to UPDATE with a subquery, which may be correlated or in a 
simple case like this just using IN:

UPDATE price SET extra=20000 WHERE price.id in (select price_id from person 
where name like 'J%')


subq = session.query(Person.price_id).where(Person.name.like('J%')).subquery()
session.query(Price).filter(Price.id.in_(subq)).update({Price.extra: 20000}, 
synchronize_session=False)



On Jun 18, 2013, at 2:01 PM, Witold Greń <witold.g...@gmail.com> wrote:

> I try resolved problem used this code:
> 
> No 5:
> 
> sql = session.query(Projects).join(Person, 
> Projects.students).filter(Person.gender==False, Person.name.like('M%'))
> 
> for projects in sql:
>     projects.supervisor_id = 1
> 
> session.commit()
> 
> but it is probably not a very efficient way?
> 
> 
> 
> 
> 
> 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.


Reply via email to