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)
気象データ
参考
以下の記事のソースを利用しています。
素のクエリ実行
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)