Dust8 的博客

>>> name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
>>> Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)

class RawQuerySet:
    ...
    @cached_property
    def columns(self):
        """
        A list of model field names in the order they'll appear in the
        query results.
        """
        columns = self.query.get_columns()
        # Adjust any column names which don't match field names
        for (query_name, model_name) in self.translations.items():
            # Ignore translations for nonexistent column names
            try:
                index = columns.index(query_name)
            except ValueError:
                pass
            else:
                columns[index] = model_name
        return columns

class RawQuery:
    ...

    def get_columns(self):
        if self.cursor is None:
            self._execute_query()
        converter = connections[self.using].introspection.identifier_converter
        return [converter(column_meta[0])
                for column_meta in self.cursor.description]

def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

# lib/export.py
from django.http import HttpResponse
from openpyxl import Workbook


class ExportExcelMixin:
    '''
    通用导出 Excel 文件动作
    '''

    def export_as_excel(self, request, queryset):
        '''
        导出为 excel 文件. 文件名为 app名.模型类名.xlsx.

        :param request:
        :param queryset:
        :return:
        '''
        # 用于定义文件名, 格式为: app名.模型类名
        meta = self.model._meta
        # 模型所有字段名
        field_names = [field.name for field in meta.fields]
        field_verbose_names = [field.verbose_name for field in meta.fields]

        # 定义响应内容类型
        response = HttpResponse(content_type='application/msexcel')
        # 定义响应数据格式
        response['Content-Disposition'] = f'attachment; filename={meta}.xlsx'

        wb = Workbook()
        ws = wb.active
        ws.append(field_verbose_names)

        # 遍历选择的对象列表
        for obj in queryset:
            # 将模型属性值的文本格式组成列表
            data = []
            for field in field_names:

                if hasattr(obj, f'get_{field}_display'):
                    # 可选属性取显示的值
                    value = getattr(obj, f'get_{field}_display')()
                else:
                    value = getattr(obj, field)

                data.append(f'{value}')

            ws.append(data)

        # 将数据存入响应内容
        wb.save(response)

        return response

    # 该动作在 admin 中的显示文字
    export_as_excel.short_description = '导出Excel'

# admin.py
from django.contrib import admin


from export import ExportExcelMixin
from .models import *

class XXAdmin(admin.ModelAdmin, ExportExcelMixin):
    ...

    actions = ['export_as_excel']

def insert(conn,table_name, data, sep='?'):
    cur = conn.cursor()

    # 把字典变成键值对数组
    zipped = []
    for key, value in data.items():
        zipped.append((key, value))

    # 把键值对数组变成对应的键数组和值数组
    keys, values = zip(*zipped)

    sql = f"insert into {table_name}({', '.join(keys)}) values ({', '.join([sep for _ in range(len(values))])})"
    cur.execute(sql, values)
    conn.commit()
    cur.close()