[PYTHON] How to use Laravel-like ORM / query builder Orator in Django

This post is the 0th day article of "Django Advent Calendar 2019 --Qiita". (Arbitrarily) (I'm going to get angry) (I'll erase it if I get angry)

By the way, what kind of library does DB Access use when you make a web application with Python? I think the most popular one is Django ORM. If you are using Flask or Pyramid, SQLAlchemy is your choice. People in the data science field may be using Pandas' DataFrame. But the library I recommend most is the "Orator" in the title. There are already some articles written about Orator here on Qiita.

Memo because I touched "Orator" Save data scraped with Scrapy to RDB with ORM

Orator is an ORM / query builder that was very much influenced by Laravel's ORM grammar. Official site The development was started by sdispater. If you are deeply addicted to Python, you may notice it. He has been working on the development of Poetry recently. Just because it was strongly influenced by Laravel's ORM, migration has to create the migration file by itself like Laravel and Rails. This will give Django a boost to automatically detect table changes and create migration files. But what's great about Orator is the ease of writing queries.

students = Student \
    .select('id', 'name', 'email') \
    .join('class', 'class.num', '=', 'students.class_num') \
    .where('class.id', 3) \
    .get()

Hey? SQL-like and intuitive, right? You can connect as many joins and where as you like with a method chain. Don't miss any complex queries!

So how do you get this into Django? I will start the main subject from here.

Let's create orator.py in the application

Based on the directory structure created by Django Official Tutorial, orator.py is in the same hierarchy as models.py. Create a file called.

├── mysite
│   ├── polls
│   │   ├── __init__.py
│   │   ├── admin.py
│   │   ├── apps.py
│   │   ├── migrations
│   │   │   └── __init__.py
│   │   ├── models.py
│   │   ├── orator.py ←←←←←←←NEW!!!
│   │   ├── tests.py
│   │   ├── urls.py
│   │   └── views.py
│   ├── manage.py
│   ├── mysite
│   │   ├── __init__.py
│   │   ├── settings.py
│   │   ├── urls.py
│   │   └── wsgi.py
│   └── templates

Next, in orator.py, set the information to inform the connection information to the DB in the orator library. You can enter a fixed value here, but since the connection information to the DB is already defined in settings.py, it is better to call it from here.

mysite/polls/orator.py

import logging

from orator import DatabaseManager
from orator import Model
from django.conf import settings

config = {
    'mysql': {
        'driver': 'mysql',
        'database': settings.DATABASES['default']['NAME'],
        'host': settings.DATABASES['default']['HOST'],
        'user': settings.DATABASES['default']['USER'],
        'password': settings.DATABASES['default']['PASSWORD'],
        'prefix': ''
        'log_queries': True,
    }
}


db = DatabaseManager(config)
Model.set_connection_resolver(db)


logger = logging.getLogger('orator.connection.queries')
logger.setLevel(logging.DEBUG)

formatter = logging.Formatter(
    'It took %(elapsed_time)sms to execute the query %(query)s'
)

handler = logging.StreamHandler()
handler.setFormatter(formatter)

logger.addHandler(handler)

You can use three types of driver:" sqlite "," mysql ", and" postgres ". Note Typo as it will not work unless you write it exactly this way.

Pull the contents of settings.py with from django.conf import settings. After that, you can get the desired value by following Dict.

Let's define a model class

Orator's model class, unlike Django and SQL Alchemy, is not a table schema. It just defines "this class is tied to this table".

As an example, take the Question and Choice classes used in the polls app in the Django Official Tutorials (https://docs.djangoproject.com/ja/2.2/intro/tutorial02/#creating-models). Let's define it.

mysite/polls/orator.py

class Question(Model):
    __table__ = 'polls_questions'


class Choice(Model):
    __table__ = 'polls_choices'

The table name created by Django is prefixed with the application name, so let's write the table name explicitly with that in mind. However, the way I usually develop is to avoid prefixing the application name when creating a table in Django.

mysite/polls/models.py

class Question(models.Model):
    question_text = models.CharField(max_length=200)
    pub_date = models.DateTimeField('date published')

    class Meta:
        # db_When you define a table, it becomes the name of the table that is actually created.
        db_table = 'questions'

mysite/polls/orator.py

class Question(Model):
    __table__ = 'questions'

Let's call Orator in View

What happens if I rewrite the DB-accessed part of this code in Django Official Tutorial with Orator? It will be as follows.

mysite/polls/views.py

from django.shortcuts import render

- from .models import Question
+ from .orator import Question

def index(request):
    - latest_question_list = Question.objects.order_by('-pub_date')[:5]
    + latest_question_list = Question.order_by('pub_date', 'asc').limit(5)
    context = {'latest_question_list': latest_question_list}
    return render(request, 'polls/index.html', context)

Why easy! Bravo!

in conclusion

Incorporate Orator in Django, let's share the roles with Orator for table creation / migration and Django model for query issuance! Orator can be a great help, especially in web app development, where you often issue complex queries. More widespread!