python Django学习（8）——数据库操作整理

前言

    记录一下Django操作数据库,包括单表操作,一对多操作,多对多操作.
项目目录:
>migrations中是数据表的操作记录.

app01:

models.py:

from django.db import models
# Create your models here.

#
class Host(models.Model):
    nid = models.AutoField(primary_key=True)
    hostname = models.CharField(max_length=32,db_index=True)
    ip = models.GenericIPAddressField(db_index=True)
    port = models.IntegerField()
    b = models.ForeignKey("Business",to_field='id',on_delete='')    # 一对多


class Business(models.Model):
    # 默认有个自增的id列,并且是主键
    caption = models.CharField(max_length=32)
    code = models.CharField(max_length=32,default='sa')


class Application(models.Model):
    name = models.CharField(max_length=32)
    r = models.ManyToManyField("Host")      # 自动创建关系表,多对多

# 当然,也可以自定义关系表
# class HostToApp(models.Model):
#     hobj = models.ForeignKey(to='Host',to_field='nid')
#     aobj = models.ForeignKey(to='Application',to_field='id')


'''
    这样会创建两个表:一个是app01_Application,还有一个app01_application_r(这是自动创建的一个关系表)
    但是这样有一点不好的就是,我们无法通过类直接操作数据库,而自定义的关系表我们可以通过类直接对数据表进行操作
'''

'''
    使用models.ManyToManyField("Host") 时候,我们对第三张表的操作如下:
        obj = Application.objects.get(id=1)
        obj.name
        obj.r.add(1)    # obj.r就是对应的第三张表
        obj.r.add(1,2,3)    # 也可以是这种形式
        obj.r.add([1,2,3,4])    # 也可以是列表
        也就是说,我们需要通过obj.r来操作第三张表
        
        obj.r.remove(1)      # remove是删除
        obj.r.remove(1,2,3)      # 也可以是这种形式
        obj.r.remove(*[1,2.3])      #　也可以是列表
        
        obj.r.clear()           # 删除id=1的所有数据
        
        obj.r.set([3,5,7])        # set就是更新
'''

url.py:

from django.urls import path
from app01 import views

urlpatterns = [
    path('business/',views.business ),
    path('host/',views.host ),
    path('text_ajax/',views.test_ajax ),
    path('app/',views.app ),
    path('ajax_add_app/',views.ajax_add_app ),
]

views.py

from django.shortcuts import render,HttpResponse,redirect
from app01 import models
import json
# Create your views here.


def business(request):
    v1 = models.Business.objects.all()   # 获取所有数据
    # QuerySet
    # [obj(id,caption,code),obj(id,caption,code),...]

    v2 = models.Business.objects.all().values('id','caption')
    # 这时v2还是一个QuerySet对象,但是内部元素就变成了字典

    v3 = models.Business.objects.all().values_list('id','caption')
    # 这是v3还是一个QuerySet对象,但是内部元素就变成了元组

    return render(request,'business.html',{'v1':v1,'v2':v2,'v3':v3})


def host(request):
    if request.method == "GET":
        v1 = models.Host.objects.filter(nid__gt=0)
        #
        for row in v1:
            print(row.nid,row.hostname,row.ip,row.port,row.b,row.b.id,row.b.caption,row.b.code)

        v2 = models.Host.objects.filter(nid__gt=0).values('nid','hostname','b_id','b__caption')

        v3 = models.Host.objects.filter(nid__gt=0).values_list('nid','hostname','b_id','b__caption')
        b_list = models.Business.objects.all()
        return render(request,'host.html',{'v1':v1,'v2':v2,'v3':v3,'b_list':b_list})

    elif request.method == "POST":
        h = request.POST.get('host-name')
        i = request.POST.get('ip')
        p = request.POST.get('port')
        b = request.POST.get('b_id')

        models.Host.objects.create(hostname=h,
                                   ip=i,
                                   port=p,
                                   b_id=b)
        return redirect('/app01/host/')


def test_ajax(request):
    ret = {'status': True, 'error': None, 'data': None}
    try:
        h = request.POST.get('hostname')
        i = request.POST.get('ip')
        p = request.POST.get('port')
        b = request.POST.get('b_id')
        if h and len(h) > 5:
            models.Host.objects.create(hostname=h,
                                       ip=i,
                                       port=p,
                                       b_id=b)
        else:
            ret['status'] = False
            ret['error'] = "太短了"
    except Exception as e:
        ret['status'] = False
        ret['error'] = '请求错误'
    return HttpResponse(json.dumps(ret))


