[sqlalchemy] sqlalchemy aggregation query
hi guys, I'm new in sqlalchemy and I need some help. I want make a simple aggregation query for a game chart, so, I need max score for a player, player name and row number (for chart position) this is the query in t-sql (mssql): select row_number() over (order by max(score) desc), max(score), id, player_name from mychart group by id, player_name how can I do that in sqlalchemy? thank you -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Help using SqlSoup with database views
I'd like to use SqlSoup with an existing database that contains views.
Accessing a table goes swimmingly, but accessing a view results in
PKNotFoundError: table '[viewname]' does not have a primary key
defined...
Do I correctly infer that SqlSoup does not work with database views (by
default, at least)? I've been unable to find anything directly relevant
on Google, SO, or the SqlAlchemy mailing list. If you were faced with
this, how would you proceed if you wanted to access non-updatable views?
I'm new to SQLAlchemy and SQLSoup.
Here's a specific example:
from sqlalchemy.ext.sqlsoup import SqlSoup
u = SqlSoup('postgresql+psycopg2://pub...@unison-db.org:5432/unison')
seq = u.pseq.filter(u.pseq.pseq_id==76).all() # okay
aliases = u.pseqalias.filter(u.pseqalias.pseq_id==76).all()
PKNotFoundError: table 'pseqalias' does not have a primary key
defined...
This is a public database. You can run the equivalent queries using
psql:
psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pseq where
pseq_id=76'
psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pseqalias
where pseq_id=76'
This question is also posted at http://stackoverflow.com/q/6286704/342839
Thanks,
Reece
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Help using SqlSoup with database views
I'd like to use SqlSoup with an existing database that contains views.
Accessing a table goes swimmingly, but accessing a view results in
PKNotFoundError: table '[viewname]' does not have a primary key
defined...
Do I correctly infer that SqlSoup does not work with database views (by
default, at least)? I've been unable to find anything directly relevant
on Google, SO, or the SqlAlchemy mailing list. If you were faced with
this, how would you proceed if you wanted to access non-updatable views?
I'm new to SQLAlchemy and SQLSoup.
Here's a specific example:
from sqlalchemy.ext.sqlsoup import SqlSoup
u = SqlSoup('postgresql+psycopg2://pub...@unison-db.org:5432/unison')
seq = u.pseq.filter(u.pseq.pseq_id==76).all() # okay
aliases = u.pseqalias.filter(u.pseqalias.pseq_id==76).all()
PKNotFoundError: table 'pseqalias' does not have a primary key
defined...
This is a public database. You can run the equivalent queries using
psql:
psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pseq where
pseq_id=76'
psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pseqalias
where pseq_id=76'
This question is also posted at http://stackoverflow.com/q/6286704/342839
Thanks,
Reece
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Declaring compound primary key on reflective model
Hi all -- I'm new on pylons, coming from a django background. I'm working on a mapfish project. I'd like to autoload a model based on a db table, but in doing so I get the error could not assemble any primary key columns for mapped table. The table itself is a postgres view with no declared primary key column, so it makes sense that no primary key columns are detected. I figure it is possible to define which columns to use as the primary key in the __table_args__ dict, but I have yet to figure out exactly how. Tips, please! Cody -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Declaring compound primary key on reflective model
A related query: reflecting two tables, and wanting to identity a one- to-one relation based on a mutual id field on the model class. Cody On Jun 10, 11:25 am, Cody Django codydja...@gmail.com wrote: Hi all -- I'm new on pylons, coming from a django background. I'm working on a mapfish project. I'd like to autoload a model based on a db table, but in doing so I get the error could not assemble any primary key columns for mapped table. The table itself is a postgres view with no declared primary key column, so it makes sense that no primary key columns are detected. I figure it is possible to define which columns to use as the primary key in the __table_args__ dict, but I have yet to figure out exactly how. Tips, please! Cody -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Declaring compound primary key on reflective model
On Jun 10, 2011, at 2:25 PM, Cody Django wrote:
Hi all -- I'm new on pylons, coming from a django background. I'm
working on a mapfish project.
I'd like to autoload a model based on a db table, but in doing so I
get the error could not assemble any primary key columns for mapped
table.
The table itself is a postgres view with no declared primary key
column, so it makes sense that no primary key columns are detected.
I figure it is possible to define which columns to use as the primary
key in the __table_args__ dict, but I have yet to figure out exactly
how.
Tips, please!
I do the same thing:
newtable = Table(name,
meta,
*props,
autoload=True
)
where props = ColumnCollection(Column('id',Integer,primary_key=True))
SQLAlchemy overwrites the autoloaded info with the passed-in column info.
Cheers,
M
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] standard way of initializing an empty clause?
I want to construct a clause by anding the previous clause with new clauses, and I was wondering if there was a standard way to initialize an empty clause. First, here's some code that illustrates the general idea of what I'm trying to do: clause = True if cond1: clause = and_(clause, cond1_clause) if cond2: clause = and_(clause, cond2_clause) and then I execute the select(cols, clause) query. Is there some better value to initialize clause to? From experimentation, None as an initial value also does what I want, but initially setting clause to None then might lead to confusion since it's False in Python and (roughly, correct me if I'm wrong) True in SQLAlchemy. Also, is there a good value to initialize clause to in order for code of the following sort to work? Or is this a bad practice? clause = some_init_value if cond1: clause = cond1_clause if cond2: clause = cond2_clause Because the and_ and or_ methods special case the single argument case (just passing them through), the simplest thing I've found to work is the and_(True, True) construct. But that's exceedingly hacky imo. Thanks in advance! --Darren -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Help using SqlSoup with database views
On Jun 9, 2011, at 12:41 AM, Reece Hart wrote:
I'd like to use SqlSoup with an existing database that contains views.
Accessing a table goes swimmingly, but accessing a view results in
PKNotFoundError: table '[viewname]' does not have a primary key
defined...
Do I correctly infer that SqlSoup does not work with database views (by
default, at least)? I've been unable to find anything directly relevant
on Google, SO, or the SqlAlchemy mailing list. If you were faced with
this, how would you proceed if you wanted to access non-updatable views?
I'm new to SQLAlchemy and SQLSoup.
Here's a specific example:
from sqlalchemy.ext.sqlsoup import SqlSoup
u = SqlSoup('postgresql+psycopg2://pub...@unison-db.org:5432/unison')
seq = u.pseq.filter(u.pseq.pseq_id==76).all() # okay
aliases = u.pseqalias.filter(u.pseqalias.pseq_id==76).all()
PKNotFoundError: table 'pseqalias' does not have a primary key
defined...
You would need to pass the columns to be considered part of the primary key to
the underlying mapper, using sqlsoup.map_to(), but unfortunately there is not a
simple interface for that at the moment, since you need the Table object as
well to get at the columns. So until this interface could be improved, for
now it would look like:
metadata = u._metadata
t = Table(pseqaliases, metadata, autoload=True)
u.map_to(pseqaliases, selectable=t, mapper_args={primary_key:[t.c.col1,
t.c.col2]})
This is just the primary_key argument to mapper, there are some examples at
http://www.sqlalchemy.org/docs/orm/mapper_config.html near the top.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] standard way of initializing an empty clause?
On Jun 10, 2011, at 6:15 PM, Darren Yin wrote: I want to construct a clause by anding the previous clause with new clauses, and I was wondering if there was a standard way to initialize an empty clause. First, here's some code that illustrates the general idea of what I'm trying to do: clause = True if cond1: clause = and_(clause, cond1_clause) if cond2: clause = and_(clause, cond2_clause) and then I execute the select(cols, clause) query. There's no slick way to do that right now, we tend to keep it simple: if clause is None: clause = myclause else: clause = clause myclause if you're doing a selection of ifs, clauses = [] if cond1: clauses.append(cond1) if cond2: clauses.append(cond2) result = and_(*clauses) Is there some better value to initialize clause to? From experimentation, None as an initial value also does what I want, but initially setting clause to None then might lead to confusion since it's False in Python and (roughly, correct me if I'm wrong) True in SQLAlchemy. and_() of any scalar value just spits the value back. So and_(None) is just None, but also isn't useful as a builder the way you're trying to do it, though I feel like im misunderstanding since you mention this same thing below Also, is there a good value to initialize clause to in order for code of the following sort to work? Or is this a bad practice? clause = some_init_value if cond1: clause = cond1_clause if cond2: clause = cond2_clause Because the and_ and or_ methods special case the single argument case (just passing them through), the simplest thing I've found to work is the and_(True, True) construct. But that's exceedingly hacky imo. Thanks in advance! --Darren -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
