[sqlalchemy] Re: union with two different orders

2009-06-07 Thread Adrian von Bidder 
On Saturday 06 June 2009 17.39:20 naktinis wrote:
 I think this was not the case, since I didn't expect the merged result
 to be ordered.

 To be more precise, the query looks like:
 q1 = Thing.query().filter(...).order_by(Thing.a.desc()).limit(1)
 q2 = Thing.query().filter(...).order_by(Thing.a.asc()).limit(1)
 q = q1.union(q2).order_by(Thing.id).all()

 The q1 returns first filtered element with largest 'a' column, q2 -
 first with smallest 'a'.

 So, I guess my question is still valid.

You didn't mention limit in your first post, so I misunderstood what you 
were trying to do, sorry.

Yes, as Michael said, subqueries are the way to go.  I'm quite new to sa, so 
I can't help you there.

cheers
-- vbi


-- 
featured link: http://www.pool.ntp.org



signature.asc
Description: This is a digitally signed message part.

[sqlalchemy] Re: ForeignKey not saved - sqlite3, TG1.1

2009-06-07 Thread Mike Conley 
Think of it this way.

The original code says set user.blogs to be this list containing exactly
one Blog. SQLAlchemy does what you told it to and issues an update removing
the foreign key from all blog entries that originally pointed to this user
and then inserts a new entry pointing where you expect it to.

The revised code says append a Blog to the already existing list in
user.blogs. This causes SQLAlchemy to issue an insert adding a new row to
the blog table, and no updates to prior existing blog records.

-- 
Mike Conley



