[sqlalchemy] Re: Efficient dictificationof result sets
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Andreas Jung Sent: 19 December 2008 06:30 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Efficient dictificationof result sets On 19.12.2008 2:57 Uhr, Michael Bayer wrote: On Dec 18, 2008, at 3:04 PM, Andreas Jung wrote: Does SA contain some official API to introspect the list of defined synonyms for a particular class? The goal is to take values defined as a synonym also into account for the dictification (for backward compatiblity reasons for an existing codebase). the mapper's get_property() method includes a resolve_synonyms keyword arg that indicates a given key which points to a synonym should return the actual referenced property, so a recipe that builds upon this would look like: set([mapper.get_property(p.key, resolve_synonyms=True) for p in mapper.iterate_properties]) However this does not apply when using the declarative layer. Any options within such a context? Andreas I haven't been following this discussion closely, so I'm probably wrong, but that statement doesn't sound right to me. As far as I'm aware, the declarative layer is just a convenience for setting up Tables and mapped classes at the same time. The end result is exactly the same as if you created the tables and classes in the traditional way. I would be very surprised if the above expression didn't work. You can get the mapper for a mapped class or object using the class_mapper and object_mapper functions. Hope that helps, Simon --~--~-~--~~~---~--~~ 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: Efficient dictificationof result sets
exactly, the declarative layer is syntax sugar, i.e. shorter way to say same thing, same as elixir and dbcook - they just differ in how many things each one automates/hides. after that, i.e. after mappers compiled, it's all plain SA - sessions, queries etc... i dont know about elixir, although dbcook does have some extra conveniences around query and overall model'metadata, they do not change the overall idea - it's another shorter way to say same thing. ciao svil On Friday 19 December 2008 12:02:07 King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Andreas Jung Sent: 19 December 2008 06:30 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Efficient dictificationof result sets On 19.12.2008 2:57 Uhr, Michael Bayer wrote: On Dec 18, 2008, at 3:04 PM, Andreas Jung wrote: Does SA contain some official API to introspect the list of defined synonyms for a particular class? The goal is to take values defined as a synonym also into account for the dictification (for backward compatiblity reasons for an existing codebase). the mapper's get_property() method includes a resolve_synonyms keyword arg that indicates a given key which points to a synonym should return the actual referenced property, so a recipe that builds upon this would look like: set([mapper.get_property(p.key, resolve_synonyms=True) for p in mapper.iterate_properties]) However this does not apply when using the declarative layer. Any options within such a context? Andreas I haven't been following this discussion closely, so I'm probably wrong, but that statement doesn't sound right to me. As far as I'm aware, the declarative layer is just a convenience for setting up Tables and mapped classes at the same time. The end result is exactly the same as if you created the tables and classes in the traditional way. I would be very surprised if the above expression didn't work. You can get the mapper for a mapped class or object using the class_mapper and object_mapper functions. Hope that helps, Simon --~--~-~--~~~---~--~~ 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: Abstract base class
Hi,
I have a similar task, so I tried to use your proposal, but it didn't
work for me:
===
from sqlalchemy import types as satypes
from sqlalchemy import schema as saschema
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base,
DeclarativeMeta
from sqlalchemy.orm import scoped_session, sessionmaker
class BaseType(DeclarativeMeta):
def __init__(newcls, classname, bases, dict_):
newcls.notes = saschema.Column(satypes.String)
DeclarativeMeta.__init__(newcls, classname, bases, dict_)
MetaData = saschema.MetaData(bind=create_engine('sqlite:///:memory:'))
Session = scoped_session(sessionmaker(bind=MetaData.bind))
Base = declarative_base(metadata=MetaData, mapper=Session.mapper,
metaclass=BaseType)
class MasterEntity(Base):
__tablename__ = master
id = saschema.Column(satypes.Integer, primary_key=True)
status = saschema.Column(satypes.CHAR(length=1), default=A)
print [ _c.key for _c in MasterEntity.__table__.columns ]
# ['id', 'status']
# = the 'notes' column is missing
=== snip
So, what am I doing wrong?
Thanks in advance,
Frank
Btw.: Hello group and many thanks to Michael for this great piece of
software!
On Dec 16, 6:58 pm, Michael Bayer mike...@zzzcomputing.com wrote:
anabstractbaseclassisn't going to set up the same fields on all
descendants since the mapper()/Table() setup occurs during the
creation of the individualclassusing the non-inheritedclassdict,
and unique instances of each of the Column, etc. elements are required
as well.
concrete inheritance, as referenced in that post, was not designed
to be used as a configurational spacesaver and always requires a
mapped selectable for the base, which you don't have here, so it's
not appropriate for this use case.
So for this you'd need a custom metaclass:
classMyMeta(DeclarativeMeta):
def __init__(cls, classname, bases, dict_):
cls.notes = Column(String)
DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base= declarative_base(metaclass=MyMeta)
On Dec 16, 2008, at 12:36 PM, Joril wrote:
Hi everyone!
I need to declare a few unrelated classes (from a business
perspective) that share some attributes/fields, so I thought I could
use anabstractclassto group these common attributes (note that just
a few classes should have them, not every one)
This post
http://groups.google.it/group/sqlalchemy/msg/d3de02f609a0bbd9?hl=it
suggests to use mixins, but I can't get it to work, SQLA generates the
tables without the common fields.
I'm testing it with this script:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
# --- Tables definition
Base= declarative_base()
classAbstract(object):
notes = Column(String)
classConcrete(Base,Abstract):
__tablename__ = 'concrete'
id = Column(Integer, primary_key=True)
# ---
# DB creation
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Here's the sql call:
CREATE TABLE concrete (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
What am I missing? Did I misunderstood how the mixin should be used?
(I'm using SQLA 0.5rc4)
Many thanks!
--~--~-~--~~~---~--~~
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: Abstract base class
oops, do it like this:
class BaseType(DeclarativeMeta):
def __init__(newcls, classname, bases, dict_):
dict_['notes'] = saschema.Column(satypes.String)
DeclarativeMeta.__init__(newcls, classname, bases, dict_)
clearly we'll have to figure out a more user friendly pattern for this
use case.
On Dec 19, 2008, at 5:27 AM, FrankB wrote:
Hi,
I have a similar task, so I tried to use your proposal, but it didn't
work for me:
===
from sqlalchemy import types as satypes
from sqlalchemy import schema as saschema
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base,
DeclarativeMeta
from sqlalchemy.orm import scoped_session, sessionmaker
class BaseType(DeclarativeMeta):
def __init__(newcls, classname, bases, dict_):
newcls.notes = saschema.Column(satypes.String)
DeclarativeMeta.__init__(newcls, classname, bases, dict_)
MetaData = saschema.MetaData(bind=create_engine('sqlite:///:memory:'))
Session = scoped_session(sessionmaker(bind=MetaData.bind))
Base = declarative_base(metadata=MetaData, mapper=Session.mapper,
metaclass=BaseType)
class MasterEntity(Base):
__tablename__ = master
id = saschema.Column(satypes.Integer, primary_key=True)
status = saschema.Column(satypes.CHAR(length=1), default=A)
print [ _c.key for _c in MasterEntity.__table__.columns ]
# ['id', 'status']
# = the 'notes' column is missing
=== snip
So, what am I doing wrong?
Thanks in advance,
Frank
Btw.: Hello group and many thanks to Michael for this great piece of
software!
On Dec 16, 6:58 pm, Michael Bayer mike...@zzzcomputing.com wrote:
anabstractbaseclassisn't going to set up the same fields on all
descendants since the mapper()/Table() setup occurs during the
creation of the individualclassusing the non-inheritedclassdict,
and unique instances of each of the Column, etc. elements are
required
as well.
concrete inheritance, as referenced in that post, was not designed
to be used as a configurational spacesaver and always requires a
mapped selectable for the base, which you don't have here, so it's
not appropriate for this use case.
So for this you'd need a custom metaclass:
classMyMeta(DeclarativeMeta):
def __init__(cls, classname, bases, dict_):
cls.notes = Column(String)
DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base= declarative_base(metaclass=MyMeta)
On Dec 16, 2008, at 12:36 PM, Joril wrote:
Hi everyone!
I need to declare a few unrelated classes (from a business
perspective) that share some attributes/fields, so I thought I could
use anabstractclassto group these common attributes (note that just
a few classes should have them, not every one)
This post
http://groups.google.it/group/sqlalchemy/msg/d3de02f609a0bbd9?hl=it
suggests to use mixins, but I can't get it to work, SQLA generates
the
tables without the common fields.
I'm testing it with this script:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
# --- Tables definition
Base= declarative_base()
classAbstract(object):
notes = Column(String)
classConcrete(Base,Abstract):
__tablename__ = 'concrete'
id = Column(Integer, primary_key=True)
# ---
# DB creation
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Here's the sql call:
CREATE TABLE concrete (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
What am I missing? Did I misunderstood how the mixin should be used?
(I'm using SQLA 0.5rc4)
Many thanks!
--~--~-~--~~~---~--~~
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: Abstract base class
On Friday 19 December 2008 16:58:12 Michael Bayer wrote: oops, do it like this: class BaseType(DeclarativeMeta): def __init__(newcls, classname, bases, dict_): dict_['notes'] = saschema.Column(satypes.String) DeclarativeMeta.__init__(newcls, classname, bases, dict_) clearly we'll have to figure out a more user friendly pattern for this use case. can't u use multiple inheritance, in the case, as mixin/s? i use it in dbcook this way, using the bases[1:] only as declaration-bringers. --~--~-~--~~~---~--~~ 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] order_by and group_by won't work as I want.
Hi. Well, I want to output the HTML code from database. And the HTML code should be order by 'cost' and group_by 'category' The database table is like below. --- ID CategoryNameCost 0 foodbanana $1 1 foodapple $2 2 bookfoo $15 3 bookfoobar $10 4 something qwert $5 5 something poiuy $7 6 anythingasdf$8 --- I want to group them by 'Category' field and order them by 'Cost' field and output the HTML table like below --- table tr td0/td tdfoodtd tdbanana/td td$1/td /tr tr class=children td1/td tdfoodtd tdapple/td td$2/td /tr tr td4/td tdsomethingtd tdqwert/td td$5/td /tr tr class=children td5/td tdsomethingtd tdpoiuy/td td$7/td /tr tr td6/td tdanythingtd tdasdf/td td$8/td /tr tr td3/td tdbooktd tdfoobar/td td$10/td /tr tr class=children td2/td tdbooktd tdfoo/td td$15/td /tr /table --- So I wrote the code like below --- ... query = session.query(orm.Some) query = query.order_by(orm.Some.cost) query = query.group_by(orm.Some.category) ... --- Then I got a similar result as I want but not exact one. The result was like --- table tr td0/td tdfoodtd tdbanana/td td$1/td /tr tr class=children td1/td tdfoodtd tdapple/td td$2/td /tr tr td4/td tdsomethingtd tdqwert/td td$5/td /tr tr class=children td5/td tdsomethingtd tdpoiuy/td td$7/td /tr tr td6/td tdanythingtd tdasdf/td td$8/td /tr !-- The result below is wrong !!! foo is more expensive than foobar so it should be the children of the foobar. -- tr td2/td tdbooktd tdfoo/td td$15/td /tr tr class=children td3/td tdbooktd tdfoobar/td td$10/td /tr /table --- I know that foo.id foobar.id but I really want to order_by 'cost' Any idea? I need help... * sorry about my stupid english. I'm not native. --~--~-~--~~~---~--~~ 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: order_by and group_by won't work as I want.
On Friday 19 December 2008 19:53:03 有末 清華 wrote: Hi. Well, I want to output the HTML code from database. And the HTML code should be order by 'cost' and group_by 'category' The database table is like below. --- ID CategoryNameCost 0 foodbanana $1 1 foodapple $2 2 bookfoo $15 3 bookfoobar $10 4 something qwert $5 5 something poiuy $7 6 anythingasdf$8 --- this is order_by (category,cost), no grouping. grouping will be: food... book... something ... anything... one per group --~--~-~--~~~---~--~~ 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] Decouple association proxy?
Hi,
I am wondering if there is a way to completely decouple the
association proxy declaration from my Python classes, and contain this
to a database class? My goal is not to 'burden' developers looking at
the main part of my code with SQLAlchemy when there is no need to
directly interact with the database. This in order to heighten the
readability/extendability of my code. All the examples I have seen
thus far declare the association proxy directly within the class
definition, which I would like to avoid.
Below I have attached a piece of generalized code to which I would
like to apply this principle (let's hope the indentation survives the
email...). In reality the class declarations would be located within a
different file and imported in the database class to decouple these
sections of the code. In case your wondering: the goal of the code is
a self-referential n-n relation where the association class has an
extra attribute which needs to be accessible from inside the program.
Most code has been borrowed from the broker/stocks example[1].
Any ideas? Or is this simply not possible at this time? This is my
first encounter with SQLAlchemy, and perhaps I have missed the obvious
somehow...
Regards,
Arn Vollebregt
[1]
http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html#building-complex-views
test.py
from sqlalchemy import create_engine, MetaData, Table, Column,
Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, mapper, relation
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection
engine = create_engine('sqlite:///test.db', echo=True)
metadata = MetaData()
Session = sessionmaker(bind=engine)
session = Session()
def createAssociationObject(otherMyObject, myProperty):
return AssociationObject(otherMyObject=otherMyObject,
myProperty=myProperty)
class MyObject(object):
myObjects = association_proxy('myPropertyProxy', 'myProperty',
creator=createAssociationObject)
def __init__(self, name):
self.name = name
def __str__(self):
# potential infinit recursion
#return myObject(name=%s, myObjects=%s) % (self.name,
self.myObjects)
return myObject(name=%s, #myObjects=%i) % (self.name, len
(self.myObjects))
def __repr__(self):
return self.__str__()
class AssociationObject(object):
def __init__(self, myObject=None, otherMyObject=None,
myProperty=0):
self.myObject = myObject
self.otherMyObject = otherMyObject
self.myProperty = myProperty
myObjectsTable = Table(myObjects, metadata,
Column('id', Integer, primary_key=True),
Column('name', String(25), nullable=False),
)
AssociationObjectsTable = Table(associationObjects, metadata,
Column('myObjectID', Integer, ForeignKey('myObjects.id'),
primary_key=True),
Column('otherMyObjectID', Integer, ForeignKey('myObjects.id'),
primary_key=True),
Column('myProperty', Integer),
)
mapper(MyObject, myObjectsTable, properties={
'myPropertyProxy': relation(AssociationObject,
collection_class=attribute_mapped_collection('myProperty'),
primaryjoin=AssociationObjectsTable.c.myObjectID==myObjectsTable.c.id,)
})
mapper(AssociationObject, AssociationObjectsTable, properties={
'myObject': relation(MyObject,
primaryjoin=AssociationObjectsTable.c.myObjectID==myObjectsTable.c.id,),
'otherMyObject': relation(MyObject,
primaryjoin=AssociationObjectsTable.c.otherMyObjectID==myObjectsTable.c.id,)
})
metadata.create_all(engine)
myObject1 = MyObject('testObject1')
myObject2 = MyObject('testObject2')
myObject3 = MyObject('testObject3')
myObject1.myObjects[myObject2] = 1
myObject2.myObjects[myObject1] = 1
myObject3.myObjects[myObject2] = 1
myObject3.myObjects[myObject1] = 2
session.add(myObject1)
session.add(myObject2)
session.add(myObject3)
session.commit()
for myObject in session.query(MyObject).order_by(MyObject.id):
print myObject
for otherMyObject, myProperty in myObject.myObjects.items():
print \t + str(otherMyObject), str(myProperty)
/test.py
--~--~-~--~~~---~--~~
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: Decouple association proxy?
class MyObject(object):
myObjects = association_proxy('myPropertyProxy', 'myProperty',
creator=createAssociationObject)
u mean, this one line there is 'burden' ? put all that in separate
function in another file, and just import and call it...
how about all the other table, mapper, etc stuff?
if u really need less verbosity, use syntax sugars, e.g. declarative,
elixir, dbcook. in general, it may or may not make your life easier.
On Friday 19 December 2008 21:11:07 Arn Vollebregt wrote:
Hi,
I am wondering if there is a way to completely decouple the
association proxy declaration from my Python classes, and contain
this to a database class? My goal is not to 'burden' developers
looking at the main part of my code with SQLAlchemy when there is
no need to directly interact with the database. This in order to
heighten the readability/extendability of my code. All the examples
I have seen thus far declare the association proxy directly within
the class definition, which I would like to avoid.
Below I have attached a piece of generalized code to which I would
like to apply this principle (let's hope the indentation survives
the email...). In reality the class declarations would be located
within a different file and imported in the database class to
decouple these sections of the code. In case your wondering: the
goal of the code is a self-referential n-n relation where the
association class has an extra attribute which needs to be
accessible from inside the program. Most code has been borrowed
from the broker/stocks example[1].
Any ideas? Or is this simply not possible at this time? This is my
first encounter with SQLAlchemy, and perhaps I have missed the
obvious somehow...
Regards,
Arn Vollebregt
[1]
http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.ht
ml#building-complex-views
test.py
from sqlalchemy import create_engine, MetaData, Table, Column,
Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, mapper, relation
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection
engine = create_engine('sqlite:///test.db', echo=True)
metadata = MetaData()
Session = sessionmaker(bind=engine)
session = Session()
def createAssociationObject(otherMyObject, myProperty):
return AssociationObject(otherMyObject=otherMyObject,
myProperty=myProperty)
class MyObject(object):
myObjects = association_proxy('myPropertyProxy', 'myProperty',
creator=createAssociationObject)
def __init__(self, name):
self.name = name
def __str__(self):
# potential infinit recursion
#return myObject(name=%s, myObjects=%s) % (self.name,
self.myObjects)
return myObject(name=%s, #myObjects=%i) % (self.name,
len (self.myObjects))
def __repr__(self):
return self.__str__()
class AssociationObject(object):
def __init__(self, myObject=None, otherMyObject=None,
myProperty=0):
self.myObject = myObject
self.otherMyObject = otherMyObject
self.myProperty = myProperty
myObjectsTable = Table(myObjects, metadata,
Column('id', Integer, primary_key=True),
Column('name', String(25), nullable=False),
)
AssociationObjectsTable = Table(associationObjects, metadata,
Column('myObjectID', Integer, ForeignKey('myObjects.id'),
primary_key=True),
Column('otherMyObjectID', Integer, ForeignKey('myObjects.id'),
primary_key=True),
Column('myProperty', Integer),
)
mapper(MyObject, myObjectsTable, properties={
'myPropertyProxy': relation(AssociationObject,
collection_class=attribute_mapped_collection('myProperty'),
primaryjoin=AssociationObjectsTable.c.myObjectID==myObjectsTable.c.
id,) })
mapper(AssociationObject, AssociationObjectsTable, properties={
'myObject': relation(MyObject,
primaryjoin=AssociationObjectsTable.c.myObjectID==myObjectsTable.c.
id,), 'otherMyObject': relation(MyObject,
primaryjoin=AssociationObjectsTable.c.otherMyObjectID==myObjectsTab
le.c.id,) })
metadata.create_all(engine)
myObject1 = MyObject('testObject1')
myObject2 = MyObject('testObject2')
myObject3 = MyObject('testObject3')
myObject1.myObjects[myObject2] = 1
myObject2.myObjects[myObject1] = 1
myObject3.myObjects[myObject2] = 1
myObject3.myObjects[myObject1] = 2
session.add(myObject1)
session.add(myObject2)
session.add(myObject3)
session.commit()
for myObject in session.query(MyObject).order_by(MyObject.id):
print myObject
for otherMyObject, myProperty in myObject.myObjects.items():
print \t + str(otherMyObject), str(myProperty)
/test.py
--~--~-~--~~~---~--~~
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
[sqlalchemy] creating and dropping foreign key constraints using ORM
Hi,
I'm writing code (see below) to drop and add back foreign key constraints
to a db table. Incidentally, this code is not working (the function just
hangs) so I may have made some kind of syntax error. Anyway, I was
wondering if there was some way to accomplish this in a more high-level
way using the sqla ORM. The tables in question were created using the ORM,
so the ORM knows about them, and, at least in theory should be able to
manipulate them.
Schema follows below.
However, currently, I'm not sure how do this. Suggestions appreciated.
Please CC me on any reply.
Regards, Faheem.
*
conn = db.connect()
conn.execute(ALTER TABLE cell DROP CONSTRAINT
cell_patient_chipid_fkey; ALTER TABLE cell DROP CONSTRAINT
cell_snp_id_fkey; ALTER TABLE cell DROP CONSTRAINT cell_snpval_id_fkey;)
#conn.execute(COPY cell FROM ' + csvfilename + ' USING DELIMITERS
',')
conn.execute(ALTER TABLE ONLY cell ADD CONSTRAINT cell_patient_chipid_fkey
FOREIGN KEY (patient_chipid)
REFERENCES patient(chipid) ON UPDATE CASCADE ON DELETE CASCADE;)
conn.execute(ALTER TABLE ONLY cell ADD CONSTRAINT cell_snp_id_fkey FOREIGN KEY
(snp_id)
REFERENCES snp(fid) ON UPDATE CASCADE ON DELETE CASCADE;)
conn.execute(ALTER TABLE ONLY cell ADD CONSTRAINT cell_snpval_id_fkey
FOREIGN KEY (snpval_id) REFERENCES snpval(val) ON UPDATE CASCADE ON DELETE
CASCADE;)
conn.close()
**
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection
from datetime import datetime
metadata = MetaData()
patient_table = Table(
'patient', metadata,
Column('chipid', String(30), primary_key=True, index=True),
Column('studyid', String(20), nullable=False, index=True),
Column('sex_id', None, ForeignKey('sex.val', onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False),
Column('race_id', None, ForeignKey('race.val', onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False),
Column('phenotype', Boolean),
)
# Allow M (male), F (female), U (unknown).
sex_table = Table(
'sex', metadata,
Column('val', String(1), primary_key=True),
)
race_table = Table(
'race', metadata,
Column('val', String(25), primary_key=True),
)
cell_table = Table(
'cell', metadata,
Column('patient_chipid', None, ForeignKey('patient.chipid',
onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False,
primary_key=True),
Column('snp_id', None, ForeignKey('snp.fid', onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False, primary_key=True),
Column('snpval_id', None, ForeignKey('snpval.val', onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False)
)
snp_table = Table(
'snp', metadata,
Column('fid', String(20), nullable=False, primary_key=True),
Column('rsid', String(20), nullable=False),
Column('chromosome', String(2), nullable=False),
Column('location', Integer, nullable=False),
Column('alleleA_id', None, ForeignKey('allele.val', onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False),
Column('alleleB_id', None, ForeignKey('allele.val', onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False),
)
allele_table = Table(
'allele', metadata,
Column('val', String(1), primary_key=True),
)
snpval_table = Table(
'snpval', metadata,
Column('val', Integer, primary_key=True),
)
def create_cell(snp, snpval):
return Cell(snp=snp, snpval=snpval)
class Patient(object):
def __init__(self, chipid, studyid, sex, race, phenotype):
self.chipid = chipid
self.studyid = studyid
self.sex = sex
self.race = race
self.phenotype = phenotype
def __repr__(self):
return 'Patient %s'%self.chipid
snps = association_proxy('by_fid', 'snpval', creator=create_cell)
class Sex(object):
def __init__(self, val):
self.val = val
def __repr__(self):
return 'Sex %s'%self.val
class Race(object):
def __init__(self, val):
self.val = val
def __repr__(self):
return 'Race %s'%self.val
class Cell(object):
def __init__(self, patient=None, snp=None, snpval=None):
self.patient = patient
self.snp = snp
self.snpval = snpval
def __repr__(self):
return 'Cell %s'%self.snpval
class Snp(object):
def __init__(self, fid, rsid, chromosome, location, alleleA, alleleB):
self.fid = fid
self.rsid = rsid
self.chromosome = chromosome
self.location = location
self.alleleA = alleleA
self.alleleB = alleleB
def __repr__(self):
[sqlalchemy] Re: Decouple association proxy?
On Dec 19, 2008, at 2:11 PM, Arn Vollebregt wrote:
Hi,
I am wondering if there is a way to completely decouple the
association proxy declaration from my Python classes, and contain this
to a database class? My goal is not to 'burden' developers looking at
the main part of my code with SQLAlchemy when there is no need to
directly interact with the database. This in order to heighten the
readability/extendability of my code. All the examples I have seen
thus far declare the association proxy directly within the class
definition, which I would like to avoid.
easy enough:
mapper(MyClass, mytable, properties={...})
MyClass.some_association = assocation_proxy(*args)
--~--~-~--~~~---~--~~
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: Decouple association proxy?
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
On Dec 19, 2008, at 2:11 PM, Arn Vollebregt wrote:
I am wondering if there is a way to completely decouple the
association proxy declaration from my Python classes, and contain
this to a database class?
easy enough:
mapper(MyClass, mytable, properties={...})
MyClass.some_association = assocation_proxy(*args)
Hmm, yes, of course :)
I'll have to find the fine line between hiding the SQLAlchemy magic, but not
hiding too much of the magic, but I can think I can work with this
suggestion. Thx!
Regards,
Arn Vollebregt
--~--~-~--~~~---~--~~
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: Vague InterfaceError (threading issue?) running normal query
I've created a full test case that should reproduce the error for you. You'll need to create a database called 'test' on your local machine. I think I've isolated the problem to the use of the creator keyword argument, which I use in my application for various reasons. http://rafb.net/p/8Ayjxc63.html Results in: http://rafb.net/p/QPoesQ74.html Here are the versions I used to cause this bug: mysqld Ver 5.0.67-0ubuntu6 for debian-linux-gnu on i486 ((Ubuntu)) MySQLdb 1.2.2 For sqlalchemy, I've tried 0.5rc4 and 0.4.8; it happens with either version. On Dec 18, 10:11 am, Michael Bayer mike...@zzzcomputing.com wrote: Would need to see how your create_engine() is configured, what versions of MySQL/MySQLdb are in use. --~--~-~--~~~---~--~~ 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: Vague InterfaceError (threading issue?) running normal query
def setup(**kwargs):
connection = MySQLdb.connections.Connection(**kwargs)
engine = create_engine('mysql://', creator=lambda: connection,
pool_recycle=2)
the creator argument is a callable that returns a new connection
when the pool needs one. Above, you are pre-connecting a single MySQL
connection and returning it from the lambda. Besides producing non-
mutexed multithreaded access to the single Connection itself (which
may or may not be OK for MySQLdb), it also prevents a new connection
from being created once it has been recycled. The previous
connection, now closed, is all that's available.
The correct form is:
def setup(**kwargs):
def connect():
return MySQLdb.connections.Connection(**kwargs)
engine = create_engine('mysql://', creator=lambda: connect,
pool_recycle=2)
On Dec 19, 2008, at 6:05 PM, Ken wrote:
I've created a full test case that should reproduce the error for you.
You'll need to create a database called 'test' on your local machine.
I think I've isolated the problem to the use of the creator keyword
argument, which I use in my application for various reasons.
http://rafb.net/p/8Ayjxc63.html
Results in:
http://rafb.net/p/QPoesQ74.html
Here are the versions I used to cause this bug:
mysqld Ver 5.0.67-0ubuntu6 for debian-linux-gnu on i486 ((Ubuntu))
MySQLdb 1.2.2
For sqlalchemy, I've tried 0.5rc4 and 0.4.8; it happens with either
version.
On Dec 18, 10:11 am, Michael Bayer mike...@zzzcomputing.com wrote:
Would need to see how your create_engine() is configured, what
versions of MySQL/MySQLdb are in use.
--~--~-~--~~~---~--~~
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: Vague InterfaceError (threading issue?) running normal query
Oh, I see. I was unaware that lambda evaluated the result only once,
not each use. Thanks so much for your help.
On Dec 19, 4:36 pm, Michael Bayer mike...@zzzcomputing.com wrote:
def setup(**kwargs):
connection = MySQLdb.connections.Connection(**kwargs)
engine = create_engine('mysql://', creator=lambda: connection,
pool_recycle=2)
the creator argument is a callable that returns a new connection
when the pool needs one. Above, you are pre-connecting a single MySQL
connection and returning it from the lambda. Besides producing non-
mutexed multithreaded access to the single Connection itself (which
may or may not be OK for MySQLdb), it also prevents a new connection
from being created once it has been recycled. The previous
connection, now closed, is all that's available.
The correct form is:
def setup(**kwargs):
def connect():
return MySQLdb.connections.Connection(**kwargs)
engine = create_engine('mysql://', creator=lambda: connect,
pool_recycle=2)
--~--~-~--~~~---~--~~
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: Vague InterfaceError (threading issue?) running normal query
oh, whoops, shouldnt be a lambda there:
def setup(**kwargs):
def connect():
return MySQLdb.connections.Connection(**kwargs)
engine = create_engine('mysql://', creator=connect,
pool_recycle=2)
On Dec 19, 2008, at 6:56 PM, Ken wrote:
Oh, I see. I was unaware that lambda evaluated the result only once,
not each use. Thanks so much for your help.
On Dec 19, 4:36 pm, Michael Bayer mike...@zzzcomputing.com wrote:
def setup(**kwargs):
connection = MySQLdb.connections.Connection(**kwargs)
engine = create_engine('mysql://', creator=lambda: connection,
pool_recycle=2)
the creator argument is a callable that returns a new connection
when the pool needs one. Above, you are pre-connecting a single
MySQL
connection and returning it from the lambda. Besides producing non-
mutexed multithreaded access to the single Connection itself (which
may or may not be OK for MySQLdb), it also prevents a new connection
from being created once it has been recycled. The previous
connection, now closed, is all that's available.
The correct form is:
def setup(**kwargs):
def connect():
return MySQLdb.connections.Connection(**kwargs)
engine = create_engine('mysql://', creator=lambda: connect,
pool_recycle=2)
--~--~-~--~~~---~--~~
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: creating and dropping foreign key constraints using ORM
On Fri, 19 Dec 2008 15:10:07 -0500, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 19, 2008, at 2:43 PM, Faheem Mitha wrote: I'm writing code (see below) to drop and add back foreign key constraints to a db table. Incidentally, this code is not working (the function just hangs) so I may have made some kind of syntax error. Anyway, I was wondering if there was some way to accomplish this in a more high- level way using the sqla ORM. The tables in question were created using the ORM, so the ORM knows about them, and, at least in theory should be able to manipulate them. The ORM operates at a higher level than that of the underlying details of the database and has no awareness of schema generation. You're probably referring to the SQL and schema expression language which is a separate component of the library. For comprehensive support of ALTER constructs, see the Migrate project at http://code.google.com/p/sqlalchemy-migrate/ . Hi Michael, Thanks very much for the suggestion. The question is where the migrate project supports on the fly schema modification as I describe. I'll take a look. Regards, Faheem. --~--~-~--~~~---~--~~ 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] Fwd: declarative_base and UNIQUE Constraint
Hi,
just for anyone arriving here to save some time: I tried this with
0.5rc4 and the following piece of code
===
from sqlalchemy import types as satypes
from sqlalchemy import schema as saschema
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class MasterEntity(Base):
__tablename__ = master
id = saschema.Column(satypes.Integer, primary_key=True)
key= saschema.Column(satypes.Unicode(16))
entitytype = saschema.Column(satypes.String(32))
__mapper_args__ = {'polymorphic_on': entitytype,
'polymorphic_identity': 'master'}
__table_args__ = ((saschema.UniqueConstraint(entitytype, key),),
{})
===
and received the error
AttributeError: 'tuple' object has no attribute '_set_parent'.
Changing the last line to
__table_args__ = (saschema.UniqueConstraint(entitytype, key), {})
(means: removing the tuple) yields
KeyError: Column('entitytype', ...)
but this (means: put column names into quotes) eventually works:
__table_args__ = ( saschema.UniqueConstraint(entitytype,
key), {} )
Regards, Frank
-- Forwarded message --
From: Michael Bayer mike...@zzzcomputing.com
Date: Sep 15, 12:51 am
Subject: declarative_base and UNIQUE Constraint
To: sqlalchemy
format is __table_args__ = ((UniqueConstraint(),), {})
On Sep 14, 2008, at 1:49 PM, GustaV wrote:
How do I create a unique constraint with the declarative plugin
(latest version 0.5) ?
both:
__table_args__ = (UniqueConstraint('region.x', 'region.y'),
{'mysql_engine':'InnoDB'} )
__table_args__ = (UniqueConstraint(x, y), {'mysql_engine':'InnoDB'} )
don't work.
Thanks!
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
