Django自带ORM的语法

交互模式进入方式

1
2
# 自动

1
2
3
4
5
6
# 手动
import os
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "resop.settings_firefly")
from django.core.wsgi import get_wsgi_application
application = get_wsgi_application()
from orm.cloud.models import GIC

查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
from django.db.models import Q
query_sql = Q(is_valid=1)
if route_id:
    query_sql &= Q(route_id=route_id)
if site_id:
    query_sql &= Q(vrrp__site_id=site_id)
vrrp_info_list = VpcVrrpResource.objects.filter(query_sql).select_related(
    "vrrp", "route", "vrrp__available_zone"
).values(
    'vrrp__available_zone__site_name', 'route__route_name', 'vrrp__name', 'vrrp__pubnet_status', 'vrrp__subnet_status',
    'vrrp__id', 'route__route_id'
)

------ # values和values_list方法区别 ------
# values 结果为字典型
books = Book.objects.filter(id__lt=6).values('number')
[{'number': '1'}, {'number': '2'}, {'number': '3'}, {'number': '4'}, {'number': '5'}]
 
# values_list 结果为元祖型
books = Book.objects.values_list('number')
[('1',), ('2',), ('3',), ('4',), ('5',)]
 
# 获取某个字段所有值2
books = Book.objects.values_list('number', flat=True)
books = ['1', '2', '3', '4', '5']



# 排序
VpcVrrpResource.objects.filter(query_sql).order_by('-create_time')

# in
id_list  = []
VpcVrrpResource.objects.filter(id__in=id_list)

# or
Customer.objects.filter(Q(id__contains=keyword) | Q(name__contains=keyword))

# 排除
query_sql = Q(cds_product_id__in=['peering', '']) | Q(cds_product_id__isnull=True)
pro_attrs = ProAttrConfSite.objects.filter(site_id=site_id, is_valid=1).exclude(query_sql)

# 分组
query_data = VpcSlbRsPort.objects.filter(listen_id__in=update_listen_ids, is_valid=1).exclude(platform='eks').values('ip', 'port').annotate(counts=Count(id))
res = ["{}:{}".format(q.get('ip'), q.get('port')) for q in query_data]

插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
vrrp = VpcVrrpResource(
    id=1,
    name='vrrp'
)
vrrp.save   
VpcVrrpResource.objects.create(vrrp)
VpcVrrpResource.objects.bulk_create([vrrp])

vrrp = {
    'id': 1,
    'name': 'vrrp'
}
VpcVrrpResource.objects.create(**vrrp)
VpcVrrpResource.objects.bulk_create([VpcVrrpResource(**vrrp)])

更新

1
GlobalAccelerator.objects.filter(id=ga_id).update(status='ok', max_connection=connections)

生成model文件

1
2
<!--到wan_server项目下操作-->
python3 manage.py inspectdb --database automatic_product vpc_vrrp

事务

装饰器

1
2
3
4
5
6
7
8
9
10
11
12
@transaction.atomic()
@transaction.atomic(using='cluster')
@transaction.atomic(using='adn')
def save_to_db(request, data_dir):
    <!--这里进行数据库读写，出现异常时三个库的事务都会回滚-->
    
    
    
try:
    save_to_db(request, data_dir)
except Exception as e:
    print(e)

执行原生SQL

cursor方式

这种方式类似于pymysql，比较原始，在结果为count这样的函数放回值时使用较为合适

1
2
3
4
5
from django.db import connection
cursor = connection.cursor()
query_sql = "select count(*) from automatic_product.subinterface where is_alloc = 1 and site_id = '{}' and app_id is not null and status = 'ok'".format(site.id)
cursor.execute(query_sql)
used_count = cursor.fetchall()

指定数据库

1
2
3
4
5
from django.db import connections
with connections['wan_fping'].cursor() as cursor:
    query_sql = "select count(*) from product_vm;"
    cursor.execute(query_sql)
    cursor.fetchall()

raw方式

这种方式会将sql查询出来的数据注入到模型类中，比较方便

1
2
query_sql = "select * from automatic_product.subinterface"
data_list = Subinterface.objects.raw(query_sql)