On Sat, Jun 6, 2009 at 11:56 PM, Adam Yee adamj...@gmail.com wrote:


 Thanks Mike, it's working now.  But I'm curious, how was the
 foreignkey erased from the previous entry upon each newly added blog?
 What does .append do that keeps the foreignkeys saved?

 On Jun 6, 5:24 am, Mike Conley mconl...@gmail.com wrote:
  Looks like the culprit is:
  user.blogs = [Blog(title=input['blog_title']
  this replaces your user.blogs relation with a single entry list
 containing
  the last Blog
 
  try this to add a Blog to the relation list
  user.blogs.append(Blog(title=input['blog_title'))
 
  --
  Mike Conley
 
  On Fri, Jun 5, 2009 at 11:46 PM, Adam Yee adamj...@gmail.com wrote:
 
   I've got a simple mapping between User and Blog:
 
   # Parent
   user_table = Table('user', metadata,
  Column('id', Integer, primary_key=True),
  Column('username', String(50)),
  Column('password', Unicode(50)),
   )
   # Child
   blog_table = Table('blog', metadata,
  Column('id', Integer, primary_key=True),
  Column('title', String(100)),
  Column('date_created', DateTime()),
  Column('user_id', Integer, ForeignKey('user.id'))
   )
 
   ...
 
   mapper(User, user_table, properties = {
  'blogs': relation(Blog, backref='user')
   })
   mapper(Blog, blog_table)
 
   My foreignkeys aren't being saved/stored properly.  No matter how many
   'children' I create, the foreignkey only stays saved with the most
   recently added:
 
   sqlite select * from blog;
   1|blog1|2009-06-05 19:41:33.555387|
   2|blog2|2009-06-05 19:41:42.551838|
   3|blog3|2009-06-05 19:42:28.280046|
   4|blog4|2009-06-05 19:44:19.180090|
   5|blog5|2009-06-05 19:46:38.580777|1
   sqlite
 
   Here's how they are being saved.
   My controller saveblog():
 
   @expose()
   def saveblog(self, **input):
  if request.method == 'POST':
  if not input['blog_title']:
  msg = 'You must give a title name.'
  redirect('/createblog', message=msg)
  try:
  session_user = cherrypy.session['username']
  user = session.query(User).filter_by
   (username=session_user).one()
  user.blogs = [Blog(title=input['blog_title'],
   date_created=datetime.datetime.now())]
  msg = 'Successfully created blog %s.' % input
   ['blog_title']
  redirect('/', message=msg)
  except KeyError:
  redirect('/', message='Session lost or timed out')
  except NoResultFound:
  redirect('/', message='Error: database corrupt.')
 
   Why is it doing this? Please help, thanks.
 


--~--~-~--~~~---~--~~
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] some the wrong about sqlsoup in mulithread request

2009-06-07 Thread tsangpo 

I use the SqlSoup to make it easy to access the database. It's all right 
when I make the http request one by one.
But it run into errors when serveral requests were sent at the same time, 
and fail to proccess requests anymore.
I log the Session, and found that each request exact has different session.
The most strange thing is that each time the sqlsoup go down, it produce 
different error message:

When everything is OK, the log is:
-
2009-06-07 17:18:33,082 INFO sqlalchemy.engine.base.Engine.0x...a530 BEGIN
2009-06-07 17:18:33,082 INFO sqlalchemy.engine.base.Engine.0x...a530 SELECT 
user.id AS user_id, user.name AS user_name, user.full_name AS 
user_full_name, user.password AS user_password, user.active AS user_active
FROM user
WHERE user.password = %s AND user.name = %s
 LIMIT 0, 1
2009-06-07 17:18:33,082 INFO sqlalchemy.engine.base.Engine.0x...a530 
['123456', 'eric']
2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530 SELECT 
role.id AS role_id, role.name AS role_name, role.description AS 
role_description, rl_user_role.user_id AS rl_user_role_user_id, 
rl_user_role.role_id AS rl_user_role_role_id, rl_role_action.role_id AS 
rl_role_action_role_id, rl_role_action.action_id AS rl_role_action_action_id
FROM role INNER JOIN rl_user_role ON role.id = rl_user_role.role_id INNER 
JOIN rl_role_action ON role.id = rl_role_action.role_id
WHERE rl_role_action.action_id = %s AND rl_user_role.user_id = %s
2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530 [1L, 
1L]
2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530 SELECT 
user.id AS user_id, user.name AS user_name, user.full_name AS 
user_full_name, user.password AS user_password, user.active AS user_active
FROM user
2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530 []
2009-06-07 17:18:33,115 INFO sqlalchemy.engine.base.Engine.0x...a530 COMMIT
--

And error edition 1:
-
Traceback (most recent call last):
  File 
D:\CodingLife\win32\home\workspace\eric\lib\google_appengine\google\appengine\ext\webapp\__init__
.py, line 499, in __call__
handler.get(*groups)
  File D:\CodingLife\win32\home\workspace\eric\src\eric\database.py, line 
44, in _func
return func(*args, **kwargs)
  File D:\CodingLife\win32\home\workspace\eric\src\eric\auth\__init__.py, 
line 23, in action_metho
d
if roles == 'everyone' or check_privilege(action_id, 
users.get_current_user().id):
  File D:\CodingLife\win32\home\workspace\eric\src\eric\auth\__init__.py, 
line 67, in check_privil
ege
db.role.id==db.rl_user_role.role_id)
  File 
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib\sqlalchemy\ext\sqlsoup.py,
 line 541, in join
return self.map(j)
  File 
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib\sqlalchemy\ext\sqlsoup.py,
 line 531, in map
t = class_for_table(selectable, **kwargs)
  File 
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib\sqlalchemy\ext\sqlsoup.py,
 line 483, in class_for_table
for k in mappr.iterate_properties:
  File 
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib\sqlalchemy\orm\mapper.py,
 
line
 777, in iterate_properties
self.compile()
  File 
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib\sqlalchemy\orm\mapper.py,
 
line
 651, in compile
for mapper in list(_mapper_registry):
  File D:\CodingLife\win32\native\Python25\lib\weakref.py, line 303, in 
iterkeys
for wr in self.data.iterkeys():
RuntimeError: dictionary changed size during iteration

the request followed produce this:

INFO:sqlalchemy.engine.base.Engine.0x...a530:ROLLBACK
ERROR:root:Traceback (most recent call last):
  File 
D:\CodingLife\win32\home\workspace\eric\lib\google_appengine\google\appengine\ext\webapp\__init__.py,
 
line 499, in __call__
handler.get(*groups)
  File D:\CodingLife\win32\home\workspace\eric\src\eric\database.py, line 
44, in _func
return func(*args, **kwargs)
  File D:\CodingLife\win32\home\workspace\eric\src\eric\auth\__init__.py, 
line 23, in action_method
if roles == 'everyone' or check_privilege(action_id, 
users.get_current_user().id):
  File D:\CodingLife\win32\home\workspace\eric\src\eric\auth\__init__.py, 
line 67, in check_privilege
db.role.id==db.rl_user_role.role_id)
  File 
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib\sqlalchemy\ext\sqlsoup.py,
 
line 541, in join
return self.map(j)
  File 
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib\sqlalchemy\ext\sqlsoup.py,
 
line 531, in map
t = class_for_table(selectable, **kwargs)
  File 
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib\sqlalchemy\ext\sqlsoup.py,
 
line 483, in class_for_table
for k in

[sqlalchemy] something wrong about sqlsoup in mulithread request

2009-06-07 Thread tsangpo 

