[sqlalchemy] self cross join in sqlalchemy
I am trying to achieve the following query (I think it is called a
cross-join):
SELECT nsm_1.id AS nsm_1_id, sum(costregister_1.costvalue /
currencycross_1.crossvalue) AS costvalue,
max(costregister_1.modifieddate) AS modifieddate
FROM nsm AS nsm_1, nsm AS nsm_2
JOIN costregister AS costregister_1 ON costregister_1.nsm_id =
nsm_2.id
JOIN currencycross AS currencycross_1 ON currencycross_1.crosscurrency
= costregister_1.costcurrency
WHERE nsm_2.lft = nsm_1.lft AND nsm_2.lft = nsm_1.rgt AND
currencycross_1.reference = :reference_1 GROUP BY nsm_1.id
I am nearly there but cannot work out how to perform the join between
the aliased nsm tables:
FROM nsm AS nsm_1, nsm AS nsm_2
Below is the result of the query generated with SQLAlchemy:
SELECT nsm_1.id AS nsm_1_id, sum(costregister_1.costvalue /
currencycross_1.crossvalue) AS costvalue,
max(costregister_1.modifieddate) AS modifieddate
FROM nsm AS nsm_1 JOIN nsm AS nsm_2 ON nsm_2.id = nsm_1.id JOIN
costregister AS costregister_1 ON costregister_1.nsm_id = nsm_2.id
JOIN currencycross AS currencycross_1 ON currencycross_1.crosscurrency
= costregister_1.costcurrency
WHERE nsm_2.lft = nsm_1.lft AND nsm_2.lft = nsm_1.rgt AND
currencycross_1.reference = :reference_1 GROUP BY nsm_1.id
and this is the code that generates the query:
tbl1 = model.aliased(model.NSM)
tbl2 = model.aliased(model.NSM)
tbl3 = model.aliased(model.CostRegister)
tbl4 = model.aliased(model.CurrencyCross)
query = self.session.query(tbl1.id,\
model.func.sum(tbl3.costvalue/
tbl4.crossvalue).label('costvalue'),\
model.func.max(tbl3.modifieddate).label('modifieddate'))\
.join((tbl2, tbl2.id==tbl1.id))\
.join((tbl3, tbl3.nsm_id==tbl2.id))\
.join((tbl4, tbl4.crosscurrency==tbl3.costcurrency))\
.filter(tbl2.left=tbl1.left)\
.filter(tbl2.left=tbl1.right)\
.filter(tbl4.reference==145)\
.group_by(tbl1.id)
I have tried several permutations on the join but it is beyond my
current knowledge how to achieve what I need.
Any assistance would be greatly appreciated.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] design question
Hi to all,
and thanks for the great work with this great tool!
Probably i'm approaching the problem in the wrong way, but i have 2
entity, Contest and Company.
I nedd to invite one or more company to one contest, for every contest
i only need to know how many company and which have been invited. I'm
using declarative_base so these are my 2 classes:
Base = declarative_base()
class Contest(Base):
__tablename__ = 'contests'
id = Column(Integer, primary_key=True)
name = Column(Unicode(20))
def __init__(self, name):
self.name = name
class Company(Base):
__tablename__ = 'companies'
id = Column(Integer, primary_key=True)
name = Column(Unicode(20))
def __init__(self, name):
self.name= name
class Invite(Base):
__tablename__ = 'invites'
id = Column(Integer, primary_key=True)
contest_id = Column(Integer, ForeignKey('contests.id'))
company_id = Column(Integer, ForeignKey('companies.id'))
How should i design the Invite table? should i treat it as a many 2
many relation?
Any help appreciated
Thanks Fabrizio
--
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] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
Hi Michael,
I'm using a manual workaround, so this is only out of curiosity now.
Cf yr point 1:
1. Merging MoviesAndDirectorsAndGenres, with no primary key identifiers set,
sets it as pending in the Session. This now indicates that an INSERT into
all three tables will take place during flush, since the object had no
primary key when being merged.
not quite sure what 'primary key identifiers' are, but am assuming
that you mean explicitly setting the attributes that are mapped to the
join's composite primary key. let's try that:
new = MoviesAndDirectorsAndGenres()
new.movies_movie_id=8 #does not already exist in the movies table
new.movies_title='new movie'
new.directors_director_id=2 #already exists in the directors table
new.genres_genre_id=6 #already exists too
so, the new object now has a primary key - (8,2,6). this is a new pk
for the join, but it is made up of pks from each of the constituent
tables - 2 of which are existing, and 1 of which is new.
The call to merge fails in the same way:
2010-07-22 13:40:46,063 INFO sqlalchemy.engine.base.Engine.0x...de90
SELECT truncated...
FROM movies JOIN movie_directors ON movies.movie_id =
movie_directors.movie_id JOIN directors ON directors.director_id =
movie_directors.director_id JOIN genres ON genres.genre_id =
movies.genre_id) AS mdg
WHERE mdg.movies_movie_id = ? AND mdg.directors_director_id = ? AND
mdg.genres_genre_id = ?
2010-07-22 13:40:46,075 INFO sqlalchemy.engine.base.Engine.0x...de90 (8, 2, 6)
2010-07-22 13:40:46,079 INFO sqlalchemy.engine.base.Engine.0x...de90
INSERT INTO genres (genre_id, name, description) VALUES (?, ?, ?)
2010-07-22 13:40:46,081 INFO sqlalchemy.engine.base.Engine.0x...de90
(6, None, None)
2010-07-22 13:40:46,084 INFO sqlalchemy.engine.base.Engine.0x...de90 ROLLBACK
attempted to add new composite object, failed with (IntegrityError)
PRIMARY KEY must be unique u'INSERT INTO genres (genre_id, name,
description) VALUES (?, ?, ?)' (6, None, None)
If I understand correctly, calling session.merge() on a composite
object does not make several different decisions about whether to do
inserts or updates across each constituent table - it simply makes
one, global decision based on its own composite pk?
Perhaps more granular decision-making would be an interesting feature
for future versions? Or does it just get impossibly complex for
arbitrary selectables?
anyways, thanks for all your help.
Harry
On Tue, Jul 20, 2010 at 5:14 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 11:51 AM, Harry Percival wrote:
attached. feel free to ignore the 'sqlite' folder, which is only
needed for ironpython. the error definitely occurs in cpython 2.6.3.
1. Merging MoviesAndDirectorsAndGenres, with no primary key identifiers set,
sets it as pending in the Session. This now indicates that an INSERT into
all three tables will take place during flush, since the object had no
primary key when being merged.
2. Existing Genres, Directors, and Movies objects are attached to the new
MoviesAndDirectorsAndGenres object. These attachments specify a primary
join condition that requests that the primary key of each of the Genres,
Directors, and Movies be populated into each of the director_id, genre_id,
and movie_id attributes of the MoviesAndDirectorsAndGenres during a flush.
3. The flush takes place, the director_id, genre_id, and movie_id attributes
of the pending MoviesAndDirectorsAndGenres are populated with integer values
that happen to already exist in those tables, and the INSERT fails.
Since you don't really want an INSERT to take place here, I would recommend
using only Genres, Directors, and Movies objects.
It would be very helpful if you could not send any more zipfiles, only a
single .py script is needed for test case illustration. This makes my life
easier and also for the many people that will be reading your post in the
future.
rgds,
hp
On Tue, Jul 20, 2010 at 3:54 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Jul 20, 2010, at 10:46 AM, Michael Bayer wrote:
On Jul 20, 2010, at 6:05 AM, Harry Percival wrote:
I'm clear on that. I'm only using session.add to do an insert when I
know i definitely want to. But you'll see I used session.merge on the
composite object, yet it still attempts to do an insert for rows that
already exist in its constituent tables...
you are setting a primary key, and adding right here:
my apologies, thats not the INSERT that fails.
please attach a full reproducing test script.
On Mon, Jul 19, 2010 at 5:19 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Jul 19, 2010, at 12:04 PM, Harry Percival wrote:
Michael, thanks, as ever, for your help.
So, I think I've managed to specify the relationships:
j = join(movies_table,md_table).join(directors_table).join(genres_table)
js = j.select(use_labels=True).alias('mdg')
r0 = relationship(Movies,
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
On Jul 22, 2010, at 9:02 AM, Harry Percival wrote: Hi Michael, I'm using a manual workaround, so this is only out of curiosity now. Cf yr point 1: 1. Merging MoviesAndDirectorsAndGenres, with no primary key identifiers set, sets it as pending in the Session. This now indicates that an INSERT into all three tables will take place during flush, since the object had no primary key when being merged. not quite sure what 'primary key identifiers' are, but am assuming that you mean explicitly setting the attributes that are mapped to the join's composite primary key. let's try that: new = MoviesAndDirectorsAndGenres() new.movies_movie_id=8 #does not already exist in the movies table new.movies_title='new movie' new.directors_director_id=2 #already exists in the directors table new.genres_genre_id=6 #already exists too so, the new object now has a primary key - (8,2,6). this is a new pk for the join, but it is made up of pks from each of the constituent tables - 2 of which are existing, and 1 of which is new. The call to merge fails in the same way: 2010-07-22 13:40:46,063 INFO sqlalchemy.engine.base.Engine.0x...de90 SELECT truncated... FROM movies JOIN movie_directors ON movies.movie_id = movie_directors.movie_id JOIN directors ON directors.director_id = movie_directors.director_id JOIN genres ON genres.genre_id = movies.genre_id) AS mdg WHERE mdg.movies_movie_id = ? AND mdg.directors_director_id = ? AND mdg.genres_genre_id = ? 2010-07-22 13:40:46,075 INFO sqlalchemy.engine.base.Engine.0x...de90 (8, 2, 6) 2010-07-22 13:40:46,079 INFO sqlalchemy.engine.base.Engine.0x...de90 INSERT INTO genres (genre_id, name, description) VALUES (?, ?, ?) 2010-07-22 13:40:46,081 INFO sqlalchemy.engine.base.Engine.0x...de90 (6, None, None) 2010-07-22 13:40:46,084 INFO sqlalchemy.engine.base.Engine.0x...de90 ROLLBACK attempted to add new composite object, failed with (IntegrityError) PRIMARY KEY must be unique u'INSERT INTO genres (genre_id, name, description) VALUES (?, ?, ?)' (6, None, None) If I understand correctly, calling session.merge() on a composite object does not make several different decisions about whether to do inserts or updates across each constituent table - it simply makes one, global decision based on its own composite pk? that is correct.the primary key of (8,2, 6) does not exist in the database for this particular view - therefore the operation is determined to be an INSERT. Perhaps more granular decision-making would be an interesting feature for future versions? Or does it just get impossibly complex for arbitrary selectables? That's what relationship() is for. If we were to try to build such functionality into a mapping of three tables, it would require configuational elements that specify which tables are grouped together in what way, which tables should be SELECTed from when a merge is detected, and...then you've just reinvented separate mappers with relationship() between them.Also, if a mapping of multiple tables is now an ad-hoc structure, what would query(MoviesAndDirectorsAndGenres).get((8, 2, 6)) return ? A partial object ? Or None ? If the latter, how is that not what merge() should do, you've defined MoviesAndDirectorsAndGenres as the lowest level of granularity for this selectable yourself, SQLAlchemy should assume that's not what you meant and dig into each table individually ? There's a bigger picture going on here. anyways, thanks for all your help. Harry On Tue, Jul 20, 2010 at 5:14 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 20, 2010, at 11:51 AM, Harry Percival wrote: attached. feel free to ignore the 'sqlite' folder, which is only needed for ironpython. the error definitely occurs in cpython 2.6.3. 1. Merging MoviesAndDirectorsAndGenres, with no primary key identifiers set, sets it as pending in the Session. This now indicates that an INSERT into all three tables will take place during flush, since the object had no primary key when being merged. 2. Existing Genres, Directors, and Movies objects are attached to the new MoviesAndDirectorsAndGenres object. These attachments specify a primary join condition that requests that the primary key of each of the Genres, Directors, and Movies be populated into each of the director_id, genre_id, and movie_id attributes of the MoviesAndDirectorsAndGenres during a flush. 3. The flush takes place, the director_id, genre_id, and movie_id attributes of the pending MoviesAndDirectorsAndGenres are populated with integer values that happen to already exist in those tables, and the INSERT fails. Since you don't really want an INSERT to take place here, I would recommend using only Genres, Directors, and Movies objects. It would be very helpful if you could not send any more zipfiles, only a single .py script is needed for test case illustration. This makes my life easier and
[sqlalchemy] creating tables dynamically
Hello, I am working on an application where I would need to create a table in a database but I do not have a priori knowledge of how many columns does this table has. Is it possible to do this in SA? If so can someone point me to an example or reference documentation explaining how to accomplish this? 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.
Re: [sqlalchemy] Re: alchemy-migrate with DeclarativeBase
Hi Shane, Sorry, I missed your reply in the remaining messages on the sqlalchemy list. On Wed, 2010-06-23 at 13:24 -0700, Shane wrote: How would you define the Account class? Calling create on my DeclarativeBase Account object was one of the first things I tried, but I keep getting: AttributeError: type object 'Account' has no attribute 'create' Maybe a version difference in sqlalchemy? I don't have an account class, and in fact I don't know why I capitalized Account in my email. In fact, the variable is called account_table, so with declarative I would probably be Account.__table__ or something like that. In fact I just ran the attached python code which works just fine. Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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. from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base base = declarative_base() class Account(base): __tablename__ = accounts id = Column(Integer, primary_key=True) name = Column(String) engine = create_engine(sqlite:///:memory:, echo=True) Account.__table__.create(engine)
[sqlalchemy] Re: how to run a stored procedure?
Hello,
What if there is a column that is called state
When I try to add use_labels inside the execute statement I get below
error. Should I be using the use_labels somewhere else?:
a[0].State
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/engine/base.py, line 1943, in
__getattr__
return self[name]
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/engine/base.py, line 1934, in
__getitem__
try 'use_labels' option on select statement. % key)
sqlalchemy.exc.InvalidRequestError: Ambiguous column name 'State' in
result set! try 'use_labels' option on select statement.
# ---unexpected keyword error
a=session.execute(assp_ReportPolicyTransactions @start_date=:start,
@end_date=:end,params={'start':20100701,'end':20100719},use_labels=True).fetchall()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 732, in
execute
engine = self.get_bind(mapper, clause=clause, **kw)
TypeError: get_bind() got an unexpected keyword argument 'use_labels'
Thank you,
Lucas
--
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] [Fwd: Re: [TurboGears] strange sql query problem.]
I have a strange problem in sa. I've asked turbogears mailinglist and someone there suggested to post it here too. I've forwarded the post from there but here is an example of the problem. example code: prc_filter=24 q_sometable=DBSession.query(model.Sometable) q_sometable=q_sometable.filter((model.Sometable.qty*model.Sometable.price)==prc_filter) -- I'm trying to filter a query by two colums multiplied. I think this is a common filtering operation. I'm using turbogears 2.1b2 and sqlite. And some other person from TG mailinglist told me that this problem is sqlite only, he had the same problem too and it was solved when he switched to mysql. And this one works. When I send the filter as string like this: f = u'sometable.qty * sometable.price = 24' q_sometable = q_sometable.filter(f) -- Orijinal Mesaj Konu: Re: [TurboGears] strange sql query problem. Tarih: Thu, 22 Jul 2010 12:34:00 +0200 Kimden: Diez B. Roggisch de...@web.de Cevapla: turboge...@googlegroups.com Kime: turboge...@googlegroups.com Tercihler: 4c4813a1.7040...@savaskarsitlari.org 201007221207.48136.de...@web.de 4c481c75.4090...@savaskarsitlari.org On Thursday, July 22, 2010 12:24:53 Timuçin Kızılay wrote: Diez B. Roggisch yazmış: On Thursday, July 22, 2010 11:47:13 Timuçin Kızılay wrote: I'm trying to make a query like this: prc_filter = 24 q_sometable = DBSession.query(model.Sometable) q_sometable = q_sometable.filter((model.Sometable.qty * model.Sometable.price)==prc_filter) This do not give any error and when I look at the paster log I see that it generated a query with where part like this: WHERE sometable.qty * sometable.price = ? 12:41:24,272 INFO [sqlalchemy.engine.base.Engine.0x...ba0c] ['24'] This looks as if the above is *not* what you really do. Because '24' is a *string*, not an integer... Diez No, it's an integer but the log shows it as string. I think the problem is filtering on two columns multiplied. When I filter only one column, it works. q_sometable = q_sometable.filter(model.Sometable.price==prc_filter) this filter works and generated sql shown on the log is still ['24'] but it works. Ah. Strange enough. I think it's a SA bug, it try to put those values into query as parameters. If so, I suggest you take it to the SA-mailinglist. Michael Bayer is incredible responsive. Of course you should try with the latest + greatest SA first. Diez -- You received this message because you are subscribed to the Google Groups TurboGears group. To post to this group, send email to turboge...@googlegroups.com. To unsubscribe from this group, send email to turbogears+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/turbogears?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] [Fwd: Re: [TurboGears] strange sql query problem.]
so here is your test case: from sqlalchemy.sql import column from sqlalchemy import Integer x = (column(foo, Integer) * column(bar, Integer)) == 27 assert isinstance(x.right.type, Integer) you show me what is particular about your setup that causes x.right.type to be String, or something other than Integer. Choices include: - SQLAlchemy version (the above is 0.6.3) - types of the qty and price columns are not Integer - etc. you should be able to reproduce the above code using your Sometable.qty and Sometable.price column objects. On Jul 22, 2010, at 5:05 PM, Timuçin Kızılay wrote: I have a strange problem in sa. I've asked turbogears mailinglist and someone there suggested to post it here too. I've forwarded the post from there but here is an example of the problem. example code: prc_filter=24 q_sometable=DBSession.query(model.Sometable) q_sometable=q_sometable.filter((model.Sometable.qty*model.Sometable.price)==prc_filter) -- I'm trying to filter a query by two colums multiplied. I think this is a common filtering operation. I'm using turbogears 2.1b2 and sqlite. And some other person from TG mailinglist told me that this problem is sqlite only, he had the same problem too and it was solved when he switched to mysql. And this one works. When I send the filter as string like this: f = u'sometable.qty * sometable.price = 24' q_sometable = q_sometable.filter(f) -- Orijinal Mesaj Konu: Re: [TurboGears] strange sql query problem. Tarih: Thu, 22 Jul 2010 12:34:00 +0200 Kimden: Diez B. Roggisch de...@web.de Cevapla: turboge...@googlegroups.com Kime: turboge...@googlegroups.com Tercihler: 4c4813a1.7040...@savaskarsitlari.org 201007221207.48136.de...@web.de 4c481c75.4090...@savaskarsitlari.org On Thursday, July 22, 2010 12:24:53 Timuçin Kızılay wrote: Diez B. Roggisch yazmış: On Thursday, July 22, 2010 11:47:13 Timuçin Kızılay wrote: I'm trying to make a query like this: prc_filter = 24 q_sometable = DBSession.query(model.Sometable) q_sometable = q_sometable.filter((model.Sometable.qty * model.Sometable.price)==prc_filter) This do not give any error and when I look at the paster log I see that it generated a query with where part like this: WHERE sometable.qty * sometable.price = ? 12:41:24,272 INFO [sqlalchemy.engine.base.Engine.0x...ba0c] ['24'] This looks as if the above is *not* what you really do. Because '24' is a *string*, not an integer... Diez No, it's an integer but the log shows it as string. I think the problem is filtering on two columns multiplied. When I filter only one column, it works. q_sometable = q_sometable.filter(model.Sometable.price==prc_filter) this filter works and generated sql shown on the log is still ['24'] but it works. Ah. Strange enough. I think it's a SA bug, it try to put those values into query as parameters. If so, I suggest you take it to the SA-mailinglist. Michael Bayer is incredible responsive. Of course you should try with the latest + greatest SA first. Diez -- You received this message because you are subscribed to the Google Groups TurboGears group. To post to this group, send email to turboge...@googlegroups.com. To unsubscribe from this group, send email to turbogears+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/turbogears?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.
