Django でテーブルを表示する

Django で JSON, csv から取り込んだテーブルを表示する方法です。
プロジェクトを作成後、MariaDB への切り替え、スーパーユーザーの作成が完了した状態で、アプリを作成します。
そこまでの手順はこちら
>Project の作成
>>[Django で Hello World](https://qiita.com/ekzemplaro/items/ef63edfa93caaab4071e)

>MariaDB を使えるようにする
>>[Django で MariaDB を使う](https://qiita.com/ekzemplaro/items/b0140f16a780c689b890)

プロダクト名は、proj01
作成するアプリ名は、city とします。

データベースは
ユーザー django
パスワード tiger123
データベース django_city
で接続できるとします。
>接続できることの確認方法

>```bash
mysql -udjango -ptiger123 django_city
```

1) アプリ city の作成

```bash
python manage.py startapp city
```

2) proj01/settings.py の編集

```py:proj01/settings.py
（省略）
INSTALLED_APPS = [
    'home',
    'city',
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
]
（省略）
```

3) proj01/urls.py の編集

```py:proj01/urls.py
（省略）
urlpatterns = [
    path('', include('home.urls')),
    path('city/', include('city.urls')),
    path('admin/', admin.site.urls),
]
```

4) テンプレートの作成

```bash
mkdir city/templates
mkdir city/templates/city
```

```py:city/templates/city/city.html
<!DOCTYPE html>
<html lang="ja">
<head>
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8" />
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
{% load static %}
<script src="{% static 'city/city.js' %}"></script>
<link rel="stylesheet" href="{% static 'city/city.css' %}">
<title>city</title>
</head>
<body>
<h2>city template!</h2>
{{ hour }}時{{ minute }}分です。<br>
{{ message | safe }}<p />
<hr />
<span class='green'>green</span>
<span class='red'>red</span>
<span class='blue'>blue</span>
<hr />
<table>
<tr>
<th>key</th>
<th>name</th>
<th>population</th>
<th>date_mod</th>
</tr>
{% for item in data %}
<tr>
<td>{{item.key}}</td>
<td>{{item.name}}</td>
<td>{{item.population}}</td>
<td>{{item.date_mod}}</td>
</tr>
{% endfor %}
</table>
<hr />
<a href='../'>Return</a><p />
<hr />
<div id="outarea_aa">outarea_aa</div>
<div id="outarea_bb">outarea_bb</div>
<div id="outarea_cc">outarea_cc</div>
<div id="outarea_dd">outarea_dd</div>
<div id="outarea_ee">outarea_ee</div>
<div id="outarea_ff">outarea_ff</div>
<div id="outarea_gg">outarea_gg</div>
<div id="outarea_hh">outarea_hh</div>
<hr />
city.html<p />
Dec/23/2018 PM 20:01<p />
</body>
</html>
```

5)  静的ファイルの作成

```bash
mkdir city/static
mkdir city/static/city
```

```js:city/static/city/city.js
// -----------------------------------------------------------------------
//	city.js
//
//					Dec/23/2018
//
// -----------------------------------------------------------------------
jQuery (function ()
{
	jQuery("#outarea_aa").html ("*** city.js *** start *** Dec/23/2018 ***")

	jQuery("#outarea_hh").html ("*** city.js *** end *** Dec/23/2018 ***")
})

// -----------------------------------------------------------------------
```

```css:city/static/city/city.css
/* -------------------------------------------------------------- */
/*
	city.css


				Dec/23/2018
*/
/* -------------------------------------------------------------- */
table.main,td,th {
table-layout:fixed;
border:1.5px #7e7e7e solid;
border-collapse: collapse;
height: 16px;
}

th {
	background: #c6c6c6;
}


table.tag {
border:0.5px green solid;
}

tr.cyan {
	background-color: #c7d7c7;
}


span.green{color: green}
span.red{color: red}
span.blue{color: blue}

/* -------------------------------------------------------------- */
```

6) city/views.py の編集

```py:city/views.py
from datetime import datetime

from django.http import HttpResponse
from django.shortcuts import render
from django.contrib.auth.decorators import login_required

from .models import City

def index(request):
	data = City.objects.all()
	message = ""
	message += 'city からのメッセージです。<br />'
	message += str(request.user.id) + '&nbsp;&nbsp;'
	message += request.user.username + '<p />'
	params = {
		'hour': datetime.now().hour,
		'minute': datetime.now().minute,
		'message': message,
		'data': data,
	}
	return render(request, 'city/city.html', params)
```

7) city/urls.py の作成

```py:city/urls.py
from django.urls import path

from . import views

urlpatterns = [
    path('', views.index, name='index'),
]
```

