Re: [sqlalchemy] Declarative syntax with column_property (v0.6.3)
Hi Michael,
I'm guilty of two over-simplifications in the code used in my example...
Firstly, the substring property/column (with which I had a problem) applies
to a subclass (using joined table inheritance) and the variable to which
it applies is specified in its parent class. Hence, I started by assuming
that I'd need something along the lines of:
class Parent(Base):
my_string
class Child(Parent):
@classproperty/@property/ some other decorator
def my_substr(cls):
return column_property ( cls.my_string )
However, trying this (ie the classproperty/column_property within Child),
returned sqlalchemy.orm.properties.ColumnProperty object ... (the error
I referred to in my original question)
I'm confused about why I'm not able to get this (or some variation of it) to
work. Is this indeed possible?
Looking at the documentation (as a consequence of the error), my I was drawn
to the syntax used in the section on 'mixins' (ie, the column_property
defined externally).
My second over-simplification regards the the function itself. The function
I'm trying to use [which I'm aware doesn't translate easily to sqlite], is:
func.substr(cls.my_str, func.instr(cls.my_str, )+1, 2)
Now, thanks to your response, I now actually have the following *working*
(using mysql):
class MySubStr(object):
@classproperty
def my_substr(cls):
return column_property(
func.substr(cls.my_string, func.instr(cls.my_string,
)+1, 2)
)
However, replacing:
func.substr(cls.my_string, func.instr(cls.my_string, )+1, 2)
with (in order to attach a label to the column):
select([func.substr(cls.my_string, func.instr(cls.my_string, )+1,
2)]).label('exch_code')
Gives me -- AttributeError: 'NoneType' object has no attribute '_keymap'
(full traceback below).
Apologies for my earlier over-simplification (and my late response!). And I
hope that what I've responded with is clear!!
Regards,
Rob
-
/home/robertsudwarts/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.pyc
in one(self)
1520
1521
- 1522 ret = list(self)
1523
1524 l = len(ret)
/home/robertsudwarts/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.pyc
in instances(self, cursor, _Query__context)
1667 break
1668 else:
- 1669 fetch = cursor.fetchall()
1670
1671 if custom_rows:
/home/robertsudwarts/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.pyc
in fetchall(self)
2381
2382 try:
- 2383 l = self.process_rows(self._fetchall_impl())
2384 self.close()
2385 return l
/home/robertsudwarts/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.pyc
in process_rows(self, rows)
2364 process_row = self._process_row
2365 metadata = self._metadata
- 2366 keymap = metadata._keymap
2367 processors = metadata._processors
2368 if self._echo:
AttributeError: 'NoneType' object has no attribute '_keymap'
On 2 August 2010 19:42, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 2, 2010, at 1:40 PM, Michael Bayer wrote:
On Aug 2, 2010, at 12:19 PM, Robert Sudwarts wrote:
Hi,
I'm having trouble understanding the correct syntax to be used with
Declarative and a column property.
The select statement I'm using is:
select([func.substr(my_table.c.my_string, 2, 3)]).label(my_substr'),
deferred=True
And (as per the docs using the expanded syntax, this works as expected.
With the Declarative syntax, I've tried:
from sqlalchemy.util import classproperty
from sqlalchemy.orm..., column_property
class MySubstr(object):
@classproperty
def my_substr(cls):
return column_property(
select([func.substr(cls.my_str, 2, 3)]).label('my_substr')
)
class MyTable(Base, MySubstr):
.
and then expect to be able to call a record in MyTable and return its
my_substr, however, all I'm getting is a representation of the object ...
eg sqlalchemy.orm.properties.ColumnProperty object at 0xa4951cc and
no apparent way of seeing its value.
oh and also, you don't need the select() here either:
class MyTable(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
my_str = Column('my_str', String)
my_substr = column_property(func.substr(cls.my_str, 2, 3))
I see nothing wrong with the example, the only potential glitch is that
my_str needs to have a name assigned up front. You didn't illustrate that
part here, so FYI it would be extremely
[sqlalchemy] bidirectional self-referential many-to-many
I'm trying to use the object association pattern from the doco. Is it
the case that this requires departure from Declarative mode and is it
wrong to mix with non-declarative? I looked at the example code
optimized_al.py but it didn't seem to be exactly what I want. I want a
symmetric relation for adjacency of nodes in an undirected graph with
cycles. ie. it is not a tree.
code
class Node(DeclarativeBase):
__tablename__ = 'node'
#with some columns id etc.
adj = Table(u'adj', metadata,
Column(u'node', Integer(), ForeignKey('node.id'),
primary_key=True),
Column(u'adj_node', Integer(), ForeignKey('node.id'),
primary_key=True),
Column(u'somedata_id', Integer(), ForeignKey('somedata.id')),
)
class Adj(object):
__table__ = _adj
#relation definitions
node = relation('node') # self-referential, bidirectional, many-
to-many
somedata = relation('SomeData')
def __init__(self, n1, n2):
self.n1 = n1
self.n2 = n2
mapper(Adj, adj, properties={
'node': relation(Adj,
primaryjoin = Node.id==Adj.node,
#secondaryjoin = Node.id==Adj.adj_node?,
backref=backref('adj_node',
remoteside=[_adj.c.node])
)
}, non_primary=True)
/code
I put the non_primary and the primaryjoin in after error messages told
me to but now this is causing another error 'Adj' has not attribute
'node' I even tried primaryjoin = Node.id==adj.c.node but then I got
Could not locate any equated, locally mapped column pairs. This
seems to be getting a bit more complicated than it should be already.
Is there an example of an association object in Declarative style?
Also, is it possible to do it with forward references only?
--
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.
Re: [sqlalchemy] Declarative syntax with column_property (v0.6.3)
On Aug 3, 2010, at 7:47 AM, Robert Sudwarts wrote:
Hi Michael,
I'm guilty of two over-simplifications in the code used in my example...
Firstly, the substring property/column (with which I had a problem) applies
to a subclass (using joined table inheritance) and the variable to which it
applies is specified in its parent class. Hence, I started by assuming that
I'd need something along the lines of:
class Parent(Base):
my_string
class Child(Parent):
@classproperty/@property/ some other decorator
def my_substr(cls):
return column_property ( cls.my_string )
However, trying this (ie the classproperty/column_property within Child),
returned sqlalchemy.orm.properties.ColumnProperty object ... (the error I
referred to in my original question)
I'm confused about why I'm not able to get this (or some variation of it) to
work. Is this indeed possible?
Just yesterday, in response to this, I added a line of code such that if you
specifically use @classproperty on the declarative class (i.e. not a mixin),
declarative will call it to figure out what it returns and configure it
normally- previously, if the attribute were not directly a MapperProperty, it
would be ignored.However, I couldn't think of any use case that this is
actually useful for. I thought that, it might be a way to establish a
column-oriented property late, so that you could get around imports not being
available. At the moment, it's not useful for that, because declarative calls
the attribute as soon as the class is created, and whatever imports that werent
available to the class declaration are still not present.
So what are you gaining above, if it were working in 0.6.3, by using
@classproperty instaed of my_substr = column_property() ? Anywhere you
reference cls in your function, you'd just reference whatever is local, or
part of Parent.
Looking at the documentation (as a consequence of the error), my I was drawn
to the syntax used in the section on 'mixins' (ie, the column_property
defined externally).
My second over-simplification regards the the function itself. The function
I'm trying to use [which I'm aware doesn't translate easily to sqlite], is:
func.substr(cls.my_str, func.instr(cls.my_str, )+1, 2)
Now, thanks to your response, I now actually have the following *working*
(using mysql):
class MySubStr(object):
@classproperty
def my_substr(cls):
return column_property(
func.substr(cls.my_string,
func.instr(cls.my_string, )+1, 2)
)
However, replacing:
func.substr(cls.my_string, func.instr(cls.my_string, )+1, 2)
with (in order to attach a label to the column):
select([func.substr(cls.my_string, func.instr(cls.my_string, )+1,
2)]).label('exch_code')
Gives me -- AttributeError: 'NoneType' object has no attribute '_keymap'
(full traceback below).
That's a really weird error. It suggests that the result is not one that
returns any rows, not like its a SELECT with zero rows, rather, it is perceived
by SQLite as something like an INSERT that isn't meant to return any rows, and
there's no cursor.description. The ResultProxy is then failing ungracefully
which is surprising (looking at the current source it appears we need to cover
this situation). It would be interesting to try out the raw SQL to see what
is produced, since the statement is definitely a SELECT - pysqlite should raise
an error if it doesn't like the SQL, or give us a cursor.description.
Anyway, your column_property() doesn't need the select() here, you can just
call .label() on the func.substr() result, but also, you don't really need the
label() in here either (unless I'm missing something? )
Apologies for my earlier over-simplification (and my late response!). And I
hope that what I've responded with is clear!!
Regards,
Rob
-
/home/robertsudwarts/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.pyc
in one(self)
1520
1521
- 1522 ret = list(self)
1523
1524 l = len(ret)
/home/robertsudwarts/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.pyc
in instances(self, cursor, _Query__context)
1667 break
1668 else:
- 1669 fetch = cursor.fetchall()
1670
1671 if custom_rows:
/home/robertsudwarts/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.pyc
in fetchall(self)
2381
2382 try:
- 2383 l = self.process_rows(self._fetchall_impl())
2384 self.close()
2385 return l
Re: [sqlalchemy] bidirectional self-referential many-to-many
On Aug 3, 2010, at 3:37 AM, Enrico wrote:
I'm trying to use the object association pattern from the doco. Is it
the case that this requires departure from Declarative mode and is it
wrong to mix with non-declarative?
it is perfectly fine to mix non-declarative with declarative. However, it is
almost always not necessary to use non_primary=True. non_primary is when you
want to have an alternative SELECT statement for a particular entity, and you
need that SELECT wrapped in a mapping so that it can be used in a
relationship().
I looked at the example code
optimized_al.py but it didn't seem to be exactly what I want. I want a
symmetric relation for adjacency of nodes in an undirected graph with
cycles. ie. it is not a tree.
The key to using association object is that you are no longer using the
secondary attribute of relationship() (and hence no secondaryjoin).
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, relationship
Base = declarative_base()
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
class Adj(Base):
__tablename__ = 'adj'
node_id = Column(Integer(), ForeignKey('node.id'),
primary_key=True)
adj_node_id = Column(Integer(), ForeignKey('node.id'),
primary_key=True)
node = relationship(Node, primaryjoin=node_id==Node.id,
backref='adj_node')
adj_node = relationship(Node,
primaryjoin=adj_node_id==Node.id,
backref='node')
def __str__(self):
return Adj(node=%s, adj_node=%s) % (self.node_id, self.adj_node_id)
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
sess = sessionmaker(engine)()
n1, n2, n3 = Node(), Node(),Node()
n1.node.append(Adj(node=n2))
n1.node.append(Adj(node=n3))
sess.add_all([
n1, n2, n3
])
sess.commit()
print sess.query(Adj).all()
code
class Node(DeclarativeBase):
__tablename__ = 'node'
#with some columns id etc.
adj = Table(u'adj', metadata,
Column(u'node', Integer(), ForeignKey('node.id'),
primary_key=True),
Column(u'adj_node', Integer(), ForeignKey('node.id'),
primary_key=True),
Column(u'somedata_id', Integer(), ForeignKey('somedata.id')),
)
class Adj(object):
__table__ = _adj
#relation definitions
node = relation('node') # self-referential, bidirectional, many-
to-many
somedata = relation('SomeData')
def __init__(self, n1, n2):
self.n1 = n1
self.n2 = n2
mapper(Adj, adj, properties={
'node': relation(Adj,
primaryjoin = Node.id==Adj.node,
#secondaryjoin = Node.id==Adj.adj_node?,
backref=backref('adj_node',
remoteside=[_adj.c.node])
)
}, non_primary=True)
/code
I put the non_primary and the primaryjoin in after error messages told
me to but now this is causing another error 'Adj' has not attribute
'node' I even tried primaryjoin = Node.id==adj.c.node but then I got
Could not locate any equated, locally mapped column pairs. This
seems to be getting a bit more complicated than it should be already.
Is there an example of an association object in Declarative style?
Also, is it possible to do it with forward references only?
--
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.
--
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.
Re: [sqlalchemy] Declarative syntax with column_property (v0.6.3)
Please see my comments below.
Regards,
Rob
On 3 August 2010 14:05, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 3, 2010, at 7:47 AM, Robert Sudwarts wrote:
Hi Michael,
I'm guilty of two over-simplifications in the code used in my example...
Firstly, the substring property/column (with which I had a problem) applies
to a subclass (using joined table inheritance) and the variable to which
it applies is specified in its parent class. Hence, I started by assuming
that I'd need something along the lines of:
class Parent(Base):
my_string
class Child(Parent):
@classproperty/@property/ some other decorator
def my_substr(cls):
return column_property ( cls.my_string )
However, trying this (ie the classproperty/column_property within Child),
returned sqlalchemy.orm.properties.ColumnProperty object ... (the error
I referred to in my original question)
I'm confused about why I'm not able to get this (or some variation of it)
to work. Is this indeed possible?
Just yesterday, in response to this, I added a line of code such that if
you specifically use @classproperty on the declarative class (i.e. not a
mixin), declarative will call it to figure out what it returns and configure
it normally- previously, if the attribute were not directly a
MapperProperty, it would be ignored.However, I couldn't think of any use
case that this is actually useful for. I thought that, it might be a way to
establish a column-oriented property late, so that you could get around
imports not being available. At the moment, it's not useful for that,
because declarative calls the attribute as soon as the class is created, and
whatever imports that werent available to the class declaration are still
not present.
Ah! Right!! I hadn't understood that ... I assumed (wrongly) that I'd have
to 'instantitate' 'cls' somehow using a decorator+function: def blah(cls):
...
I didn't realise that I could simply use Parent.my_str
So what are you gaining above, if it were working in 0.6.3, by using
@classproperty instaed of my_substr = column_property() ? Anywhere you
reference cls in your function, you'd just reference whatever is local, or
part of Parent.
Looking at the documentation (as a consequence of the error), my I was
drawn to the syntax used in the section on 'mixins' (ie, the column_property
defined externally).
My second over-simplification regards the the function itself. The
function I'm trying to use [which I'm aware doesn't translate easily to
sqlite], is:
func.substr(cls.my_str, func.instr(cls.my_str, )+1, 2)
Now, thanks to your response, I now actually have the following *working*
(using mysql):
class MySubStr(object):
@classproperty
def my_substr(cls):
return column_property(
func.substr(cls.my_string,
func.instr(cls.my_string, )+1, 2)
)
However, replacing:
func.substr(cls.my_string, func.instr(cls.my_string, )+1, 2)
with (in order to attach a label to the column):
select([func.substr(cls.my_string, func.instr(cls.my_string, )+1,
2)]).label('exch_code')
Gives me -- AttributeError: 'NoneType' object has no attribute '_keymap'
(full traceback below).
That's a really weird error. It suggests that the result is not one that
returns any rows, not like its a SELECT with zero rows, rather, it is
perceived by SQLite as something like an INSERT that isn't meant to return
any rows, and there's no cursor.description. The ResultProxy is then
failing ungracefully which is surprising (looking at the current source it
appears we need to cover this situation). It would be interesting to try
out the raw SQL to see what is produced, since the statement is definitely a
SELECT - pysqlite should raise an error if it doesn't like the SQL, or give
us a cursor.description.
Just to be clear -- this error/traceback was produced with MySQL as the
database
Anyway, your column_property() doesn't need the select() here, you can just
call .label() on the func.substr() result, but also, you don't really need
the label() in here either (unless I'm missing something? )
Again ... I hadn't realised that I could apply .label() to the directly to
'func' ... I've changed this and it works.
Apologies for my earlier over-simplification (and my late response!). And
I hope that what I've responded with is clear!!
Regards,
Rob
-
/home/robertsudwarts/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.pyc
in one(self)
1520
1521
- 1522 ret = list(self)
1523
1524 l = len(ret)
/home/robertsudwarts/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.pyc
in instances(self, cursor, _Query__context)
[sqlalchemy] forcing an insert
Is there a way to force the ORM to insert a new row instead of updating? Something like the must_insert argument to model's save method in Django's ORM. The use case is I must create a unique session key (for a cookie) and want an error when the key isn't unique, so perhaps there's a better way? -- 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.
Re: [sqlalchemy] forcing an insert
On 8/3/10 16:50 , Benjamin Peterson wrote: Is there a way to force the ORM to insert a new row instead of updating? Something like the must_insert argument to model's save method in Django's ORM. The use case is I must create a unique session key (for a cookie) and want an error when the key isn't unique, so perhaps there's a better way? Declare the key to be unique? Wichert. -- 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.
Re: [sqlalchemy] forcing an insert
On Aug 3, 2010, at 10:50 AM, Benjamin Peterson wrote: Is there a way to force the ORM to insert a new row instead of updating? Something like the must_insert argument to model's save method in Django's ORM. The use case is I must create a unique session key (for a cookie) and want an error when the key isn't unique, so perhaps there's a better way? Use make_transient(). http://www.sqlalchemy.org/docs/reference/orm/sessions.html?highlight=make_transient#sqlalchemy.orm.session.make_transient An example usage is at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedRows -- 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. -- 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.
[sqlalchemy] mapping columns phone1, phone2, phone3 to a list-based property, phones
Say you have a denormalized table with columns phone1, phone2, phone3 and you would like to map the class so that the .phones property is an iterable. e.g. if I have data like user_id, phone1, phone2, phone3 1, 1234, 5678, 9012 2, 3456,7890,1234 I would like to say something like for p in S.query(User).get(1).phones: print p.ordinal, p.number and get this as output: 1 1234 2 5678 3 9012 While one could use an operator like SQL Server's UNPIVOT, I would be quite happy to have the mapper do the magic. I was reading through the examples/vertical.py source today so I think that what I want is doable, I am just not sure how to approach it. I assume that I would proxy a list-based relation? pjjH -- 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.
[sqlalchemy] class definition missing from docs?
http://www.sqlalchemy.org/docs/mappers.html#association-object
refers to an Assocation class here:
---
mapper(Parent, left_table, properties={
'children':relationship(Association)
})
mapper(Association, association_table, properties={
'child':relationship(Child)
})
---
but it doesn't seem to be defined in the example.
--
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.
Re: [sqlalchemy] class definition missing from docs?
well neither is Parent and Child for that and the previous example if you want
to get technical (or not even).will find some time to add them all in as
that is a current project
On Aug 3, 2010, at 1:36 PM, botz wrote:
http://www.sqlalchemy.org/docs/mappers.html#association-object
refers to an Assocation class here:
---
mapper(Parent, left_table, properties={
'children':relationship(Association)
})
mapper(Association, association_table, properties={
'child':relationship(Child)
})
---
but it doesn't seem to be defined in the example.
--
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.
--
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.
Re: [sqlalchemy] mapping columns phone1, phone2, phone3 to a list-based property, phones
On Aug 3, 2010, at 1:05 PM, phrrn...@googlemail.com wrote: Say you have a denormalized table with columns phone1, phone2, phone3 and you would like to map the class so that the .phones property is an iterable. e.g. if I have data like user_id, phone1, phone2, phone3 1, 1234, 5678, 9012 2, 3456,7890,1234 I would like to say something like for p in S.query(User).get(1).phones: print p.ordinal, p.number and get this as output: 1 1234 2 5678 3 9012 While one could use an operator like SQL Server's UNPIVOT, I would be quite happy to have the mapper do the magic. I was reading through the examples/vertical.py source today so I think that what I want is doable, I am just not sure how to approach it. I assume that I would proxy a list-based relation? you'd make a Phone object that takes the place of Animal in the dictlike.py example.So you'd need User-Phone-PhoneFact. pjjH -- 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. -- 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.
[sqlalchemy] Re: To select only some columns from some tables using session object, relation many-to-many
It works out, thank you! How could I just retrieve some columns from
both tables? For example, if I try to select some columns from Item
and Channel, I get class 'sqlalchemy.util.NamedTuple' when I'd
like to get a channel type with its items:
result = session.query(Channel.title,
Item.title).join('items').filter(Item.typeItem == zeppelin/
channel).order_by(Channel.titleView).all()
I just need some values many times, I don't need to retrieve the whole
object.
Thanks in advance!
On Aug 3, 1:40 am, Kalium raymond.ma...@gmail.com wrote:
On Aug 3, 8:43 am, Alvaro Reinoso alvrein...@gmail.com wrote:
Hello,
I have these classes where items (class Item) is related to channel
object. Channel can contain many items:
channel_items = Table(
channel_items,
metadata,
Column(channel_id, Integer,
ForeignKey(channels.id)),
Column(item_id, Integer,
ForeignKey(Item.id))
)
class Channel(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(channels)
id = Column(id, Integer, primary_key=True)
title = Column(title, String(100))
items = relation(Item, secondary=channel_items,
backref=channels)
class Item(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(items)
id = Column(id, Integer, primary_key=True)
title = Column(title, String(100))
I know how to get all the columns using something like:
session = rdb.Session() channels =
session.query(Channel).order_by(Channel.title)
However, I'd like to select some columns from both tables with some
conditions in Item. For example, select all the channels where
item.type = 'jpg'. I'd like to get a channel object with items
attributes with that condition for example. How can I do that?
I've tried something like (no one worked out):
result = session.query(Channel).filter(Item.typeItem != 'zeppelin/
channel').all()
result = session.query(Channel, Item).filter(Item.typeItem !=
'zeppelin/channel').all()
Thanks in advance!
Try something like
session.query(Channel).join('items').filter(Item.typeItem !=
'whatever').all()
--
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.
[sqlalchemy] func type_ not being used
This returns a Decimal type for c2, which is what I want: c1 = literal(5, type_=Numeric) c2 = func.sum(c1, type_=Numeric) This returns a Float type for c2, but I'm telling c1 that it is a Numeric. How can I get a decimal returned when using an if function? c1 = func.if_(Table.foo != None, Table.foo, Table.bar, type_=Numeric) c2 = func.sum(c1, type_=Numeric) -- 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.
Re: [sqlalchemy] func type_ not being used
On Aug 3, 2010, at 2:56 PM, Bryan wrote: This returns a Decimal type for c2, which is what I want: c1 = literal(5, type_=Numeric) c2 = func.sum(c1, type_=Numeric) This returns a Float type for c2, but I'm telling c1 that it is a Numeric. How can I get a decimal returned when using an if function? c1 = func.if_(Table.foo != None, Table.foo, Table.bar, type_=Numeric) c2 = func.sum(c1, type_=Numeric) I see nothing wrong with that code.Can I get some SQLA version / database backend / DBAPI details ? -- 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. -- 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.
[sqlalchemy] Re: func type_ not being used
Python 2.5.4 MySQL python 1.2.3c1 sqlalchemy 0.5.2 Here is the actual code. It references my object model etc so it won't run for you, but just in case I made a mistake converting it to a simplified version of the problem here it is: dollars = func.if_(EmpTime.actTotal != None, EmpTime.actTotal, EmpTime.estTotal, type_=types.Numeric) q = orm.query( Account.code, func.lower(TimeType.shortName), func.sum(EmpTime.hours), func.sum(dollars, type_=types.Numeric) ) q = q.join(EmpTime.acc).join(EmpTime.timeType) q = q.group_by(Account.code).group_by(TimeType.shortName) q = q.filter(EmpTime.day = start) q = q.filter(EmpTime.day = end) q = q.filter(EmpTime.jobId == jobId) labor = q.all() On Aug 3, 1:26 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 3, 2010, at 2:56 PM, Bryan wrote: This returns a Decimal type for c2, which is what I want: c1 = literal(5, type_=Numeric) c2 = func.sum(c1, type_=Numeric) This returns a Float type for c2, but I'm telling c1 that it is a Numeric. How can I get a decimal returned when using an if function? c1 = func.if_(Table.foo != None, Table.foo, Table.bar, type_=Numeric) c2 = func.sum(c1, type_=Numeric) I see nothing wrong with that code. Can I get some SQLA version / database backend / DBAPI details ? -- 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 athttp://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 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.
[sqlalchemy] is it better to have 2 distinct sessions or just use a single session
so our db setup is that we have both vertical and horizontal partitioning going on. first, is it possible to defined a session as a scoped, sharded session to achieve both? namely, define the shardedsession wrapped by a scoped session and simply have the various shard lookup functions (*chooser) do the mapping for the vertical partition as well as the horizontal ones? can this be cleanly done by using bind_table on the session for the vertically partitioned ones and only have the various shard lookup functions concern themselves with the horizontally partitioned tables? failing the above, it seems pretty straightforward and possible (although contrived) to use 2 different sessions (one sharded for horizontal and one normal one for vertical w/ the table binds) to achieve the same effect. however, we do lose out on the transaction level details spanning all our db sessions. thoughts and advice appreciated. i'm clearly a nub when it comes to this stuff. -- 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.
Re: [sqlalchemy] Re: func type_ not being used
On Aug 3, 2010, at 5:00 PM, Bryan wrote: Python 2.5.4 MySQL python 1.2.3c1 sqlalchemy 0.5.2 just curious can you try with SQLA 0.6.3 ? Here is the actual code. It references my object model etc so it won't run for you, but just in case I made a mistake converting it to a simplified version of the problem here it is: dollars = func.if_(EmpTime.actTotal != None, EmpTime.actTotal, EmpTime.estTotal, type_=types.Numeric) q = orm.query( Account.code, func.lower(TimeType.shortName), func.sum(EmpTime.hours), func.sum(dollars, type_=types.Numeric) ) q = q.join(EmpTime.acc).join(EmpTime.timeType) q = q.group_by(Account.code).group_by(TimeType.shortName) q = q.filter(EmpTime.day = start) q = q.filter(EmpTime.day = end) q = q.filter(EmpTime.jobId == jobId) labor = q.all() On Aug 3, 1:26 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 3, 2010, at 2:56 PM, Bryan wrote: This returns a Decimal type for c2, which is what I want: c1 = literal(5, type_=Numeric) c2 = func.sum(c1, type_=Numeric) This returns a Float type for c2, but I'm telling c1 that it is a Numeric. How can I get a decimal returned when using an if function? c1 = func.if_(Table.foo != None, Table.foo, Table.bar, type_=Numeric) c2 = func.sum(c1, type_=Numeric) I see nothing wrong with that code.Can I get some SQLA version / database backend / DBAPI details ? -- 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 athttp://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 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. -- 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.
[sqlalchemy] declarative autoloading table class with composite foreign/primary key
Hi
I'm relatively new to SQLAlchemy, so thanks in advance for any help
with this issue.
I'm trying to construct a class to model a legacy table which has a
composite primary key which is also
a composite foreign key referencing the composite primary key of a
second table. I'm trying to define this
class declaratively, and also have it autoload the remaining table
structure from the underlying table.
This is what I have:
class Wcs(skymapper_db.db.TableBase):
from sqlalchemy import Column, Integer, ForeignKeyConstraint
__tablename__ = 'wcs'
__table_args__ = (
ForeignKeyConstraint(['image_id', 'amp'],
['science_amp.image_id', 'science_amp.amp']),
{'autoload':True}
)
image_id = Column(Integer, primary_key=True)
amp = Column(Integer, primary_key=True)
def __init__(self):
pass
def __repr__(self):
return Wcs(%s, %s) % (self.image_id, self.amp)
As it stands, I get an error when I try to instantiate this class:
C:\Users\jgs900\Work\skymapper-alchemywcs.py
Traceback (most recent call last):
File C:\Users\jgs900\Work\skymapper-alchemy\wcs.py, line 13, in
module
class Wcs(skymapper_db.db.TableBase):
File C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py,
line 1017, in __init__
_as_declarative(cls, classname, cls.__dict__)
File C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py,
line 926, in _as_declarative
**table_kw)
File C:\Python27\lib\site-packages\sqlalchemy\schema.py, line 209,
in __new__
table._init(name, metadata, *args, **kw)
File C:\Python27\lib\site-packages\sqlalchemy\schema.py, line 269,
in _init
self._init_items(*args)
File C:\Python27\lib\site-packages\sqlalchemy\schema.py, line 60,
in _init_items
item._set_parent(self)
File C:\Python27\lib\site-packages\sqlalchemy\schema.py, line 809,
in _set_parent
table.constraints.remove(fk.constraint)
KeyError: ForeignKeyConstraint()
but if I leave out the autoload instruction, there is no problem.
Am i doing something fundamentally wrong? Or am I just making a syntax
error of some sort. Any help
would be greatly appreciated.
--
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.
Re: [sqlalchemy] declarative autoloading table class with composite foreign/primary key
On Aug 3, 2010, at 10:24 PM, jgs9000 wrote:
Hi
I'm relatively new to SQLAlchemy, so thanks in advance for any help
with this issue.
I'm trying to construct a class to model a legacy table which has a
composite primary key which is also
a composite foreign key referencing the composite primary key of a
second table. I'm trying to define this
class declaratively, and also have it autoload the remaining table
structure from the underlying table.
i dont know that we have any tests which do a pure autoload plus a foreign key
constraint otherwise not associated with anything. so its likely a bug. you
might want to try calling table.append_constraint(constraint) after the
autoload completes.
This is what I have:
class Wcs(skymapper_db.db.TableBase):
from sqlalchemy import Column, Integer, ForeignKeyConstraint
__tablename__ = 'wcs'
__table_args__ = (
ForeignKeyConstraint(['image_id', 'amp'],
['science_amp.image_id', 'science_amp.amp']),
{'autoload':True}
)
image_id = Column(Integer, primary_key=True)
amp = Column(Integer, primary_key=True)
def __init__(self):
pass
def __repr__(self):
return Wcs(%s, %s) % (self.image_id, self.amp)
As it stands, I get an error when I try to instantiate this class:
C:\Users\jgs900\Work\skymapper-alchemywcs.py
Traceback (most recent call last):
File C:\Users\jgs900\Work\skymapper-alchemy\wcs.py, line 13, in
module
class Wcs(skymapper_db.db.TableBase):
File C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py,
line 1017, in __init__
_as_declarative(cls, classname, cls.__dict__)
File C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py,
line 926, in _as_declarative
**table_kw)
File C:\Python27\lib\site-packages\sqlalchemy\schema.py, line 209,
in __new__
table._init(name, metadata, *args, **kw)
File C:\Python27\lib\site-packages\sqlalchemy\schema.py, line 269,
in _init
self._init_items(*args)
File C:\Python27\lib\site-packages\sqlalchemy\schema.py, line 60,
in _init_items
item._set_parent(self)
File C:\Python27\lib\site-packages\sqlalchemy\schema.py, line 809,
in _set_parent
table.constraints.remove(fk.constraint)
KeyError: ForeignKeyConstraint()
but if I leave out the autoload instruction, there is no problem.
Am i doing something fundamentally wrong? Or am I just making a syntax
error of some sort. Any help
would be greatly appreciated.
--
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.
--
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.
Re: [sqlalchemy] is it better to have 2 distinct sessions or just use a single session
On Aug 3, 2010, at 6:45 PM, razamatan wrote: so our db setup is that we have both vertical and horizontal partitioning going on. first, is it possible to defined a session as a scoped, sharded session to achieve both? namely, define the shardedsession wrapped by a scoped session and simply have the various shard lookup functions (*chooser) do the mapping for the vertical partition as well as the horizontal ones? can this be cleanly done by using bind_table on the session for the vertically partitioned ones and only have the various shard lookup functions concern themselves with the horizontally partitioned tables? failing the above, it seems pretty straightforward and possible (although contrived) to use 2 different sessions (one sharded for horizontal and one normal one for vertical w/ the table binds) to achieve the same effect. however, we do lose out on the transaction level details spanning all our db sessions. thoughts and advice appreciated. i'm clearly a nub when it comes to this stuff. well unfortunately so are the rest of us, I haven't actually tried using the horizontal sharding for anything though I have been aware of people who do. The key takeaway from the horizontal shard module is that you can rewrite the get_bind() method of Session to do anything you want, and there's just enough hooks so that you can get it to call get_bind() for each individual row its going to work with.So for an intricate scheme of database switching, the horizontal shard module and/or writing your own get_bind() will definitely work though write yourself a bunch of tests.Over here I have a project where I have not quite a horizontal shard scenario, but there are two databases used simultaneously - but after trying out a custom get_bind() situation that more or less worked, I am using just two separate scoped_sessions, only because there's not too much interaction between them and i really didnt want to confuse all the other developers, as the second DB isn't really used for most of the application. Its something you need to feel out. -- 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. -- 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.
