Djangoで素のSQLを実行する方法

Djangoで素のSQLを実行する方法をご紹介します。

条件

  • Django 2.1.4
  • Python 3.7.0

前提

モデル

場所:気象データ = 1:N になるようモデルを作成します。

# models.py
from django.db import models
from django.urls import reverse
from datetime import datetime as dt


class Location(models.Model):
    """場所モデル"""
    class Meta:
        db_table = 'location'

    name = models.CharField(verbose_name='ロケーション名', max_length=255)
    memo = models.CharField(verbose_name='メモ', max_length=255, default='', blank=True)
    author = models.ForeignKey(
        'auth.User',
        on_delete=models.CASCADE,
    )
    created_at = models.DateTimeField(verbose_name='登録日時', auto_now_add=True)
    updated_at = models.DateTimeField(verbose_name='更新日時', auto_now=True)

    def __str__(self):
        return self.name

    @staticmethod
    def get_absolute_url(self):
        return reverse('monitor:index')


class WeatherData(models.Model):
    """気象データモデル"""
    class Meta:
        db_table = 'weather_data'
        unique_together = (('location', 'data_datetime'),)

    location = models.ForeignKey(Location, verbose_name='ロケーション', on_delete=models.PROTECT)
    data_datetime = models.DateTimeField(verbose_name='データ日時', default=dt.strptime('2001-01-01 00:00:00', '%Y-%m-%d %H:%M:%S'))
    temperature = models.FloatField(verbose_name='気温')
    humidity = models.FloatField(verbose_name='湿度')
    created_at = models.DateTimeField(verbose_name='登録日時', auto_now_add=True)
    updated_at = models.DateTimeField(verbose_name='更新日時', auto_now=True)

    def __str__(self):
        return self.location.name + ":" + str(self.data_datetime)

DBデータ

以下のようなレコードが入っているものとします。

場所(Location)

気象データ

参考

以下の記事のソースを利用しています。

Djangoでmatplotlibによるグラフ表示する方法

素のクエリ実行

raw() を使用することで、SQL文を指定して結果を取得することが出来ます。

以下はそれぞれ同じ結果となります。

weather_data = WeatherData.objects.raw('SELECT * FROM weather_data')
weather_data = WeatherData.objects.all()

パラメタを渡すことも可能です。

def setPlt(pk):

    weather_data = WeatherData.objects.raw('SELECT * FROM weather_data WHERE location_id = %s', str(pk))

独自のSQLを直接実行

モデル層を完全に迂回してデータベースに直接アクセスすることも出来ます。

django.db.connectionのカーソルオブジェクトを使用します。

from django.db import connection

def setPlt(pk):

    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM weather_data WHERE location_id = %s", [str(pk)])
        weather_data = cursor.fetchall()

処理の流れは以下の通りです。

  • カーソルオブジェクトを取得
    • connection.cursor()
  • SQL を実行
    • cursor.execute(sql, [params])
  • 結果の行を取得
    • すべての結果を取得:cursor.fetchall()、または、1つの結果を取得:cursor.fetchone()

ちなみに、上記の処理ではweather_dataはリストとして返ります。

from django.db import connection

def setPlt(pk):

    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM weather_data WHERE location_id = %s", [str(pk)])
        weather_data = cursor.fetchall()

    for data in weather_data:
        print(data)

<実行結果>

(6, datetime.datetime(2018, 12, 10, 0, 0), 20.0, 50.0, datetime.datetime(2018, 12, 10, 10, 56, 24, 817118), datetime.datetime(2018, 12, 10, 10, 56, 24, 817118), 2)
(7, datetime.datetime(2018, 12, 10, 1, 0), 19.0, 70.0, datetime.datetime(2018, 12, 10, 10, 56, 43, 795075), datetime.datetime(2018, 12, 10, 10, 56, 43, 795075), 2)
(8, datetime.datetime(2018, 12, 10, 2, 0), 18.0, 60.0, datetime.datetime(2018, 12, 10, 10, 57, 4, 174500), datetime.datetime(2018, 12, 10, 10, 57, 4, 174500), 2)
(9, datetime.datetime(2018, 12, 10, 3, 0), 17.0, 80.0, datetime.datetime(2018, 12, 10, 10, 57, 20, 700227), datetime.datetime(2018, 12, 10, 10, 57, 20, 700227), 2)
(10, datetime.datetime(2018, 12, 10, 4, 0), 16.0, 75.0, datetime.datetime(2018, 12, 10, 10, 57, 34, 804380), datetime.datetime(2018, 12, 10, 10, 57, 34, 804380), 2)

辞書形式での結果を取得

以下のような関数を用いることで、結果を辞書形式で取得することが出来ます。
(処理能力とメモリを少々使用します。)

from django.db import connection

def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

def setPlt(pk):

    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM weather_data WHERE location_id = %s", [str(pk)])
        weather_data = dictfetchall(cursor)
    
    for data in weather_data:
        print(data)

<実行結果>

