[sqlalchemy] Re: stuck on IntegrityError
I don't know whether to laugh or cry ... thanks
On Mar 28, 6:25 pm, Michael Bayer wrote:
> your enum is set for "uni", "list", "set" and you're passing in "one"
>
> On Mar 28, 2011, at 6:35 AM, farcat wrote:
>
>
>
>
>
>
>
> > Hi all,
>
> > I am stuck on an integrity error. The code worked before, but i cannot
> > figure out what changed so it does not anymore. It is possibly a dumn
> > oversight.
>
> > The code is:
>
> > from sqlalchemy import *
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import relationship
> > from sqlalchemy.orm.session import sessionmaker
>
> > Base = declarative_base()
>
> > def trim(txt):
> > txt.strip()
> > return txt
>
> > inheritance_table = Table('inheritance', Base.metadata,
> > Column('sub_name', String(50), ForeignKey('Type.name'),
> > primary_key=True),
> > Column('super_name', String(50), ForeignKey('Type.name'),
> > primary_key=True))
>
> > class Member(Base):
> > __tablename__ = 'Member'
> > name = Column(String(50), primary_key=True)
> > multiplicity = Column(Enum("uni", "list", "set"), nullable =
> > False)
> > reference = Column(Boolean, nullable = False)
> > type_name = Column(String(50),ForeignKey('Type.name'), nullable =
> > False)
> > of_type_name = Column(String(50),ForeignKey('Type.name'),
> > primary_key=True)
> > def __init__(self, name, type, multiplicity = "uni", reference =
> > False):
> > self.name = trim(name)
> > self.type = type
> > self.reference = reference
> > self.multiplicity = multiplicity
> > def __repr__(self):
> > return "Member(%r, %r, %r, %r, %r)" % (self.name,
> > self.of_type_name,
> > self.type_name,
> > self.multiplicity,
> > self.reference)
>
> > class Type(Base):
> > __tablename__ = 'Type'
> > name = Column(String(50), primary_key=True)
> > abstract = Column(Boolean, nullable = False)
> > atomic = Column(Boolean, nullable = False)
> > subtypes = relationship('Type',
> > secondary=inheritance_table,
> > primaryjoin=inheritance_table.c.super_name==name,
> > secondaryjoin= name == inheritance_table.c.sub_name,
> > backref='supertypes')
> > members = relationship('Member',
> > primaryjoin=Member.of_type_name==name,
> > backref='of_type')
> > of_members = relationship("Member",
> > primaryjoin = Member.type_name == name,
> > backref= "type")
>
> > def isSubOf(self, tp):
> > if self == tp:
> > return True
> > for typ in self.supertypes:
> > if typ.isSubOf(tp): return True
> > return False
> > def addSub(self, tp):
> > if tp.atomic:
> > raise Exception("type " + tp.name + " cannot have
> > supertype")
> > if self.isSubOf(tp):
> > raise Exception("adding subtype " + tp + "creates cyclic
> > inheritance")
> > for mem in tp.members:
> > self.check(mem.name, mem.type)
> > self.subtypes.append(tp)
> > def getAll(self):
> > out = set(self.members)
> > for typ in self.supertypes:
> > out.update(typ.getAll())
> > return out
> > def getAny(self, name):
> > temp = self.getAll()
> > for mem in temp:
> > if mem.name == name: return mem
> > return None
> > def hasAny(self, name):
> > return self.getAny(name) != None
> > def check(self, name, typ, reference):
> > if self.atomic:
> > raise Exception("atomic type " + self.name + " cannot be
> > changed")
> > if self.hasAny(name):
> > raise Exception("member name " + name + "already exists in
> > this type or supertype")
> > elif typ.isSubOf(self) and not reference:
> > raise Exception("member type " + type.name + " equals this
> > type or subtype")
> > def add(self, name, type, multiplicity = "one", reference =
> > False):
> > self.check(name, type, reference)
> > self.members.append(Member(name, type, multiplicity,
> > reference))
> > def __init__(self, name, atomic = False, abstract = False):
> > self.name = name
> > self.atomic = atomic
> > self.abstract = abstract
> > def __repr__(self):
> > out = "Type(%r): {" % (self.name)
> > for mem in self.members:
> > out += str(mem) + ", "
> > out += "}"
> > return out
>
> > if __name__ == "__main__":
> > engine = create_engine('sqlite:///:memory:', echo=True)
> > Base.metadata.drop_all(engine)
> > Base.metadata.create_all(engine)
> > Session = sessionmaker(bind=engine)
> > session = Session()
> > try:
> > c1 = Type("A")
> > c2 = Type("B")
> > c1.add("m11", c2)
> > se
Re: [sqlalchemy] stuck on IntegrityError
your enum is set for "uni", "list", "set" and you're passing in "one"
On Mar 28, 2011, at 6:35 AM, farcat wrote:
> Hi all,
>
> I am stuck on an integrity error. The code worked before, but i cannot
> figure out what changed so it does not anymore. It is possibly a dumn
> oversight.
>
> The code is:
>
>
>
> from sqlalchemy import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm.session import sessionmaker
>
> Base = declarative_base()
>
> def trim(txt):
>txt.strip()
>return txt
>
> inheritance_table = Table('inheritance', Base.metadata,
>Column('sub_name', String(50), ForeignKey('Type.name'),
> primary_key=True),
>Column('super_name', String(50), ForeignKey('Type.name'),
> primary_key=True))
>
> class Member(Base):
>__tablename__ = 'Member'
>name = Column(String(50), primary_key=True)
>multiplicity = Column(Enum("uni", "list", "set"), nullable =
> False)
>reference = Column(Boolean, nullable = False)
>type_name = Column(String(50),ForeignKey('Type.name'), nullable =
> False)
>of_type_name = Column(String(50),ForeignKey('Type.name'),
> primary_key=True)
>def __init__(self, name, type, multiplicity = "uni", reference =
> False):
>self.name = trim(name)
>self.type = type
>self.reference = reference
>self.multiplicity = multiplicity
>def __repr__(self):
>return "Member(%r, %r, %r, %r, %r)" % (self.name,
> self.of_type_name,
> self.type_name,
> self.multiplicity,
> self.reference)
>
> class Type(Base):
>__tablename__ = 'Type'
>name = Column(String(50), primary_key=True)
>abstract = Column(Boolean, nullable = False)
>atomic = Column(Boolean, nullable = False)
>subtypes = relationship('Type',
>secondary=inheritance_table,
>primaryjoin=inheritance_table.c.super_name==name,
>secondaryjoin= name == inheritance_table.c.sub_name,
>backref='supertypes')
>members = relationship('Member',
>primaryjoin=Member.of_type_name==name,
>backref='of_type')
>of_members = relationship("Member",
>primaryjoin = Member.type_name == name,
>backref= "type")
>
>def isSubOf(self, tp):
>if self == tp:
>return True
>for typ in self.supertypes:
>if typ.isSubOf(tp): return True
>return False
>def addSub(self, tp):
>if tp.atomic:
>raise Exception("type " + tp.name + " cannot have
> supertype")
>if self.isSubOf(tp):
>raise Exception("adding subtype " + tp + "creates cyclic
> inheritance")
>for mem in tp.members:
>self.check(mem.name, mem.type)
>self.subtypes.append(tp)
>def getAll(self):
>out = set(self.members)
>for typ in self.supertypes:
>out.update(typ.getAll())
>return out
>def getAny(self, name):
>temp = self.getAll()
>for mem in temp:
>if mem.name == name: return mem
>return None
>def hasAny(self, name):
>return self.getAny(name) != None
>def check(self, name, typ, reference):
>if self.atomic:
>raise Exception("atomic type " + self.name + " cannot be
> changed")
>if self.hasAny(name):
>raise Exception("member name " + name + "already exists in
> this type or supertype")
>elif typ.isSubOf(self) and not reference:
>raise Exception("member type " + type.name + " equals this
> type or subtype")
>def add(self, name, type, multiplicity = "one", reference =
> False):
>self.check(name, type, reference)
>self.members.append(Member(name, type, multiplicity,
> reference))
>def __init__(self, name, atomic = False, abstract = False):
>self.name = name
>self.atomic = atomic
>self.abstract = abstract
>def __repr__(self):
>out = "Type(%r): {" % (self.name)
>for mem in self.members:
>out += str(mem) + ", "
>out += "}"
>return out
>
>
> if __name__ == "__main__":
>engine = create_engine('sqlite:///:memory:', echo=True)
>Base.metadata.drop_all(engine)
>Base.metadata.create_all(engine)
>Session = sessionmaker(bind=engine)
>session = Session()
>try:
>c1 = Type("A")
>c2 = Type("B")
>c1.add("m11", c2)
>session.add(c1)
>session.add(c2)
>session.commit() #<== ERROR
>except Exception, e:
>print "error: " + str(e)
>raise
>
> and the error:
>
>
> Traceback (most recent call last):
> File "D:\Documents\Code\Eclipse\workspace\test2\src\metadata.py",
> line 119, in
>session.commit()
> File "
Re: [sqlalchemy] Re: Exists/Correlate newbie question
right so, the correlate() can be against multi_obs directly, not just __table__, and also shouldn't be required at all since auto-correlation is sufficient here. On Mar 28, 2011, at 12:23 PM, dan wrote: > Thanks for the feedback. I found an example in google groups that got > me on the right track. > > For those interested, my sqlalchemy looks like: > > > multi_obs2 = aliased(multi_obs) > subQ = qaqc.db.session.query(multi_obs2).\ >filter(multi_obs2.m_date >= beginDate).\ >filter(multi_obs2.m_date < endDate).\ >filter(multi_obs2.sensor_id == nnSensorId).\ >filter(multi_obs2.d_top_of_hour == 1).\ >filter(multi_obs2.d_report_hour == > multi_obs.d_report_hour).\ >correlate(multi_obs.__table__).\ >statement > recs = qaqc.db.session.query(multi_obs).\ > filter(multi_obs.m_date >= beginDate).\ > filter(multi_obs.m_date < endDate).\ > filter(multi_obs.sensor_id == sensorId).\ > filter(multi_obs.d_top_of_hour == 1).\ > filter(exists(subQ)).\ > order_by(multi_obs.m_date.asc()).all() > > -- > 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. > -- 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.
[sqlalchemy] Re: Exists/Correlate newbie question
Thanks for the feedback. I found an example in google groups that got me on the right track. For those interested, my sqlalchemy looks like: multi_obs2 = aliased(multi_obs) subQ = qaqc.db.session.query(multi_obs2).\ filter(multi_obs2.m_date >= beginDate).\ filter(multi_obs2.m_date < endDate).\ filter(multi_obs2.sensor_id == nnSensorId).\ filter(multi_obs2.d_top_of_hour == 1).\ filter(multi_obs2.d_report_hour == multi_obs.d_report_hour).\ correlate(multi_obs.__table__).\ statement recs = qaqc.db.session.query(multi_obs).\ filter(multi_obs.m_date >= beginDate).\ filter(multi_obs.m_date < endDate).\ filter(multi_obs.sensor_id == sensorId).\ filter(multi_obs.d_top_of_hour == 1).\ filter(exists(subQ)).\ order_by(multi_obs.m_date.asc()).all() -- 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.
Re: [sqlalchemy] Simple problem with SQLite and Standard Deviation
you'd want to hook this in using a Python standard deviation function with sqlite3's create_aggregate(): http://docs.python.org/library/sqlite3.html#sqlite3.Connection.create_aggregate SQLAlchemy can pass you the connection when first created via poollistener: http://www.sqlalchemy.org/docs/core/interfaces.html?highlight=poollistener#sqlalchemy.interfaces.PoolListener Then call any function with SQLA using "func.". func.my_standard_deviation_function(xyz) On Mar 28, 2011, at 7:51 AM, Massi wrote: > Hi everyone, I'm using sqlalchemy 0.6.6 with SQLite...my problem is > simple, is there a way to compute standard deviation in sqlalchemy > with SQlite as a backend? I googled and found that native SQLite does > not support any aggregate function like MySQL 'stdev', but there exist > some extensions which could make it available. Does sqlalchemy support > something similar? Or can anyone point me out some (even ugly) hack to > work around the problem? > Thanks in advance! > > -- > 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. > -- 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.
[sqlalchemy] Simple problem with SQLite and Standard Deviation
Hi everyone, I'm using sqlalchemy 0.6.6 with SQLite...my problem is simple, is there a way to compute standard deviation in sqlalchemy with SQlite as a backend? I googled and found that native SQLite does not support any aggregate function like MySQL 'stdev', but there exist some extensions which could make it available. Does sqlalchemy support something similar? Or can anyone point me out some (even ugly) hack to work around the problem? Thanks in advance! -- 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.
[sqlalchemy] stuck on IntegrityError
Hi all,
I am stuck on an integrity error. The code worked before, but i cannot
figure out what changed so it does not anymore. It is possibly a dumn
oversight.
The code is:
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
Base = declarative_base()
def trim(txt):
txt.strip()
return txt
inheritance_table = Table('inheritance', Base.metadata,
Column('sub_name', String(50), ForeignKey('Type.name'),
primary_key=True),
Column('super_name', String(50), ForeignKey('Type.name'),
primary_key=True))
class Member(Base):
__tablename__ = 'Member'
name = Column(String(50), primary_key=True)
multiplicity = Column(Enum("uni", "list", "set"), nullable =
False)
reference = Column(Boolean, nullable = False)
type_name = Column(String(50),ForeignKey('Type.name'), nullable =
False)
of_type_name = Column(String(50),ForeignKey('Type.name'),
primary_key=True)
def __init__(self, name, type, multiplicity = "uni", reference =
False):
self.name = trim(name)
self.type = type
self.reference = reference
self.multiplicity = multiplicity
def __repr__(self):
return "Member(%r, %r, %r, %r, %r)" % (self.name,
self.of_type_name,
self.type_name,
self.multiplicity,
self.reference)
class Type(Base):
__tablename__ = 'Type'
name = Column(String(50), primary_key=True)
abstract = Column(Boolean, nullable = False)
atomic = Column(Boolean, nullable = False)
subtypes = relationship('Type',
secondary=inheritance_table,
primaryjoin=inheritance_table.c.super_name==name,
secondaryjoin= name == inheritance_table.c.sub_name,
backref='supertypes')
members = relationship('Member',
primaryjoin=Member.of_type_name==name,
backref='of_type')
of_members = relationship("Member",
primaryjoin = Member.type_name == name,
backref= "type")
def isSubOf(self, tp):
if self == tp:
return True
for typ in self.supertypes:
if typ.isSubOf(tp): return True
return False
def addSub(self, tp):
if tp.atomic:
raise Exception("type " + tp.name + " cannot have
supertype")
if self.isSubOf(tp):
raise Exception("adding subtype " + tp + "creates cyclic
inheritance")
for mem in tp.members:
self.check(mem.name, mem.type)
self.subtypes.append(tp)
def getAll(self):
out = set(self.members)
for typ in self.supertypes:
out.update(typ.getAll())
return out
def getAny(self, name):
temp = self.getAll()
for mem in temp:
if mem.name == name: return mem
return None
def hasAny(self, name):
return self.getAny(name) != None
def check(self, name, typ, reference):
if self.atomic:
raise Exception("atomic type " + self.name + " cannot be
changed")
if self.hasAny(name):
raise Exception("member name " + name + "already exists in
this type or supertype")
elif typ.isSubOf(self) and not reference:
raise Exception("member type " + type.name + " equals this
type or subtype")
def add(self, name, type, multiplicity = "one", reference =
False):
self.check(name, type, reference)
self.members.append(Member(name, type, multiplicity,
reference))
def __init__(self, name, atomic = False, abstract = False):
self.name = name
self.atomic = atomic
self.abstract = abstract
def __repr__(self):
out = "Type(%r): {" % (self.name)
for mem in self.members:
out += str(mem) + ", "
out += "}"
return out
if __name__ == "__main__":
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
try:
c1 = Type("A")
c2 = Type("B")
c1.add("m11", c2)
session.add(c1)
session.add(c2)
session.commit() #<== ERROR
except Exception, e:
print "error: " + str(e)
raise
and the error:
Traceback (most recent call last):
File "D:\Documents\Code\Eclipse\workspace\test2\src\metadata.py",
line 119, in
session.commit()
File "C:\python27\lib\site-packages\sqlalchemy\orm\session.py", line
614, in commit
self.transaction.commit()
File "C:\python27\lib\site-packages\sqlalchemy\orm\session.py", line
385, in commit
self._prepare_impl()
File "C:\python27\lib\site-packages\sqlalchemy\orm\session.py", line
369, in _prepare_i