def app(request):
    if request.method == "GET":
        app_list = models.Application.objects.all()
        # for row in app_list:
        #     print(row.name,row.r.all())

        host_list = models.Host.objects.all()
        return render(request, 'app.html', {"app_list": app_list, 'host_list': host_list})
    elif request.method == "POST":
        app_name = request.POST.get('app_name')
        host_list = request.POST.getlist('host_list')
        print(app_name, host_list)

        obj = models.Application.objects.create(name=app_name)
        obj.r.add(*host_list)

        return redirect('/app01/app/')

def ajax_add_app(request):
    ret = {'status':True, 'error':None, 'data': None}

    app_name = request.POST.get('app_name')
    host_list = request.POST.getlist('host_list')
    obj = models.Application.objects.create(name=app_name)
    obj.r.add(*host_list)
    return HttpResponse(json.dumps(ret))

static:

static中是jQuery文件.

templates:

app.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title></title>
    <style>
        .host-tag{
            display: inline-block;
            padding: 3px;
            border: 1px solid red;
            background-color: palevioletred;
        }
        .hide{
            display: none;
        }
        .shade{
            position: fixed;
            top: 0;
            right: 0;
            left: 0;
            bottom: 0;
            background: black;
            opacity: 0.6;
            z-index: 100;
        }
        .add-modal,.edit-modal{
            position: fixed;
            height: 300px;
            width: 400px;
            top:100px;
            left: 50%;
            z-index: 101;
            border: 1px solid red;
            background: white;
            margin-left: -200px;
        }
    </style>
</head>
<body>

    <h1>应用列表</h1>
     <div>
        <input id="add_app" type="button" value="添加" />
    </div>
    <table border="1">
        <thead>
            <tr>
                <td>应用名称</td>
                <td>应用主机列表</td>
            </tr>
        </thead>
        <tbody>
            {% for app in app_list %}
                <tr aid="{{ app.id }}">
                    <td>{{ app.name }}</td>
                    <td>
                        {% for host in app.r.all %}
                            <span class="host-tag" hid="{{ host.nid }}"> {{ host.hostname }} </span>
                        {% endfor %}
                    </td>
                    <td>
                        <a class="edit">编辑</a>
                    </td>
                </tr>
            {% endfor %}
        </tbody>
    </table>



    <div class="shade hide"></div>
    <div class="add-modal hide">
        <form id="add_form" method="POST" action="/app01/app/">
            <div class="group">
                <input id="app_name" type="text" placeholder="应用名称" name="app_name" />
            </div>
            <div class="group">
                <select id="host_list" name="host_list" multiple>
                    {% for op in host_list %}
                        <option value="{{ op.nid }}">{{ op.hostname }}</option>
                    {% endfor %}
                </select>
            </div>

            <input type="submit" value="提交" />
            <input id="add_submit_ajax" type="button" value="Ajax提交" />
        </form>


    </div>

    <div class="edit-modal hide">
        <form id="edit_form" method="POST" action="/host">
                <input type="text" name="nid" style="display:none" />
                <input type="text" placeholder="应用名称" name="app" />
                <select name="host_list" multiple>
                    {% for op in host_list %}
                        <option value="{{ op.nid }}">{{ op.hostname }}</option>
                    {% endfor %}
                </select>
            <a id="ajax_submit_edit" >确认编辑</a>
        </form>


    </div>

     <script src="/static/jquery-1.12.4.js"></script>
    <script>
        $(function(){

            $('#add_app').click(function(){
                $('.shade,.add-modal').removeClass('hide');
            });

            $('#cancel').click(function(){
                $('.shade,.add-modal').addClass('hide');
            });


            $('#add_submit_ajax').click(function(){
                $.ajax({
                    url: '/ajax_add_app',
                    // data: {'user': 123,'host_list': [1,2,3,4]},
                    data: $('#add_form').serialize(),
                    type: "POST",
                    dataType: 'JSON', // 内部
                    traditional: true,
                    success: function(obj){
                        console.log(obj);
                    },
                    error: function () {

                    }

                })
            });


            $('.edit').click(function(){

                $('.edit-modal,.shade').removeClass('hide');

                var hid_list = [];
                $(this).parent().prev().children().each(function(){
                    var hid = $(this).attr('hid');
                    hid_list.push(hid)
                });

                $('#edit_form').find('select').val(hid_list);
                // 如果发送到后台
                //
                /*
                obj = models.Application.objects.get(id=ai)
                obj.name = "新Name"
                obj.save()
                obj.r.set([1,2,3,4])
                */


            })

        })
    </script>
</body>
</html>

business.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>business</title>
</head>
<body>
    <h1>业务线列表(对象方式)</h1>
    <ul>
        {% for row in v1 %}
            <li>{{ row.id }}-{{ row.caption }}-{{ row.code }}</li>
        {% endfor %}
    </ul>

    <h1>业务线列表(字典方式)</h1>
    <ul>
        {% for row in v2 %}
            <li>{{ row.id }}-{{ row.caption }}</li>
        {% endfor %}
    </ul>

    <h1>业务线列表(元组方式)</h1>
    <ul>
        {% for row in v3 %}
            <li>{{ row.0 }}-{{ row.1 }}</li>
        {% endfor %}
    </ul>
