Re: [sqlalchemy] subqueryload for a ColumnProperty?
Mapped selects, nice! I wish I'd seen that part in the documentation
earlier. Do you think it might be worth a reference near Alternatives to
column_property()http://www.sqlalchemy.org/docs/orm/mapper_config.html#alternatives-to-column-propertywhere
you also mention object_session()?
So there's one more issue. Once there's a lot of customers and orders, this
query performs pretty poorly in MySQL (tried 5.5.14 Source and 5.0.92-50-log
Percona). To simplify, I'll use joinedload() in my example instead of
subqueryload().
f =
s.query(Customer).filter_by(id=46).options(joinedload(_num_orders)).all()2011-09-11
03:04:33,598 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)2011-09-11
03:04:33,599 INFO sqlalchemy.engine.base.Engine SELECT customer.id AS
customer_id, anon_1.customer_id AS anon_1_customer_id, anon_1.count AS
anon_1_count
FROM customer LEFT OUTER JOIN (SELECT `order`.customer_id AS
customer_id, count(`order`.id) AS count
FROM `order` GROUP BY `order`.customer_id) AS anon_1 ON customer.id =
anon_1.customer_id
WHERE customer.id = %(id_1)s2011-09-11 03:04:33,599 INFO
sqlalchemy.engine.base.Engine {'id_1': 46}
The EXPLAIN plan:
mysql explain SELECT customer.id AS customer_id, anon_1.customer_id
AS anon_1_customer_id, anon_1.count AS anon_1_count FROM customer
LEFT OUTER JOIN (SELECT `order`.customer_id AS customer_id,
count(`order`.id) AS count FROM `order` GROUP BY `order`.customer_id)
AS anon_1 ON customer.id = anon_1.customer_id WHERE customer.id = 46
\G
*** 1. row ***
id: 1
select_type: PRIMARY
table: customer
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: Using index
*** 2. row ***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 11200
Extra:
*** 3. row ***
id: 2
select_type: DERIVED
table: order
type: index
possible_keys: NULL
key: customer_id
key_len: 5
ref: NULL
rows: 28725
Extra: Using index3 rows in set (0.04 sec)
It results in a huge derived table and a table scan over the order table.
Out of curiosity, I ran the same statement in PostgresSQL (9.0.4) and
noticed it works much better:
sumeet=# explain analyze SELECT customer.id AS customer_id,
anon_1.customer_id AS anon_1_customer_id, anon_1.count AS anon_1_count
FROM customer LEFT OUTER JOIN (SELECT order.customer_id AS
customer_id, count(order.id) AS count FROM order GROUP BY
order.customer_id) AS anon_1 ON customer.id = anon_1.customer_id
WHERE customer.id = 46;
QUERY PLAN
--
Nested Loop Left Join (cost=0.00..508.06 rows=1 width=16) (actual
time=4.069..4.070 rows=1 loops=1)
Join Filter: (customer.id = order.customer_id)
- Index Scan using customer_pkey on customer (cost=0.00..8.27
rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (id = 46)
- GroupAggregate (cost=0.00..499.77 rows=1 width=8) (actual
time=4.046..4.046 rows=1 loops=1)
- Seq Scan on order (cost=0.00..499.75 rows=2 width=8)
(actual time=0.021..4.042 rows=3 loops=1)
Filter: (customer_id = 46)
Total runtime: 4.127 ms
(8 rows)
Not that it helps me any :) I'm on MySQL. I can dramatically improve the
query by adding a WHERE clause inside the subquery. I bolded where the
change is:
mysql explain SELECT customer.id AS customer_id, anon_1.customer_id
AS anon_1_customer_id, anon_1.count AS anon_1_count FROM customer
LEFT OUTER JOIN (SELECT `order`.customer_id AS customer_id,
count(`order`.id) AS count FROM `order` *WHERE customer_id = 46*
GROUP BY `order`.customer_id) AS anon_1 ON customer.id =
anon_1.customer_id WHERE customer.id = 46 \G
*** 1. row ***
id: 1
select_type: PRIMARY
table: customer
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: Using index
*** 2. row ***
id: 1
select_type: PRIMARY
table: derived2
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
*** 3. row ***
id: 2
select_type: DERIVED
table: order
type: ref
possible_keys: customer_id
key: customer_id
key_len: 5
ref:
rows: 3
Extra: Using where; Using index3 rows in
Re: [sqlalchemy] subqueryload for a ColumnProperty?
On Sep 11, 2011, at 7:05 AM, Sumeet Agarwal wrote:
It results in a huge derived table and a table scan over the order table. Out
of curiosity, I ran the same statement in PostgresSQL (9.0.4) and noticed it
works much better:
Yeah one thing I do when I use MySQL, is that I assume at the gate that joins
are out of the question. MySQL is not a system that is reasonably capable of
basic relational tasks. If I'm using MySQL, it's usually because I'm
integrating with some existing PHP application or some manager forced some bad
decision on me.
I know this is not helping and you want this:
mysql explain SELECT customer.id AS customer_id, anon_1.customer_id AS
anon_1_customer_id, anon_1.count AS anon_1_count FROM customer LEFT OUTER
JOIN (SELECT `order`.customer_id AS customer_id, count(`order`.id) AS count
FROM `order` WHERE customer_id = 46 GROUP BY `order`.custome r_id) AS anon_1
ON customer.id = anon_1.customer_id WHERE customer.id = 46
So to get exactly the behavior of, copy the WHERE clause into the joined load
or subq load, that's not directly possible with the JoinedLoader or
SubqueryLoader.It's also the kind of thing that's not very easy to do
generically, if your lead query were much more complex than a simple WHERE
clause for example.
By far the easiest way is to just write it out as two queries and assign the
value. You don't even need the whole relationship() thing, just emit a second
query for the counts, then set the attribute. That's doityourself.py
attached, where I purposely did it with no joins whatsoever since if you have
the customer_id here, you don't need the JOIN at all - MySQL hates joins so
this approach will definitely be the fastest (plus no SQLAlchemy attribute
overhead).You can make it a little more generic by creating a function that
you give the WHERE clause to, it then applies the WHERE to both the customer
and order tables in some way.
If you still wanted it with the JOIN to the parent, and optionally deal with
the relationship() to OrderCount again, that approach is actually a
specialization of the subqueryload recipe at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading .The
recipe then uses q.from_self(Child).join(Parent.children) to create the join
from the parent to child. But here the level of hardcoding is already so
great I don't see what it buys you except a slower MySQL query.
If you were doing joined loading, you can keep using OrderCount and use
contains_eager() to write it out explicitly:
subq = s.query(OrderCount).filter(OrderCount.customer_id==1).subquery()
for c in s.query(Customer).\
filter(Customer.id==1).\
outerjoin(subq, Customer._num_orders).\
options(contains_eager(_num_orders, alias=subq)):
print c.id, c.num_orders
The query there still has more nesting than MySQL likes.
Next approach, hardcode a bind in there, very brittle but would produce this
result:
order_counts = select([Order.customer_id,
func.count(Order.id).label('count')]).\
where(Order.customer_id==bindparam(cust_id)).\
group_by(Order.customer_id).alias()
mapper(
OrderCount,
order_counts,
primary_key=order_counts.c.customer_id
)
the joinedload scenario would work like this:
s.query(Customer).filter(Customer.id==48).options(joinedload(_num_orders)).params(cust_id=48)
subqueryload(), not as easy - we need to use the technique at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GlobalFilter (which also works
with the joinedload version):
from sqlalchemy.orm.interfaces import MapperOption
class SetABindOption(MapperOption):
propagate_to_loaders = True
def __init__(self, **kw):
self.kw = kw
def process_query_conditionally(self, query):
process query during a lazyload
query._params = query._params.union(self.kw)
def process_query(self, query):
process query during a primary user query
self.process_query_conditionally(query)
s.query(Customer).filter(Customer.id==1).options(subqueryload(_num_orders),
SetABindOption(cust_id=1))
But I'd stick with, just do an extra SELECT statement unless more genericism is
needed. Since it appears this case is so special purpose already and MySQL is
going to chug to a halt if you fall back on other methods.
--
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.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base= declarative_base()
class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
orders = relationship(Order)
class Order(Base):
[sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
I think the subject line makes it pritty clear. I want to know how i can use the expression api to make calls to postgresql stored procedures written in plpgsql. For example how to pass input parameters and how to manipulate cursor objects etc. happy hacking. Krishnakant. -- 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] support of regular expressions?
I'm needing to extract domain information from stored email addresses -- something akin to the following: SELECT DISTINCT (REGEXP_MATCHES(email, '@(.+)$'))[1] AS domain FROM tablename WHERE email ~ '@.+$' While I was able to gather the information through session.execute(), I didn't find an equivalent filter (?) in the code for regular expression related functions. Is this too database specific, or did I miss something? Thanks, and thank you for SQLAlchemy. Jim -- 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] still unclear how to invoke stored procedures with sqlalchemy
You use the func construct to invoke a function. This can be passed to an
execute() method directly where it should embed itself into a SELECT:
from sqlalchemy import func
result = engine.execute(func.name_of_my_pg_function(1, 2, 3))
Manipulation of cursors is not supported by SQLAlchemy beyond calling the basic
fetchone()/fetchmany()/fetchall() methods of DBAPI. If you need non-standard
cursor control methods like scroll(), you can no longer use engine.execute()
and need to use psycopg2 cursors directly:
http://initd.org/psycopg/docs/cursor.html
To get at a psycopg2 cursor from a SQLAlchemy engine:
connection = engine.raw_connection()
cursor = connection.cursor()
Usage is then that described at http://initd.org/psycopg/docs/cursor.html
cursor.execute(SELECT my_pg_function(%(param1)s, %(param2)s,
%(param3)s), {'param1':1, 'param2':2, 'param3':3})
hope this helps !
On Sep 11, 2011, at 1:48 PM, Krishnakant Mane wrote:
I think the subject line makes it pritty clear.
I want to know how i can use the expression api to make calls to postgresql
stored procedures written in plpgsql.
For example how to pass input parameters and how to manipulate cursor objects
etc.
happy hacking.
Krishnakant.
--
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] still unclear how to invoke stored procedures with sqlalchemy
On 12/09/11 00:56, Michael Bayer wrote:
You use the func construct to invoke a function. This can be passed to an
execute() method directly where it should embed itself into a SELECT:
from sqlalchemy import func
result = engine.execute(func.name_of_my_pg_function(1, 2, 3))
So does it mean that name_of_my_pg_function is should be the name of the
concerned stored procedure?
And let's say if I am using an ide for Python like pydev with eclipse,
will func. give me list of those procedures which are available for calling?
happy hacking.
Krishnakant.
Manipulation of cursors is not supported by SQLAlchemy beyond calling the basic
fetchone()/fetchmany()/fetchall() methods of DBAPI. If you need non-standard cursor
control methods like scroll(), you can no longer use engine.execute() and
need to use psycopg2 cursors directly:
http://initd.org/psycopg/docs/cursor.html
To get at a psycopg2 cursor from a SQLAlchemy engine:
connection = engine.raw_connection()
cursor = connection.cursor()
Usage is then that described at http://initd.org/psycopg/docs/cursor.html
cursor.execute(SELECT my_pg_function(%(param1)s, %(param2)s,
%(param3)s), {'param1':1, 'param2':2, 'param3':3})
hope this helps !
On Sep 11, 2011, at 1:48 PM, Krishnakant Mane wrote:
I think the subject line makes it pritty clear.
I want to know how i can use the expression api to make calls to postgresql
stored procedures written in plpgsql.
For example how to pass input parameters and how to manipulate cursor objects
etc.
happy hacking.
Krishnakant.
--
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] still unclear how to invoke stored procedures with sqlalchemy
On Sep 11, 2011, at 3:43 PM, Krishnakant Mane wrote: On 12/09/11 00:56, Michael Bayer wrote: You use the func construct to invoke a function. This can be passed to an execute() method directly where it should embed itself into a SELECT: from sqlalchemy import func result = engine.execute(func.name_of_my_pg_function(1, 2, 3)) So does it mean that name_of_my_pg_function is should be the name of the concerned stored procedure? And let's say if I am using an ide for Python like pydev with eclipse, will func. give me list of those procedures which are available for calling? happy hacking. Krishnakant. no func. doesn't do any kind of inspection of existing database functions, sorry. -- 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] support of regular expressions?
We support match for PG directly, though that's full text matching.
For the general form of somename(arg1, arg2, ...) typically the func
construct is used unless some more specific construct exists:
func.regexp_matches(email, 'somestring')
The array index you have there, not supported directly yet, here's a recipe,
will give you the as_indexed() function:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PGArrayIndex
for the ~ operator, that can be done ad hoc using col.op():
table.c.email.op('~')('expression')
put it all together, and you'd get:
session.query(as_indexed(func.regexp_matches(table.c.email,
'expression'))[1].label('domain')).filter(email.op('~')('expression')).distinct()
another way to do the first part, you can use literal_column:
session.query(literal_column((REGEXP_MATCHES(email,
'@(.+)$'))[1]).label('domain'))...
and of course the @compiles extension lets you build whatever you want but
that's probably not needed here.Hopefully either of those will work !
On Sep 11, 2011, at 2:02 PM, James Hartley wrote:
I'm needing to extract domain information from stored email addresses --
something akin to the following:
SELECT DISTINCT (REGEXP_MATCHES(email, '@(.+)$'))[1] AS domain
FROM tablename
WHERE email ~ '@.+$'
While I was able to gather the information through session.execute(), I
didn't find an equivalent filter (?) in the code for regular expression
related functions. Is this too database specific, or did I miss something?
Thanks, and thank you for SQLAlchemy.
Jim
--
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.
