Django ORM 使用原生 SQL

使用原生sql的 方法 :

1. raw

   # row方法:（掺杂着原生sql和orm来执行的操作）
   res = CookBook.objects.raw('select id as nid  from  epos_cookbook  where  id>%s', params=[1, ])
   print(res.columns) # ['nid']
   print(type(res)) # <class 'django.db.models.query.RawQuerySet'>
   
   # 在select里面查询到的数据orm里面的要一一对应
   res = CookBook.objects.raw("select * from epos_cookbook")
   print(res)
   for i in res:
       print(i.create_date)
       print(i)
       
   res = CookBook.objects.raw('select * from epos_cookbook where id>%s', params=[1, ])
   # 后面可以加参数进来
   print(res)
   for i in res:
       # print(i.create_date)
       print(i)
   

2. extra

   # (1,2) 必须两个以上
   # res = CookBook.objects.extra(select={"aaa": "cook_type = 1"}, where=['id in (1,2)', ]).values()
   res = CookBook.objects.extra(select={"aaa": "cook_type = 1"}, where=['id in (1,2)', ])
   print(res)  # <QuerySet [<CookBook: 鱼香肉丝>, <CookBook: 水煮鱼>]>
   for r in res:
       print(r)
   

3. connections (最原生)

   from django.db import connection, connections
   # 需要配置数据库
   # cursor=connection['default'].cursor() 
   cursor = connection.cursor()  
   # 不传参数的情况
   cursor.execute("""select  * from epos_cookbook""")
   
   # 为原生sql语句设置参数的情况
   # cursor.execute("""select  * from  epos_cookbook   where   id=%s""",[2,]) # 2 是 id
   # cursor.execute("""select  * from  api_userinfo   where   id=%s"""%1)
   
   # 防止注入攻击
   cursor.execute("select  * from  epos_cookbook   where   id=%s", params=[1, ])
   # row=cursor.fetchone()
   # row=cursor.fetchmany()
   row = cursor.fetchall()  ##拿到全部的数据
   
   print(row)
   
   

   
   from django.db import connection
   
   cursor=connection.cursor()
   
   # 插入操作
   cursor.execute("insert into hello_author(name) values('钱钟书')")
   
   # 更新操作
   cursor.execute("update hello_author set name='abc' where name='bcd'")
   
   # 删除操作
   cursor.execute("delete from hello_author where name='abc'")
   
   # 查询操作
   cursor.execute("select * from hello_author")
   
   raw=cursor.fetchone()  # 返回结果行游标直读向前，读取一条
   cursor.fetchall()  # 读取所有
   
   

数据库分离使用原生sql

from django.db import connection, connections
# cursor = connection.cursor()
cursor = connections['db2'].cursor()
cursor.execute("""SELECT * from app01_student """, )
row = cursor.fetchall()
print(row)
 ```