On Monday 15 March 2010 01:01 AM, Mike Orr wrote:
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.
Thanks mike. A little more research reveals that SQLAlchemy with its
query builder might suit the requirement. Basically I had to find a
mid-way between db independence and performance. Surely after your
explaination and what I read elsewhere, ORM might not be the best
solution, although where bulk querys are not involved, I might still go
with ORM.
But the SQLAlchemy's query builder is the solution I was looking for.
I can write queries and (almost) need not worry about what RDBMS is
being used.
"Free in terms of freedom" refers to the license. Postgres, MySQL, and
SQLite are all free in that sense. (And Firebird?)
Well I was doing a pun on that word. I know the licenses like GPL.
What I intended to say is nothing to do with the copyright, but the fact
that if I am forcing my user to *only* use one specific db software,
then I am taking away freedom in another sence. :)
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.)
Well, it is a kind of agreement we have got into which is going to work
very well. They are going to fund the development of this project
because their univercity needs it. But they too are interested to
release the product as free software under GPL.
So although they might buy a dedicated server, others might not agree
with the decision regarding the choice of a particular engine like MySQL
or Postgresql.
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.
Well, actually I am asking all these questions because this is a
paradime shift for me and my team. We have pritty good expertese on sql.
But for interoperability reasons we are shifting to ORM.
May I inform you that we are using the declarative syntax of
SQLAlchemy. So with reference to the pylons application, my model
folder will have the classes which declare and map the tables at the
same time.
Does this have any advantage or disadvantage that directly might impact
the performance?
Then you can use either the SQL builder or the ORM whenever you wish.
Ok, so it comes down to combining both approaches.
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()
Thanks, that's a very good explaination.
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()
Thanks a Lot Mike.
I think now the problems seams to be pritty clear, except if you or any
one on the list would want to add some thing with reference to using
this in pylons. (cashing and all that ).
Happy hacking.
Krishnakant.
--
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.
