[sqlalchemy] Help on Counting Please
Hi All Please excuse this relatively noob question, but I can not for the life of me find the answer in docs. (Probably because I don't know what I'm looking for). I have a table with two columns A and B. A can have many duplicate values. e.g. A B 1 a 1 b 1 c 1 d 2 f 2 g 3 z I want to have a result that returns all the values of A, and how many times they appear in the table. Sample result from above would be; 14 22 31 Clearly, I'm no SQL wizz, so I have the added issue of not knowing the raw SQL, let alone SQLAlchemy's take on it. If you can provide the answer using table.select() type query, rather than the ORM equivalent it would be most appreciated. :-) Thanks in anticipation. Cheers Warwick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Help on Counting Please
Hi, Have you tried: select A, count(*) from the_table group by A On Wed, Oct 23, 2013 at 4:17 PM, Warwick Prince warwi...@mushroomsys.com wrote: Hi All Please excuse this relatively noob question, but I can not for the life of me find the answer in docs. (Probably because I don't know what I'm looking for). I have a table with two columns A and B. A can have many duplicate values. e.g. A B 1 a 1 b 1 c 1 d 2 f 2 g 3 z I want to have a result that returns all the values of A, and how many times they appear in the table. Sample result from above would be; 14 22 31 Clearly, I'm no SQL wizz, so I have the added issue of not knowing the raw SQL, let alone SQLAlchemy's take on it. If you can provide the answer using table.select() type query, rather than the ORM equivalent it would be most appreciated. :-) Thanks in anticipation. Cheers Warwick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Help on Counting Please
Hi Anh
Thanks for clearing my head. I had devised considerably more complex attempts!
So, the answer is;
table.select().group_by(table.c.A).with_only_columns([table.c.A,
func.count(1).label('count')]).execute().fetchall()
:-)
n 23/10/2013, at 7:29 PM, anh le anh...@gmail.com wrote:
Hi,
Have you tried:
select A, count(*) from the_table group by A
On Wed, Oct 23, 2013 at 4:17 PM, Warwick Prince
warwi...@mushroomsys.com wrote:
Hi All
Please excuse this relatively noob question, but I can not for the life of
me find the answer in docs. (Probably because I don't know what I'm looking
for).
I have a table with two columns A and B. A can have many duplicate
values. e.g.
A B
1 a
1 b
1 c
1 d
2 f
2 g
3 z
I want to have a result that returns all the values of A, and how many times
they appear in the table.
Sample result from above would be;
14
22
31
Clearly, I'm no SQL wizz, so I have the added issue of not knowing the raw
SQL, let alone SQLAlchemy's take on it.
If you can provide the answer using table.select() type query, rather than
the ORM equivalent it would be most appreciated. :-)
Thanks in anticipation.
Cheers
Warwick
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] is ForeignKey() the only ORM feature (outside of table setup) that requires table knowledge
this has just confused me for a while , and constantly trips me up... class FirstTable() __tablename__ = 'first_table' id = Column(Integer, primary_key=True) class SecondTable() __tablename__ = 'second_table' id = Column(sa.Integer, primary_key=True) first_table_id = Column(sa.Integer, ForeignKey(first_table.id) ) first_table = relationship(FirstTable, primaryjoin=FirstTable.id == SecondTable.first_table_id) was there a specific reason why the string in `ForeignKey` is for the table+column, and not an object, like in `relationship` ? ( i know that `ForeignKey` can take an object too , i'm just wondering about the string ) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Getting the unmodified version of an object without losing changes
I have a situation where an object in the ORM performs some calculations and other assorted checks. One of these situations is checking to see the difference between the original value of an attribute and the current value -- in particular, some of the validation going on should prohibit a change that causes a particular check to fail, but only if that check wasn't already failing (e.g. some legacy) I see that I can use inspect() to get the history of attributes, but that quickly gets unwieldy when working with a large number of attributes. What I'd love to be able to do is something like: def do_something(self): orig = get_original_version(self) delta = self.value - orig.value # Is there a simple way to accomplish this? Right now I'm doing something like: orig_value, = inspect(self).attrs.value.history.non_added or (default_value, ) which seems messy and only works for scalar values. -- Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Getting the unmodified version of an object without losing changes
On Oct 23, 2013, at 8:06 PM, Daniel Grace thisgenericn...@gmail.com wrote:
I have a situation where an object in the ORM performs some calculations and
other assorted checks. One of these situations is checking to see the
difference between the original value of an attribute and the current value
-- in particular, some of the validation going on should prohibit a change
that causes a particular check to fail, but only if that check wasn't already
failing (e.g. some legacy)
I see that I can use inspect() to get the history of attributes, but that
quickly gets unwieldy when working with a large number of attributes. What
I'd love to be able to do is something like:
def do_something(self):
orig = get_original_version(self)
delta = self.value - orig.value
#
Is there a simple way to accomplish this? Right now I'm doing something like:
orig_value, = inspect(self).attrs.value.history.non_added or (default_value, )
which seems messy and only works for scalar values.
a phrase like “unwieldy with a large number of attributes” doesn’t make sense
to me, is this a collection of attributes in a list or something, in which case
iterate through that list and call getattr(attrs, attrname).history? if an
API is verbose, just put a function around it that does what you need. if as
implied in the subject that some side effect is occurring that’s causing a
flush to occur hence “losing changes”, probably run your operations within a
session.no_autoflush: block
(http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html?highlight=no_autoflush#sqlalchemy.orm.session.Session.no_autoflush).
history works for collections as well. this is the fine grained API to get at
attribute history before a flush and attrs supports iteration through all
available attributes, so just use a loop like this:
def delta(obj):
d = {}
for attr in inspect(obj).attrs:
if attr.history.has_changes():
d[attr.key] = attr.history.added, attr.history.deleted
return d
im not sure what exactly you’d like to see for collections, the above will just
put a tuple of (things added), (things removed) for that entry in the
dictionary.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
