Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@tuk19(tuk)

Docker-Django-Mysqlで作る家計簿

Last updated at Posted at 2024-08-25

Mysqlで作った家計簿(https://qiita.com/tuk19/items/49db62eacfa68d1a77e4) をアプリにしたかったので、Djangoでつくってみました。

Docker

docker-compose.yml
version: '3'
services:
  db:
    build:
      context: .
      dockerfile: myapp/mysql/Dockerfile
    platform: linux/x86_64
    container_name: djangoapp_mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: djangoapp-db
      MYSQL_USER: djangoapp
      MYSQL_PASSWORD: djangoapp
      TZ: 'Asia/Tokyo'
    volumes:
      - data-volume:/var/lib/mysql
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
  web:
    build: .
    container_name: djangoapp
    command: python3 myapp/manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/code
    ports:
      - "8000:8000"
    depends_on:
      - db

volumes:
  data-volume:
Dcokerfile
FROM python:3
ENV PYTHONUNBUFFERED 1
RUN apt-get update && apt-get -y install default-mysql-client
RUN mkdir /code
WORKDIR /code
COPY requirements.txt /code/
RUN pip install --upgrade pip && pip install -r requirements.txt
COPY . /code/
requirements.txt
Django==3.1
mysqlclient
django-pandas
numpy
pandas

MySQLのDocker

mysql/Dockerfile
FROM mysql:5.7
COPY myapp/mysql/my.cnf /etc/mysql/conf.d/my.cnf

RUN apt-key adv --keyserver pgp.mit.edu --recv-keys 467B942D3A79BD29
RUN apt-get update
RUN apt-get install -y locales
RUN sed -i -e 's/# \(ja_JP.UTF-8\)/\1/' /etc/locale.gen
RUN locale-gen
RUN update-locale LANG=ja_JP.UTF-8

ENV LC_ALL ja_JP.UTF-8

ENV TZ Asia/Tokyo
ENV LANG=ja\_JP.UTF-8

CMD ["mysqld"]

EXPOSE 3306
mysql/my.cnf
[mysqld]

character_set_server=utf8mb4
collation_server=utf8mb4_bin

default_time_zone=SYSTEM
log_timestamps=SYSTEM

default_authentication_plugin=mysql_native_password

[mysql]

default_character_set=utf8mb4

[client]

default_character_set=utf8mb4

プロジェクトアプリケーションディレクトリ

myapp/settings.py
# 家計簿アプリケーションを追加
INSTALLED_APPS = [
    'kakeibo',
    :
    :
]

# データベースにMySQLを設定
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'djangoapp-db',
        'USER': 'root',
        'PASSWORD': 'root',
        'HOST': 'djangoapp_mysql',
        'PORT': '3306',
        'OPTIONS': {
            'charset': 'utf8mb4',
        },
        'TEST': {
            'NAME': "test_djangoapp_db_",
            'MIRROR': "default",
        },
    }
}