</body>
</html>

host.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title></title>
    <style>
        .hide{
            display: none;
        }
        .shade{
            position: fixed;
            top: 0;
            right: 0;
            left: 0;
            bottom: 0;
            background: black;
            opacity: 0.6;
            z-index: 100;
        }
        .add-modal,.edit-modal{
            position: fixed;
            height: 300px;
            width: 400px;
            top:100px;
            left: 50%;
            z-index: 101;
            border: 1px solid red;
            background: white;
            margin-left: -200px;
        }
    </style>
</head>
<body>
    <h1>主机列表(对象)</h1>
    <div>
        <input id="add_host" type="button" value="添加" />
    </div>
    <table border="1">
        <thead>
            <tr>
                <th>序号</th>
                <th>主机名</th>
                <th>IP</th>
                <th>端口</th>
                <th>业务线名称</th>
                <th>操作</th>
            </tr>
        </thead>
        <tbody>

                {% for row in v1 %}
                    <tr hid="{{ row.nid }}" bid="{{ row.b_id }}">
                        <td>{{ forloop.counter }}</td>
                        <td>{{ row.hostname }}</td>
                        <td>{{ row.ip }}</td>
                        <td>{{ row.port }}</td>
                        <td>{{ row.b.caption }}</td>
                        <td>
                            <a class="edit">编辑</a>|<a class="delete">删除</a>
                        </td>
                    </tr>
                {% endfor %}


        </tbody>
    </table>

    <h1>主机列表(字典)</h1>
    <table border="1">
        <thead>
            <tr>
                <th>主机名</th>
                <th>业务线名称</th>
            </tr>
        </thead>
        <tbody>
            {% for row in v2 %}
                <tr hid="{{ row.nid }}" bid="{{ row.b_id }}">
                    <td>{{ row.hostname }}</td>
                    <td>{{ row.b__caption }}</td>
                </tr>
            {% endfor %}

        </tbody>
    </table>
    <h1>主机列表(元组)</h1>
    <table border="1">
        <thead>
            <tr>
                <th>主机名</th>
                <th>业务线名称</th>
            </tr>
        </thead>
        <tbody>
            {% for row in v3 %}
                <tr hid="{{ row.0 }}" bid="{{ row.2 }}">
                    <td>{{ row.1 }}</td>
                    <td>{{ row.3 }}</td>
                </tr>
            {% endfor %}

        </tbody>
    </table>


    <div class="shade hide"></div>
    <div class="add-modal hide">
        <form id="add_form" method="POST" action="/host">
            <div class="group">
                <input id="host" type="text" placeholder="主机名" name="hostname" />
            </div>

            <div class="group">
                <input id="ip" type="text" placeholder="IP" name="ip" />
            </div>

            <div class="group">
                <input id="port" type="text" placeholder="端口" name="port" />
            </div>

            <div class="group">
                <select id="sel" name="b_id">
                    {% for op in b_list %}
                    <option value="{{ op.id }}">{{ op.caption }}</option>
                    {% endfor %}
                </select>
            </div>

            <input type="submit" value="提交" />
            <a id="ajax_submit" >悄悄提交</a>
            <input id="cancel" type="button" value="取消" />
            <span id="erro_msg" style="color: red"></span>
        </form>


    </div>

    <div class="edit-modal hide">
        <form id="edit_form" method="POST" action="/host">
                <input type="text" name="nid" style="display:none" />
                <input type="text" placeholder="主机名" name="hostname" />
                <input type="text" placeholder="IP" name="ip" />
                <input  type="text" placeholder="端口" name="port" />
                <select name="b_id">
                    {% for op in b_list %}
                    <option value="{{ op.id }}">{{ op.caption }}</option>
                    {% endfor %}
                </select>
            <a id="ajax_submit_edit" >确认编辑</a>
        </form>


    </div>



    <script src="/static/jquery-1.12.4.js"></script>
    <script>
        $(function(){

            $('#add_host').click(function(){
                $('.shade,.add-modal').removeClass('hide');
            });

            $('#cancel').click(function(){
                $('.shade,.add-modal').addClass('hide');
            });

            $('#ajax_submit').click(function(){
                $.ajax({
                    url: "/test_ajax",
                    type: 'POST',
                    //data: {'hostname': $('#host').val(), 'ip': $('#ip').val(), 'port': $('#port').val(), 'b_id': $('#sel').val()},
                    data: $('#add_form').serialize(),
                    success: function(data){
                        var obj = JSON.parse(data);
                        if(obj.status){
                            location.reload();
                        }else{
                            $('#erro_msg').text(obj.error);
                        }
                    }
                })
            });


            $('.edit').click(function(){
                $('.shade,.edit-modal').removeClass('hide');

                var bid = $(this).parent().parent().attr('bid');
                var nid = $(this).parent().parent().attr('hid');

                $('#edit_form').find('select').val(bid);
                $('#edit_form').find('input[name="nid"]').val(nid);

                // 修改
                /*
                $.ajax({
                    data: $('#edit_form').serialize()
                });
                */
                // models.Host.objects.filter(nid=nid).update()
            })
        })
    </script>
