Thanks for your help Michael :)
Resolved:
No.2
subq = session.query(Person.group_leader_id).filter(or_(Person.name ==
'Jan', Person.surname == 'Nowak')).subquery()
session.query(Person).filter(Person.id.in_(subq)).update({Person.name:
u'Łukasz'}, synchronize_session=False)
# All support but not SQLite:
# 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)
No. 3
subq =
session.query(Person.price_id).filter(Person.name.like('J%')).subquery()
session.query(Price).filter(Price.id.in_(subq)).update({Price.extra:
20000}, synchronize_session=False)
# All support but not SQLite:
#
session.query(Price).join(Person).filter(Person.name.like('J%')).update({Price.extra:
20000}, synchronize_session=False)
No.4
subq =
session.query(Person.addressess_id).filter(Person.surname.like('%k'))\
.filter(sqlalchemy.not_(Person.surname.like('%o%'))).subquery()
session.query(Addressess).filter(Addressess.id.in_(subq)).update({Addressess.house_no:
10}, synchronize_session=False)
# All support but not SQLite:
#
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)
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()
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.
