The problem with the update

[Witold Greń]

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?

[Witold Greń]

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?

[Michael Bayer]

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)

--
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/groups/opt_out.
 
 

[Witold Greń]

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ł:

[Michael Bayer]

On Jun 18, 2013, at 4:46 PM, Witold Greń <witol...@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()

[Witold Greń]

Resolverd no. 5:

session.query(Projects).filter(Projects.students.any(and_(Person.gender == False, Person.name.like('M%') )))\

    .update({Projects.name: u'New project name', Projects.supervisor_id: 1}, synchronize_session=False)

Again many thanks Michael :)

[Witold Greń]

This is an interesting question :)

Description: Looking for a project in which students (ManyToMany) to address (OneToOne) with the street dying text "Jana" and delete this row.

subq = session.query(Addressess.id).filter(Addressess.street.like('%Jana%')).subquery()

session.query(Projects).filter(Projects.students.any(Person.addressess_id.in_(subq))).delete(synchronize_session='fetch')

session.commit()