Il 08/08/11 22.53, Aviv Giladi ha scritto:
Dear Stefano,
I have been able to create a reproducing project. I am sending it to
you directly because I was unable to figure out how to attach the test
file to the message board. I will post our findings there once we are
done.
The included script includes all my models. I have 1 rating object,
called Rating, 2 subratings - ProductsRating and
CustomerServiceRating, and a subrating property - ProductsProperty,
that is assigned to ProductsRating.
When you run the code I am attaching as is, you will encounter my
issue (InterfaceError exception).
However, the odd thing is that the following 2 adjustments to the
script fix the problem:
1) deleting the three lines 126, 127 and 128 causes the script to work
just fine
2) instead of querying the session for the ProductsProperty, creating
it with its c'tor makes the script works as well.
I am extremely confused by this. Any help you could give me would be
beyond appreciated.
Thank you so much,
Aviv
Hi Aviv.
You made a mistake using 'cascade' and your code produces 2 warnings:
SAWarning: On Rating.products_rating, delete-orphan cascade is not
supported on a many-to-many or many-to-one relationship when
single_parent is not set. Set single_parent=True on the relationship().
SAWarning: On Rating.customer_service_rating, delete-orphan cascade is
not supported on a many-to-many or many-to-one relationship when
single_parent is not set. Set single_parent=True on the relationship().
The problem is 'cascade' setting in the following relationships:
products_rating = relationship("ProductsRating", cascade="all,
delete-orphan", backref=backref("rating", uselist=False))
customer_service_rating = relationship("CustomerServiceRating",
cascade="all, delete-orphan", backref=backref("rating", uselist=False))
you cannot set 'cascade' in ManyToMany and ManyToOne relationships, to
do that you must specify 'single_parent=True'.
Check the docs to understand the usage of 'single_parent'.
Otherwise use 'cascade' as follow:
products_rating = relationship("ProductsRating",
backref=backref("rating", cascade="all, delete-orphan", uselist=False))
customer_service_rating = relationship("CustomerServiceRating",
backref=backref("rating", cascade="all, delete-orphan", uselist=False))
I don't know the meaning of you model for that reason you must choose
the right solution.
I attached your script. I fixed it and SQLA doesn't give me any
InterfaceError.
HINT: when you use the declarative SQLA you don't need to define
__init__ function in your entities. The default __init__ is often enough.
It is needed when you want a custom behaviour.
Regards,
Stefano.
PS: you are using Pyramid! Great project!
PS2: I send this message in ML to help other users in the future.
--
Ing. Stefano Fontanelli
Asidev S.r.l.
Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
E-mail: s.fontane...@asidev.com Web: www.asidev.com
Skype: stefanofontanelli
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
import transaction
from sqlalchemy import create_engine
from sqlalchemy import *
from sqlalchemy.orm import backref
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
from zope.sqlalchemy import ZopeTransactionExtension
import datetime
import random
import string
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()
#describes a business
class Business(Base):
__tablename__ = 'tbl_businesses'
id = Column(Integer, primary_key=True)
short_name = Column(Unicode(255), unique=True)
name = Column(Unicode(255))
city = Column(Unicode(64))
state = Column(Unicode(8))
ratings = relationship("Rating", backref="business")
def __init__(self, short_name, name, city, state):
self.short_name = short_name
self.name = name
self.city = city
self.state = state
#desribes a user in the system
class User(Base):
__tablename__ = 'tbl_users'
id = Column(Integer, primary_key=True)
first_name = Column(Unicode(32))
last_name = Column(Unicode(32))
email = Column(Unicode(64), unique=True)
gender = Column(Boolean)
is_valid = Column(Boolean)
created = Column(DateTime, default=datetime.datetime.utcnow)
ratings = relationship("Rating", backref="user")
def __init__(self, first_name, last_name, email, gender):
self.first_name = first_name
self.last_name = last_name
self.email = email
if gender == "male":
self.gender = True
else:
self.gender = False
self.is_valid = False
#describes a type of user, such as business owner, customer, etc
class UserType(Base):
__tablename__ = 'tbl_user_types'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32))
ratings = relationship("Rating", backref="user_type")
def __init__(self, name):
self.name = name
#describes a rating that a user gave the customer service of the business
class CustomerServiceRating(Base):
__tablename__ = 'tbl_customer_service_ratings'
id = Column(Integer, primary_key=True)
score = Column(Numeric(precision=6, scale=4))
#describes a rating that a user gave the products of the business
class ProductsRating(Base):
__tablename__ = 'tbl_prodcts_ratings'
id = Column(Integer, primary_key=True)
score = Column(Numeric(precision=6, scale=4))
#the table that associated the products ratings and their properties
products_properties_association = Table('tbl_products_properties_association', Base.metadata,
Column('prodcts_rating_id', Integer, ForeignKey('tbl_prodcts_ratings.id')),
Column('products_proprty_id', Integer, ForeignKey('tbl_products_proprties.id')))
#describes a property that can be given (one or more) to the product rating.
#for example, the user can rate the products of the business at 7.5 out of 10,
#and assign their products properties such as "good quality" or "good availability"
class ProductsProperty(Base):
__tablename__ = 'tbl_products_proprties'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
ratings = relationship("ProductsRating",
secondary=products_properties_association,
backref="products_proprties")
def __init__(self, name):
self.name = name
#describes the rating that holds the user, business, user_type, and the different ratings such as
#products rating and customer rating
class Rating(Base):
__tablename__ = 'tbl_ratings'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('tbl_users.id'))
business_id = Column(Integer, ForeignKey('tbl_businesses.id'))
user_type_id = Column(Integer, ForeignKey('tbl_user_types.id'))
is_valid = Column(Boolean)
created = Column(DateTime, default=datetime.datetime.utcnow)
products_rating_id = Column(Integer, ForeignKey('tbl_prodcts_ratings.id'))
products_rating = relationship("ProductsRating", backref=backref("rating", cascade="all, delete-orphan", uselist=False))
customer_service_rating_id = Column(Integer, ForeignKey('tbl_customer_service_ratings.id'))
customer_service_rating = relationship("CustomerServiceRating", backref=backref("rating", cascade="all, delete-orphan", uselist=False))
def __init__(self, user, business, user_type):
if user.is_valid:
self.is_valid = True
else:
self.is_valid = False
###commenting out these 3 members assignments FIXES the problem!
#####DOESN'T WORK
self.user = user
self.business = business
self.user_type = user_type
#####removing the above 3 lines - WORKS!
#populate the database with some dummy data
def populate():
session = DBSession()
session.add(Business(u"test-business", u"Test Business", u"Fakeville", u"XX"))
session.add(User(first_name=u"first", last_name=u"last", email=u"em...@email.com", gender=u"male"))
session.add(UserType(name=u"owner"))
session.add(UserType(name=u"visitor"))
session.add(ProductsProperty(u"good quality"))
session.add(ProductsProperty(u"always in stock"))
session.add(ProductsProperty(u"decently priced"))
transaction.commit()
if __name__ == '__main__':
engine = create_engine('sqlite:///', echo=False)
DBSession.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all(engine)
#create all the products properties and fake user, usertype and business in the DB
populate()
session = DBSession()
#get the business, user and user type objects from the session
business = session.query(Business).first()
user = session.query(User).first()
user_type = session.query(UserType).first()
#create a rating object with the acquired business, user and user type objects
rating = Rating(user, business, user_type)
#create a product rating object with a score
products_rating = ProductsRating(score = 8.5)
#getting the product property from the session doesnt work.
#commenting it out and simply creating the product property with its c'tor fixes the issue!
#####DOESN'T WORK!
products_property = session.query(ProductsProperty).first()
#####WORKS!
#products_property = ProductsProperty(name="dummy property")
#add the acquired property to the product rating
products_rating.products_proprties.append(products_property)
#set the rating object's product rating to the crreating product rating
rating.products_rating = products_rating
session.add(rating)
session.flush()
transaction.commit()
