[sqlalchemy] Simple one-to-one translation with hybrid_attribute
Hello everyone,
Trying to use hybrid_attribute to provide friendly names for integers
representing object states. Storage and retrieval works fine, but I
can't get filtering working. I want the translation to happen on the
Python side prior to filling in the query parameters, but
hybrid_attribute is thinking the DB should do it. Example at the
bottom.
I don't really understand how to write the @state.expression the way I
want things to happen.
Thanks,
Ross
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
from sqlalchemy.ext.declarative import declarative_base
engine = sa.create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
Session = orm.scoped_session(orm.sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
statei2a = {
0 : captured,
1 : registered,
2 : prepared,
}
statea2i = dict((v, k) for k, v in statei2a.iteritems())
class Device(Base):
__tablename__ = device
id = sa.Column(sa.Integer, primary_key=True)
statenum = sa.Column(sa.Integer, nullable=False, default=0)
def __init__(self, state):
self.state = state
@hybrid_property
def state(self):
return statei2a[self.statenum]
@state.setter
def state(self, state):
self.statenum = statea2i[state]
@state.expression
def state(self):
return statea2i[self.statenum]
Base.metadata.create_all(engine)
d1 = Device(captured)
d2 = Device(registered)
d3 = Device(prepared)
Session.add_all([d1, d2, d3])
Session.commit()
q = Session.query(Device)
q.filter_by(state=captured)
q.filter(Device.state 0)
signature.asc
Description: This is a digitally signed message part
Re: [sqlalchemy] Simple one-to-one translation with hybrid_attribute
On Mon, 2011-08-01 at 17:22 -0400, Michael Bayer wrote: You're looking to convert from int-string using a mapping in a SQL expression, so I think you'd need to write @state.expression as a CASE statement. from sqlalchemy import case @state.expression def state(self): return case(self.statenum, statei2a) Exactly what I needed, works like a charm - thank you so much! For the archives - the args are flipped above. Should be case(statei2a, self.statenum). Thanks, Ross signature.asc Description: This is a digitally signed message part
Re: [sqlalchemy] Re: Self-referential, one-to-one, reflexive mappings
On Tue, Aug 17, 2010 at 07:32:41PM -0700, Enrico wrote:
Micahael gave me this advice:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/df6e451855d13a60/386232232434ff92?lnk=gstq=enrico#386232232434ff92
in which there are two backrefs and I think it's declarative whereas
yours is classical.
Hmmm, that's more of a general one-to-many relation with an
association object - it doesn't enfore one-to-one-ness or reflexivity.
There is something I don't understand in there though - the node and
adj_node properties mutually overwrite one-another with backrefs. How
does that work?
Here's what I want to be able to do:
n1 = Node(Node1)
n2 = Node(Node2)
n1.peer = n2
print n2.peer
Node(Node1)
print n1.peer
Node(Node2)
q = session.query(Node)
n1 = q.filter_by(peer=n2).one()
Usual backref patterns don't work because the backref would need to be
named the same thing as the forward reference. Hence why I went with
an association object.
But since the association object has a relation to both nodes, there
not an easy way to know that n1's peer is the OTHER entry in
n1.node. Hence why I added the property an synonym.
Ross
On Aug 18, 4:59 am, Ross Vandegrift r...@kallisti.us wrote:
Hi everyone,
Does anyone have a good setup for one-to-one relationships that are
always symmetric and provide a common property to access the paired
object? Ie, it's always the case that:
1) (x,y) is in the relation iff (y,x) is in the relation.
2) x.peer = y
3) y.peer = x
Here's the best thing I've come up with so far. It's not perfect -
for instance, there's no way to query by partner.
class Person(object):
def _get_partner(self):
if self.marriage:
return self.marriage.get_partner_of(self)
else:
return None
partner = property(_get_partner)
class Marriage(object):
def __init__(self, a, b):
self.partners = [a, b]
def get_partner_of(self, a):
x = list(self.partners)
x.remove(a)
return x[0]
person_t = Table('person', metadata,
Column('id', Integer, primary_key=True),
Column('marriageid', Integer, ForeignKey('marriage.id')))
marriage_t = Table('marriage', metadata,
Column('id', Integer, primary_key=True))
person_m = orm.mapper(Person, person_t)
marriage_m = orm.mapper(Marriage, marriage_t,
properties={'partners': orm.relation(Person,
backref=marriage)})
Ross
--
Ross Vandegrift
r...@kallisti.us
If the fight gets hot, the songs get hotter. If the going gets tough,
the songs get tougher.
--Woody Guthrie
signature.asc
1KViewDownload
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
Ross Vandegrift
r...@kallisti.us
If the fight gets hot, the songs get hotter. If the going gets tough,
the songs get tougher.
--Woody Guthrie
signature.asc
Description: Digital signature
[sqlalchemy] Self-referential, one-to-one, reflexive mappings
Hi everyone,
Does anyone have a good setup for one-to-one relationships that are
always symmetric and provide a common property to access the paired
object? Ie, it's always the case that:
1) (x,y) is in the relation iff (y,x) is in the relation.
2) x.peer = y
3) y.peer = x
Here's the best thing I've come up with so far. It's not perfect -
for instance, there's no way to query by partner.
class Person(object):
def _get_partner(self):
if self.marriage:
return self.marriage.get_partner_of(self)
else:
return None
partner = property(_get_partner)
class Marriage(object):
def __init__(self, a, b):
self.partners = [a, b]
def get_partner_of(self, a):
x = list(self.partners)
x.remove(a)
return x[0]
person_t = Table('person', metadata,
Column('id', Integer, primary_key=True),
Column('marriageid', Integer, ForeignKey('marriage.id')))
marriage_t = Table('marriage', metadata,
Column('id', Integer, primary_key=True))
person_m = orm.mapper(Person, person_t)
marriage_m = orm.mapper(Marriage, marriage_t,
properties={'partners': orm.relation(Person,
backref=marriage)})
Ross
--
Ross Vandegrift
r...@kallisti.us
If the fight gets hot, the songs get hotter. If the going gets tough,
the songs get tougher.
--Woody Guthrie
signature.asc
Description: Digital signature
[sqlalchemy] polymorphic_on a join leads to incorrect collection
= orm.mapper(SwitchPort, inherits=interface_m,
polymorphic_identity=1)
ciscoswitchport_m = orm.mapper(CiscoSwitchPort, inherits=switchport_m,
polymorphic_identity=2)
juniperswitchport_m = orm.mapper(JuniperSwitchPort,
inherits=switchport_m,
polymorphic_identity=3)
routerport_m = orm.mapper(RouterPort, inherits=interface_m,
polymorphic_identity=4)
ciscorouterport_m = orm.mapper(CiscoRouterPort, inherits=routerport_m,
polymorphic_identity=5)
juniperrouterport_m = orm.mapper(JuniperRouterPort,
inherits=routerport_m,
polymorphic_identity=6)
binding_m = orm.mapper(Binding, binding_t,
properties={'routerports':
orm.relation(RouterPort),
'switchports':
orm.relation(SwitchPort)})
Session = orm.sessionmaker(bind=engine)
session = Session()
b1 = Binding()
b2 = Binding()
r1 = CiscoRouter()
rp1 = CiscoRouterPort()
rp1.device = r1
rp1.binding = b1
r2 = JuniperRouter()
rp2 = JuniperRouterPort()
rp2.device = r2
rp2.binding = b2
s1 = CiscoSwitch()
sp1 = CiscoSwitchPort()
sp1.device = s1
sp1.binding = b1
s2 = JuniperSwitch()
sp2 = JuniperSwitchPort()
sp2.device = s2
sp2.binding = b2
session.add_all([r1, rp1, r2, rp2, s1, sp1, s2, sp2, b1, b2])
session.flush()
session.commit()
engine.echo = True
print [type(x) for x in b1.routerports]
print [type(x) for x in b1.switchports]
print [type(x) for x in b2.routerports]
print [type(x) for x in b2.switchports]
--
Ross Vandegrift
r...@kallisti.us
If the fight gets hot, the songs get hotter. If the going gets tough,
the songs get tougher.
--Woody Guthrie
signature.asc
Description: Digital signature
[sqlalchemy] ALLCAPS datatypes
Hi everyone, I've been going through my app to prepare for an upgrade to SA 0.6 and was wondering what the deal is with the ALLCAPS vs. Capitalized data types. It looks like the ALLCAPS are designed to have the same name as the dialect specific types, while the Capitalized versions are designed to be generic. Am I right in that? For example, I'm on MySQL with enumeration columns. I used to use MSEnum with strict=True. It looks like I want to move to ENUM to continue using the strict option. But will an Enum automatically support the ENUM features if the database supports it? I guess I wonder this - which am I supposed to use? Ross -- Ross Vandegrift r...@kallisti.us If the fight gets hot, the songs get hotter. If the going gets tough, the songs get tougher. --Woody Guthrie signature.asc Description: Digital signature
Re: [sqlalchemy] Mixed inheritance mapping and multiple binds
On Mon, Jan 18, 2010 at 07:24:45PM -0500, Michael Bayer wrote:
The argument against the tables being broken out in get_bind() is
partially one of implementation performance and partially one of
ambiguity, i.e. join of a to b, which table wins ? Neither argument
is completely compelling, though a decision on changing this
behavior is not likely to happen hastily. Also, we don't support
persisting one half of the object in one database and the other half
in another. Which again is something that is certainly possible
architecturally, just would require a lot of thought such that it
doesn't get in the way performancewise of the vast majority of cases
that don't want to do that.
Wow, sounds like I stumbled on a bit of a deep question here. Sounds
like a kinda cool idea, pushing parts of objects across databases.
Fortunately, I have no need for that functionality.
On Jan 18, 2010, at 6:27 PM, Ross Vandegrift wrote:
engine = create_engine('sqlite:///:memory:', echo=True)
metadata.create_all(engine)
binds = { device_table : engine,
switch_table : engine }
Currently its looking at the mapped table of each object's class,
which in this case is a join object, not either of the Table objects
by themselves. That might not be ideal, but in any case its better
if you bind classes and not tables to each engine. i.e.:
binds = {
DeviceBase:engine,
Switch:engine
}
Wow, talk about a simple fix! Works perfectly when I concoct things
like that. Michael, as usual, you are amazing.
Ross
--
Ross Vandegrift
r...@kallisti.us
If the fight gets hot, the songs get hotter. If the going gets tough,
the songs get tougher.
--Woody Guthrie
signature.asc
Description: Digital signature
[sqlalchemy] Mixed inheritance mapping and multiple binds
Hello,
I've run into a case where my code works with a single engine binding
but breaks if bound to multiple engines. I'm doing some things that
might be considered weird - mixing joined and single table inheritance
to reduce data storage, inheriting mappers from other derived mappers.
The exception I get looks like my Session hasn't been bound:
sqlalchemy.exc.UnboundExecutionError: Could not locate a bind configured on
mapper Mapper|CiscoSwitch|switch or this Session
I hacked up a quick stand-alone script. If I bind to a single engine
with orm.sessionmaker(bind=engine), everything works fine. But if I
bind to multiple engines via something like
orm.scoped_session(orm.sessionmaker(binds=binds)), commits fail.
Sorry for the long code sample below - takes a bit to get my example
going. Swapping the sessionmaker as described above fixes the below
code.
Shouldn't this work on SA 0.5.8?
-
from sqlalchemy import *
from sqlalchemy import orm
metadata = MetaData()
class DeviceBase(object):
pass
class Switch(DeviceBase):
pass
class CiscoSwitch(Switch):
pass
class JuniperSwitch(Switch):
pass
class Router(DeviceBase):
pass
class CiscoRouter(Router):
pass
class JuniperRouter(Router):
pass
device_table = Table('device', metadata,
Column('id', Integer, primary_key=True),
Column('model', Integer, nullable=False))
switch_table = Table('switch', metadata,
Column('id', Integer, ForeignKey('device.id'),
primary_key=True),
Column('ussid1', Integer, ForeignKey('switch.id')),
Column('ussid2', Integer, ForeignKey('switch.id')))
router_table = Table('router', metadata,
Column('id', Integer, ForeignKey('device.id'),
primary_key=True))
device_mapper = orm.mapper(DeviceBase, device_table,
polymorphic_on=device_table.c.model,
polymorphic_identity=0)
switch_mapper = orm.mapper(Switch, switch_table, inherits=device_mapper,
polymorphic_identity=1,
properties={'upstream1': orm.relation(Switch,
remote_side=[switch_table.c.id],
primaryjoin=switch_table.c.ussid1==switch_table.c.id),
'upstream2': orm.relation(Switch,
remote_side=[switch_table.c.id],
primaryjoin=switch_table.c.ussid2==switch_table.c.id)})
cisco_mapper = orm.mapper(CiscoSwitch, inherits=switch_mapper,
polymorphic_identity=2)
juniper_mapper = orm.mapper(JuniperSwitch, inherits=switch_mapper,
polymorphic_identity=3)
router_mapper = orm.mapper(Router, inherits=device_mapper,
polymorphic_identity=4)
ciscorouter_mapper = orm.mapper(CiscoRouter, inherits=router_mapper,
polymorphic_identity=5)
juniperrouter_mapper = orm.mapper(JuniperRouter, inherits=router_mapper,
polymorphic_identity=6)
engine = create_engine('sqlite:///:memory:', echo=True)
metadata.create_all(engine)
binds = { device_table : engine,
switch_table : engine }
Session = orm.scoped_session(orm.sessionmaker(binds=binds))
session = Session()
r1 = CiscoRouter()
r2 = JuniperRouter()
parent1 = CiscoSwitch()
parent2 = CiscoSwitch()
child1 = JuniperSwitch()
child1.upstream1 = parent1
child1.upstream2 = parent2
child2 = JuniperSwitch()
child2.upstream1 = parent1
child2.upstream2 = parent2
session.add_all([r1, r2])
session.add_all([parent1, parent2, child1, child2])
session.commit()
--
Ross Vandegrift
r...@kallisti.us
If the fight gets hot, the songs get hotter. If the going gets tough,
the songs get tougher.
--Woody Guthrie
signature.asc
Description: Digital signature
[sqlalchemy] Re: VIEW alternative in SQLAlchemy
On Fri, Aug 07, 2009 at 09:39:33AM -0700, allen.fowler wrote: As an aside, I wonder if it is possible to just subclass my Records object so that the CurrentRecords class adds/enforces certain filter_by parameters for any query against it. Yes - SA's ORM can map to arbitrary Selectable object. Selectable is the base class for things like select() in SA. This feature is awesome! Rather than specifying your Table object to orm.mapper, specify a Selectable that returns rows that are what you want to see. -- Ross Vandegrift r...@kallisti.us If the fight gets hot, the songs get hotter. If the going gets tough, the songs get tougher. --Woody Guthrie --~--~-~--~~~---~--~~ 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: ORM lifecycle questions
On Thu, Dec 11, 2008 at 02:59:39PM -0500, Michael Bayer wrote: On Dec 11, 2008, at 2:04 PM, Ross Vandegrift wrote: So if I do something like this: p = meta.session.query(model.Pool) pool = p.get(7) meta.session.refresh(pool) InvalidRequestError: Instance 'p...@0xa6aca8c' is not persistent within this Session um thats kind of weird. that sort of looks like meta.session is being closed out at some point (i.e. not illustrated in the example) and being recreated later on is that possible ? I think I worked around this issue and I believe it really stemmed from a misunderstanding of how shared session data is. Suppose that I am running paster serve --reload dev.ini and paster shell dev.ini against the same db. Potentially, using the web-version of the app could cause objects to change state in the shell version of the app? Ross -- Ross Vandegrift r...@kallisti.us If the fight gets hot, the songs get hotter. If the going gets tough, the songs get tougher. --Woody Guthrie --~--~-~--~~~---~--~~ 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] [PATCH] add MSMediumInteger
Hey Mike,
I have a DB that uses MySQL's MEDIUMINT type in a bunch of places.
Because many of those are used as foreign keys, I need SQLAlchemy to
be happy emitting tables with MEDIUMINT types. The below patch does
that. This is against an 0.4 checkout from a few mins ago.
Index: lib/sqlalchemy/databases/mysql.py
===
--- lib/sqlalchemy/databases/mysql.py (revision 5048)
+++ lib/sqlalchemy/databases/mysql.py (working copy)
@@ -172,7 +172,7 @@
'MSMediumBlob', 'MSMediumText', 'MSNChar', 'MSNVarChar',
'MSNumeric', 'MSSet', 'MSSmallInteger', 'MSString', 'MSText',
'MSTime', 'MSTimeStamp', 'MSTinyBlob', 'MSTinyInteger',
-'MSTinyText', 'MSVarBinary', 'MSYear' )
+'MSTinyText', 'MSVarBinary', 'MSYear', 'MSMediumInteger' )
RESERVED_WORDS = util.Set(
@@ -549,6 +549,33 @@
return self._extend(BIGINT)
+class MSMediumInteger(MSInteger):
+MySQL MEDIUMINTEGER type.
+
+def __init__(self, length=None, **kw):
+Construct a MEDIUMINTEGER
+
+length
+ Optional, maximum display width for this number.
+
+unsigned
+ Optional.
+
+zerofill
+ Optional. If true, values will be stored as strings left-padded with
+ zeros. Note that this does not effect the values returned by the
+ underlying database API, which continue to be numeric.
+
+
+super(MSMediumInteger, self).__init__(length, **kw)
+
+def get_col_spec(self):
+if self.length is not None:
+return self._extend(MEDIUMINT(%(length)s) % {'length':
self.length})
+else:
+return self._extend(MEDIUMINT)
+
+
class MSTinyInteger(MSInteger):
MySQL TINYINT type.
--
Ross Vandegrift
[EMAIL PROTECTED]
The good Christian should beware of mathematicians, and all those who
make empty prophecies. The danger already exists that the mathematicians
have made a covenant with the devil to darken the spirit and to confine
man in the bonds of Hell.
--St. Augustine, De Genesi ad Litteram, Book II, xviii, 37
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] sqlalchemy 0.4 and multiple database binds
Hello everyone,
According to the Pylons wiki docs on multiple engines with SQLALchemy
0.4, I should be able to do:
def init_model(default_engine, alternate_engine):
binds = { 'tableA': default_engine,
'tableB': alternate_engine }
meta.session = orm.scoped_session(orm.sessionmaker(binds=binds))
This doesn't work for me on Pylons 0.9.6.2 and SQLAlchemy 0.4.7. Is
the example out of date?
I've reviewed the code in sqlalchemy.orm.sessionmaker and I don't see
anything that does something with a kwarg binds, only bind - which
leads me to believe that the documentation gives the incorrect
approach.
--
Ross Vandegrift
[EMAIL PROTECTED]
The good Christian should beware of mathematicians, and all those who
make empty prophecies. The danger already exists that the mathematicians
have made a covenant with the devil to darken the spirit and to confine
man in the bonds of Hell.
--St. Augustine, De Genesi ad Litteram, Book II, xviii, 37
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy 0.4 and multiple database binds
On Mon, Aug 18, 2008 at 10:14:44AM -0400, Michael Bayer wrote:
On Aug 18, 2008, at 10:07 AM, Ross Vandegrift wrote:
Hello everyone,
According to the Pylons wiki docs on multiple engines with SQLALchemy
0.4, I should be able to do:
def init_model(default_engine, alternate_engine):
binds = { 'tableA': default_engine,
'tableB': alternate_engine }
meta.session = orm.scoped_session(orm.sessionmaker(binds=binds))
This doesn't work for me on Pylons 0.9.6.2 and SQLAlchemy 0.4.7. Is
the example out of date?
I've reviewed the code in sqlalchemy.orm.sessionmaker and I don't see
anything that does something with a kwarg binds, only bind - which
leads me to believe that the documentation gives the incorrect
approach.
binds is passed through via **kwargs to the Session constructor.
The keys inside of binds are expected to be Table, mapper(), or
class objects.
Documentation at:
http://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual_partitioning_vertical
Aha, that makes all the difference in the world - thanks again for
your quick and clear help Mike!
I'll comment the Pylons wiki to clarify that section.
Ross
--
Ross Vandegrift
[EMAIL PROTECTED]
The good Christian should beware of mathematicians, and all those who
make empty prophecies. The danger already exists that the mathematicians
have made a covenant with the devil to darken the spirit and to confine
man in the bonds of Hell.
--St. Augustine, De Genesi ad Litteram, Book II, xviii, 37
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: web application too slow
On Sun, Apr 06, 2008 at 05:26:31PM -0700, tux21b wrote: It's an Apache 2 with mod_wsgi. The server setup isn't configured for performance yet, and we can probably tune it a bit. But even when I test with the wsgi-ref server (without parallel requests) the application is slow. Beside sqlalchemy and memcached, we are using django (there are still some dependencies left, since it started as a django-app, but we are working on that *g*), werkzeug (a wsgi toolkit) and jinja (template engine). Are you testing on X11 with Mozilla, and if so, do you have transparent backgrounds? There's a pretty major performance problem with Mozilla and X11 where render operations on pages with transparent divs are excruciatingly slow on many video cards. The problem is compounded by multiple layers of transparency. In other words - make sure the slowness isn't the client :) -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