I use the SqlSoup to make it easy to access the database. It's all
right
when I make the http request one by one.
But it run into errors when serveral requests were sent at the same
time,
and fail to proccess requests anymore.
I log the Session, and found that each request exact has different
session.
The most strange thing is that each time the sqlsoup go down, it
produce
different error message:

When everything is OK, the log is:
-
2009-06-07 17:18:33,082 INFO sqlalchemy.engine.base.Engine.0x...a530
BEGIN
2009-06-07 17:18:33,082 INFO sqlalchemy.engine.base.Engine.0x...a530
SELECT
user.id AS user_id, user.name AS user_name, user.full_name AS
user_full_name, user.password AS user_password, user.active AS
user_active
FROM user
WHERE user.password = %s AND user.name = %s
 LIMIT 0, 1
2009-06-07 17:18:33,082 INFO sqlalchemy.engine.base.Engine.0x...a530
['123456', 'eric']
2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530
SELECT
role.id AS role_id, role.name AS role_name, role.description AS
role_description, rl_user_role.user_id AS rl_user_role_user_id,
rl_user_role.role_id AS rl_user_role_role_id, rl_role_action.role_id
AS
rl_role_action_role_id, rl_role_action.action_id AS
rl_role_action_action_id
FROM role INNER JOIN rl_user_role ON role.id = rl_user_role.role_id
INNER
JOIN rl_role_action ON role.id = rl_role_action.role_id
WHERE rl_role_action.action_id = %s AND rl_user_role.user_id = %s
2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530
[1L,
1L]
2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530
SELECT
user.id AS user_id, user.name AS user_name, user.full_name AS
user_full_name, user.password AS user_password, user.active AS
user_active
FROM user
2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530
[]
2009-06-07 17:18:33,115 INFO sqlalchemy.engine.base.Engine.0x...a530
COMMIT
--

And error edition 1:
-
Traceback (most recent call last):
  File
D:\CodingLife\win32\home\workspace\eric\lib\google_appengine\google
\appengine\ext\webapp\__init__
.py, line 499, in __call__
handler.get(*groups)
  File D:\CodingLife\win32\home\workspace\eric\src\eric\database.py,
line
44, in _func
return func(*args, **kwargs)
  File D:\CodingLife\win32\home\workspace\eric\src\eric\auth
\__init__.py,
line 23, in action_metho
d
if roles == 'everyone' or check_privilege(action_id,
users.get_current_user().id):
  File D:\CodingLife\win32\home\workspace\eric\src\eric\auth
\__init__.py,
line 67, in check_privil
ege
db.role.id==db.rl_user_role.role_id)
  File
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib
\sqlalchemy\ext\sqlsoup.py,
 line 541, in join
return self.map(j)
  File
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib
\sqlalchemy\ext\sqlsoup.py,
 line 531, in map
t = class_for_table(selectable, **kwargs)
  File
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib
\sqlalchemy\ext\sqlsoup.py,
 line 483, in class_for_table
for k in mappr.iterate_properties:
  File
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib
\sqlalchemy\orm\mapper.py,
line
 777, in iterate_properties
self.compile()
  File
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib
\sqlalchemy\orm\mapper.py,
line
 651, in compile
for mapper in list(_mapper_registry):
  File D:\CodingLife\win32\native\Python25\lib\weakref.py, line 303,
in
iterkeys
for wr in self.data.iterkeys():
RuntimeError: dictionary changed size during iteration

the request followed produce this:

INFO:sqlalchemy.engine.base.Engine.0x...a530:ROLLBACK
ERROR:root:Traceback (most recent call last):
  File
D:\CodingLife\win32\home\workspace\eric\lib\google_appengine\google
\appengine\ext\webapp\__init__.py,
line 499, in __call__
handler.get(*groups)
  File D:\CodingLife\win32\home\workspace\eric\src\eric\database.py,
line
44, in _func
return func(*args, **kwargs)
  File D:\CodingLife\win32\home\workspace\eric\src\eric\auth
\__init__.py,
line 23, in action_method
if roles == 'everyone' or check_privilege(action_id,
users.get_current_user().id):
  File D:\CodingLife\win32\home\workspace\eric\src\eric\auth
\__init__.py,
line 67, in check_privilege
db.role.id==db.rl_user_role.role_id)
  File
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib
\sqlalchemy\ext\sqlsoup.py,
line 541, in join
return self.map(j)
  File
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib
\sqlalchemy\ext\sqlsoup.py,
line 531, in map
t = class_for_table(selectable, **kwargs)
  File
D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib
\sqlalchemy\ext\sqlsoup.py,
line 483, in class_for_table
for k in mappr.iterate_properties:
  File