# staticディレクトリを追加
STATICFILES_DIRS = (
    [
        os.path.join(BASE_DIR, "static"),
    ]

家計簿アプリ

admin.py
from django.contrib import admin
from .models import Himoku, Kakeibo

admin.site.register(Himoku)
admin.site.register(Kakeibo)
forms.py
from django import forms
from .models import Himoku, Kakeibo

class HimokuForm(forms.ModelForm):
    class Meta:
        model = Himoku
        fields = ['name']

class KakeiboForm(forms.ModelForm):
    class Meta:
        model = Kakeibo
        fields = ['item_id', 'amount', 'income', 'create_date', 'detail']
        widgets = {'create_date': forms.NumberInput(attrs={'type': 'date'})}

class PaginatorForm(forms.Form):
    def __init__(self, *args, **kwargs):
        super(PaginatorForm, self).__init__(*args, **kwargs)
        self.fields['pages'] = forms.ChoiceField(
            label='件数',
            choices=[('-', '-'), (10, 10), (50, 50), (100, 100)],
            widget=forms.Select(attrs={'class': 'form-control'}),
        )

class SearchForm(forms.Form):
    search_himoku = forms.ChoiceField(label='費目', choices=[('-', '-')] + [(item.name, item.name) for item in Himoku.objects.all()], widget=forms.Select(attrs={'class': 'form-control'}))
    search_sday = forms.DateField(label='開始日', required=False, widget=forms.NumberInput(attrs={'class':'form-control', 'type':'date'}))
    search_eday = forms.DateField(label='終了日', required=False, widget=forms.NumberInput(attrs={'class':'form-control', 'type':'date'}))

class CalcForm(forms.Form):
    calc_form = forms.CharField(widget=forms.TextInput(attrs={'class': 'form-control'}))
models.py
from django.db import models

class Himoku(models.Model):
    name = models.CharField(max_length=20)
    def __str__(self):
        return self.name

class Kakeibo(models.Model):
    item_id = models.ForeignKey(Himoku, on_delete=models.PROTECT, related_name='himoku_id')
    amount = models.IntegerField(blank=True, null=True)
    income = models.IntegerField(blank=True, null=True)
    create_date = models.DateField()
    detail = models.CharField(max_length=200, blank=True, null=True)
urls.py
from django.urls import path
from . import views

urlpatterns = [
    path('', views.kakeibo, name='kakeibo'),
    path('<int:num>', views.kakeibo, name='kakeibo'),
    path('himoku', views.himoku, name='himoku'),
    path('create', views.kakeibo_create, name='kakeibo_create'),
    path('himoku/create', views.himoku_create, name='himoku_create'),
    path('edit/<int:num>', views.kakeibo_edit, name='kakeibo_edit'),
    path('himoku/edit/<int:num>', views.himoku_edit, name='himoku_edit'),
]
views.py
from django.shortcuts import render, redirect
from django.core.paginator import Paginator
from django.core.cache import cache
from django.db.models import Q, Count, Sum, Max, Min
from django_pandas.io import read_frame
from .models import Himoku, Kakeibo
from .forms import HimokuForm, KakeiboForm, PaginatorForm, SearchForm, CalcForm

import datetime

def himoku(request):
    data = Himoku.objects.all()
    params = {
        'title': '費目',
        'data': data,
    }
    return render(request, 'kakeibo/himoku.html', params)

def himoku_create(request):
    if (request.method == 'POST'):
        obj = Himoku()
        himoku = HimokuForm(request.POST, instance=obj)
        himoku.save()
        return redirect(to='/kakeibo/himoku')

    params = {
        'title': '費目追加',
        'form': HimokuForm(),
    }
    return render(request, 'kakeibo/himoku_create.html', params)

def kakeibo(request, num=1):
    page_form = PaginatorForm
    search_form = SearchForm

    page = Kakeibo.objects.all().count()
    if cache.get('data'):
        data = cache.get('data')
    else:
        data = Kakeibo.objects.all()

    if request.method == 'GET':
        page_form = PaginatorForm(request.session.get('page_form'))

    if request.method == 'POST':
        if request.POST['mode'] == '__page_form__':
            page = request.POST['pages']
            if page == '-':
                page = Kakeibo.objects.all().count()
            request.session['pages'] = page
            request.session['page_form'] = request.POST
            search_form = SearchForm
        elif request.POST['mode'] == '__search__':
            if "search" in request.POST:
                shimoku = Q()
                ssday = Q()
                seday = Q()
                if request.POST['search_himoku'] != '-':
                    shimoku = Q(item_id__name=request.POST['search_himoku'])

                if request.POST['search_sday']:
                    ssday = Q(create_date__gte=request.POST['search_sday'])

                if request.POST['search_eday']:
                    seday = Q(create_date__lte=request.POST['search_eday'])

                data = Kakeibo.objects.filter(shimoku, ssday, seday)
                data_sum = data.aggregate(Sum("amount"))
                data_max = data.aggregate(Max("amount"))
                data_aggregation = f'合計：{str(data_sum["amount__sum"])}円 \n 最大：{str(data_max["amount__max"])}円'

                request.session['search_form'] = request.POST
                cache.set('data', data)
                request.session['pages'] = data.count()
                request.session['data_aggregation'] = data_aggregation
            elif "CSV" in request.POST:
                data_csv = None
                if cache.get('data'):
                    data_csv = cache.get('data')
                else:
                    data_csv = Kakeibo.objects.all()
                df = read_frame(data_csv)
                now = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
                df.to_csv(f'myapp/kakeibo/tmp/csv/data_{now}.csv')
            page_form = PaginatorForm(request.session.get('page_form'))
            search_form = SearchForm(request.session.get('search_form'))

    if request.session.get('pages'):
        page = request.session.get('pages')

    page_message = f'{page}件表示します'
    paginator = Paginator(data.order_by('id').reverse(), page)

    params = {
        'title': '家計簿',
        'data': paginator.get_page(num),
        'page_form': page_form,
        'page_message': page_message,
        'search_form': search_form,
    }

    if request.session.get('data_aggregation'):
        params['data_aggregation'] = request.session['data_aggregation']

    return render(request, 'kakeibo/kakeibo.html', params)

def kakeibo_create(request):
    message = '最新のデータを表示します。'
    if (request.method == 'GET'):
        request.session['calc_sum'] = 0
    elif (request.method == 'POST'):
        if 'calc' in request.POST:
            calc_list = request.POST['calc_form'].split()
            calc_sum = sum(map(int, calc_list))
            request.session['calc_sum'] = calc_sum
        else:
            obj = Kakeibo()
            kakeibo = KakeiboForm(request.POST, instance=obj)
            kakeibo.save()
            message = '以下のデータを追加しました。'
            request.session['calc_sum'] = 0

    params = {
        'title': '家計簿追加',
        'form': KakeiboForm(),
        'item': Kakeibo.objects.last(),
        'message': message,
        'calc_form': CalcForm,
    }


    if request.session['calc_sum'] != 0:
        params['form'] = KakeiboForm({'amount': int(request.session['calc_sum'])})

    return render(request, 'kakeibo/kakeibo_create.html', params)

def kakeibo_edit(request, num):
    obj = Kakeibo.objects.get(id=num)
    if (request.method == 'GET'):
        request.session['edit_calc_sum'] = 0
    elif (request.method == 'POST'):
        if 'calc' in request.POST:
            edit_calc_list = request.POST['calc_form'].split()
            edit_calc_sum = sum(map(int, edit_calc_list))
            request.session['edit_calc_sum'] = edit_calc_sum
        else:
            kakeibo = KakeiboForm(request.POST, instance=obj)
            kakeibo.save()
            return redirect(to='/kakeibo')

    params = {
        'title': '家計簿修正',
        'id': num,
        'form': KakeiboForm(instance=obj),
        'calc_form': CalcForm,
    }

    if request.session['edit_calc_sum'] != 0:
        params['form'] = KakeiboForm({
                            'item_id': obj.item_id,
                            'amount': int(request.session['edit_calc_sum']),
                            'income': obj.income,
                            'create_date': obj.create_date,
                            'detail': obj.detail
                        })

    return render(request, 'kakeibo/kakeibo_edit.html', params)

def himoku_edit(request, num):
    obj = Himoku.objects.get(id=num)
    if (request.method == 'POST'):
        himoku = HimokuForm(request.POST, instance=obj)
        himoku.save()
        return redirect(to='/kakeibo/himoku')

    params = {
        'title': '費目修正',
        'id': num,
        'form': HimokuForm(instance=obj)
    }
    return render(request, 'kakeibo/himoku_edit.html', params)

templates

layout.html
{% load static %}
<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>{% block title %}{% endblock %}</title>
    <link rel="stylesheet" type="text/css" href="{% static 'kakeibo/css/kakeibo.css' %}">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
    <script type="text/javascript" src="{% static 'kakeibo/js/kakeibo.js' %}"></script>
</head>
<body>
    <div>
        {% block header %}
        {% endblock %}
    </div>
    <div>
        <nav>
            <ul class="link_nav flex">
                <li><a href="{% url 'kakeibo' %}">家計簿</a></li>
                <li><a href="{% url 'himoku' %}">費目</a></li>
                <li><a href="{% url 'kakeibo_create' %}">家計簿追加</a></li>
                <li><a href="{% url 'himoku_create' %}">費目追加</a></li>
            </ul>
        </nav>
    </div>

    <div>
        {% block content %}
        {% endblock %}
    </div>
    <div>
        <nav>
            <ul class="link_nav flex">
                <li><a href="{% url 'kakeibo' %}">家計簿</a></li>
                <li><a href="{% url 'himoku' %}">費目</a></li>
                <li><a href="{% url 'kakeibo_create' %}">家計簿追加</a></li>
                <li><a href="{% url 'himoku_create' %}">費目追加</a></li>
            </ul>
        </nav>
    </div>
</body>
</html>
himoku.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<div>
    <table class="data_table">
        <tr>
            <th>ID</th>
            <th>名前</th>
            <th>修正</th>
        </tr>
        {% for item in data %}
        <tr>
            <td>{{ item.id }}</td>
            <td>{{ item.name }}</td>
            <td><a href="{% url 'himoku_edit' item.id %}">修正</a></td>
        </tr>
        {% endfor %}
    </table>
</div>
{% endblock %}
himoku_create.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<form action="{% url 'himoku_create' %}", method="post">
    {% csrf_token %}
    <table>
        {{ form.as_table }}
        <tr>
            <th>
                <td><input type="submit" value="登録"></td>
            </th>
        </tr>
    </table>
</form>
{% endblock %}
himoku_edit.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<form action="{% url 'himoku_edit' id %}", method="post">
    {% csrf_token %}
    <table>
        {{ form.as_table }}
        <tr>
            <th>
                <td><input type="submit" value="修正"></td>
            </th>
        </tr>
    </table>
</form>
{% endblock %}
kakeibo.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<div>
    <form action="{% url 'kakeibo' %}", method="POST">
        {% csrf_token %}
        <input type="hidden" name="mode" value="__page_form__">
        {{ page_form.as_p }}
        <button type="submit">ページ変更</button>
    </form>
</div>
<div>
    <p>{{ page_message }}</p>
</div>
<div>
    <form action="{% url 'kakeibo' %}", method="POST">
        {% csrf_token %}
        <input type="hidden" name="mode" value="__search__">
        {{ search_form.as_p }}
        <button type="submit" name="search">検索</button>
        <button type="submit" name="CSV">CSV出力</button>
    </form>
</div>
{% if data_aggregation %}
<div>
    {{ data_aggregation }}
</div>
{% endif %}
<div>
    <table id="kakeibo_table" class="data_table">
        <tr>
            <!-- <th><span id="kakeibo_table_id" onclick="id_click()">ID</span></th> -->
            <th id="kakeibo_table_id">ID<span id="kakeibo_table_id_order"></span></th>
            <th id="kakeibo_table_himoku">費目名<span id="kakeibo_table_himoku_order"></span></th>
            <th id="kakiebo_table_amount">出費<span id="kakeibo_table_amount_order"></span></th>
            <th id="kakeibo_table_income">収入<span id="kakeibo_table_income_order"></span></th>
            <th id="kakeibo_table_createdate">作成日<span id="kakeibo_table_createdate_order"></span></th>
            <th>詳細</th>
            <th>修正</th>
        </tr>
        {% for item in data %}
        <tr>
            <td class="kakeibo_tr_id">{{ item.id }}</td>
            <td class="kakeibo_tr_himoku">{{ item.item_id.name }}</td>
            <td align="right"><span class="kakeibo_tr_amount" >{{ item.amount }}</span>円</td>
            {% if item.income %}
            <td class="kakeibo_tr_income">{{ item.income }}</td>
            {% else %}
            <td></td>
            {% endif %}
            <td class="kakeibo_tr_createdate">{{ item.create_date|date:"Y年m月d日" }}</td>
            {% if item.detail %}
            <td>{{ item.detail }}</td>
            {% else %}
            <td></td>
            {% endif %}
            <td><a href="{% url 'kakeibo_edit' item.id %}">修正</a></td>
        </tr>
        {% endfor %}
    </table>
    <div>
        <ul class="flex">
            {% if data.has_previous %}
            <li><a href="{% url 'kakeibo' %}">&laquo; first</a></li>
            <li><a href="{% url 'kakeibo' %}{{ data.previous_page_number }}">&laquo; prev</a></li>
            {% else %}
            <li><a>&laquo; first</a></li>
            <li><a>&laquo; prev</a></li>
            {% endif %}
            <li><a>{{ data.number }}/{{ data.paginator.num_pages }}</a></li>
            {% if data.has_next %}
            <li><a href="{% url 'kakeibo' %}{{ data.next_page_number }}">next &raquo;</a></li>
            <li><a href="{% url 'kakeibo' %}{{ data.paginator.num_pages }}">last &raquo;</a></li>
            {% else %}
            <li><a>next &raquo;</a></li>
            <li><a>last &laquo;</a></li>
            {% endif %}
        </ul>
    </div>
</div>
{% endblock %}
kakeibo_create.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<form action="{% url 'kakeibo_create' %}", method="post">
    {% csrf_token %}
    <!-- <input type="hidden" name="mode" value="__calc_form__"> -->
        <div class="flex">
            {{ calc_form.as_p }}
            <p><input type="submit" name="calc" value="計算"></p>
        </div>
    </div>
</form>

<form action="{% url 'kakeibo_create' %}", method="post">
    {% csrf_token %}
    <!-- <input type="hidden" name="mode" value="__kakeibo_form__"> -->

        <table>
            {{ form.as_table }}
            <tr>
                <th>
                    <td><input type="submit" value="登録"></td>
                </th>
            </tr>
        </table>
</form>

<div>
    <p>{{ calc_sum }}</p>
</div>
<div>
    <p>{{ message }}</p>
    <table class="data_table">
        <tr>
            <th>ID</th>
            <th>費目名</th>
            <th>出費</th>
            <th>収入</th>
            <th>作成日</th>
            <th>詳細</th>
            <th>修正</th>
        </tr>
        <tr>
            <td>{{ item.id }}</td>
            <td>{{ item.item_id.name }}</td>
            <td>{{ item.amount }}</td>
            <td>{{ item.income }}</td>
            <td>{{ item.create_date|date:"Y年m月d日" }}</td>
            <td>{{ item.detail }}</td>
            <td><a href="{% url 'kakeibo_edit' item.id %}">修正</a></td>
        </tr>
    </table>
</div>
{% endblock %}
kakeibo_edit.html
{% extends 'kakeibo/layout.html' %}

{% block title %}{{ title }}{% endblock %}

{% block header %}
<h2>
    {{ title }}
</h2>
{% endblock %}

{% block content %}
<form action="{% url 'kakeibo_edit' id %}", method="post">
    {% csrf_token %}
    <!-- <input type="hidden" name="mode" value="__calc_form__"> -->
        <div class="flex">
            {{ calc_form.as_p }}
            <p><input type="submit" name="calc" value="計算"></p>
        </div>
    </div>
</form>

<form action="{% url 'kakeibo_edit' id %}", method="post">
    {% csrf_token %}
    <table>
        {{ form.as_table }}
        <tr>
            <th>
                <td><input type="submit" value="修正"></td>
            </th>
        </tr>
    </table>
</form>
{% endblock %}

static

kakeibo.css
h2 {
    color: red;
}

.data_table {
    width: 100%;
    font-size: larger;
    border-spacing: 0px;
}

.data_table th {
    color: white;
    background-color: blue;
}

.data_table td {
    background-color: aquamarine;
    padding-top: 10px;
}

.link_nav {
    list-style-type: none;
    font-size: larger;
}

.flex {
    display: flex;
    margin: 0;
    padding: 0;
}

.flex li {
    margin: 0;
    width: 100%;
}
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?