Django执行原始SQL并返回字典类型数据的两种方案

手动构建字典的方法

# coding:utf-8
from django.db import connection, transaction

'''执行django原始sql语句 并返回一个数组对象'''
def executeQuery(sql):
 cursor = connection.cursor() # 获得一个游标(cursor)对象
 cursor.execute(sql)
 rawData = cursor.fetchall()
 print rawData
 col_names = [desc[0] for desc in cursor.description]
 print col_names

 result = []
 for row in rawData:
  objDict = {}
  # 把每一行的数据遍历出来放到Dict中
  for index, value in enumerate(row):
  print index, col_names[index], value
  objDict[col_names[index]] = value

  result.append(objDict)

 return result

驱动重写与配置

from django.db.backends.mysql import base
from django.db.backends.mysql import features
from django.utils.functional import cached_property
 
class DatabaseFeatures(features.DatabaseFeatures):
 @cached_property
 def is_sql_auto_is_null_enabled(self):
  with self.connection.cursor() as cursor:
   cursor.execute('SELECT @@SQL_AUTO_IS_NULL')
   result = cursor.fetchone()
   return result and result['@@SQL_AUTO_IS_NULL'] == 1
 
 
class DatabaseWrapper(base.DatabaseWrapper):
 features_class = DatabaseFeatures
 
 def create_cursor(self, name=None):
  cursor = self.connection.cursor(self.Database.cursors.DictCursor)
  return base.CursorWrapper(cursor)
 
 @cached_property
 def mysql_server_info(self):
  with self.temporary_connection() as cursor:
   cursor.execute('SELECT VERSION()')
   return cursor.fetchone()['VERSION()']

DATABASES = {
 'default': {
  'ENGINE': 'Test.mysql', # 指定数据库驱动为刚刚创建的mysql文件夹
  'NAME': 'test', # 指定的数据库名
  'USER': 'root', # 数据库登录的用户名
  'PASSWORD': '123456', # 登录数据库的密码
  'HOST': '127.0.0.1',
  'PORT': '3306', # 数据库服务器端口，mysql默认为3306
  'DATABASE_OPTIONS': {
   'connect_timeout': 60,
   'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
   'charset': 'utf8mb4',
  },
 }
}

from django.db import connections
 
def f():
 search_sql = "SELECT propertyphotoid,propertyid,alykey FROM lansi_architecture_data.propertyphoto limit 0,5"
 cursor = connections['default'].cursor()
 try:
  cursor.execute(search_sql)
  rows = cursor.fetchall()
 except Exception as e:
  print(e)
  rows = 1
 
 print(rows)

 def create_cursor(self, name=None):
  cursor = self.connection.cursor()
  return CursorWrapper(cursor)

 @cached_property
 def is_sql_auto_is_null_enabled(self):
  with self.connection.cursor() as cursor:
   cursor.execute('SELECT @@SQL_AUTO_IS_NULL')
   result = cursor.fetchone()
   return result and result[0] == 1