django脚本orm中使用原生sql

from django.core.management.base import BaseCommand
from chat_greeting_messages.models import Greeting
import os
from django.db import connection


class Command(BaseCommand):
    help = "Patch initialization recruiter not suitable gretting"

    def handle(self, *args, **options):
        ENV = os.environ.get("APP_ENV", "<replace_me>")
        print(ENV)
        try:
            if ENV == "production":
                print("start====")
                # exclude_not_suitable_ids = [2, 3, 4, 5, 6]

                # recruiter_ids = Greeting.objects.exclude(not_suitable_messages_id__in=exclude_not_suitable_ids).filter(
                #     message_type_id=3).values_list("recruiter_id", flat=True).distinct()

                # 执行查询
                query = """
                SELECT DISTINCT recruiter_id 
                FROM recruiter_chat_greeting_messages AS r 
                WHERE r.not_suitable_messages_id = 1 
                AND r.message_type_id = 3 
                AND NOT EXISTS (
                    SELECT 1 
                    FROM recruiter_chat_greeting_messages AS r2 
                    WHERE r2.recruiter_id = r.recruiter_id 
                    AND r2.not_suitable_messages_id IN (2,3,4,5,6)
                )
                """
                with connection.cursor() as cursor:
                    cursor.execute(query)
                    recruiter_ids = [row[0] for row in cursor.fetchall()]

                print(recruiter_ids)

                default_not_suitable_message_list = [
                    "Salary mismatch: Sorry, the expected salary in your profile does not match this job's range. Thank you for applying.",
                    "Educational qualifications mismatch: Sorry, your academic qualifications do not match this job's requirements. Thank you for applying.",
                    "Mismatch of expectations: Sorry, your profile does not match our current position requirements. Thanks for your time.",
                    "Experience mismatch: Thanks for your application, but we're looking for candidates with a different experience level for this job. We hope you'll find something that matches.",
                    "Thanks for your resume, but we have already found a candidate fo fill this role. We hope to have another vacancy that suits you.",
                ]
                for recruiter_id in recruiter_ids:
                    objects = [
                        Greeting(
                            recruiter_id=recruiter_id,
                            message=message,
                            message_type_id=3,
                            not_suitable_messages_id=index + 2,
                        )
                        for index, message in enumerate(default_not_suitable_message_list)
                    ]
                    Greeting.objects.bulk_create(objects)

        except Exception as e:
            raise e

 

posted @ 2024-03-24 21:49  Οo白麒麟оΟ  阅读(11)  评论(0编辑  收藏  举报