Python query.use_read_replica_if_available函数代码示例

    def get_users(self, course_id, user_id=None):
        """
        Gets the users for a given target.

        Result is returned in the form of a queryset, and may contain duplicates.
        """
        staff_qset = CourseStaffRole(course_id).users_with_role()
        instructor_qset = CourseInstructorRole(course_id).users_with_role()
        staff_instructor_qset = (staff_qset | instructor_qset)
        enrollment_qset = User.objects.filter(
            is_active=True,
            courseenrollment__course_id=course_id,
            courseenrollment__is_active=True
        )
        if self.target_type == SEND_TO_MYSELF:
            if user_id is None:
                raise ValueError("Must define self user to send email to self.")
            user = User.objects.filter(id=user_id)
            return use_read_replica_if_available(user)
        elif self.target_type == SEND_TO_STAFF:
            return use_read_replica_if_available(staff_instructor_qset)
        elif self.target_type == SEND_TO_LEARNERS:
            return use_read_replica_if_available(enrollment_qset.exclude(id__in=staff_instructor_qset))
        elif self.target_type == SEND_TO_COHORT:
            return User.objects.none()  # TODO: cohorts aren't hooked up, put that logic here
        else:
            raise ValueError("Unrecognized target type {}".format(self.target_type))

    def get_users(self, course_id, user_id=None):
        """
        Gets the users for a given target.

        Result is returned in the form of a queryset, and may contain duplicates.
        """
        staff_qset = CourseStaffRole(course_id).users_with_role()
        instructor_qset = CourseInstructorRole(course_id).users_with_role()
        staff_instructor_qset = (staff_qset | instructor_qset)
        enrollment_qset = User.objects.filter(
            is_active=True,
            courseenrollment__course_id=course_id,
            courseenrollment__is_active=True
        )
        if self.target_type == SEND_TO_MYSELF:
            if user_id is None:
                raise ValueError("Must define self user to send email to self.")
            user = User.objects.filter(id=user_id)
            return use_read_replica_if_available(user)
        elif self.target_type == SEND_TO_STAFF:
            return use_read_replica_if_available(staff_instructor_qset)
        elif self.target_type == SEND_TO_LEARNERS:
            return use_read_replica_if_available(enrollment_qset.exclude(id__in=staff_instructor_qset))
        elif self.target_type == SEND_TO_COHORT:
            return self.cohorttarget.cohort.users.filter(id__in=enrollment_qset)  # pylint: disable=no-member
        elif self.target_type == SEND_TO_TRACK:
            return use_read_replica_if_available(
                enrollment_qset.filter(
                    courseenrollment__mode=self.coursemodetarget.track.mode_slug
                )
            )
        else:
            raise ValueError("Unrecognized target type {}".format(self.target_type))

