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!

Recommended Posts

How to use Laravel-like ORM / query builder Orator in Django
How to use bootstrap in Django generic class view
How to use Decorator in Django and how to make it
How to use classes in Theano
How to check ORM behavior in one file with django
How to use SQLite in Python
How to generate a query using the IN operator in Django
How to reflect CSS in Django
How to use Mysql in python
How to use ChemSpider in Python
How to use PubChem in Python
How to use calculated columns in CASTable
[Introduction to Python] How to use class in Python?
How to delete expired sessions in Django
How to use Google Test in C
How to use Anaconda interpreter in PyCharm
How to use __slots__ in Python class
How to do Server-Sent Events in Django
How to use regular expressions in Python
How to convert DateTimeField format in Django
How to use Map in Android ViewPager
How to use is and == in Python
How to use the C library in Python
How to implement Rails helper-like functionality in Django
How to use Python Image Library in python3 series
How to reflect ImageField in Django + Docker (pillow)
How to run some script regularly in Django
Summary of how to use MNIST in Python
How to use tkinter with python in pyenv
How to create a Rest Api in Django
How to use xml.etree.ElementTree
How to use Python-shell
How to use tf.data
How to use virtualenv
How to use Seaboan
How to use image-match
How to use shogun
How to use Virtualenv
How to use numpy.vectorize
How to use pytest_report_header
How to use partial
How to use Bio.Phylo
How to use SymPy
How to use x-means
How to use WikiExtractor.py
How to use IPython
How to use virtualenv
How to use Matplotlib
How to use iptables
How to use numpy
How to use TokyoTechFes2015
How to use venv
How to use dictionary {}
How to use Pyenv
How to use list []
How to use python-kabusapi
How to use OptParse
How to use return
How to use dotenv
How to use pyenv-virtualenv
How to use Go.mod