django - (二) 配置数据库连接

django - (二) 配置数据库连接

新增数据库链接(mysql)

  1. 打开一个mysql并创建一个数据库

    mysql -uroot -p123
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> create database student_sys;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | student_sys        |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    
  2. 修改工程目录下的setting.py 中的数据库配置项,修改链接到上一步创建的数据库上

    DATABASES = {
        'default': {
            # 'ENGINE': 'django.db.backends.sqlite3',
            # 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
            'ENGINE': 'django.db.backends.mysql',
            'NAME': 'student_sys',
            'HOST': '127.0.0.1',
            'PORT': '3306',
            'USER': 'root',
            'PASSWORD': '123',
        }
    }
    
  3. $(根项目目录)/init.py (即student_sys/student_sys/init.py)

    import pymysql
    
    pymysql.install_as_MySQLdb()
    
  4. 安装mysql驱动

    pip install pymysql
    

models编写

  1. 编写一个学员信息表

    class Student(models.Model):
        SEX_ITEMS= [
            (1,'男'),
            (2,'女'),
            (0,'未知'),
        ]
        STATUS_ITEMS = [
            (0,'申请'),
            (1, '通过'),
            (2,'拒绝'),
        ]
        name = models.CharField(max_length=128,verbose_name ='姓名')
        sex = models.IntegerField(choices=SEX_ITEMS,verbose_name='性别')
        profession = models.CharField(max_length=123,verbose_name="职业")
        email=models.EmailField(verbose_name='Email')
        qq = models.CharField(max_length=128,verbose_name='QQ')
        phone = models.IntegerField(max_length=128,verbose_name='电话')
    
        status = models.IntegerField(choices=STATUS_ITEMS,default=0,verbose_name='审核状态')
    
        created_time = models.DateTimeField(auto_now_add=True,editable=False,verbose_name="创建时间")
    
        def __str__(self):
            return '<Student: %s>' % (self.name)
    
        class Meta:
            verbose_name=verbose_name_plural = "学员信息"
    
  2. 注册app student到工程上student_sys上,修改setting.py如下

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

实例化到数据库

  1. django 操作数据库的常用操作
    1. python manage.py makemigrations 创建数据库修改脚本
    2. python manage.py migrate 提交修改到数据库
    3. python manage.py check 检查数据库模型问题
    4. python manage.py makemigrations student 同步某一个应用的数据库修改脚本
    5. python manage.py sqlmigrate student 0002 查看某一个版本过程中对数据库有哪些修改
    
  2. 依次执行上一步中的命令1,2,查看数据库
    mysql> use student_sys
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +----------------------------+
    | Tables_in_student_sys      |
    +----------------------------+
    | auth_group                 |
    | auth_group_permissions     |
    | auth_permission            |
    | auth_user                  |
    | auth_user_groups           |
    | auth_user_user_permissions |
    | django_admin_log           |
    | django_content_type        |
    | django_migrations          |
    | django_session             |
    | student_student            |
    +----------------------------+
    11 rows in set (0.00 sec)
    
    

