advantages and disadvantages of Raw sql queries in django

[vijay shanker]

hi
i want to know pros and cons associated with running raw sql queries
over django's ORM .
googled it out but couldn find many useful links.

[Mike Dewhirst]

I firmly believe in using the ORM for everything until i am forced to use handwritten SQL.

The main reason is transparency of intentions when the source is examined by a non-DBA.

I want my code to be maintainable by other people and provably correct. I suppose you can unit-test code with embedded SQL but it adds an extra layer of complexity.

I could also argue against custom SQL because it contains business logic and i prefer to keep that all in one place, ie., in models in python code.

So the bottom line for me is to avoid it where possible.  Once everything is profiled and the last remaining bottlenecks are significant and cannot be removed unless I use custom SQL, that's when I'll do it.

Haven't gotten there yet.

-----Original message-----

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[Jon Black]

Also, using ORM means you're less likely to be tied to a specific
database...or in other words, your hand-crafted SQL is likely to be
specific to certain databases.

On 06/30/2012 08:33 AM, Mike Dewhirst wrote:
> I firmly believe in using the ORM for everything until i am forced to
> use handwritten SQL.
>
> The main reason is transparency of intentions when the source is
> examined by a non-DBA.
>
> I want my code to be maintainable by other people and provably correct.
> I suppose you can unit-test code with embedded SQL but it adds an extra
> layer of complexity.
>
> I could also argue against custom SQL because it contains business logic
> and i prefer to keep that all in one place, ie., in models in python code.
>
> So the bottom line for me is to avoid it where possible. Once
> everything is profiled and the last remaining bottlenecks are
> significant and cannot be removed unless I use custom SQL, that's when
> I'll do it.
>
> Haven't gotten there yet.
>
> -----Original message-----
>

> *From: *vijay shanker <vshan...@gmail.com>*
> To: *Django users <django...@googlegroups.com>*
> Sent: *Sat, 30 Jun 2012, 14:35:54 AEST*
> Subject: *advantages and disadvantages of Raw sql queries in django

[Nick Apostolakis]

At some point in the future you may find yourself in a position when you
will need to change the underlying DB engine.
In these cases ORM is a life saver.

This scenario is not far-fetched it is very common actually. I use
sqlite to develop my app, but in the production server I use mysql.
The code is the same, I have to change only 2 lines in settings.py
This makes development very easy and very portable.
I can reproduce my development environment in 10mins (with the use of
pip and virtualenv of course) and start working in a new machine with
minimum hassle.

--
--------------------------------------------------------------
Nick Apostolakis
e-mail: nick...@oncrete.gr
Web Site: http://nick.oncrete.gr
--------------------------------------------------------------

[Javier Guerra Giraldez]

On Sat, Jun 30, 2012 at 1:33 AM, Mike Dewhirst <mi...@dewhirst.com.au> wrote:
> I firmly believe in using the ORM for everything until i am forced to use
> handwritten SQL.

absolutely agree

> I could also argue against custom SQL because it contains business logic and
> i prefer to keep that all in one place, ie., in models in python code.

of course, when you have to use SQL, the best place to put it is in
the models file, either on the model itself or on a custom manager.
That way, if/when you modify the database representation, you don't
have to change other parts of the code.

Even when using the ORM, any complex query is a good candidate to be
encapsulated as part of the model. Ideally, the model should expose a
high level view of your data objects, not simply an easy to use view
of the database.

--
Javier

[Nick Apostolakis]

On 30/06/2012 10:58 μμ, Javier Guerra Giraldez wrote:
>
> Even when using the ORM, any complex query is a good candidate to be
> encapsulated as part of the model. Ideally, the model should expose a
> high level view of your data objects, not simply an easy to use view
> of the database.
>
>

Interesting idea, I haven't used that yet, could you give an example of
that?
Would you use a method calling the ORM that would include something like
self.objects.select_related().bla bla?

Thanks

[Javier Guerra Giraldez]

On Sat, Jun 30, 2012 at 5:26 PM, Nick Apostolakis <nick...@oncrete.gr> wrote:
> Interesting idea, I haven't used that yet, could you give an example of
> that?
> Would you use a method calling the ORM that would include something like
> self.objects.select_related().bla bla?

for example: when modelling the requested/accepted/rejected of a
'friendship' relation between 'person' records, you have to design the
exact DB representation, but later on you might want to modify that
representation. Ideally, such changes shouldn't impact _all_ your
code.

specifically: my Person model has a ManyToManyField to 'self' called
'friends', where the intermediate table ('Friendship' model) has a
'state' field. These are implementation details, so i added some
methods to get the list of friends, list of unanswered requests sent,
received, and rejections sent and received:

def get_friends(self):
return self.friends.filter(as_B__state=Friendship.ACCEPTED)

def get_sent_requests_unanswered(self):
return self.friends.filter(as_B__state=Friendship.REQUESTED)

def get_rcvd_requests_unanswered(self):
return self.receivedRequests.filter(as_A__state=Friendship.REQUESTED)

def get_sent_requests_rejected(self):
return self.friends.filter(as_B__state=Friendship.REJECTED)

def get_rcvd_requests_rejected(self):
return self.receivedRequests.filter(as_A__state=Friendship.REJECTED)

as you can see, these queries are readable enough to be used anywhere;
but if later on i want to replace the single 'friends' link field with
several independent relationships, i don't have to search through all
my code, just the models.py

similarly, there are methods to send a request and to accept or reject
it. Again, the code is simple enough that it could be used directly;
but encapsulating it with the Person model makes it easy to maintain
consistency:

for example, the friendship request method:

def send_friend_request(self, target):
try:
f = Friendship.objects.get(personA=self, personB=target)
return f in (Friendship.REQUESTED, Friendship.ACCEPTED)
except Friendship.DoesNotExist:
Friendship.objects.create(
personA=self, personB=target,
state=Friendship.REQUESTED)
return True

doesn't allow a second request if one is already pending or rejected.
if later on this policy is changed (maybe a rejector could change
mind, or an old request could expire, etc), i know where to do the
changes.

also, this lets the views be _really_ thin. since the interface
presented by the models is really close to the conceptual data objects
presented to the users. After that, adding for example a REST layer
isn't hard; since it only has to use the same high-level methods as
the HTML views. again, consistency is guaranteed by the common
underlying implementation.

in short: an application model is _not_ an OOP view of the database;
it's the implementation of the conceptual data objects as handled by
the application.

--
Javier

[Nick Apostolakis]

On 01/07/2012 06:25 πμ, Javier Guerra Giraldez wrote:
> <snip>

> in short: an application model is _not_ an OOP view of the database;
> it's the implementation of the conceptual data objects as handled by
> the application.
>
>

All right, I see the advantages. Nice technique.
Thanks a lot.