If that ID is the primary key, then don't bother with a DISTINCT(). Just
select everything from the table. Otherwise you're going to make the
backend select everything , then waste time doing the distinct.
A quick way to confirm would be to just run these 2 commands:
SELECT
That makes sense. Part of my problem is that, as I've mentioned in the
past, I was recently hired. I didn't set anything up, and I still
don't know for sure what I can trust to be unique, or 6 versus 8
characters, or a lot of other small details. That said, SSMS shows the
item ID as a primary key,
On Wednesday, March 9, 2016 at 3:02:05 PM UTC-5, Alex Hall wrote:
>
> Fair enough, thanks. I didn't realize it was such a complex task; I
> figured it was just a matter of passing an argument to distinct() or
> something equally easy.
>
Yeah PostgreSQL is the only db that supports "DISTINCT
Fair enough, thanks. I didn't realize it was such a complex task; I
figured it was just a matter of passing an argument to distinct() or
something equally easy. Speed isn't a huge concern, so I suppose I
could get around this by storing the item numbers I find and then
checking that the row I'm
It would probably be best for you to figure out the correct raw sql you
want, then convert it to SqlAlchemy.
Postgres is the only DB I know of that offers "DISTINCT ON (columns)" --
and even that works a bit awkward.
The query that you want to do isn't actually simple -- there are concerns
On Nov 7, 2011, at 9:16 AM, Paul wrote:
Michael Bayer mike_mp at zzzcomputing.com writes:
sure it does, if you convert it to a SQL token first:
literal(C:\test\testfile.txt).like(Table.path + %)
or even
literal(C:\test\testfile.txt).startswith(Table.path)
Thanks for the quick
thank you very much michael, this is some kind of mind spinning sqla
expression !!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
Hi Michael,
thank you very much for your prompt answer.
What I want to achieve is, counting the number of distinct items,
grouped by user_name
Given
Item1:
item_id = 1
service_id = 'test'
item_class = 'dummy'
Item2:
item_id = 2
service_id = 'other'
item_class = 'dummy'
from sqlalchemy import func, distinct
query(Invoice.user_name,
func.count(distinct(Item.id))).join(Invoice.items).group_by(Invoice.user_name)
On Jan 20, 2011, at 9:51 AM, NiL wrote:
Hi Michael,
thank you very much for your prompt answer.
What I want to achieve is, counting the number
thanks again
but the unicity of Item is guaranteed by the triplet of PK
I can't just discriminate the distinct() based on the item_id only (it
is not unique by itself in my set of data)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to
session.query(Invoice.user_name,
Item).join(Invoice.item).distinct().from_self(Invoice.user_name,
func.count(1)).group_by(Invoice.user_name)
On Jan 20, 2011, at 11:45 AM, NiL wrote:
thanks again
but the unicity of Item is guaranteed by the triplet of PK
I can't just discriminate the
This works:
select([
func.ARRAY(
select([t.c.value])
.where(t.c.id3)
.as_scalar()
)
.label('array_col')
])
- Gulli
On Nov 9, 3:43 pm, Michael Hipp mich...@hipp.com wrote:
Can someone show me the gist of how to construct an SA query that
produces SQL* of the form
SELECT
On 11/9/2010 3:55 PM, Gunnlaugur Briem wrote:
select([
func.ARRAY(
select([t.c.value])
.where(t.c.id3)
.as_scalar()
)
.label('array_col')
])
Thank you! That works swimmingly.
I now know why my search didn't turn up anything as it is evidently one of
those If the
On Nov 16, 5:15 pm, Conor conor.edward.da...@gmail.com wrote:
Tomas Zulberti wrote:
Hi. I am sort of a newbie on SQLAlchemy. Is there a way to do a query
with the ORM, and doing an as on the select.
For example:
class Example(Base):
name = Column(Unicode(512) )
query =
Tomas Zulberti wrote:
Hi. I am sort of a newbie on SQLAlchemy. Is there a way to do a query
with the ORM, and doing an as on the select.
For example:
class Example(Base):
name = Column(Unicode(512) )
query = session.query(Example.name AS foo)
query.all()
Every column
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of kkapron
Sent: 01 October 2009 21:27
To: sqlalchemy
Subject: [sqlalchemy] Select from multiple databases
Hello, I'm a beginner in sqlalchemy and I've got a problem with select
You supplied the join() as a the whereclouse (2nd) parameter to the
select method.
This should work:
select([tags_table.c.name,tags_table.c.id, func.count
(deal_tags_table.c.dealid).label('dealcount')],
from_obj=join(tags_table,
deal_tags_table),group_by=[deal_tags_table.c.dealid])
Regards
I forgot, in the Ctrl+c and ctrl+v, to add that the problem only happens
when using limit and offset with oracle dialect (sorry about that)..
so
self.fields = ' case field1
when 34 then field2
when 94 then field3
when 48 then field4
end
On May 19, 2009, at 10:39 AM, Tiago Becker wrote:
Hi.
Im still trying to use the select object from sqlalchemy, but i
found a strange (bug or not) behavior:
sql = select(columns=[self.fields], from_obj=self.tables,
whereclause=self.where, bind=self.req.cfg.engine, order_by= ' 1 ')
this indicates a None is being sent as a column somewhere.
I'm just not passing the order_by clause...
I dont see the SQLAlchemy expression you're dealing with here. the
symptom you describe is when placing a FROM object in the columns
clause of a SELECT, the object is also added to the FROM
send along full reproducing test cases and that will reveal all.
On May 19, 2009, at 11:36 AM, Tiago Becker wrote:
this indicates a None is being sent as a column somewhere.
I'm just not passing the order_by clause...
I dont see the SQLAlchemy expression you're dealing with here. the
Thanks that ended up working.
so a query like this would do it:
session.query(JobInfo).filter(~JobInfo.Job.has()
--
David Gardner
Pipeline Tools Programmer, Sid the Science Kid
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
You
On Apr 6, 2009, at 8:11 PM, David Gardner wrote:
I am trying to get all of the rows in table A that do not have a match
in table B. I believe the problem is that I am using a text foreign
key,
and for the rows I am looking for the field will still have a value,
it
just won't match
Sanjay ha scritto:
I got that the obvious way is just writing the string query, i.e.
query.filter(col IN SELECT col FROM some_table)
something like this:
query.filter( col.in_( select([col],...).correlate(None) ))
Glauco
--~--~-~--~~~---~--~~
You
Sanjay wrote:
Hi,
I want to write something like
query.filter(BizObj.col.in_(SELECT col FROM some_table)).
Could not find out how to do it. The code on neither the above nor the
below worked.
query.filter(BizObj.col1.in_(sqlalchemy.sql.text(SELECT col1 FROM
sometable))).
in_() is
On Wed, Mar 18, 2009 at 11:00 AM, Michael Bayer mike...@zzzcomputing.comwrote:
Sanjay wrote:
Hi,
I want to write something like
query.filter(BizObj.col.in_(SELECT col FROM some_table)).
Could not find out how to do it. The code on neither the above nor the
below worked.
col.in_(select([MyClass.col2]))
Mike Conley wrote:
On Wed, Mar 18, 2009 at 11:00 AM, Michael Bayer
mike...@zzzcomputing.comwrote:
Sanjay wrote:
Hi,
I want to write something like
query.filter(BizObj.col.in_(SELECT col FROM some_table)).
Could not find out how to do it. The
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of John Hunter
Sent: 11 November 2008 01:54
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: select where field=max(field)
On Mon, Nov 10, 2008 at 11:10 AM, Michael Bayer
[EMAIL
On Tue, Nov 11, 2008 at 4:31 AM, King Simon-NFHD78
[EMAIL PROTECTED] wrote:
Which is pretty much the query we wanted, apart from the names. I hope
it works in your original example as well!
This worked great -- and I learned a bunch of useful sql and
sqlalchemy tricks along the way. Many
On Mon, Nov 10, 2008 at 4:33 AM, King Simon-NFHD78
[EMAIL PROTECTED] wrote:
I'm no SQL expert, so please take this with a pinch of salt, but as far
as I know, conditions in the 'WHERE' clause of an SQL statement are
applied BEFORE any grouping, so you can't use grouping functions (such
as
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of John Hunter
Sent: 10 November 2008 14:07
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: select where field=max(field)
On Mon, Nov 10, 2008 at 4:33 AM, King Simon-NFHD78
On Nov 10, 2008, at 12:08 PM, John Hunter wrote:
On Mon, Nov 10, 2008 at 10:05 AM, King Simon-NFHD78
[EMAIL PROTECTED] wrote:
Actually, the section after that (Using Subqueries) probably does
something very close to what you want. What's the result of these
lines:
q1 =
On Mon, Nov 10, 2008 at 10:05 AM, King Simon-NFHD78
[EMAIL PROTECTED] wrote:
Actually, the section after that (Using Subqueries) probably does
something very close to what you want. What's the result of these lines:
q1 = (session.query(Snapshot.strategy, Snapshot.symbol, sum_pnl)
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of John Hunter
Sent: 08 November 2008 05:09
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: select where field=max(field)
[SNIP]
Here is a query that lists the sum(pnl) for each
On Mon, Nov 10, 2008 at 11:10 AM, Michael Bayer
[EMAIL PROTECTED] wrote:
you need an extra tuple on the join, query.join((q1, s.s==q1.c.s))
This gets past the syntax error, but does not produce the right
results. I had to take some time off today to work on other problems,
but am now
Theres a good tutorial on the topic of GROUP BY from a SQL
perspective, here:
http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/20/60261.aspx
in this case you probably want
query.filter(Snapshot.totalqty==func.max(Snapshot.totalqty).select()).
On Nov 7, 2008, at 3:22 PM, John Hunter
If you are okay with only getting one record in the case of ties you can do
session.query(Snapshot).order_by(Snapshot.totalqty.desc()).first()
On Fri, Nov 7, 2008 at 12:22 PM, John Hunter [EMAIL PROTECTED] wrote:
I am having trouble writing a sqlalchemy query which selects all rows
where a
On Fri, Nov 7, 2008 at 3:57 PM, Michael Bayer [EMAIL PROTECTED] wrote:
Theres a good tutorial on the topic of GROUP BY from a SQL
perspective, here:
http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/20/60261.aspx
in this case you probably want
On Sat, Oct 11, 2008 at 10:24 PM, Michael Bayer
[EMAIL PROTECTED] wrote:
We should really fix the MSSQL dialect to not be throwing a
SystemError when a SQL statement is not successfully interpreted.
I guess syntax error would be more appropriate.
I can't see anything wrong with the
On Fri, Oct 10, 2008 at 10:32 AM, Simon [EMAIL PROTECTED] wrote:
The statement looks good in my book...what does system error mean
exactly?
On 10 Okt., 17:15, Lukasz Szybalski [EMAIL PROTECTED] wrote:
Hello,
Could anybody tell me what is wrong with this select statement?
Records is a
Actually, if TRANS_TYPE is a mapped attribute of the RecordClass class
(or whatever its name is), it should be Records.c.TRANS_TYPE. Also,
group_by does not take a list but all individual columns as parameters
(so just omit those []s).
On 10 Okt., 17:38, Lukasz Szybalski [EMAIL PROTECTED] wrote:
group_by does not take a list but all individual columns as parameters
(so just omit those []s).
has fixed the error.
I think the newer version of sqlachemy no longer has Record.c its just
Record.somefield...
What also confused me was the fact that in order to do where zyx =2 in
query you
On Jul 21, 2008, at 7:19 AM, jrpfinch wrote:
Hi
What is the most elegant way of performing a SELECT DISTINCT on a
single column at the ORM level on SQLAlchemy 0.4.6?
It looks like you can do
q=session.query(Order.support_manager).distinct() in the beta
version. This is not supported
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of lilo
Sent: 10 June 2008 17:23
To: sqlalchemy
Subject: [sqlalchemy] select db engine with create_session
can someone tell me how session chooses the right db engine to insert
records in
what DB backend ? often you can just compare to 1 or 0.
On Jun 10, 2008, at 12:03 PM, Jonathan Vanasco wrote:
this should be straightforward, I think...
I'm trying to do
select email_address from user where is_verified is not true
( or is not null or is not false )
This part is
On May 24, 2008, at 12:25 AM, Jeff Putsch wrote:
Now, when I add the filter like this:
print NisAccount.query().select_from(a1.join(a2, (a1.c.eid ==
a2.c.eid) (a1.c.uid != a2.c.uid))).filter(a1.c.eid.in_(s3)
(a1.c.domain_id == 41)).compile()
I get an infinite recursion error.
0.5 may
On May 24, 2008, at 7:00 AM, Michael Bayer wrote:
On May 24, 2008, at 12:25 AM, Jeff Putsch wrote:
Now, when I add the filter like this:
print NisAccount.query().select_from(a1.join(a2, (a1.c.eid ==
a2.c.eid) (a1.c.uid != a2.c.uid))).filter(a1.c.eid.in_(s3)
(a1.c.domain_id ==
On May 23, 2008, at 7:42 PM, Jeff Putsch wrote:
Howdy,
I'm a newbie to sqlalchemy and am having trouble understanding how to
turn selects into objects.
I've got two tables mapped into objects like this:
nis_accounts_table = Table( ... )
nis_users_table = Table( ... )
class
On May 23, 2008, at 5:07 PM, Michael Bayer wrote:
Jeff Putsch wrote:
Then I define some selects and execute them:
s = select([nis_accounts_table, nis_users_table],
from_obj=[nis_accounts_table.join(nis_users_table)]).where(
nis_users_table.c.eid != ''
)
Michael Bayer ha scritto:
On Feb 11, 2008, at 6:36 AM, Glauco wrote:
Hi all,
What's the simplest way for do a simple:
select * from myTable where id in (1,2,3);
I've solved this by using Subquery but final qry isn't pretty as
this one.
Heh, I have the opposite problem, I now find myself typing in_ and == in
interactive query editors
On Feb 13, 2008 3:40 AM, Glauco [EMAIL PROTECTED] wrote:
Michael Bayer ha scritto:
On Feb 11, 2008, at 6:36 AM, Glauco wrote:
Hi all,
What's the simplest way for do a simple:
Is this what you want?
select([my_table.c.my_column], distinct=True)
Barry
- Original Message
From: JamesT [EMAIL PROTECTED]
To: sqlalchemy sqlalchemy@googlegroups.com
Sent: Wednesday, November 7, 2007 2:05:13 AM
Subject: [sqlalchemy] Select entire column
I am looking to filter
I would like something to run on the Query class. It looks like you
used to be able to run select on this class, but the function is
deprecated. If I cannot, I will just use the execute function and run
plain SQL.
On Nov 7, 6:39 am, Barry Hart [EMAIL PROTECTED] wrote:
Is this what you want?
One of the reasons that Query.select() is deprecated is that the way it was
named led to this kind of confusion.
The Query() class is used for ORM operations, and when it's used as mapped
against a table, it's going to give you all the columns from the table by
default. There are ways of defining
Thanks. I have it working in SQL expression language, using text in
execute(). For better modularity and scalabililty, I will probably
move it over to using the select(), join(), etc. functions that are
built in.
On Nov 7, 5:26 pm, Rick Morrison [EMAIL PROTECTED] wrote:
One of the reasons that
On Oct 3, 2007, at 2:46 PM, Paul Kippes wrote:
Is there some other way I can execute a query using a list as a
bound parameter?
res = db.db_con.text('SELECT module_extra_key_name FROM
module_extra_keys
WHERE module_id IN :module_ids').execute({'module_ids': [1,2]})
The above is
Michael Bayer ha scritto:
On May 2, 2007, at 11:23 AM, Glauco wrote:
Example:
create table people (
name text,
surname text,
type CASE 'A','B','C'
)
There is no solution to do for example the simple query based over
the mapper People:
select count(type) from people group by
Michael Bayer ha scritto:
On Apr 24, 2007, at 4:05 PM, Chris Shenton wrote:
Am I being stupid about not seeing the difference -- what keywords and
arguments I can use -- between:
self.session.query(MyClass).select(...)
and
select(...)
these two methods are fundamentally
On May 2, 2007, at 11:23 AM, Glauco wrote:
Example:
create table people (
name text,
surname text,
type CASE 'A','B','C'
)
There is no solution to do for example the simple query based over
the mapper People:
select count(type) from people group by type;
first of all, i dont
Disrupt07 wrote:
I have a table storing users' info.
table: userinfo
columns: name, surname, age, location, ...
I need to query this table using SQLAlchemy's ORM methods (e.g.
select(), select_by(), get_by()). The query should be like
SELECT * FROM userinfo WHERE name LIKE 'Ben%'
Disrupt07 wrote
@Simon
Thanks. But what is your_query? Is it SQLAlchemy or pure SQL?
It is a Query object, as described here:
http://www.sqlalchemy.org/docs/datamapping.html
If you haven't read them yet, I'd recommend working through a tutorial -
I found this one really helpful:
King Simon-NFHD78 ha scritto:
Shouldn't acl.cod_ruolo be inside the [] - part of the first parameter
to 'select'?
The parameters to select are 'columns=None, whereclause=None,
from_obj=[], **kwargs', so your 'and_' part is going in as the from_obj
parameter, and then you are supplying
Bertrand Croq wrote:
hi,
I am currently using sqlalchemy to build SQL queries and it's a fantastic
tool! By now, I am looking for a way to build:
SELECT 'a_fixed_string', atable.col1, atable.col2
FROM atable
using the syntax:
select([XXX, atable.c.col1, atable.c.col2])
but I don't know
Le Mardi 20 Mars 2007 10:38, jose a écrit :
try this:
select([literal('a_fixed_string'), atable.c.col1, atable.c.col2])
Perfect ! Thanks a lot.
--
Bertrand Croq,
Ingénieur Développement
___
Net-ng Tel
use literal_column('fixedstring') in the column clause
On Mar 19, 2007, at 9:38 AM, Bertrand Croq wrote:
hi,
I am currently using sqlalchemy to build SQL queries and it's a
fantastic
tool! By now, I am looking for a way to build:
SELECT 'a_fixed_string', atable.col1, atable.col2
Something like this should work:
select(['count(distinct flow.node_id) as nodes', ip],
from_obj=[flow.join(id)], group_by=[flow.c.src_id], having=['nodes
5'], order_by=[desc('nodes')])
If you wanted to do it w/o text blocks I think you'd have to create a
subselect first to pull the nodes
interesting ! that answers that question (i.e., the question above the assertion statement) changed it to a "continue" in rev 2072.however, your mapping isnt going to work with those textual columns anyway since it cant intelligently construct an aliased select from it, you have to say:s =
Heh, that was easy.
I'm curious now, though, why it can't find the primary key with the
revised select you gave. (Works fine when I manually tell the mapper
what to use.)
On 10/30/06, Michael Bayer [EMAIL PROTECTED] wrote:
interesting ! that answers that question (i.e., the question above
because the available columns of the selectable is always treated
based on the columns declared. the FROM/WHERE is like the backend
to the query and the column clauses are the public interface. heh.
On Oct 30, 2006, at 7:02 PM, Jonathan Ellis wrote:
Heh, that was easy.
I'm curious
69 matches
Mail list logo