def _get_recipient_querysets(user_id, to_option, course_id):
    """
    Returns a list of query sets of email recipients corresponding to the
    requested `to_option` category.

    `to_option` is either SEND_TO_MYSELF, SEND_TO_STAFF, or SEND_TO_ALL.

    Recipients who are in more than one category (e.g. enrolled in the course
    and are staff or self) will be properly deduped.
    """
    if to_option.isdigit():
        if not GroupedQuery.objects.filter(id=int(to_option)).exists():
            message = "Bulk email TO_OPTION query id {query_id} does not exist".format(query_id=to_option)
            log.error(message)
            raise Exception(message)
    elif to_option not in TO_OPTIONS:
        log.error("Unexpected bulk email TO_OPTION found: %s", to_option)
        raise Exception("Unexpected bulk email TO_OPTION found: {0}".format(to_option))

    if to_option.isdigit():
        recipient_queryset = get_group_query_students(course_id, int(to_option))
        return [recipient_queryset]
    elif to_option == SEND_TO_MYSELF:
        user = User.objects.filter(id=user_id)
        return [use_read_replica_if_available(user)]
    else:
        staff_qset = CourseStaffRole(course_id).users_with_role()
        instructor_qset = CourseInstructorRole(course_id).users_with_role()
        staff_instructor_qset = (staff_qset | instructor_qset).distinct()
        if to_option == SEND_TO_STAFF:
            return [use_read_replica_if_available(staff_instructor_qset)]

        if to_option == SEND_TO_ALL:
            # We also require students to have activated their accounts to
            # provide verification that the provided email address is valid.
            enrollment_qset = User.objects.filter(
                is_active=True,
                courseenrollment__course_id=course_id,
                courseenrollment__is_active=True
            )

            # to avoid duplicates, we only want to email unenrolled course staff
            # members here
            unenrolled_staff_qset = staff_instructor_qset.exclude(
                courseenrollment__course_id=course_id, courseenrollment__is_active=True
            )

            # use read_replica if available
            recipient_qsets = [
                use_read_replica_if_available(unenrolled_staff_qset),
                use_read_replica_if_available(enrollment_qset),
            ]
            return recipient_qsets

    def handle(self, *args, **options):
        """Fix newline courses in CSM!"""
        if len(args) != 2:
            raise CommandError('Must specify start and end dates: e.g. "2016-08-23 16:43:00" "2016-08-24 22:00:00"')

        start, end = args
        dry_run = options['dry_run']

        log.info(
            "Starting fix_student_module_newlines in %s mode!",
            "dry_run" if dry_run else "real"
        )

        rows_to_fix = use_read_replica_if_available(
            # pylint: disable=no-member
            StudentModule.objects.raw(
                "select * from courseware_studentmodule where modified between %s and %s and course_id like %s",
                (start, end, '%\n')
            )
        )

        results = [self.fix_row(row, dry_run=dry_run) for row in rows_to_fix]
        log.info(
            "Finished fix_student_module_newlines in %s mode!",
            "dry_run" if dry_run else "real"
        )
        log.info("Stats: %s rows detected", len(results))
        if results:
            # Add up all the columns
            aggregated_result = FixResult(*[sum(col) for col in zip(*results)])
            log.info("Results: %s", aggregated_result)

 def verified_certificates_contributing_more_than_minimum(cls, course_id):
     return use_read_replica_if_available(
         CertificateItem.objects.filter(
             course_id=course_id,
             mode='verified',
             status='purchased',
             unit_cost__gt=(CourseMode.min_course_price_for_verified_for_currency(course_id, 'usd'))).count())

    def _get_unsynced_users(self, site_domain, last_synced_user, days_threshold):
        """
        Args:
            site_domain: site where we need unsynced users
            last_synced_user: last synced user
            days_threshold: number of days threshold to sync users in case we don't have last synced user

        Returns: Ordered list of users needs to be synced

        """
        if last_synced_user:
            users = User.objects.select_related('profile').filter(id__gt=last_synced_user.id).order_by('pk')
        else:
            # If we don't have last synced user get all users who joined on between today and threshold days ago
            start_date = datetime.now().date() - timedelta(days_threshold)
            self.stdout.write(
                'Started pulling unsynced contacts for site {site} from {start_date}'.format(
                    site=site_domain, start_date=start_date
                )
            )
            users = User.objects.select_related('profile').filter(date_joined__date__gte=start_date).order_by('pk')

        unsynced_users = [
            user for user in use_read_replica_if_available(users)
            if UserAttribute.get_user_attribute(user, 'created_on_site') == site_domain
        ]
        return unsynced_users

 def get(self, request, program_uuid=None):
     """ Defines the GET list endpoint for ProgramEnrollment objects. """
     enrollments = use_read_replica_if_available(
         ProgramEnrollment.objects.filter(program_uuid=program_uuid)
     )
     paginated_enrollments = self.paginate_queryset(enrollments)
     serializer = ProgramEnrollmentListSerializer(paginated_enrollments, many=True)
     return self.get_paginated_response(serializer.data)

