django之原生SQL操作封装

公司的产品使用原生SQL，封装一个类，用于调用

 

 

# import pymysql
# 使用MySQL原生语句
from django.db import connection
import pymysql
class Exsql():
    '''
    配合django 内置的数据库操作函数使用
    '''
    def __init__(self):
        pass
        
    def get_one(self,sql):
        res=None
        try:
            with connection.cursor() as cursor:
                cursor.execute(sql)#需要先执行
                res=cursor.fetchone()
        except Exception as e:
            res={'code':1,'error':e}
        return res
    def get_one_dict(self,sql):
        res=None
        try:
             with connection.cursor() as cursor:
                cursor.execute(sql)#需要先执行
                desc = cursor.description
                res=[
                dict(zip([col[0] for col in desc], cursor.fetchone()))]
        except Exception as e:
            res={'code':1,'error':e}
        return res
    
    def get_all(self,sql):
        res=None
        try:
            with connection.cursor() as cursor:
                cursor.execute(sql)#需要先执行
                res=cursor.fetchall() 
        except Exception as e:
            res={'code':1,'error':e}
            
        return res
    def get_all_dict(self,sql):
        res=[]
        try:
            with connection.cursor() as cursor:
                cursor.execute(sql)#需要先执行
                desc = cursor.description
                res=[
                dict(zip([col[0] for col in desc], row))
                    for row in cursor.fetchall()
                ]
            
        except Exception as e:
            res={'code':1,'error':e}    
        return res
    
    def insert(self,sql):
        return self.__edit(sql)
    def update(self,sql):
        return self.__edit(sql)
    def delete(self,sql):
        return self.__edit(sql)
    
    def __edit(self,sql):
        count=0
        cursor=connection.cursor()
        try:
            count=cursor.execute(sql)
        except Exception as e:
            res={'code':1,'error':e}
            cursor.rollback()
        finally:
            cursor.close()
            
        return count