Django Shell和ORM操作

  1. 实体类

    class sample(models.Model):
        col1=models.CharField(max_length=10,primary_key=True)
        col2=models.CharField(max_length=10)
    
  2. shell 操作

    ggg@ggg-X550JX ~/project/gggsweb $ python manage.py shell
    Python 3.6.4 |Anaconda, Inc.| (default, Jan 16 2018, 18:10:19) 
    Type 'copyright', 'credits' or 'license' for more information
    IPython 6.2.1 -- An enhanced Interactive Python. Type '?' for help.
    
    In [1]: from crm.models import sample
    
    In [2]: p1=sample(col1="李先生",col2="256元")
    
    In [3]: p1.save()
    
    In [4]: p1=sample(col1="张先生",col2="432元")
    
    In [5]: p1.save()
    
    In [6]: p1=sample.objects.all()
    
    In [7]: p1
    Out[7]: <QuerySet [<sample: sample object (张先生)>, <sample: sample object (李先生)>]>
    
    In [8]: p2=sample.objects.create(col1="孙先生",col2="132元")
    
    In [9]: sample.objects.all()
    Out[9]: <QuerySet [<sample: sample object (孙先生)>, <sample: sample object (张先生)>, <sample: sample object (李先生)>]>
    
    In [11]: p2.col1
    Out[11]: '孙先生'
    
    In [12]: p2.col2
    Out[12]: '132元'
    
    In [13]: p2.col2 = "99元" ##update
    
    In [14]: p2.save()
    
    In [15]: sample.objects.all()
    
    In [16]: p3 = sample.objects.filter(col1="张先生") ##过滤,这里可以返回结果集
    
    In [17]: p3
    Out[17]: <QuerySet [<sample: sample object (张先生)>]>
    
    In [18]: p4=sample.objects.filter(col1__contains="孙") ## like '%孙%'
    ##此处还有icontains区分大小写like,startwith,endswith
    
    In [19]: p4
    Out[19]: <QuerySet [<sample: sample object (孙先生)>]>
    
    In [20]: p5 = sample.objects.get(col1="李先生") ##这里只允许返回单行
    
    In [21]: p5
    Out[21]: <sample: sample object (李先生)>
    
    In [22]: p6=sample.objects.order_by('col2')
    
    In [23]: p6
    Out[23]: <QuerySet [<sample: sample object (李先生)>, <sample: sample object (张先生)>, <sample: sample object (孙先生)>]>
    
    In [24]: sample.objects.order_by('-col2')
    Out[24]: <QuerySet [<sample: sample object (孙先生)>, <sample: sample object (张先生)>, <sample: sample object (李先生)>]>
    
    In [25]: p6[0]
    Out[25]: <sample: sample object (李先生)>
    
    In [26]: p6[0:1] #这里不支持负数索引
    Out[26]: <QuerySet [<sample: sample object (李先生)>]>
    
    In [27]: p6.update(col2="543元")#更新多条 #这里不用save可以直接写库
    Out[27]: 3
    
    In [28]: p3.delete() #删除一条或多条 #直接写库
    Out[28]: (1, {'crm.sample': 1})
    
    In [29]: p6.delete()
    Out[29]: (2, {'crm.sample': 2})
    
    In [30]: sample.objects.all().delete() #truncate
    Out[30]: (0, {'crm.sample': 0})
    
    

添加额外的model管理方法

返回条目

  1. models.py

    class sampleManager(models.Manager):
        def col2_likes(self,keyword):
            #函数用来统计col2中包含某个字符串的条目个数
            return self.filter(col2__icontains=keyword).count()
    
    class sample(models.Model):
        col1=models.CharField(max_length=10,primary_key=True)
        col2=models.CharField(max_length=10)
        object = sampleManager() #管理方法需添加到models对象里
    
  2. 使用

    In [1]: from crm import models
    
    In [2]: models.sample.object.all()
    Out[2]: <QuerySet [<sample: 1 abc>, <sample: 2 bcd>, <sample: 3 cde>, <sample: 4 ade>]>
    
    In [3]: models.sample.object.col2_likes('d')
    Out[3]: 3
    
    In [4]: models.sample.object.col2_likes('a')
    Out[4]: 2
    
    

返回对象

  1. models.py

    class dahlsampleManager(models.Manager):
        #对象方法中必须要覆盖这一函数
        def get_queryset(self):
            return super(dahlsampleManager,self).get_queryset().filter(col2__contains='a')
    class sample(models.Model):
        col1=models.CharField(max_length=10,primary_key=True)
        col2=models.CharField(max_length=10)
        dahl_objects = dahlsampleManager()
    
  2. 调用

    In [1]: from crm.models import *
    
    In [2]: sample.dahl_objects.all() #这里能看到返回的结果可以使用query_set的成员函数
    Out[2]: <QuerySet [<sample: 1 abc>, <sample: 4 ade>]>
    
    In [3]: sample.dahl_objects.count()
    Out[3]: 2
    
    

MYSQL中中文编码不能插入

    ALTER TABLE crm_cust_info MODIFY COLUMN cust_name VARCHAR(50) CHARACTER SET 'utf8';
上一篇:记录SQL Server中一次无法重现的死锁


下一篇:insert into语法范例