*This is my testing model:*
# -*- coding: utf-8 -*-
from sqlalchemy import Column, Table, Sequence, Integer, String, Boolean,
Numeric, ForeignKey, MetaData, DateTime, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref, validates
Base = declarative_base()
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(100))
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, cascade="all, delete-orphan"))
price_id = Column(Integer, ForeignKey('prices.id'), nullable=True)
price = relationship("Price", backref=backref("person", uselist=False,
cascade="all, delete-orphan"))
@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
*When I try used example query delete, always show error:*
session.query(Person).filter(Person.email.like('%@email.com')).delete(synchronize_session='fetch')
subq = session.query(Person.id).filter(Person.name.like('%P%')).subquery()
session.query(Person).filter(Person.group_leader_id.in_(subq)).delete(synchronize_session='fetch')
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')
subq =
session.query(Addressess.id).filter(Addressess.postal_code.like('%01%')).subquery()
session.query(Person).filter(Person.addressess_id.in_(subq)).delete(synchronize_session='fetch')
session.query(Person).delete(synchronize_session='fetch')
*this error:*
(IntegrityError) update or delete on table "person" violates foreign key
constraint "association_person_id_fkey" on table "association"
DETAIL: Key (id)=(2990) is still referenced from table "association".
'DELETE FROM person WHERE person.email LIKE %(email_1)s' {'email_1':
'%@email.com%'}
(IntegrityError) update or delete on table "person" violates foreign key
constraint "association_person_id_fkey" on table "association"
DETAIL: Key (id)=(1581) is still referenced from table "association".
'DELETE FROM person WHERE person.group_leader_id IN (SELECT person.id
\nFROM person \nWHERE person.name LIKE %(name_1)s)' {'name_1': '%P%'}
(IntegrityError) update or delete on table "projects" violates foreign key
constraint "association_projects_id_fkey" on table "association"
DETAIL: Key (id)=(10941) is still referenced from table "association".
'DELETE FROM projects WHERE EXISTS (SELECT 1 \nFROM association, person
\nWHERE projects.id = association.projects_id AND person.id =
association.person_id AND person.addressess_id IN (SELECT addressess.id
\nFROM addressess \nWHERE addressess.street LIKE %(street_1)s))'
{'street_1': '%Jana%'}
(IntegrityError) update or delete on table "person" violates foreign key
constraint "person_group_leader_id_fkey" on table "person"
DETAIL: Key (id)=(879) is still referenced from table "person".
'DELETE FROM person WHERE person.addressess_id IN (SELECT addressess.id
\nFROM addressess \nWHERE addressess.postal_code LIKE %(postal_code_1)s)'
{'postal_code_1': '%01%'}
(IntegrityError) update or delete on table "person" violates foreign key
constraint "association_person_id_fkey" on table "association"
DETAIL: Key (id)=(845) is still referenced from table "association".
'DELETE FROM person' {}
*I read docs:
http://docs.sqlalchemy.org/en/latest/orm/session.html#cascades but I do not
understand how to set cascade deleting.*
*I using self ForeignKey and Many2Many filter.. how to set these fields?*
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
