date:20090606

[sqlalchemy] Re: Reading in single columns as numpy arrays

2009-06-06 Thread Can Xue

2009/6/5 Thomas thomas.robitai...@gmail.com


 Hi,

 I'm trying to read in single columns from an SQL database as 1D numpy
 arrays with the correct types. So a FLOAT column would be returned as
 a numpy.float32 array, etc. Is there an easy way to do this?



Maybe you have to define your own Float datatype using NumPy and modify
all table definistions to use this datatype.

--
XUE Can

--~--~-~--~~~---~--~~
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: ForeignKey not saved - sqlite3, TG1.1

2009-06-06 Thread Mike Conley

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] Re: union with two different orders

2009-06-06 Thread Adrian von Bidder


On Saturday 06 June 2009 14.18:33 naktinis wrote:
 I want to use union on two queries, which have different order:
 q1 = Thing.query().order_by(Thing.a)
 q2 = Thing.query().order_by(Thing.b)
 q = q1.union(q2).all()

SQL doesn't work as you think it does here.

A UNION does not concatenate the results of the two queries, but is allowed 
to return the result in any order.  ORDER BY can *then* be applied to the 
end result of your union.  So even if you use subqueries, the order by in 
the subqueries might just be ignored.

This is to allow the SQL query planner to be clever while building the union 
(perhaps a large union over two queries over the same table: if both queries 
require a table scan over the large table, the planner might decide to build 
the union by scanning the table only once while running both queries in 
parallel, so the table is loaded from disk once insead of twice.  The UNION 
would then contain the resulting rows in more or less random order.)

But I digress.

What you want to do is something like:

SELECT 1 as COL1, ... FROM ...
UNION
SELECT 2 as COL1, ... FROM ...
ORDER BY COL1, ...

cheers
-- vbi

 But after this query I get MySQL error message Incorrect usage of
 UNION and ORDER BY.

 I guess that this could be because having SELECT ... UNION SELECT ...
 ORDER BY B, it is not clear whether the second subquery or both
 queries should be ordered using B criteria. I think this can be solved
 by adding brackets to each of the subquery: (SELECT ...) UNION
 (SELECT ...).

 Is there any way to create this query using SQLAlchemy ORM?

 I am using SQLAlchemy 0.5.4.
 
-- 
Vertrauen ist gut.  Anwalt ist saugeil.


--~--~-~--~~~---~--~~
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: Session is already flushing

2009-06-06 Thread Michael Bayer


usually it means mutiple threads are hitting a single Session and/or  
connection.  keep in mind all objects attached to a session are an  
extension of that session's state and similarly cannot be shared among  
threads unless detached.


On Jun 6, 2009, at 1:03 AM, Michael Mileusnich wrote:

 I am using a scoped session in my sql alchemy app.  Can anybody help  
 me with what this error means:
 Session is already flushing
 _mysql_exceptions.ProgrammingError: (2014, Commands out of sync;  
 you can't run this command now5


 


--~--~-~--~~~---~--~~
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: union with two different orders

2009-06-06 Thread Michael Bayer



On Jun 6, 2009, at 11:39 AM, 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.

if youre using limit with order by, you would have to wrap those  
queries within subqueries in order for UNION to accept them as  
encapsulated relations.




 On 6 Bir, 17:49, Adrian von Bidder avbid...@fortytwo.ch wrote:
 On Saturday 06 June 2009 14.18:33 naktinis wrote:

 I want to use union on two queries, which have different order:
 q1 = Thing.query().order_by(Thing.a)
 q2 = Thing.query().order_by(Thing.b)
 q = q1.union(q2).all()

 SQL doesn't work as you think it does here.

 A UNION does not concatenate the results of the two queries, but is  
 allowed
 to return the result in any order.  ORDER BY can *then* be applied  
 to the
 end result of your union.  So even if you use subqueries, the order  
 by in
 the subqueries might just be ignored.

 This is to allow the SQL query planner to be clever while building  
 the union
 (perhaps a large union over two queries over the same table: if  
 both queries
 require a table scan over the large table, the planner might decide  
 to build
 the union by scanning the table only once while running both  
 queries in
 parallel, so the table is loaded from disk once insead of twice.   
 The UNION
 would then contain the resulting rows in more or less random order.)

 But I digress.

 What you want to do is something like:

 SELECT 1 as COL1, ... FROM ...
 UNION
 SELECT 2 as COL1, ... FROM ...
 ORDER BY COL1, ...

 cheers
 -- vbi

 But after this query I get MySQL error message Incorrect usage of
 UNION and ORDER BY.

 I guess that this could be because having SELECT ... UNION  
 SELECT ...
 ORDER BY B, it is not clear whether the second subquery or both
 queries should be ordered using B criteria. I think this can be  
 solved
 by adding brackets to each of the subquery: (SELECT ...) UNION
 (SELECT ...).

 Is there any way to create this query using SQLAlchemy ORM?

 I am using SQLAlchemy 0.5.4.

 --
 Vertrauen ist gut.  Anwalt ist saugeil.
 


--~--~-~--~~~---~--~~
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: ForeignKey not saved - sqlite3, TG1.1

2009-06-06 Thread Adam Yee


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] Re: Reading in single columns as numpy arrays

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

[sqlalchemy] Re: union with two different orders

[sqlalchemy] Re: Session is already flushing

[sqlalchemy] Re: union with two different orders

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

6 matches

Site Navigation

Mail list logo

Footer information