8) city/models.py の編集

```py:city/models.py
from django.db import models

class City(models.Model):
	key = models.CharField(max_length=20)
	name = models.CharField(max_length=50)
	population = models.IntegerField()
	date_mod = models.CharField(max_length=20)
	def __unicode__(self):
		return self.key
```

9) city/admin.py の編集
>管理画面でデータを編集できるようにします。

```py:city/admin.py
from django.contrib import admin
from city.models import City
 
admin.site.register(City)
```

10) データベースにテーブルを作成

```bash
python manage.py makemigrations city
python manage.py migrate
```

11) カスタムコマンドの作成

```bash
mkdir city/management
mkdir city/management/commands
```

次の４つのコマンドを作成します。
> 一覧表示
>city/management/commands/list_cities.py

> JSON からのインポート
>city/management/commands/register_cities_json.py

> csv からのインポート
>city/management/commands/register_cities_csv.py

> データの削除
>city/management/commands/delete_all_cities.py

```py:city/management/commands/list_cities.py
from django.core.management.base import BaseCommand
from django.utils import timezone
from city.models import City

class Command(BaseCommand):
	help = 'Displays current time'

	def handle(self, *args, **kwargs):
		time = timezone.now().strftime('%X')
		self.stdout.write("It's now %s" % time)
#
		for hh in City.objects.all():
			str_out = ""
			str_out += hh.key + "\t"
			str_out += hh.name + "\t"
			str_out += str(hh.population) + "\t"
			str_out += str(hh.date_mod) + "\t"
			print(str_out)
```

```py:city/management/commands/register_cities_json.py
from django.core.management.base import BaseCommand
from django.utils import timezone
from city.models import City
import json

# --------------------------------------------------------------------
def file_to_str_proc(file_in):
	str_out = ""
	try:
		fp_in = open(file_in,encoding='utf-8')
		str_out = fp_in.read()
		fp_in.close()
	except Exception as ee:
		sys.stderr.write("*** error *** file_to_str_proc ***\n")
		sys.stderr.write(str (ee))
#
	return	str_out
# --------------------------------------------------------------------

file_json = "static/city/cities.json"

class Command(BaseCommand):
	help = 'Displays current time'

	def handle(self, *args, **kwargs):
		time = timezone.now().strftime('%X')
		self.stdout.write("It's now %s" % time)
#
		json_str = file_to_str_proc(file_json)
		dict_aa = json.loads(json_str)
		for key in sorted(dict_aa.keys()):
			unit = dict_aa[key]
			hh = City()
			hh.key = key
			hh.name = unit['name']
			hh.population = str(unit['population'])
			hh.date_mod = str(unit['date_mod'])
			hh.save()
#
# --------------------------------------------------------------------
```

```py:city/management/commands/register_cities_csv.py
from django.core.management.base import BaseCommand
from django.utils import timezone
from city.models import City
import csv

file_csv = "static/city/cities.csv"

class Command(BaseCommand):
	help = 'Displays current time'

	def handle(self, *args, **kwargs):
		time = timezone.now().strftime('%X')
		self.stdout.write("It's now %s" % time)
#
		fp = open(file_csv, 'r')
		reader = csv.reader(fp)
		for rr in reader:
			print(rr)
			hh = City()
			hh.key = rr[0]
			hh.name = rr[1]
			hh.population = rr[2]
			hh.date_mod = rr[3]
			hh.save()
#
		fp.close()
```

```py:city/management/commands/delete_all_cities.py
from django.core.management.base import BaseCommand
from django.utils import timezone
from city.models import City

class Command(BaseCommand):
	help = 'Displays current time'

	def handle(self, *args, **kwargs):
		time = timezone.now().strftime('%X')
		self.stdout.write("It's now %s" % time)
#
		hh = City.objects.all()
		hh.delete()
```

12) データの用意

