[sqlalchemy] Re: API that allows me to do additional database operations just before insert execution for SQL Expression
Hi, Can anyone suggest a solution to the last two posts I made? -- 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] Re: API that allows me to do additional database operations just before insert execution for SQL Expression
I would never want to start the transaction *inside* the connection proxy. The transactional context for a particular set of operations should be enclosing the full set of operations. Unless below, self.connection is a different connection that is ad-hoc. In which case just procure a new connection from the engine inside the proxy class. On Apr 27, 2011, at 9:56 AM, bool wrote: I want to some updates before doing any insert. So I needed the above. Now the issue I am facing is that I need all these updates and the current insert to be in a transaction. That is either all should be successful or none. How is that possible with a connection proxy (something like below)? === class MyProxy(ConnectionProxy): def execute(self, conn, execute, clauseelement, *multiparams, **params): if isinstance(clauseelement, Insert): trans = self.connection.begin() try: # Do some updates # Execute the current insert also ... trans.commit() except: trans.rollback() = -- 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.
Re: [sqlalchemy] Re: API that allows me to do additional database operations just before insert execution for SQL Expression
of course...just return the default execution as illustrated at http://www.sqlalchemy.org/docs/core/interfaces.html#sqlalchemy.interfaces.ConnectionProxy . if you want to move to 0.7 the interface becomes much simpler and no explicit return is required. On Apr 28, 2011, at 2:58 AM, bool wrote: Also based on certain conditions I would not like to execute the insert at all... So is there a way to just return from execute method of MyProxy class with out actually executing the insert statement? -- 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.
[sqlalchemy] sqlalchemy speed help
I'm facing some interesting speed issues with my database that only seem to crop up within sqlalchemy. I have 2 tables in a one-to-one relationship, with about ~1 million rows each. From Python, I grab an object from one table (table A): rowA = session.query(A).limit(1).one() And then access a row in table B: blah = rowA.b.someRow I noticed that for ~500 of these, it started taking a long time - so I wrapped it in time() calls: time1 = time.time() blah = rowA.b.someRow print time.time() - time1 What I find is that when I run this in Python, it takes ~0.01 seconds per object, but when I execute it directly in the database: EXPLAIN ANALYZE SELECT * FROM a WHERE a.b_pk = 20; It only takes ~0.01 milliseconds! Is there some optimization I can do from within the Model Class definitions, or do I really need to execute raw sql from my code to get raw speed? Thanks, Adrian -- 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] sqlalchemy speed help
On May 2, 2011, at 11:14 AM, Adrian wrote: I'm facing some interesting speed issues with my database that only seem to crop up within sqlalchemy. I have 2 tables in a one-to-one relationship, with about ~1 million rows each. From Python, I grab an object from one table (table A): rowA = session.query(A).limit(1).one() And then access a row in table B: blah = rowA.b.someRow I noticed that for ~500 of these, it started taking a long time - so I wrapped it in time() calls: time1 = time.time() blah = rowA.b.someRow print time.time() - time1 What I find is that when I run this in Python, it takes ~0.01 seconds per object, but when I execute it directly in the database: EXPLAIN ANALYZE SELECT * FROM a WHERE a.b_pk = 20; It only takes ~0.01 milliseconds! Is there some optimization I can do from within the Model Class definitions, or do I really need to execute raw sql from my code to get raw speed? a. using EXPLAIN ANALYZE does not take time spent fetching rows, network overhead, or the communication between the DBAPI and the database into account . A more apt comparison would be against raw DBAPI access using connection.cursor() where all rows are fetched. b. turn on echo=True, see what SQL is being emitted. c. assuming every rowA.b.someRow is a lazyload of the related row, you are executing 2x as many queries. Use eager loading: http://www.sqlalchemy.org/docs/orm/loading.html d. it is faster to load individual columns, that is sess.query(cls.id, cls.name), rather than the whole object, i.e. sess.query(cls). The latter has a lot more bookkeeping to perform. e. Emitting hundreds of single object queries is by far the least efficient way to get data back, it would be much better if you could emit fewer queries to load records in batches of 1000 or so. f. ORMs add lots of overhead as the cost of automation and state management, not unlike Python itself does at the cost of high level, easy to use objects instead of writing raw C code. Some perspective on this specific to SQLAlchemy is described here: http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-profiles/ Thanks, Adrian -- 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.
[sqlalchemy] Re: sqlalchemy speed help
Awesome, I'll work through these suggestions -- thanks for the speedy reply! On May 2, 11:29 am, Michael Bayer mike...@zzzcomputing.com wrote: On May 2, 2011, at 11:14 AM, Adrian wrote: I'm facing some interesting speed issues with my database that only seem to crop up within sqlalchemy. I have 2 tables in a one-to-one relationship, with about ~1 million rows each. From Python, I grab an object from one table (table A): rowA = session.query(A).limit(1).one() And then access a row in table B: blah = rowA.b.someRow I noticed that for ~500 of these, it started taking a long time - so I wrapped it in time() calls: time1 = time.time() blah = rowA.b.someRow print time.time() - time1 What I find is that when I run this in Python, it takes ~0.01 seconds per object, but when I execute it directly in the database: EXPLAIN ANALYZE SELECT * FROM a WHERE a.b_pk = 20; It only takes ~0.01 milliseconds! Is there some optimization I can do from within the Model Class definitions, or do I really need to execute raw sql from my code to get raw speed? a. using EXPLAIN ANALYZE does not take time spent fetching rows, network overhead, or the communication between the DBAPI and the database into account . A more apt comparison would be against raw DBAPI access using connection.cursor() where all rows are fetched. b. turn on echo=True, see what SQL is being emitted. c. assuming every rowA.b.someRow is a lazyload of the related row, you are executing 2x as many queries. Use eager loading: http://www.sqlalchemy.org/docs/orm/loading.html d. it is faster to load individual columns, that is sess.query(cls.id, cls.name), rather than the whole object, i.e. sess.query(cls). The latter has a lot more bookkeeping to perform. e. Emitting hundreds of single object queries is by far the least efficient way to get data back, it would be much better if you could emit fewer queries to load records in batches of 1000 or so. f. ORMs add lots of overhead as the cost of automation and state management, not unlike Python itself does at the cost of high level, easy to use objects instead of writing raw C code. Some perspective on this specific to SQLAlchemy is described here: http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-profiles/ Thanks, Adrian -- 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 athttp://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.
[sqlalchemy] add_column for subselects.
How can I add a column with add_column to a subselect?
Consider the following table schema:
+--+ +--+
|p2_fieldtype| n---1 | p2_span |
+--++---+
^
| joined table
| inheritance
++
| p2_alphanumeric |
++
Querying the above schema happens in this way:
query = session.query(p2_fieldtype).outerjoin(p2_alphanumeric)
query = query.add_column(case([(some_condition, 'false')],
else_='true').label('linked'))
which results in a SQL statement roughly like this:
SELECT [..],
CASE WHEN (p2_span_alphanumeric.fk_field_type IS NULL) THEN
'false' ELSE 'true' END AS linked
FROM p2_span_alphanumeric,
p2_fieldtype LEFT OUTER JOIN (
SELECT [..]
FROM p2_span
INNER JOIN p2_span_alphanumeric ON p2_span.id =
p2_span_alphanumeric.id) AS anon_1
ON p2_fieldtype.id = anon_1.p2_span_alphanumeric_fk_field_type
The problem with the above is the cross join between
p2_span_alphanumeric and the result of the left outer join. This
happens as soon as the add_column method is applied to the query. This
leads to too many records in the result. What I'd like to do is to
move the case statement to the subselect (and along with carve out the
p2_span_alphanumeric cross join). The result would be something like
this:
SELECT [..], linked
FROM p2_fieldtype
LEFT OUTER JOIN (
SELECT [..],
CASE WHEN (p2_span_alphanumeric.fk_field_type IS NULL)
THEN 'false' ELSE 'true' END AS linked
FROM p2_span
INNER JOIN p2_span_alphanumeric ON p2_span.id =
p2_span_alphanumeric.id) AS anon_1
ON p2_fieldtype.id = anon_1.p2_span_alphanumeric_fk_field_type
I can't find a way how to accomplish this. Can anybody help me please?
Kind regards,
Michael
--
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.