{'id': 6, 'data_datetime': datetime.datetime(2018, 12, 10, 0, 0), 'temperature': 20.0, 'humidity': 50.0, 'created_at': datetime.datetime(2018, 12, 10, 10, 56, 24, 817118), 'updated_at': datetime.datetime(2018, 12, 10, 10, 56, 24, 817118), 'location_id': 2}
{'id': 7, 'data_datetime': datetime.datetime(2018, 12, 10, 1, 0), 'temperature': 19.0, 'humidity': 70.0, 'created_at': datetime.datetime(2018, 12, 10, 10, 56, 43, 795075), 'updated_at': datetime.datetime(2018, 12, 10, 10, 56, 43, 795075), 'location_id': 2}
{'id': 8, 'data_datetime': datetime.datetime(2018, 12, 10, 2, 0), 'temperature': 18.0, 'humidity': 60.0, 'created_at': datetime.datetime(2018, 12, 10, 10, 57, 4, 174500), 'updated_at': datetime.datetime(2018, 12, 10, 10, 57, 4, 174500), 'location_id': 2}
{'id': 9, 'data_datetime': datetime.datetime(2018, 12, 10, 3, 0), 'temperature': 17.0, 'humidity': 80.0, 'created_at': datetime.datetime(2018, 12, 10, 10, 57, 20, 700227), 'updated_at': datetime.datetime(2018, 12, 10, 10, 57, 20, 700227), 'location_id': 2}
{'id': 10, 'data_datetime': datetime.datetime(2018, 12, 10, 4, 0), 'temperature': 16.0, 'humidity': 75.0, 'created_at': datetime.datetime(2018, 12, 10, 10, 57, 34, 804380), 'updated_at': datetime.datetime(2018, 12, 10, 10, 57, 34, 804380), 'location_id': 2}

collections.namedtuple() を利用

collections.namedtuple()を用いることで、より利用しやすい結果を取得することが出来ます。

from django.db import connection
from collections import namedtuple

def namedtuplefetchall(cursor):
    "Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

def setPlt(pk):

    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM weather_data WHERE location_id = %s", [str(pk)])
        weather_data = namedtuplefetchall(cursor)
    
    for data in weather_data:
        print(data)

<実行結果>

Result(id=6, data_datetime=datetime.datetime(2018, 12, 10, 0, 0), temperature=20.0, humidity=50.0, created_at=datetime.datetime(2018, 12, 10, 10, 56, 24, 817118), updated_at=datetime.datetime(2018, 12, 10, 10, 56, 24, 817118), location_id=2)
Result(id=7, data_datetime=datetime.datetime(2018, 12, 10, 1, 0), temperature=19.0, humidity=70.0, created_at=datetime.datetime(2018, 12, 10, 10, 56, 43, 795075), updated_at=datetime.datetime(2018, 12, 10, 10, 56, 43, 795075), location_id=2)
Result(id=8, data_datetime=datetime.datetime(2018, 12, 10, 2, 0), temperature=18.0, humidity=60.0, created_at=datetime.datetime(2018, 12, 10, 10, 57, 4, 174500), updated_at=datetime.datetime(2018, 12, 10, 10, 57, 4, 174500), location_id=2)
Result(id=9, data_datetime=datetime.datetime(2018, 12, 10, 3, 0), temperature=17.0, humidity=80.0, created_at=datetime.datetime(2018, 12, 10, 10, 57, 20, 700227), updated_at=datetime.datetime(2018, 12, 10, 10, 57, 20, 700227), location_id=2)
Result(id=10, data_datetime=datetime.datetime(2018, 12, 10, 4, 0), temperature=16.0, humidity=75.0, created_at=datetime.datetime(2018, 12, 10, 10, 57, 34, 804380), updated_at=datetime.datetime(2018, 12, 10, 10, 57, 34, 804380), location_id=2)

namedtupleオブジェクトは以下のような特徴があり便利です。

  • 属性名でアクセスできる。
  • インデックスでアクセスできる。

例)以下のようなアクセスが出来ます。(同じ結果が取得されます)

print(weather_data[0].data_datetime)
print(weather_data[0][1])

<実行結果>

2018-12-10 00:00:00
2018-12-10 00:00:00

様々な実装方法

参考として様々な実装方法の例を以下に示します。

1.raw() を使用する場合

def setPlt(pk):

    weather_data = WeatherData.objects.raw('SELECT * FROM weather_data WHERE location_id = %s', str(pk))

    x = [data.data_datetime for data in weather_data] # 日時
    y1 = [data.temperature for data in weather_data] # 気温
    y2 = [data.humidity for data in weather_data]  # 湿度

    plt.plot(x, y1, x, y2)

2.カーソルを利用する場合

from django.db import connection

def setPlt(pk):

    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM weather_data WHERE location_id = %s", [str(pk)])
        weather_data = cursor.fetchall()

    x = [data[1] for data in weather_data]  # 日時
    y1 = [data[2] for data in weather_data] # 気温
    y2 = [data[3] for data in weather_data]  # 湿度

    plt.plot(x, y1, x, y2)

3.カーソルを利用する場合(辞書形式の結果取得)

from django.db import connection

def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]
    
def setPlt(pk):

    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM weather_data WHERE location_id = %s", [str(pk)])
        weather_data = cursor.fetchall()

    x = [data.get("data_datetime") for data in weather_data]  # 日時
    y1 = [data.get("temperature") for data in weather_data]  # 気温
    y2 = [data.get("humidity") for data in weather_data]  # 湿度

    plt.plot(x, y1, x, y2)

4.カーソルを利用する場合(namedtupleの結果取得)

from django.db import connection
from collections import namedtuple

def namedtuplefetchall(cursor):
    "Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

def setPlt(pk):

    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM weather_data WHERE location_id = %s", [str(pk)])
        weather_data = namedtuplefetchall(cursor)

    x = [data.data_datetime for data in weather_data] # 日時
    y1 = [data.temperature for data in weather_data] # 気温
    y2 = [data.humidity for data in weather_data]  # 湿度

    plt.plot(x, y1, x, y2)

参考

Django公式:素のSQL文の実行

https://docs.djangoproject.com/ja/2.1/topics/db/sql/

Django

コメントを残す

メールアドレスが公開されることはありません。