Django—Database Operations—Incomplete

Django—Database Operations

In the settings.py file:

import pymysql         # 一定要添加这两行！通过pip install pymysql！
pymysql.install_as_MySQLdb()
# 修改DATABASES的值
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mysite',
        'HOST': '192.168.1.1',
        'USER': 'root',
        'PASSWORD': 'pwd',
        'PORT': '3306',
    }
}

Creating Data: Data Types

from django.db import models
class UserInfo(models.Model):
    uid = models.AutoField(primary_key=True)
    # 自增长ID,必须带参数为primary_key=True,并且是唯一主键
    user = models.CharField(max_length=10)
    # 字符串类型，最大长度为10
    age = models.IntegerField(default=1)
    # 数字类型，默认为1
    sex = models.BooleanField()
    # BOOL类型
    email = models.EmailField()
    # 自带检查 Email 合法性的 CharField
    Intor = models.TextField()
    # 长文本类型
    Blog = models.URLField()
    # 自带检查 URL 合法性的 CharField

    # -------------------文件上传-----------------
    upload = models.FileField(upload_to='uploads/')
    # 文件上传到 MEDIA_ROOT/uploads
    # 这个字段不能设置primary_key和unique选项.在数据库中存储类型是varchar，默认最大长度为100.
    upload_s = models.FileField(upload_to='uploads/%Y/%m/%d/')
    # 文件上传到 MEDIA_ROOT/uploads/2019/07/04

    upload_e = models.FilePathField(path="/home/images", match="foo.*", recursive=True)
    # 这个字段的值被限制在系统上某个目录中的所有文件名集合中。有三个参数
    # 　　　　path = '':  该参数必需。上行所说的‘某个目录’的绝对路径。Example: "/home/images".
    # 　　　　match = 'pattern':  可选参数。格式是正则表达式。用来拣选符合匹配正则表达式的文件
    # 　　　　recursive = True / False: 可选参数，默认为False。设定是否递归该目录下所有子目录的所有文件

    add_date = models.DateTimeField(auto_now_add=True)
    # 设置初次创建的时间
    update_date = models.DateTimeField(auto_now=True)
    # 设置最后修改的时间

Creating a database requires three steps:

Modify the models in models.py.
Run python3 manage.py makemigrations to create migration records for the changes.
Run python3 manage.py migrate to synchronize the operations with the database.

Adding, Deleting, Updating, and Querying Data

Method for adding data: the all-in-one approach

data.objects.create(User='zz')

The individual assignment approach

datas = data()
datas.User = '陈二狗'
datas.save()
```#### Querying

## There are generally five methods for retrieving data, all of which return datasets

The first is data.objects.all\(\)

This method retrieves all data in the table.

The second is data.objects.get\(id=1\)

**This one is rather troublesome and is not commonly used; if nothing is found, it immediately raises an exception.**  
This method requires that exactly one result be retrieved (multiple objects will immediately cause an exception). An exception is also raised when no matching object exists. Here, the retrieval condition is id=1.

The third method is data.objects.filter\(id=2\)

This method filters for data where id=2. Multiple conditions can also be specified inside the parentheses, such as \(id=2,gender=’boy’\).

The fourth method is data.objects.exclude\(id=2\)

This method retrieves data where id is not equal to 2.

The fifth method is data.objects.order\_by\(name\)

This method retrieves results sorted by name.

#### Retrieving a Single Object

The first method is data.objects.first\(name=’zeo’\)  
data.objects.last\(name=’zeo’\)

This method retrieves the first object that meets the conditions. You can, of course, also retrieve the last object by using .last\(name=’Xiaotaohong’\).

**Note: These methods can all be used iteratively.**

Retrieving data for the current day by date```python
from django.utils import timezone
from datetime import timedelta
now = timezone.now()
start = now - timedelta(hours=23,minutes=59,seconds=59)
CurrentDaySubdomain = URL.objects.filter(change_time__gt=start)
for c in CurrentDaySubdomain:
    print(c.url)

Other methods for retrieving data by time

gt: later than a specified time
now = datetime.datetime.now()

start = now – datetime.timedelta(hours=23, minutes=59, seconds=59)
a=yourobject.objects .filter(youdatetimcolumn__gt=start)#previous day

gte: later than or equal to a specified time:
a=yourobject.objects .filter(youdatetimcolumn__gte=start)
lt: earlier than
a=yourobject.objects .filter(youdatetimcolumn__lt=start)
lte: earlier than or equal to
a=yourobject.objects .filter(youdatetimcolumn__lte=start)
range: query a time range
start_date = datetime.date(2005, 1, 1)
end_date = datetime.date(2005, 3, 31)
Entry.objects.filter(pub_date__range=(start_date, end_date))
year: query a specific year
Entry.objects.filter(pub_date__year=2005)
month: query a specific month
Entry.objects.filter(pub_date__month=12)
day: query a specific day
Entry.objects.filter(pub_date__day=3)
week_day: query a day of the week
Entry.objects.filter(pub_date__week_day=2)
Get today’s date in yyyy-MM-dd format

from django.utils.timezone import now, timedelta
date = now().date() + timedelta(days=-1) #yesterday
date = now().date() + timedelta(days=0) #today
date = now().date() + timedelta(days=1) #tomorrow

The data returned by the methods above is of the QuerySet() type, which is a result class. To obtain the final actual result, you need to retrieve it using an index.

datas = data.objects.filter(id=2).values()[0]
The result is a dictionary. Index 0 retrieves the first result from the values() list.

You can, of course, also retrieve it by assigning it to a variable.

datas = data.objects.get(id=1)
u_id = datas.id
u_User = datas.User

这种方式进行获取数据，这个前提是在只有一个数据的情况下进行

When all() or another method retrieves a larger dataset, you can also retrieve each result like this:

data_0 = data.objects.all()
# 这里的结果是一个数据集合，可以循环迭代和使用索引获取值
data_1 = data_0[0]
u_id = data_1.id
u_User = data_1.User
# 通过这样的方式，就可以实现进行单独数据的获取

For a collection like this, if you want to print all the data it contains, you can process it as follows:

ret = ''
data_0 = data.objects.all()
for data_1 in data_0:
    ret += data_1.id + '|' + data_1.User + '<br>'

Batch operations: querying and updating

all_subdomains.filter(subdomain_ip=all_subdomain_one.subdomain_ip).update(nmapscan=result_domain_ports_numbners)
all_subdomains.filter(subdomain_ip=all_subdomain_one.subdomain_ip).update(nmapscan_ports=result_domain_ports)