On Sun, Mar 14, 2010 at 11:08 AM, Krishnakant Mane <[email protected]> wrote:
> On Sunday 14 March 2010 11:08 PM, Mike Orr wrote:
>>
>> On Sun, Mar 14, 2010 at 12:17 AM, Krishnakant Mane<[email protected]>
>> wrote:
>>
>>>
>>> Now since database is going to be hit by so many queries, having an ORM
>>> tested and tryed to that extent is very crutial.
>>>
>>
>> SQLAlchemy can handle it because the person who wrote SQLAlchemy
>> builds those kind of applications for a living. But an ORM may not be
>> the best approach. The more an application is calculation-intensive
>> and dealing with complex queries, the more it makes sense to push the
>> entire queries into the database behind stored procedure, and
>> input/output only the minimum necessary data. The database can do
>> calculations much faster than Python can, and there's overhead in
>> bringing values out of the database, and even more overhead to convert
>> them to ORM instances. All that overhead is lost if you just use the
>> values for further queries.
>>
>>
>
> Agreed, but there is a chance that we might make this application RDBMS
> independent. I would surely be releasing the web application as free
> software and making it depend on One database software is not really free in
> terms of freedom.
> And as you know many people have there database choices and I don't want to
> make my software a reason for another flame war.
It's a tradeoff between portability and performance. I prefer
portability because I've had to change db backends more than once. But
if the application is extremely calculation-intensive, the speed of
db-specific stored procedures may be an advantage.
"Free in terms of freedom" refers to the license. Postgres, MySQL, and
SQLite are all free in that sense. (And Firebird?) What you're
talking about is interoperability. An application tied to one
database is not as interoperable as one that can use any SQLAlchemy
database. But an admissions system inherently requires a large and
use-intensive database and a dedicated server. Thinking back to my
35,000 student university, where everybody registered during the same
week, any efficiency would be important, even if it tied the app to a
particular database. And if they've already agreed to buy a dedicated
server, then the choice of database engine would be minor. (Provided
it's a reputable engine, and they can figure out how to back it up.)
>> The first is to try to do only a few queries per request. If you're
>> performing a dozen queries or more, try to combine them into fewer
>> queries because each query adds overhead. If you need a result from
>> several related tables and you've set up the relations in SQLAlchemy's
>> ORM, you can use SQLAlchemy's "eager join" option to get the whole
>> thing with one join query rather than issuing a separate query for
>> each subtable as it's needed. (But if you *don't* need the data in
>> the subtables, leave eagerjoin off so that it only queries the main
>> table.)
>>
>>
>
> I am confused on this.
> So should I not have relations setup in the ORM or should I set up those
> relations in the ORM and then use the query builder?
You'll probably want to define both the tables and the ORM objects at
the beginning, even if you don't end up using all of them. That way
they'll always be ready to use if you do find a reason to. For
relations, there is some work at both the table level and the ORM
level. At the table level, you'll define your foreign keys. At the ORM
level, you;ll define which subtables should be attached to which
attributes. It will automatically determine the relationship using the
foreign keys, although in complex cases you may have to give it some
hints.
Then you can use either the SQL builder or the ORM whenever you wish.
With SQLAlchemy >= 0.5, you can also do custom queries using ORM
syntax but with SQL builder speed. This happens when you Query on
specific columns rather than just a single ORM object, and it returns
a low-level result object. Example:
# Example 1: Normal ORM query -- result is a list of ORM Person objects
q = Session.query(Person).filter(Person.last_name == "Smith")
q = q.order_by(Person.last_name, Person.first_name)
result = q.all()
# Example 2: SQL query using ORM syntax -- result is low-level row objects
q = Session.query(Person.id, Person.first_name, Person.last_name)
q = q.filter(Person.last_name == "Smith")
q = q.order_by(Person.last_name, Person.first_name)
result = q.all()
# Example 3: SQL query using SQL builder syntax
# Execute the query in the ORM session so that it's within the transaction
# Assuming SQLAlchemy Declarative, so the person table is
``Person.__table__``
import sqlalchemy as sa
tc = Person.__table__.columns
sql = sa.select([tc.id, tc.first_name, tc.last_name], tc.last_name
== "Smith",
order_by=[tc.last_name, tc.first_name])
result = Session.execute(sql).fetchall()
Example 1 instantiates a Person object for every record. This has more
overhead, but is more convenient to use. It's good if you want to
display all the record's details, or if you plan to modify many
fields. It's also good if you want to add/modify a record but don't
know whether it exists (see below).
Example 2 returns only the columns we're interested in. It returns the
low-level result directly rather than mapping it to ORM objects. It's
as fast as example 3, which does the same thing. (#2 may have a
slight overhead in assembling the query, but that's too small to worry
about. #3 is slightly dumber because it has access only to the
table-level configuration, but this doesn't matter except in advanced
cases.)
If you need to modify a record but don't know whether it exists, the
ORM is more convenient:
# Example 4: create or update a record -- we don't know whether it exists
p = Session.query(Person).get(1234)
if p is None:
# The record does not exist, so create a new one.
p = Person(id=1234)
Session.add(p)
p.first_name = "John"
p.last_name = "Smith"
Session.commit()
# Example 5: create or update a record using SQL builder syntax
# (This assumes we're setting all the columns. There's another syntax if
# we're setting only some of the columns.)
r = {"first_name": "John", "last_name": "Smith", ...}
sql = sa.select([tc.id], tc.id == 1234)
if Session.execute(sql).fetchone() is None:
# Zero results, so record doesn't exist
r["id"] = 1234
sql = Person.__table__.insert()
else:
# Record does exist, so do UPDATE rather than INSERT
sql = Person.__table__.update(tc.id == 1234)
Session.execute(sql, r)
Session.commit()
--
Mike Orr <[email protected]>
--
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en.
