RE: [sqlalchemy] Re: To select only some columns from some tables using session object, relation many-to-many
Alvaro Reinoso wrote:
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!
It sounds like you are looking for deferred column loading:
http://www.sqlalchemy.org/docs/mappers.html#deferred-column-loading
You can mark certain columns as not to be loaded until they are
accessed. This can be done at mapper definition time as well as at query
time.
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 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] polymorphic query - anon_1, anon_2 ... can they be used?
hi all,
in a polymorphic query, after a 'print' command i realized that SA generates
some columns named 'anon_X', which can be helpful for me.
the question is:
1. is it safe to use those anon_X columns to refine my query?
--- regarding this, my concern is that once the polymorphic query is done by
SA, will it anytime change it's name, or anything else in future releases?
2. can i use them in a more pythonic way, without using as simple strings?
--- in a query, for example, to access order_by anon_1 i must use:
data_obj.order_by('anon_1 asc')
--- the case here is, can it be named?
thanks in advance,
Richard.
--
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] SQL Expression lang; change table in from clause after query construction; copying queries; printing queries with filled params
suppose:
summary_table = Table(
'summary', metadata,
Column('ts',Integer, index=True, nullable=False),
Column('url',String, index=True, nullable=False),
Column('hits',Integer, nullable=False),
PrimaryKeyConstraint('ts','url','hits',name='summary_pk'),
)
And a query like:
s = summary_table.c
q = select([
s.url,
s.ts,
]).\
where(s.site == bindparam(url)).\
where(s.ts == bindparam(ts)
* how to 'copy' a query. copy.copy(q) seems to be inadequate
* how to print it, with params filled in. str(q) isn't quite enough.
(I know this has been covered before, but I can't seem to find it,
and
if it's not in the docs, it should be!). The query is bound to an
engine already. My desired goal is to see the actual sql (with
filled quoted params) that would get sent to the engine. If I had
this, I could always just use a regex to change the table.
* how to change the table being called. My specific subcase is for an
inherited
table. I tried this, but it seems very dirty:
q._froms[0].name = 'summary_1279234800'
Is there a general method for that I should use? (Also, this will
be fine
if I can get the 'copy' business to work.
As usual, SA is great, and I'm doing rude, mean things to it, so if
there are simpler ways out of this mess (wanting simple query
construction,
but one that I can alter to use on specific Postgres INHERIT tables),
please
inform me.
--
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] Basic query questions ...
Hello, a couple of really basic questions ... 1) How do I write a query beforehand that will be evaluated later at some other place in the code? (Note that the session hopefully won't exist until that later time.) Also, certain parameters may need to be passed to the query at eval time (e.g. id==id_to_get). 2) How do I specify a query to load only certain columns? I think I've read the 'defer' docs, it seems to be a way to specify such in the mapper, not the query; or else do something like mark every column deferred and then undefer then at query time. Is there a simple way to just specify which columns to load at query time? Thanks, Michael -- 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
Same behavior with 0.6.3. On Aug 3, 4:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: 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 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.
Re: [sqlalchemy] SQL Expression lang; change table in from clause after query construction; copying queries; printing queries with filled params
On Aug 4, 2010, at 10:34 AM, Gregg Lind wrote:
suppose:
summary_table = Table(
'summary', metadata,
Column('ts',Integer, index=True, nullable=False),
Column('url',String, index=True, nullable=False),
Column('hits',Integer, nullable=False),
PrimaryKeyConstraint('ts','url','hits',name='summary_pk'),
)
And a query like:
s = summary_table.c
q = select([
s.url,
s.ts,
]).\
where(s.site == bindparam(url)).\
where(s.ts == bindparam(ts)
* how to 'copy' a query. copy.copy(q) seems to be inadequate
select() has a _generate() method that is used internally for generative
operations. But select() supports the generative interface specifically so
that you can treat it as an immutable structure, and copying should not be
necessary (I use it occasionally when I want to attach some additional state to
a select and not affect the original, though that is already a hacky situation).
* how to print it, with params filled in. str(q) isn't quite enough.
(I know this has been covered before, but I can't seem to find it,
and
if it's not in the docs, it should be!). The query is bound to an
engine already. My desired goal is to see the actual sql (with
filled quoted params) that would get sent to the engine. If I had
this, I could always just use a regex to change the table.
if the query is bound to an engine, meaning, its against a Table who's MetaData
is bound to the engine, then str(q) will invoke the compiler for that engine's
dialect and you will get the exact SQL that would be emitted. If not, you
call q.compile(bind=myengine) or q.compile(dialect=somedialect).
* how to change the table being called. My specific subcase is for an
inherited
table. I tried this, but it seems very dirty:
q._froms[0].name = 'summary_1279234800'
Is there a general method for that I should use? (Also, this will
be fine
if I can get the 'copy' business to work.
This is not the right way to go. select()s are really not designed for
mutation, even though they have some mutative capabilities (which is mostly for
performance reasons). If you want a select that is against some other table,
you need a new select() object.Additionally, above you're even modifying
your Table construct, which, if you have a typical case with Table objects
declared at the module level, definitely isn't going to work very well (you
could do it with ad-hoc table() constructs, perhaps).
The canonical way to create new selects out of old ones that are different is
to use clause transformation.In this case it would be:
t1 = Table('summary_table', ...)
t2 = Table('summary_table_xyz', ...)
def replace(obj):
if obj is t1:
return t2
elif obj in t1.c:
return t2.c[obj.key]
else:
return None
from sqlalchemy.sql.visitors import replacement_traverse
new_select = replacement_traverse(old_select, None, replace)
The name of the table you have above there seems to suggest you have some kind
of I have a ton of tables with the same columns thing going on, so here is a
recipe for that:
from sqlalchemy.sql import Alias
from sqlalchemy.ext.compiler import compiles
class InhTable(Alias):
def __init__(self, table, name):
Alias.__init__(self, table, table.name + _ + name)
@compiles(InhTable)
def compile(element, compiler, **kw):
table_name = compiler.process(element.original, **kw)
return table_name.replace(element.original.name,
element.name)
#usage:
t1 = Table('asdf', MetaData(), Column('x', Integer), Column('y', Integer))
t2 = InhTable(t1, 1279234800)
print select([t2])
--
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
nothing wrong with the code I see, and I am noticing that to recreate your test is taking me longer than one minute, so please provide a fully reproducing test case. On Aug 4, 2010, at 11:14 AM, Bryan wrote: Same behavior with 0.6.3. On Aug 3, 4:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: 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 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.
Re: [sqlalchemy] polymorphic query - anon_1, anon_2 ... can they be used?
On Aug 4, 2010, at 10:07 AM, Richard Kuesters wrote:
hi all,
in a polymorphic query, after a 'print' command i realized that SA generates
some columns named 'anon_X', which can be helpful for me.
the question is:
1. is it safe to use those anon_X columns to refine my query?
--- regarding this, my concern is that once the polymorphic query is done by
SA, will it anytime change it's name, or anything else in future releases?
it's not. When anonymous aliases are generated within the orm Query against
some entity that you've given it explicitly, you use the column attributes on
that entity to reference those columns, not strings. Never use strings except
for those that you've named explicitly, and at the same level as where you
named it.
For example, you might use a string if you wanted to ORDER BY some expression
that's named in the columns clause:
q = sess.query(func.myfunction(...).label('bar')).order_by('bar')
However, once you nest q as a subquery, it gains a .c. collection, and now
you use that .c. collection for all future references to 'bar':
q = q.subquery()
q2 = sess.query(MyEntity).join((q, q.c.bar==MyEntity.bar))
--
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
OK, I'll put together a case later today. On Aug 4, 8:24 am, Michael Bayer mike...@zzzcomputing.com wrote: nothing wrong with the code I see, and I am noticing that to recreate your test is taking me longer than one minute, so please provide a fully reproducing test case. On Aug 4, 2010, at 11:14 AM, Bryan wrote: Same behavior with 0.6.3. On Aug 3, 4:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: 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 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 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.
Re: [sqlalchemy] polymorphic query - anon_1, anon_2 ... can they be used?
thanks Michael, that's what i have in mind. since anon_N can't be used, i
was thinking if they can be named or, in other cases, the sql that generates
an anon_N can be extracted?
ps: the object that contains these anon_N columns is a orm.Query object
created from a polymorphic mapper with multiple relationships.
On Wed, Aug 4, 2010 at 12:29 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Aug 4, 2010, at 10:07 AM, Richard Kuesters wrote:
hi all,
in a polymorphic query, after a 'print' command i realized that SA
generates some columns named 'anon_X', which can be helpful for me.
the question is:
1. is it safe to use those anon_X columns to refine my query?
--- regarding this, my concern is that once the polymorphic query is done
by SA, will it anytime change it's name, or anything else in future
releases?
it's not. When anonymous aliases are generated within the orm Query
against some entity that you've given it explicitly, you use the column
attributes on that entity to reference those columns, not strings. Never
use strings except for those that you've named explicitly, and at the same
level as where you named it.
For example, you might use a string if you wanted to ORDER BY some
expression that's named in the columns clause:
q = sess.query(func.myfunction(...).label('bar')).order_by('bar')
However, once you nest q as a subquery, it gains a .c. collection, and
now you use that .c. collection for all future references to 'bar':
q = q.subquery()
q2 = sess.query(MyEntity).join((q, q.c.bar==MyEntity.bar))
--
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.comsqlalchemy%2bunsubscr...@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] polymorphic query - anon_1, anon_2 ... can they be used?
On Aug 4, 2010, at 11:51 AM, Richard Kuesters wrote:
thanks Michael, that's what i have in mind. since anon_N can't be used, i was
thinking if they can be named or, in other cases, the sql that generates an
anon_N can be extracted?
ps: the object that contains these anon_N columns is a orm.Query object
created from a polymorphic mapper with multiple relationships.
What I am saying is, they are already extracted, assuming they are appropriate
for use.If these anon's are the product of eagerload()/joinedload(), those
aren't available to you. Otherwise, if you show me the scenario, I will show
you how the anon_1 is available.
On Wed, Aug 4, 2010 at 12:29 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Aug 4, 2010, at 10:07 AM, Richard Kuesters wrote:
hi all,
in a polymorphic query, after a 'print' command i realized that SA
generates some columns named 'anon_X', which can be helpful for me.
the question is:
1. is it safe to use those anon_X columns to refine my query?
--- regarding this, my concern is that once the polymorphic query is done
by SA, will it anytime change it's name, or anything else in future
releases?
it's not. When anonymous aliases are generated within the orm Query against
some entity that you've given it explicitly, you use the column attributes on
that entity to reference those columns, not strings. Never use strings
except for those that you've named explicitly, and at the same level as where
you named it.
For example, you might use a string if you wanted to ORDER BY some expression
that's named in the columns clause:
q = sess.query(func.myfunction(...).label('bar')).order_by('bar')
However, once you nest q as a subquery, it gains a .c. collection, and now
you use that .c. collection for all future references to 'bar':
q = q.subquery()
q2 = sess.query(MyEntity).join((q, q.c.bar==MyEntity.bar))
--
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.
--
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: SQL Expression lang; change table in from clause after query construction; copying queries; printing queries with filled params
Thanks for the advice!
One minor nit. At least in my experience, str(bound query) doesn't
fill the params, or do quoting properly. Here is a demonstration:
fake_table = Table(
'faketable', metadata,
Column('ts',Integer, index=True, nullable=False),
Column('url',String, index=True, nullable=False),
Column('hits',Integer, nullable=False),
PrimaryKeyConstraint('ts','url','hits',name='summary_pk'),
)
s = fake_table.c
q = select([
s.url,
s.ts,
]).\
where(s.url == bindparam(url)).\
where(s.ts == bindparam(ts)).\
where(s.hits 100)
assert fake_table.metadata.bind.name == 'postgresql' #it's bound
assert str(q) == \
SELECT faketable.url, faketable.ts
FROM faketable
WHERE faketable.url = %(url)s AND faketable.ts = %(ts)s AND
faketable.hits %(hits_1)s
As you can see the 'url' isn't quoted, which is a problem! Also, the
(hits_1) paremeter isn't filled in, even though it's already
determined.
What I would ideally like to see is this:
whatwould_happen(q,**some_dict):
SELECT faketable.url, faketable.ts
FROM faketable
WHERE faketable.url = 'http://mypage.com/index.html' AND
faketable.ts = 1829292929 AND faketable.hits 100
If I had this string repr with filled params, I could just a string
sub / regex,
and go all the way into hackery!
On Aug 4, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 4, 2010, at 10:34 AM, Gregg Lind wrote:
suppose:
summary_table = Table(
'summary', metadata,
Column('ts',Integer, index=True, nullable=False),
Column('url',String, index=True, nullable=False),
Column('hits',Integer, nullable=False),
PrimaryKeyConstraint('ts','url','hits',name='summary_pk'),
)
And a query like:
s = summary_table.c
q = select([
s.url,
s.ts,
]).\
where(s.site == bindparam(url)).\
where(s.ts == bindparam(ts)
* how to 'copy' a query. copy.copy(q) seems to be inadequate
select() has a _generate() method that is used internally for generative
operations. But select() supports the generative interface specifically so
that you can treat it as an immutable structure, and copying should not be
necessary (I use it occasionally when I want to attach some additional state
to a select and not affect the original, though that is already a hacky
situation).
* how to print it, with params filled in. str(q) isn't quite enough.
(I know this has been covered before, but I can't seem to find it,
and
if it's not in the docs, it should be!). The query is bound to an
engine already. My desired goal is to see the actual sql (with
filled quoted params) that would get sent to the engine. If I had
this, I could always just use a regex to change the table.
if the query is bound to an engine, meaning, its against a Table who's
MetaData is bound to the engine, then str(q) will invoke the compiler for
that engine's dialect and you will get the exact SQL that would be emitted.
If not, you call q.compile(bind=myengine) or q.compile(dialect=somedialect).
* how to change the table being called. My specific subcase is for an
inherited
table. I tried this, but it seems very dirty:
q._froms[0].name = 'summary_1279234800'
Is there a general method for that I should use? (Also, this will
be fine
if I can get the 'copy' business to work.
This is not the right way to go. select()s are really not designed for
mutation, even though they have some mutative capabilities (which is mostly
for performance reasons). If you want a select that is against some other
table, you need a new select() object. Additionally, above you're even
modifying your Table construct, which, if you have a typical case with Table
objects declared at the module level, definitely isn't going to work very
well (you could do it with ad-hoc table() constructs, perhaps).
The canonical way to create new selects out of old ones that are different
is to use clause transformation. In this case it would be:
t1 = Table('summary_table', ...)
t2 = Table('summary_table_xyz', ...)
def replace(obj):
if obj is t1:
return t2
elif obj in t1.c:
return t2.c[obj.key]
else:
return None
from sqlalchemy.sql.visitors import replacement_traverse
new_select = replacement_traverse(old_select, None, replace)
The name of the table you have above there seems to suggest you have some
kind of I have a ton of tables with the same columns thing going on, so
here is a recipe for that:
from sqlalchemy.sql import Alias
from sqlalchemy.ext.compiler import compiles
class InhTable(Alias):
def __init__(self, table, name):
Alias.__init__(self, table, table.name + _ + name)
@compiles(InhTable)
def compile(element, compiler, **kw):
table_name = compiler.process(element.original, **kw)
Re: [sqlalchemy] Re: SQL Expression lang; change table in from clause after query construction; copying queries; printing queries with filled params
On Aug 4, 2010, at 1:26 PM, Gregg Lind wrote:
Thanks for the advice!
One minor nit. At least in my experience, str(bound query) doesn't
fill the params, or do quoting properly. Here is a demonstration:
fake_table = Table(
'faketable', metadata,
Column('ts',Integer, index=True, nullable=False),
Column('url',String, index=True, nullable=False),
Column('hits',Integer, nullable=False),
PrimaryKeyConstraint('ts','url','hits',name='summary_pk'),
)
s = fake_table.c
q = select([
s.url,
s.ts,
]).\
where(s.url == bindparam(url)).\
where(s.ts == bindparam(ts)).\
where(s.hits 100)
assert fake_table.metadata.bind.name == 'postgresql' #it's bound
assert str(q) == \
SELECT faketable.url, faketable.ts
FROM faketable
WHERE faketable.url = %(url)s AND faketable.ts = %(ts)s AND
faketable.hits %(hits_1)s
As you can see the 'url' isn't quoted, which is a problem!
But, that is exactly what is sent to the DBAPI. The quoting happens at the
earliest in the DBAPI layer. Some DBAPIs don't ever quote anything, the
binds are sent separately for some backends and the database server itself
handles interpolation internally.If you turn on your PG logs to log SQL,
you'd see the quoting affair is pretty unpleasant so its critical that DBAPIs
handle this.
The parameters are available from the compiled object as the params
collection.
Also, the
(hits_1) paremeter isn't filled in, even though it's already
determined.
What I would ideally like to see is this:
whatwould_happen(q,**some_dict):
SELECT faketable.url, faketable.ts
FROM faketable
WHERE faketable.url = 'http://mypage.com/index.html' AND
faketable.ts = 1829292929 AND faketable.hits 100
If I had this string repr with filled params, I could just a string
sub / regex,
and go all the way into hackery!
On Aug 4, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 4, 2010, at 10:34 AM, Gregg Lind wrote:
suppose:
summary_table = Table(
'summary', metadata,
Column('ts',Integer, index=True, nullable=False),
Column('url',String, index=True, nullable=False),
Column('hits',Integer, nullable=False),
PrimaryKeyConstraint('ts','url','hits',name='summary_pk'),
)
And a query like:
s = summary_table.c
q = select([
s.url,
s.ts,
]).\
where(s.site == bindparam(url)).\
where(s.ts == bindparam(ts)
* how to 'copy' a query. copy.copy(q) seems to be inadequate
select() has a _generate() method that is used internally for generative
operations. But select() supports the generative interface specifically
so that you can treat it as an immutable structure, and copying should not
be necessary (I use it occasionally when I want to attach some additional
state to a select and not affect the original, though that is already a
hacky situation).
* how to print it, with params filled in. str(q) isn't quite enough.
(I know this has been covered before, but I can't seem to find it,
and
if it's not in the docs, it should be!). The query is bound to an
engine already. My desired goal is to see the actual sql (with
filled quoted params) that would get sent to the engine. If I had
this, I could always just use a regex to change the table.
if the query is bound to an engine, meaning, its against a Table who's
MetaData is bound to the engine, then str(q) will invoke the compiler for
that engine's dialect and you will get the exact SQL that would be emitted.
If not, you call q.compile(bind=myengine) or q.compile(dialect=somedialect).
* how to change the table being called. My specific subcase is for an
inherited
table. I tried this, but it seems very dirty:
q._froms[0].name = 'summary_1279234800'
Is there a general method for that I should use? (Also, this will
be fine
if I can get the 'copy' business to work.
This is not the right way to go. select()s are really not designed for
mutation, even though they have some mutative capabilities (which is mostly
for performance reasons). If you want a select that is against some other
table, you need a new select() object.Additionally, above you're even
modifying your Table construct, which, if you have a typical case with Table
objects declared at the module level, definitely isn't going to work very
well (you could do it with ad-hoc table() constructs, perhaps).
The canonical way to create new selects out of old ones that are different
is to use clause transformation.In this case it would be:
t1 = Table('summary_table', ...)
t2 = Table('summary_table_xyz', ...)
def replace(obj):
if obj is t1:
return t2
elif obj in t1.c:
return t2.c[obj.key]
else:
return None
from sqlalchemy.sql.visitors import replacement_traverse
new_select =
[sqlalchemy] Re: SQL Expression lang; change table in from clause after query construction; copying queries; printing queries with filled params
Thank you for the more detailed explanation! I will do some
experiments with it!
Gregg
On Aug 4, 12:39 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 4, 2010, at 1:26 PM, Gregg Lind wrote:
Thanks for the advice!
One minor nit. At least in my experience, str(bound query) doesn't
fill the params, or do quoting properly. Here is a demonstration:
fake_table = Table(
'faketable', metadata,
Column('ts',Integer, index=True, nullable=False),
Column('url',String, index=True, nullable=False),
Column('hits',Integer, nullable=False),
PrimaryKeyConstraint('ts','url','hits',name='summary_pk'),
)
s = fake_table.c
q = select([
s.url,
s.ts,
]).\
where(s.url == bindparam(url)).\
where(s.ts == bindparam(ts)).\
where(s.hits 100)
assert fake_table.metadata.bind.name == 'postgresql' #it's bound
assert str(q) == \
SELECT faketable.url, faketable.ts
FROM faketable
WHERE faketable.url = %(url)s AND faketable.ts = %(ts)s AND
faketable.hits %(hits_1)s
As you can see the 'url' isn't quoted, which is a problem!
But, that is exactly what is sent to the DBAPI. The quoting happens at
the earliest in the DBAPI layer. Some DBAPIs don't ever quote anything,
the binds are sent separately for some backends and the database server
itself handles interpolation internally. If you turn on your PG logs to
log SQL, you'd see the quoting affair is pretty unpleasant so its critical
that DBAPIs handle this.
The parameters are available from the compiled object as the params
collection.
Also, the
(hits_1) paremeter isn't filled in, even though it's already
determined.
What I would ideally like to see is this:
whatwould_happen(q,**some_dict):
SELECT faketable.url, faketable.ts
FROM faketable
WHERE faketable.url = 'http://mypage.com/index.html'AND
faketable.ts = 1829292929 AND faketable.hits 100
If I had this string repr with filled params, I could just a string
sub / regex,
and go all the way into hackery!
On Aug 4, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 4, 2010, at 10:34 AM, Gregg Lind wrote:
suppose:
summary_table = Table(
'summary', metadata,
Column('ts',Integer, index=True, nullable=False),
Column('url',String, index=True, nullable=False),
Column('hits',Integer, nullable=False),
PrimaryKeyConstraint('ts','url','hits',name='summary_pk'),
)
And a query like:
s = summary_table.c
q = select([
s.url,
s.ts,
]).\
where(s.site == bindparam(url)).\
where(s.ts == bindparam(ts)
* how to 'copy' a query. copy.copy(q) seems to be inadequate
select() has a _generate() method that is used internally for generative
operations. But select() supports the generative interface specifically
so that you can treat it as an immutable structure, and copying should not
be necessary (I use it occasionally when I want to attach some additional
state to a select and not affect the original, though that is already a
hacky situation).
* how to print it, with params filled in. str(q) isn't quite enough.
(I know this has been covered before, but I can't seem to find it,
and
if it's not in the docs, it should be!). The query is bound to an
engine already. My desired goal is to see the actual sql (with
filled quoted params) that would get sent to the engine. If I had
this, I could always just use a regex to change the table.
if the query is bound to an engine, meaning, its against a Table who's
MetaData is bound to the engine, then str(q) will invoke the compiler for
that engine's dialect and you will get the exact SQL that would be
emitted. If not, you call q.compile(bind=myengine) or
q.compile(dialect=somedialect).
* how to change the table being called. My specific subcase is for an
inherited
table. I tried this, but it seems very dirty:
q._froms[0].name = 'summary_1279234800'
Is there a general method for that I should use? (Also, this will
be fine
if I can get the 'copy' business to work.
This is not the right way to go. select()s are really not designed for
mutation, even though they have some mutative capabilities (which is
mostly for performance reasons). If you want a select that is against
some other table, you need a new select() object. Additionally, above
you're even modifying your Table construct, which, if you have a typical
case with Table objects declared at the module level, definitely isn't
going to work very well (you could do it with ad-hoc table() constructs,
perhaps).
The canonical way to create new selects out of old ones that are
different is to use clause transformation. In this case it would be:
t1 = Table('summary_table', ...)
t2 = Table('summary_table_xyz', ...)
def
[sqlalchemy] Problems with the relation one to many
Hello, I got this error when I've tried to relate some classes: UnmappedClassError: Class 'zeppelinlib.user.UserTest.User' is not mapped I don't get errors when I have relation many-to-many. This is my file where I store all the User classes. user_channels = Table( user_channels, metadata, Column(user_id, Integer, ForeignKey(users.id)), Column(channel_id, Integer, ForeignKey(Channel.id)) ) group_permissions = Table( group_permissions, metadata, Column(group_id, Integer, ForeignKey(user_groups.id)), Column(permission_id, Integer, ForeignKey(Permission.id)) ) class User(rdb.Model): Represents the user rdb.metadata(metadata) rdb.tablename(users) id = Column(id, Integer, primary_key=True) name = Column(name, String(50)) email = Column(email, String(50)) group_id = Column(group_id, Integer, ForeignKey(user_groups.id)) channels = relation(Channel, secondary=user_channels, backref=channels) class UserGroup(rdb.Model): Represents a group of users with the same features rdb.metadata(metadata) rdb.tablename(user_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(50)) users = relation(User, backref=users) permissions = relation(Permission, secondary=group_permissions, backref=permissions) How can I solve it? Thanks in advance! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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] joined queries across databases and declarative_base
Hello!
I have two classes that are mapped to tables from different databases.
The classes are defined with declarative_base. I can query each class
individually, but a joined query fails. Here is an example:
from sqlalchemy.orm import
sessionmaker
import sqlalchemy as
sa
from sqlalchemy.ext.declarative import
declarative_base
people_engine =
sa.create_engine('sqlite://')
Base1 =
declarative_base(bind=people_engine)
class
People(Base1):
__tablename__ = 'People'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String)
def __init__(self, name):
self.name = name
def __repr__(self):
return self.name
Base1.metadata.create_all()
email_engine = sa.create_engine('sqlite://')
Base2 = declarative_base(bind=email_engine)
class Email(Base2):
__tablename__ = 'Email'
id = sa.Column(sa.Integer, primary_key=True)
email = sa.Column(sa.String)
people_id = sa.Column(sa.Integer, sa.ForeignKey(People.id))
People = sa.orm.relationship(People, backref='Emails')
def __init__(self, email):
self.email = email
def __repr__(self):
return self.email
Base2.metadata.create_all()
session = sessionmaker(binds={Email:email_engine,
People:people_engine})()
# add some data
p1 = People('Joe')
p1.Emails = [Email('j...@gmail.com'), Email('j...@hotmail.com')]
session.add(p1)
p2 = People('Mary')
p2.Emails = [Email('m...@gmail.com'), Email('m...@yahoo.com')]
session.add(p2)
p3 = People('Pat')
p3.Emails = [Email('p...@hotmail.com')]
session.add(p3)
session.commit()
# try some queries
# these queries run fine
print session.query(Email).filter(Email.email.like('%gmail%')).all()
print session.query(People).filter(People.name.like('%a%')).all()
# this query fails with the error message:
# sqlalchemy.exc.OperationalError: (OperationalError) no such table:
People u'SELECT Email.id
# AS Email_id, Email.email AS Email_email, Email.people_id
AS Email_people_id
# FROM Email JOIN People ON People.id = Email.people_id
\nWHERE People.name = ?' ('Mary',)
print
session.query(Email).join(People).filter(People.name=='Mary').all()
When the same DB is used for the two classes, everything runs ok,
including the joined query. I believe this is an example of vertical
partitioning. Is there anything I'm missing?
Thanks!
--
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] strange error with dynamic_loader
It seems as though attributes which are dynamic_loaders cannot be told
to eagerly load subattributes which are themselves dynamic_loaders.
IE, A has dynamic_loader for instances of B, which has dynamic_loader
for instances of C.
Assuming we have an instance of A, this works fine:
query = a_instance.b # gimme instances of B, query-like
for b_instance in query:
# do stuff, possibly accessing b_instance.c
However, if I do this:
query = a_instance.b # gimme instances of B, query-like
query = query.options(sa_orm.eagerload('c'))
Then the following is broken:
for b_instance in query:
with this error:
for b_instance in query:
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py,
line 1676, in instances
rows = [process[0](row, None) for row in fetch]
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 2234, in _instance
populate_state(state, dict_, row, isnew, only_load_props)
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 2113, in populate_state
populator(state, dict_, row)
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/strategies.py,
line 1109, in new_execute
'append_without_event')
File /usr/lib64/python2.6/site-packages/sqlalchemy/util.py, line
1206, in __init__
self._data_appender = getattr(data, via)
AttributeError: 'list' object has no attribute 'append_without_event'
This is with sqlalchemy 0.6.3
--
See, when the GOVERNMENT spends money, it creates jobs; whereas when
the money is left in the hands of TAXPAYERS, God only knows what they
do with it. Bake it into pies, probably. Anything to avoid creating
jobs. - Dave Barry?
Jon
--
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: Problems with the relation one to many
Solved it. I'm using grok as CMS and didn't realize I have to grok every component every time when I use it. That's why I got that error. Thanks anyway! On Aug 4, 4:27 pm, Alvaro Reinoso alvrein...@gmail.com wrote: Hello, I got this error when I've tried to relate some classes: UnmappedClassError: Class 'zeppelinlib.user.UserTest.User' is not mapped I don't get errors when I have relation many-to-many. This is my file where I store all the User classes. user_channels = Table( user_channels, metadata, Column(user_id, Integer, ForeignKey(users.id)), Column(channel_id, Integer, ForeignKey(Channel.id)) ) group_permissions = Table( group_permissions, metadata, Column(group_id, Integer, ForeignKey(user_groups.id)), Column(permission_id, Integer, ForeignKey(Permission.id)) ) class User(rdb.Model): Represents the user rdb.metadata(metadata) rdb.tablename(users) id = Column(id, Integer, primary_key=True) name = Column(name, String(50)) email = Column(email, String(50)) group_id = Column(group_id, Integer, ForeignKey(user_groups.id)) channels = relation(Channel, secondary=user_channels, backref=channels) class UserGroup(rdb.Model): Represents a group of users with the same features rdb.metadata(metadata) rdb.tablename(user_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(50)) users = relation(User, backref=users) permissions = relation(Permission, secondary=group_permissions, backref=permissions) How can I solve it? Thanks in advance! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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] strange error with dynamic_loader
On Aug 4, 2010, at 5:31 PM, Jon Nelson wrote:
It seems as though attributes which are dynamic_loaders cannot be told
to eagerly load subattributes which are themselves dynamic_loaders.
that is correct. dynamic loaders are not backed by in-memory collections, and
no alternate loader strategies can be applied to them (last line of
http://www.sqlalchemy.org/docs/mappers.html#dynamic-relationship-loaders ).
query = a_instance.b # gimme instances of B, query-like
query = query.options(sa_orm.eagerload('c'))
Then the following is broken:
for b_instance in query:
with this error:
for b_instance in query:
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py,
line 1676, in instances
rows = [process[0](row, None) for row in fetch]
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 2234, in _instance
populate_state(state, dict_, row, isnew, only_load_props)
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 2113, in populate_state
populator(state, dict_, row)
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/strategies.py,
line 1109, in new_execute
'append_without_event')
File /usr/lib64/python2.6/site-packages/sqlalchemy/util.py, line
1206, in __init__
self._data_appender = getattr(data, via)
AttributeError: 'list' object has no attribute 'append_without_event'
its broken in that there's a nasty exception. There should be a nice clean
exception saying you can't do that.ticket #1864 is added.
This is with sqlalchemy 0.6.3
--
See, when the GOVERNMENT spends money, it creates jobs; whereas when
the money is left in the hands of TAXPAYERS, God only knows what they
do with it. Bake it into pies, probably. Anything to avoid creating
jobs. - Dave Barry?
Jon
--
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] inheritance and column_property() - subqueries are being limited
I have a single-table inheritance setup:
class Device(Base):
__tablename__ = 'devices'
devtype = Column(Unicode(20), nullable = False)
mac = Column(Unicode(128), primary_key = True)
...
class PC(Device):
__mapper_args_ = {'polymorphic_identity':u'PC'}
switch_mac = Column(Unicode(128), ForeignKey(mac), nullable = True) #
ignore any typos here - this is munged code
class Switch(Device):
__mapper_args_ = {'polymorphic_identity':u'Switch'}
...
pc_ct_subq = select([func.count(PC.mac)]).where(PC.switch_mac ==
Switch.mac).as_scalar()
Switch.pc_ct = column_property(pc_ct_subq)
When I didn't have the inheritance set up (when Switch and PC were separate
tables and separate objects), pc_ct worked fine. Now, however, it's failing
- I think because the inheritance is appending AND devices.devtype IN 'AP'
to the initial load query since it's trying to bring up just devices of type
'Switch'.
How do I get around this? I just want Switch to have an attribute that
represents the number of PCs that are associated to it (where the PC's
switch_mac equals the Switch's mac).
Thanks,
S.
--
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: inheritance and column_property() - subqueries are being limited
...and I've figured out a workaround:
in Switch(Device):
pcs = relationship('PC', primaryjoin = 'Switch.mac ==
PC.switch_mac', lazy='dynamic')
Now, switch.pcs.count() works :)
S.
On Aug 4, 5:00 pm, Zippy P sqlalchemyu...@gmail.com wrote:
I have a single-table inheritance setup:
class Device(Base):
__tablename__ = 'devices'
devtype = Column(Unicode(20), nullable = False)
mac = Column(Unicode(128), primary_key = True)
...
class PC(Device):
__mapper_args_ = {'polymorphic_identity':u'PC'}
switch_mac = Column(Unicode(128), ForeignKey(mac), nullable = True) #
ignore any typos here - this is munged code
class Switch(Device):
__mapper_args_ = {'polymorphic_identity':u'Switch'}
...
pc_ct_subq = select([func.count(PC.mac)]).where(PC.switch_mac ==
Switch.mac).as_scalar()
Switch.pc_ct = column_property(pc_ct_subq)
When I didn't have the inheritance set up (when Switch and PC were separate
tables and separate objects), pc_ct worked fine. Now, however, it's failing
- I think because the inheritance is appending AND devices.devtype IN 'AP'
to the initial load query since it's trying to bring up just devices of type
'Switch'.
How do I get around this? I just want Switch to have an attribute that
represents the number of PCs that are associated to it (where the PC's
switch_mac equals the Switch's mac).
Thanks,
S.
--
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: declarative autoloading table class with composite foreign/primary key
Michael
thanks very much for your helpful advice - the problem seems to
actually involve the autoloading of the table in question.
The table structure is as follows:
CREATE TABLE wcs (
image_id INTEGER NOT NULL,
amp INTEGER NOT NULL,
ctype1 TEXT,
(other column defs deleted)
PRIMARY KEY (image_id, amp),
FOREIGN KEY (image_id, amp) REFERENCES science_amp
ON DELETE NO ACTION
ON UPDATE CASCADE)
and the class definition is as follows:
class Wcs(skymapper_db.db.TableBase):
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey, ForeignKeyConstraint
from sqlalchemy.orm import relationship, backref
__tablename__ = 'wcs'
__table_args__ = {'autoload':True}
image_id = Column(Integer, primary_key=True)
amp = Column(Integer, primary_key=True)
def __init__(self):
from sqlalchemy import ForeignKeyConstraint
self.__table__.append_constraint(ForeignKeyConstraint(['image_id',
'amp'], ['science_amp.image_id', 'science_amp.amp']))
def __repr__(self):
return Wcs(%s, %s) % (self.image_id, self.amp)
If I attempt to instantiate the class as defined, I get:
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 disable the autoloading, there is no problem
PS: sqlalchemy v0.6.3, Python 2.7, PostgreSQL 8.4.1
Thanks again - Jon
On Aug 4, 2:06 pm, Michael Bayer mike...@zzzcomputing.com wrote:
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
Re: [sqlalchemy] Re: declarative autoloading table class with composite foreign/primary key
On Aug 4, 2010, at 9:05 PM, jgs9000 wrote:
Michael
thanks very much for your helpful advice - the problem seems to
actually involve the autoloading of the table in question.
so there's a small bug that is easy to fix, that is ticket #1865 and it is
fixed in r742bd985b4e0, latest tip, so at the very least the code you have
(minus your __init__ method) will work.
But, if you are on Postgresql, and you're using reflection, there is absolutely
no reason to specify image_id and amp explicitly, nor is there a need to
specify the composite foreign key constraint - all of that will be reflected.
Postgresql reflection is very complete and will pull all those details in for
you. Perhaps though you're using reflection just to pull in extra columns.
I hardly ever use reflection for non-trivial applications.
The __init__ method of a declarative class is not where you'd put things
related to the configuration of the mapped table. A Python class's __init__
method is called for each instantaition of the object, and in the case of an
ORM a mapped class instantiation corresponds to a row in the mapped table.
You can keep the ForeignKeyConstraint in the __table_args__ now (even though
like I said none of that should be needed), but if you were to use
append_constraint(), you'd do that outside of the class definition, right after
the class has been declared.
The table structure is as follows:
CREATE TABLE wcs (
image_id INTEGER NOT NULL,
amp INTEGER NOT NULL,
ctype1 TEXT,
(other column defs deleted)
PRIMARY KEY (image_id, amp),
FOREIGN KEY (image_id, amp) REFERENCES science_amp
ON DELETE NO ACTION
ON UPDATE CASCADE)
and the class definition is as follows:
class Wcs(skymapper_db.db.TableBase):
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey, ForeignKeyConstraint
from sqlalchemy.orm import relationship, backref
__tablename__ = 'wcs'
__table_args__ = {'autoload':True}
image_id = Column(Integer, primary_key=True)
amp = Column(Integer, primary_key=True)
def __init__(self):
from sqlalchemy import ForeignKeyConstraint
self.__table__.append_constraint(ForeignKeyConstraint(['image_id',
'amp'], ['science_amp.image_id', 'science_amp.amp']))
def __repr__(self):
return Wcs(%s, %s) % (self.image_id, self.amp)
If I attempt to instantiate the class as defined, I get:
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 disable the autoloading, there is no problem
PS: sqlalchemy v0.6.3, Python 2.7, PostgreSQL 8.4.1
Thanks again - Jon
On Aug 4, 2:06 pm, Michael Bayer mike...@zzzcomputing.com wrote:
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
[sqlalchemy] create_all() fails silently
Can someone tell me why this code won't create any tables? The tables are defined in another file that calls declarative_base(). I presume the problem is that it doesn't know which tables to create. If so, how do I tell it what tables to create? Base = declarative_base() database = 'sqlite:///convert/db.sqlite' engine = create_engine(database, echo=True) metadata = Base.metadata metadata.create_all(engine) # Does nothing, says nothing Session = sessionmaker() Session.configure(bind=engine) Thanks, Michael -- 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] create_all() fails silently
On Wed, Aug 4, 2010 at 9:39 PM, Michael Hipp mich...@hipp.com wrote: Can someone tell me why this code won't create any tables? The tables are defined in another file that calls declarative_base(). I presume the problem is that it doesn't know which tables to create. If so, how do I tell it what tables to create? Base = declarative_base() database = 'sqlite:///convert/db.sqlite' engine = create_engine(database, echo=True) metadata = Base.metadata metadata.create_all(engine) # Does nothing, says nothing Session = sessionmaker() Session.configure(bind=engine) Thanks, Michael Well, metadata here doesn't refer to the metadata that holds table definitions. What about something like import otherfile Base = otherfile.Base# assuming you use Base = declarative_base() in otherfile then continue as your in sample from database =. This should give you access to metadata from the other file. -- 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.
