django execute raw query sql, and return Dict dictionary example

Many times the execution of sql statements, data faster than django's model, but do not want to care about the return of the field, for example, you can perform: select * from product this sql, this method will be returned to the database with the same column name of the key-value pairs , the format is like this:

result = [{“id”:1,”name”:”product1”},{“id”:2,”name”:”product2”}]

Of course you can.

import json
(result )

Returns a string of json data, isn't that perfect.

# coding:utf-8
from  import connection, transaction

'''Execute django raw sql statement and return an array object'''
def executeQuery(sql):
 cursor = () # Get a cursor object.
 (sql)
 rawData = ()
 print rawData
 col_names = [desc[0] for desc in ]
 print col_names

 result = []
 for row in rawData:
  objDict = {}
  # Iterate through each row of data into the Dict #
  for index, value in enumerate(row):
  print index, col_names[index], value
  objDict[col_names[index]] = value

  (objDict)

 return result

Additional knowledge:Rewrite django's mysql driver to implement native sql statement query to return dictionary type data

When using django, some needs require particularly high query efficiency, so you need to use the native sql statement query, but the query result is usually a tuple nested tuple. In order to deal with the convenience of the need to query from the database directly after the return of the dictionary type of data.

The method used here is to inherit the driver

First create a mysql folder under the django project.

from  import base
from  import features
from  import cached_property
 
class DatabaseFeatures():
 @cached_property
 def is_sql_auto_is_null_enabled(self):
  with () as cursor:
   ('SELECT @@SQL_AUTO_IS_NULL')
   result = ()
   return result and result['@@SQL_AUTO_IS_NULL'] == 1
 
 
class DatabaseWrapper():
 features_class = DatabaseFeatures
 
 def create_cursor(self, name=None):
  cursor = ()
  return (cursor)
 
 @cached_property
 def mysql_server_info(self):
  with self.temporary_connection() as cursor:
   ('SELECT VERSION()')
   return ()['VERSION()']

Finally, change the database engine in the django project's files for the database configuration

DATABASES = {
 'default': {
  'ENGINE': '', # Specify the database driver as the mysql folder just created
  'NAME': 'test', # Specified database name
  'USER': 'root', # Username for database login
  'PASSWORD': '123456', # Password for logging into the database
  'HOST': '127.0.0.1',
  'PORT': '3306', # database server port, mysql default is 3306
  'DATABASE_OPTIONS': {
   'connect_timeout': 60,
   'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
   'charset': 'utf8mb4',
  },
 }
}

beta (software)

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

output result

[{'propertyphotoid': 27, 'propertyid': 0, 'alykey': '123456'}, {'propertyphotoid': 28, 'propertyid': 10837, 'alykey': 'Property/6113/207504A1-AC65-4E3B-BE86-538B3807D364'}, {'propertyphotoid': 29, 'propertyid': 6113, 'alykey': 'Property/6113/357A4EAE-750A-4F59-AF01-271B4225CFBD'}, {'propertyphotoid': 31, 'propertyid': 6113, 'alykey': 'Property/6113/6DF1A2C1-F54C-4462-8363-619806A2F085'}, {'propertyphotoid': 36, 'propertyid': 6113, 'alykey': 'Property/6113/572CB245-ABC0-4FD6-8353-729EBD5E5D46'}]

Source code parsing:

The __getitem__ method of the

The cursor to get the connection object is returned by create_cursor of the DatabaseWrapper class. So you only need to override the create_cursor method to change the type of data returned by the cursor.

The create_cursor method in the class is as follows:

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

Here, theoretically, the rewrite goal has been completed, but in the test when the error, in the is_sql_auto_is_null_enabled method in the KeyError error.

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

The reason is that the is_sql_auto_is_null_enabled method uses a rewritten cursor, and the result returned by ('SELECT @@SQL_AUTO_IS_NULL') is not a tuple, but a dictionary. So result[0] will report a KeyError error. Rewrite the is_sql_auto_is_null_enabled method to change result[0] to result['@@SQL_AUTO_IS_NULL'] and it will work.

Finally, you also need to assign the features_class in the DatabaseWrapper class to the rewritten DatabaseFeatures class.

Above this django execution of the original query sql, and return Dict dictionary example is all that I have shared with you, I hope to be able to give you a reference, and I hope you support me more.