```bash
mkdir static
mkdir static/city
```
```json:static/city/cities.json
{
  "t0921": {
    "name": "宇都宮",
    "population": 41295,
    "date_mod": "2003-8-12"
  },
  "t0922": {
    "name": "小山",
    "population": 38756,
    "date_mod": "2003-5-15"
  },
  "t0923": {
    "name": "佐野",
    "population": 71294,
    "date_mod": "2003-6-8"
  },
  "t0924": {
    "name": "足利",
    "population": 27138,
    "date_mod": "2003-7-21"
  },
  "t0925": {
    "name": "日光",
    "population": 74682,
    "date_mod": "2003-4-19"
  },
  "t0926": {
    "name": "下野",
    "population": 82951,
    "date_mod": "2003-10-14"
  },
  "t0927": {
    "name": "さくら",
    "population": 96823,
    "date_mod": "2003-5-24"
  },
  "t0928": {
    "name": "矢板",
    "population": 57926,
    "date_mod": "2003-2-12"
  },
  "t0929": {
    "name": "真岡",
    "population": 64187,
    "date_mod": "2003-11-14"
  },
  "t0930": {
    "name": "栃木",
    "population": 82354,
    "date_mod": "2003-7-04"
  },
  "t0931": {
    "name": "大田原",
    "population": 72681,
    "date_mod": "2003-9-17"
  },
  "t0932": {
    "name": "鹿沼",
    "population": 23749,
    "date_mod": "2003-7-20"
  },
  "t0933": {
    "name": "那須塩原",
    "population": 12759,
    "date_mod": "2003-3-12"
  },
  "t0934": {
    "name": "那須烏山",
    "population": 62531,
    "date_mod": "2003-8-17"
  }
}
```

```csv:static/city/cities.csv
t1271,千葉,59178,2003-9-21
t1272,勝浦,47215,2003-10-15
t1273,市原,83654,2003-6-14
t1274,流山,74251,2003-9-9
t1275,八千代,42516,2003-8-4
t1276,我孫子,35987,2003-1-21
t1277,鴨川,81256,2003-7-23
t1278,銚子,29374,2003-10-26
t1279,市川,87613,2003-2-17
```

12) カスタムコマンドが出来ているのを確認

```text
$ python manage.py help
（省略）
[city]
    delete_all_cities
    list_cities
    register_cities_csv
    register_cities_json
（省略）
```

13) JSON からデータの取り込み

```text
$ python manage.py register_cities_json
It's now 10:10:39
```

14) データの表示

```text
$ python manage.py list_cities
It's now 10:11:21
t0921	宇都宮	41295	2003-8-12	
t0922	小山	38756	2003-5-15	
t0923	佐野	71294	2003-6-8	
t0924	足利	27138	2003-7-21	
t0925	日光	74682	2003-4-19	
t0926	下野	82951	2003-10-14	
t0927	さくら	96823	2003-5-24	
t0928	矢板	57926	2003-2-12	
t0929	真岡	64187	2003-11-14	
t0930	栃木	82354	2003-7-04	
t0931	大田原	72681	2003-9-17	
t0932	鹿沼	23749	2003-7-20	
t0933	那須塩原	12759	2003-3-12	
t0934	那須烏山	62531	2003-8-17
```

15) データの削除

```text
$ python manage.py delete_all_cities
It's now 10:13:06
```

16) csv からデータの取り込み

```text
$ python manage.py register_cities_csv
It's now 10:13:49
['t1271', '千葉', '59178', '2003-9-21']
['t1272', '勝浦', '47215', '2003-10-15']
['t1273', '市原', '83654', '2003-6-14']
['t1274', '流山', '74251', '2003-9-9']
['t1275', '八千代', '42516', '2003-8-4']
['t1276', '我孫子', '35987', '2003-1-21']
['t1277', '鴨川', '81256', '2003-7-23']
['t1278', '銚子', '29374', '2003-10-26']
['t1279', '市川', '87613', '2003-2-17']
```

17) データの表示

```text
$ python manage.py list_cities
It's now 10:14:21
t1271	千葉	59178	2003-9-21	
t1272	勝浦	47215	2003-10-15	
t1273	市原	83654	2003-6-14	
t1274	流山	74251	2003-9-9	
t1275	八千代	42516	2003-8-4	
t1276	我孫子	35987	2003-1-21	
t1277	鴨川	81256	2003-7-23	
t1278	銚子	29374	2003-10-26	
t1279	市川	87613	2003-2-17
```

18)  開発サーバーを起動

```bash
python manage.py runserver
```

ブラウザーで　http://127.0.0.1:8000/city/ にアクセス
![city_dec2401.png](https://qiita-image-store.s3.amazonaws.com/0/179446/53821aba-8232-2d7b-86b5-c5869e8ea129.png)

19) 管理ページにログインしてデータの CRUD ができます。
>http://127.0.0.1:8000/admin/city/city/
![city_dec2402.png](https://qiita-image-store.s3.amazonaws.com/0/179446/4d47c316-4934-cc69-d4f0-cbd4ab6c1ca5.png)

![city_dec2403.png](https://qiita-image-store.s3.amazonaws.com/0/179446/73d809cf-b773-e81f-870e-8a1382f3ee97.png)


次のバージョンで確認しました。

```text
$ python --version
Python 3.8.5
$ python -m django --version
3.1.1
```