</body>
</html>

web_fan_1:

init.py

import pymysql
pymysql.install_as_MySQLdb()

settings.py

"""
Django settings for web_fan_1 project.

Generated by 'django-admin startproject' using Django 2.1.

For more information on this file, see
https://docs.djangoproject.com/en/2.1/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/2.1/ref/settings/
"""

import os

# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))


# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/2.1/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = '^^q&kcn_tin@q-i-a_ub5&!etkb=p58obge9j_k37!i_(j5%k6'

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True

ALLOWED_HOSTS = []


# Application definition

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'app01',
]

MIDDLEWARE = [
    'django.middleware.security.SecurityMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.common.CommonMiddleware',
    # 'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    'django.middleware.clickjacking.XFrameOptionsMiddleware',
]

ROOT_URLCONF = 'web_fan_1.urls'

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [os.path.join(BASE_DIR, 'templates')]
        ,
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

WSGI_APPLICATION = 'web_fan_1.wsgi.application'


# Database
# https://docs.djangoproject.com/en/2.1/ref/settings/#databases

# DATABASES = {
#     'default': {
#         'ENGINE': 'django.db.backends.sqlite3',
#         'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
#     }
# }

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'cmdb1',
        'USER':'root',
        'PASSWORD':'123456',
        'HOST':'localhost',
        'POST':'3306',
    }
}


# Password validation
# https://docs.djangoproject.com/en/2.1/ref/settings/#auth-password-validators

AUTH_PASSWORD_VALIDATORS = [
    {
        'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
    },
]


# Internationalization
# https://docs.djangoproject.com/en/2.1/topics/i18n/

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_L10N = True

USE_TZ = True


# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/2.1/howto/static-files/

STATIC_URL = '/static/'
STATICFILES_DIRS = (
    os.path.join(BASE_DIR,'static'),
)

urls.py

"""web_fan_1 URL Configuration

The `urlpatterns` list routes URLs to views. For more information please see:
    https://docs.djangoproject.com/en/2.1/topics/http/urls/
Examples:
Function views
    1. Add an import:  from my_app import views
    2. Add a URL to urlpatterns:  path('', views.home, name='home')
Class-based views
    1. Add an import:  from other_app.views import Home
    2. Add a URL to urlpatterns:  path('', Home.as_view(), name='home')
Including another URLconf
    1. Import the include() function: from django.urls import include, path
    2. Add a URL to urlpatterns:  path('blog/', include('blog.urls'))
"""
from django.contrib import admin
from django.urls import path
from django.urls.conf import include
from app01 import views

urlpatterns = [
    path('admin/', admin.site.urls),
    path('app01/',include('app01.url') ),
]

mysql:

mysql> select * from app01_application;
+----+--------+
| id | name   |
+----+--------+
|  1 | 测试   |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from app01_application_r;
+----+----------------+---------+
| id | application_id | host_id |
+----+----------------+---------+
|  1 |              1 |       3 |
+----+----------------+---------+
1 row in set (0.00 sec)

mysql> select * from app01_business;
+----+-----------+------+
| id | caption   | code |
+----+-----------+------+
|  1 | 开发部    | sa   |
|  2 | 测试      | sa   |
|  3 | 运维      | sa   |
+----+-----------+------+
3 rows in set (0.00 sec)

mysql> select * from app01_host;
+-----+----------+---------+------+------+
| nid | hostname | ip      | port | b_id |
+-----+----------+---------+------+------+
|   1 | c1.com   | 1.1.1.1 |   80 |    1 |
|   2 | c2.com   | 1.1.1.2 |   90 |    1 |
|   3 | c3.com   | 1.1.1.3 |   70 |    2 |
|   4 | c4.com   | 1.1.1.4 |   60 |    2 |
|   5 | c4.com   | 1.1.1.5 |   50 |    3 |
|   6 | c5.com   | 1.1.1.5 |   50 |    2 |
|   7 | c6.com   | 1.1.1.6 |   40 |    1 |
|   8 | c7.com   | 1.1.1.7 |   30 |    3 |
+-----+----------+---------+------+------+
8 rows in set (0.00 sec)

写在最后

    本文是个人的一些学习笔记,如有侵权,请及时联系我进行删除,谢谢大家.