Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to implement native SQL in Django. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Table structure

File: django_project/app01/models

Class Book (models.Model): title = models.CharField (verbose_name= "title", max_length=32) describe = models.TextField (verbose_name= "description") author = models.CharField (verbose_name= "author", max_length=32) publisher = models.CharField (verbose_name= "Press", max_length=32) publisher_date = models.DateField (verbose_name= "publisher")

It's just a very simple book list.

Input some data through admin to test the use of

Extra mode

It is strongly recommended that there is no need to learn, no use for hair.

Raw mode

Compared with extra, this is more useful.

The syntax is as follows

Models. Table name. Objecs.raw (sql) models. Table name .objecs.raw (sql, [parameter 1, parameter 2])

Note: if there are no parameters, just write the sql statement. If there are parameters, you need to use a list later, as shown in the figure.

Give an example

Still returned Book objects one by one

True native sql mode

In fact, the above is still somewhat bound to django's model. But I'm just saying, I just want native sql and don't bind it to anything.

By the way, don't use pymysql to execute native sql on django. Some strange problems can occur. Be sure to import from django.db import connection to execute sql. The code is as follows:

From django.db import connection def book_list (request): # True native sql, cursor = connection.cursor () print (type (cursor)) cursor.execute ("select * from app01_book where id=%s", [1,]) raw = cursor.fetchall () print (raw)

The returned content is shown in the following figure:

As you can see, what is returned is an array of arrays in the list. I was thinking, is there any way to return the queried sql directly to a dictionary? The answer is yes!

Execute native sql and return as dict

I encapsulated it into two functions by executing the native sql and returning it directly into a dictionary

One is to query multiple ones, and the code is as follows:

Def query_all_dict (sql, params=None):''query all results return dictionary type data: param sql:: param params:: return:' 'with connection.cursor () as cursor: if params: cursor.execute (sql Params=params) else: cursor.execute (sql) col_names = [desc [0] for desc in cursor.description] row = cursor.fetchall () rowList = [] for list in row: tMap = dict (zip (col_names, list)) rowList.append (tMap) return rowList

One is to query one, and the code is as follows:

Def query_one_dict (sql, params=None): "" query a result returns dictionary type data: param sql:: param params:: return: "with connection.cursor () as cursor: if params: cursor.execute (sql Params=params) else: cursor.execute (sql) col_names = [desc [0] for desc in cursor.description] row = cursor.fetchone () tMap = dict (zip (col_names, row)) return tMap

The usage is as follows, calling the function directly in the view

The returned result is as follows, directly in list set dictionary format

What about querying people with conditions? it's actually the same as pymysql.

Return the result

But there is a problem, the above query, we know very well, let will only return a value, but still return a list set of dictionary format, does not seem to be quite right?

In fact, I wrote the above two methods, if you are sure to query a value, using the query_one_dict method.

The above summary

There are three ways to execute native sql in django, extra,raw,from django.db import connection

Among them, extra is basically useless, raw makes do, but it is bound to models. Connection is the most flexible, but the default return format is [tuple,tuple,tuple,].

After improvement, two methods are encapsulated, query_all_dict,query_one_dict, one is to query multiple, and the other is to query a single, and return to [dict,dict,dict,]

Suggestion

Use only query_all_dict,query_one_dict

Project code

Django_exec_sql.zip above is how to implement native SQL in the Django shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.