def _get_recipient_queryset(user_id, to_option, course_id, course_location):
    """
    Returns a query set of email recipients corresponding to the requested to_option category.

    `to_option` is either SEND_TO_MYSELF, SEND_TO_STAFF, or SEND_TO_ALL.

    Recipients who are in more than one category (e.g. enrolled in the course and are staff or self)
    will be properly deduped.
    """
    if to_option not in TO_OPTIONS:
        log.error("Unexpected bulk email TO_OPTION found: %s", to_option)
        raise Exception("Unexpected bulk email TO_OPTION found: {0}".format(to_option))

    if to_option == SEND_TO_MYSELF:
        recipient_qset = User.objects.filter(id=user_id)
    else:
        staff_qset = CourseStaffRole(course_id).users_with_role()
        instructor_qset = CourseInstructorRole(course_id).users_with_role()
        recipient_qset = (staff_qset | instructor_qset).distinct()
        if to_option == SEND_TO_ALL:
            # We also require students to have activated their accounts to
            # provide verification that the provided email address is valid.
            enrollment_qset = User.objects.filter(
                is_active=True,
                courseenrollment__course_id=course_id,
                courseenrollment__is_active=True
            )
            # Now we do some queryset sidestepping to avoid doing a DISTINCT
            # query across the course staff and the enrolled students, which
            # forces the creation of a temporary table in the db.
            unenrolled_staff_qset = recipient_qset.exclude(
                courseenrollment__course_id=course_id, courseenrollment__is_active=True
            )
            # use read_replica if available:
            unenrolled_staff_qset = use_read_replica_if_available(unenrolled_staff_qset)

            unenrolled_staff_ids = [user.id for user in unenrolled_staff_qset]
            recipient_qset = enrollment_qset | User.objects.filter(id__in=unenrolled_staff_ids)

    # again, use read_replica if available to lighten the load for large queries
    return use_read_replica_if_available(recipient_qset)

 def _get_users_queryset(self, initial_days):
     """
     initial_days: numbers of days to go back from today
     :return: users queryset
     """
     start_date = datetime.now().date() - timedelta(initial_days)
     end_date = datetime.now().date() - timedelta(1)
     self.stdout.write(u'Getting users from {start} to {end}'.format(start=start_date, end=end_date))
     users_qs = User.objects.filter(
         date_joined__date__gte=start_date,
         date_joined__date__lte=end_date
     ).order_by('id')
     return use_read_replica_if_available(users_qs)

    def handle(self, *args, **options):
        """
        Handler for the command

        It creates batches of expired Software Secure Photo Verification and sends it to send_verification_expiry_email
        that used edx_ace to send email to these learners
        """
        resend_days = settings.VERIFICATION_EXPIRY_EMAIL['RESEND_DAYS']
        days = settings.VERIFICATION_EXPIRY_EMAIL['DAYS_RANGE']
        batch_size = options['batch_size']
        sleep_time = options['sleep_time']
        dry_run = options['dry_run']

        end_date = now().replace(hour=0, minute=0, second=0, microsecond=0)
        # If email was sent and user did not re-verify then this date will be used as the criteria for resending email
        date_resend_days_ago = end_date - timedelta(days=resend_days)

        start_date = end_date - timedelta(days=days)

        # Adding an order_by() clause will override the class meta ordering as we don't need ordering here
        query = SoftwareSecurePhotoVerification.objects.filter(Q(status='approved') &
                                                               (Q(expiry_date__gte=start_date,
                                                                  expiry_date__lt=end_date) |
                                                                Q(expiry_email_date__lte=date_resend_days_ago)
                                                                )).order_by()

        sspv = use_read_replica_if_available(query)

        total_verification = sspv.count()
        if not total_verification:
            logger.info(u"No approved expired entries found in SoftwareSecurePhotoVerification for the "
                        u"date range {} - {}".format(start_date.date(), now().date()))
            return

        logger.info(u"For the date range {} - {}, total Software Secure Photo verification filtered are {}"
                    .format(start_date.date(), now().date(), total_verification))

        batch_verifications = []

        for verification in sspv:
            if not verification.expiry_email_date or verification.expiry_email_date <= date_resend_days_ago:
                batch_verifications.append(verification)

                if len(batch_verifications) == batch_size:
                    send_verification_expiry_email(batch_verifications, dry_run)
                    time.sleep(sleep_time)
                    batch_verifications = []

        # If selected verification in batch are less than batch_size
        if batch_verifications:
            send_verification_expiry_email(batch_verifications, dry_run)

 def enrollment_counts(cls, course_id):
     """
     Returns a dictionary that stores the total enrollment count for a course, as well as the
     enrollment count for each individual mode.
     """
     # Unfortunately, Django's "group by"-style queries look super-awkward
     query = use_read_replica_if_available(cls.objects.filter(course_id=course_id, is_active=True).values('mode').order_by().annotate(Count('mode')))
     total = 0
     d = defaultdict(int)
     for item in query:
         d[item['mode']] = item['mode__count']
         total += item['mode__count']
     d['total'] = total
     return d

    def enrollment_counts(cls, course_id, date):

        query = use_read_replica_if_available(CourseEnrollment.objects.filter(
            course_id=course_id,
            is_active=True,
            created__lte=date,
        ).values('mode').order_by().annotate(Count('mode')))
        total = 0
        enroll_dict = defaultdict(int)
        for item in query:
            enroll_dict[item['mode']] = item['mode__count']
            total += item['mode__count']
        enroll_dict['total'] = total
        return enroll_dict

    def rows(self):
        query1 = use_read_replica_if_available(
            CertificateItem.objects.select_related('user__profile').filter(
                status="refunded",
                refund_requested_time__gte=self.start_date,
                refund_requested_time__lt=self.end_date,
            ).order_by('refund_requested_time'))
        query2 = use_read_replica_if_available(
            CertificateItem.objects.select_related('user__profile').filter(
                status="refunded",
                refund_requested_time=None,
            ))

        query = query1 | query2

        for item in query:
            yield [
                item.order_id,
                item.user.profile.name,
                item.fulfilled_time,
                item.refund_requested_time,
                item.line_cost,
                item.service_fee,
            ]

    def verified_certificates_monetary_field_sum(cls, course_id, status, field_to_aggregate):
        """
        Returns a Decimal indicating the total sum of field_to_aggregate for all verified certificates with a particular status.

        Sample usages:
        - status 'refunded' and field_to_aggregate 'unit_cost' will give the total amount of money refunded for course_id
        - status 'purchased' and field_to_aggregate 'service_fees' gives the sum of all service fees for purchased certificates
        etc
        """
        query = use_read_replica_if_available(
            CertificateItem.objects.filter(course_id=course_id, mode='verified', status=status).aggregate(Sum(field_to_aggregate)))[field_to_aggregate + '__sum']
        if query is None:
            return Decimal(0.00)
        else:
            return query

    def handle(self, *args, **options):
        """
        Handler for the command

        It filters approved Software Secure Photo Verification and then for each distinct user it finds the most
        recent approved verification and set its expiry_date
        """
        batch_size = options['batch_size']
        sleep_time = options['sleep_time']

        query = SoftwareSecurePhotoVerification.objects.filter(status='approved').order_by()
        sspv = use_read_replica_if_available(query)

        if not sspv.count():
            logger.info("No approved entries found in SoftwareSecurePhotoVerification")
            return

        distinct_user_ids = set()
        update_verification_ids = []
        update_verification_count = 0

        for verification in sspv:
            if verification.user_id not in distinct_user_ids:
                distinct_user_ids.add(verification.user_id)

                recent_verification = self.find_recent_verification(sspv, verification.user_id)
                if not recent_verification.expiry_date:
                    update_verification_ids.append(recent_verification.pk)
                    update_verification_count += 1

                if update_verification_count == batch_size:
                    self.bulk_update(update_verification_ids)
                    update_verification_count = 0
                    update_verification_ids = []
                    time.sleep(sleep_time)

        if update_verification_ids:
            self.bulk_update(update_verification_ids)