sql code to django

Question

I have a SQL query:

select name, min(time(eventime)), max(time(eventime)) 
from events 
    inner join emp 
        on empid = id 
group by date(eventime)

How do I make this work in Django?

My models is:

class Emp(models.Model):
      id = models.IntegerField(primary_key=True)
      lastname = models.CharField(max_length=192)
      firstname = models.CharField(max_length=192, blank=True)
      midname = models.CharField(max_length=96, blank=True)

class Events(models.Model):
      serialid = models.IntegerField(primary_key=True)
      empid = models.IntegerField()
      cardnum = models.IntegerField()
      eventime = models.DateTimeField()

Are you sure the sql query is correct? There is no name field in your Django models. It looks like you might want to group by emp.id as well as date. — Alasdair
– Alasdair, Commented Oct 21, 2011 at 16:20

Alasdair · Accepted Answer · 2011-10-21 16:25:00Z

1

You can execute pretty much any SQL query like this:

from django.db import connection
cursor = connection.cursor()
sql = 'select name, min(time(eventime)), max(time(eventime)) ' \
      'from abc_table group by date(eventime)'
cursor.execute(sql)
retval = cursor.fetchall()

For more information see the Django docs on executing custom sql directly.

edited Oct 21, 2011 at 16:25

Alasdair

310k59 gold badges605 silver badges534 bronze badges

answered Oct 21, 2011 at 15:11

ayanami

1,7711 gold badge16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ewok Over a year ago

While this is possible, it is bad form to use raw SQL in Django. Django is designed for the specific purpose of hiding the SQL and making the database easier to work with.

Alasdair Over a year ago

I disagree with the downvote. I don't think this particular SQL query maps to an ORM query easily (although I'm happy to be proved wrong). In cases like this, there's no shame in dropping back to raw SQL.

ayanami Over a year ago

@ewok: Django ORM is created for the specific purpose of simplifying CRUD, and given more or less complex query you are forced to go back to plain old SQL. I believe that the original poster’s query actually can be converted to ORM, but it won’t get any more readable from doing so, and it doesn’t map to a model anyway.

Collectives™ on Stack Overflow

sql code to django

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related