Python django.db() Examples

def get_fields_from_path(model, path):
    """ Return list of Fields given path relative to model.

    e.g. (ModelX, "user__groups__name") -> [
        <django.db.models.fields.related.ForeignKey object at 0x...>,
        <django.db.models.fields.related.ManyToManyField object at 0x...>,
        <django.db.models.fields.CharField object at 0x...>,
    ]
    """
    pieces = path.split(LOOKUP_SEP)
    fields = []
    for piece in pieces:
        if fields:
            parent = get_model_from_relation(fields[-1])
        else:
            parent = model
        fields.append(parent._meta.get_field(piece))
    return fields 

def executemany_wrapper(wrapped, instance, args, kwargs):
    """
    CursorWrapper.executemany() wrapper for Django < 2.0
    """
    try:
        sql, param_list = _extract_sql_param_list(*args, **kwargs)
    except TypeError:
        sql = None
        param_list = None

    if sql is not None:
        tracked_request = TrackedRequest.instance()
        span = tracked_request.start_span(operation="SQL/Many")
        span.tag("db.statement", sql)

    try:
        return wrapped(*args, **kwargs)
    finally:
        if sql is not None:
            tracked_request.stop_span()
            if tracked_request.n_plus_one_tracker.should_capture_backtrace(
                sql=sql, duration=span.duration(), count=len(param_list),
            ):
                span.capture_backtrace() 

def execute_wrapper(wrapped, instance, args, kwargs):
    """
    CursorWrapper.execute() wrapper for Django < 2.0
    """
    try:
        sql = _extract_sql(*args, **kwargs)
    except TypeError:
        sql = None

    if sql is not None:
        tracked_request = TrackedRequest.instance()
        span = tracked_request.start_span(operation="SQL/Query")
        span.tag("db.statement", sql)

    try:
        return wrapped(*args, **kwargs)
    finally:
        if sql is not None:
            tracked_request.stop_span()
            if tracked_request.n_plus_one_tracker.should_capture_backtrace(
                sql, span.duration()
            ):
                span.capture_backtrace() 

def get_fields_from_path(model, path):
    """ Return list of Fields given path relative to model.

    e.g. (ModelX, "user__groups__name") -> [
        <django.db.models.fields.related.ForeignKey object at 0x...>,
        <django.db.models.fields.related.ManyToManyField object at 0x...>,
        <django.db.models.fields.CharField object at 0x...>,
    ]
    """
    pieces = path.split(LOOKUP_SEP)
    fields = []
    for piece in pieces:
        if fields:
            parent = get_model_from_relation(fields[-1])
        else:
            parent = model
        fields.append(parent._meta.get_field(piece))
    return fields 

def get_fields_from_path(model, path):
    """ Return list of Fields given path relative to model.

    e.g. (ModelX, "user__groups__name") -> [
        <django.db.models.fields.related.ForeignKey object at 0x...>,
        <django.db.models.fields.related.ManyToManyField object at 0x...>,
        <django.db.models.fields.CharField object at 0x...>,
    ]
    """
    pieces = path.split(LOOKUP_SEP)
    fields = []
    for piece in pieces:
        if fields:
            parent = get_model_from_relation(fields[-1])
        else:
            parent = model
        fields.append(parent._meta.get_field(piece))
    return fields 

def get_fields_from_path(model, path):
    """ Return list of Fields given path relative to model.

    e.g. (ModelX, "user__groups__name") -> [
        <django.db.models.fields.related.ForeignKey object at 0x...>,
        <django.db.models.fields.related.ManyToManyField object at 0x...>,
        <django.db.models.fields.CharField object at 0x...>,
    ]
    """
    pieces = path.split(LOOKUP_SEP)
    fields = []
    for piece in pieces:
        if fields:
            parent = get_model_from_relation(fields[-1])
        else:
            parent = model
        fields.append(parent._meta.get_field(piece))
    return fields 

def get_fields_from_path(model, path):
    """ Return list of Fields given path relative to model.

    e.g. (ModelX, "user__groups__name") -> [
        <django.db.models.fields.related.ForeignKey object at 0x...>,
        <django.db.models.fields.related.ManyToManyField object at 0x...>,
        <django.db.models.fields.CharField object at 0x...>,
    ]
    """
    pieces = path.split(LOOKUP_SEP)
    fields = []
    for piece in pieces:
        if fields:
            parent = get_model_from_relation(fields[-1])
        else:
            parent = model
        fields.append(parent._meta.get_field(piece))
    return fields 

def get_fields_from_path(model, path):
    """ Return list of Fields given path relative to model.

    e.g. (ModelX, "user__groups__name") -> [
        <django.db.models.fields.related.ForeignKey object at 0x...>,
        <django.db.models.fields.related.ManyToManyField object at 0x...>,
        <django.db.models.fields.CharField object at 0x...>,
    ]
    """
    pieces = path.split(LOOKUP_SEP)
    fields = []
    for piece in pieces:
        if fields:
            parent = get_model_from_relation(fields[-1])
        else:
            parent = model
        fields.append(parent._meta.get_field(piece))
    return fields 

def fetchExdbText(self, guid):
        filepath = DataDir + "exdb/data/" + guid
        if not os.path.exists(filepath):
            url = "https://www.exploit-db.com/raw/{id}".format(id=guid)
            logger.info(url)
            headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko'}
            try:
                res = requests.get(url, headers=headers)
            except Exception as e:
                logger.error(e)
            if not res.text == '':
                open(filepath, 'w').write(res.text)
        if guid.endswith("pdf"):
            text = ''
        else:
            text = open(filepath).read()
        sleep(5)
        return text 

def test_dates_with_aggregation(self):
        """
        .dates() returns a distinct set of dates when applied to a
        QuerySet with aggregation.

        Refs #18056. Previously, .dates() would return distinct (date_kind,
        aggregation) sets, in this case (year, num_authors), so 2008 would be
        returned twice because there are books from 2008 with a different
        number of authors.
        """
        srv_ver = connection.get_server_version()
        if (12, 0, 0, 0) <= srv_ver < (13, 0, 0, 0):
            # this test fails on SQL server 2014
            self.skipTest("TODO fix django.db.utils.OperationalError: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.")
        dates = Book.objects.annotate(num_authors=Count("authors")).dates('pubdate', 'year')
        self.assertQuerysetEqual(
            dates, [
                "datetime.date(1991, 1, 1)",
                "datetime.date(1995, 1, 1)",
                "datetime.date(2007, 1, 1)",
                "datetime.date(2008, 1, 1)"
            ]
        ) 

def get_fields_from_path(model, path):
    """ Return list of Fields given path relative to model.

    e.g. (ModelX, "user__groups__name") -> [
        <django.db.models.fields.related.ForeignKey object at 0x...>,
        <django.db.models.fields.related.ManyToManyField object at 0x...>,
        <django.db.models.fields.CharField object at 0x...>,
    ]
    """
    pieces = path.split(LOOKUP_SEP)
    fields = []
    for piece in pieces:
        if fields:
            parent = get_model_from_relation(fields[-1])
        else:
            parent = model
        fields.append(parent._meta.get_field(piece))
    return fields 

def import_places(self):
        # munigeo saves addresses in local db, we just create Places from them.
        # note that the addresses only change daily and the import is time-consuming, so we should not run this hourly

        # addresses require the municipalities to be present in the db
        call_command('geo_import', 'finland', municipalities=True)
        call_command('geo_import', 'helsinki', addresses=True)

        queryset = Place.objects.filter(data_source=self.data_source)
        if self.options.get('single', None):
            obj_id = self.options['single']
            obj_list = [self.pk_get('Address', obj_id)]
            queryset = queryset.filter(id=obj_id)
        else:
            logger.info("Loading addresses...")
            obj_list = self.pk_get('Address')
            logger.info("%s addresses loaded" % len(obj_list))
        syncher = ModelSyncher(queryset, lambda obj: obj.origin_id, delete_func=self.mark_deleted,
                               check_deleted_func=self.check_deleted)
        for idx, obj in enumerate(obj_list):
            if idx and (idx % 1000) == 0:
                logger.info("%s addresses processed" % idx)
            self._import_address(syncher, obj)

        syncher.finish(self.options.get('remap', False)) 

def run(self):
        for db_alias in settings.DATABASES:
            self.db_alias = db_alias
            self.db_vendor = connections[self.db_alias].vendor
            print('Benchmarking %s…' % self.db_vendor)
            for cache_alias in settings.CACHES:
                cache = caches[cache_alias]
                self.cache_name = cache.__class__.__name__[:-5].lower()
                with override_settings(CACHALOT_CACHE=cache_alias):
                    self.execute_benchmark()

        self.df = pd.DataFrame.from_records(self.data)
        if not os.path.exists(RESULTS_PATH):
            os.mkdir(RESULTS_PATH)
        self.df.to_csv(os.path.join(RESULTS_PATH, 'data.csv'))

        self.xlim = (0, self.df['time'].max() * 1.01)
        self.output('db')
        self.output('cache') 

def test_in_lookup_allows_F_expressions_and_expressions_for_integers(self):
        # __in lookups can use F() expressions for integers.
        self.skipTest("TODO fix django.db.transaction.TransactionManagementError: An error occurred in the current transaction. You can't execute queries until the end of the 'atomic' block.")
        queryset = Company.objects.filter(num_employees__in=([F('num_chairs') - 10]))
        self.assertQuerysetEqual(queryset, ['<Company: 5060 Ltd>'], ordered=False)
        self.assertQuerysetEqual(
            Company.objects.filter(num_employees__in=([F('num_chairs') - 10, F('num_chairs') + 10])),
            ['<Company: 5040 Ltd>', '<Company: 5060 Ltd>'],
            ordered=False
        )
        self.assertQuerysetEqual(
            Company.objects.filter(
                num_employees__in=([F('num_chairs') - 10, F('num_chairs'), F('num_chairs') + 10])
            ),
            ['<Company: 5040 Ltd>', '<Company: 5050 Ltd>', '<Company: 5060 Ltd>'],
            ordered=False
        ) 

def get_fields_from_path(model, path):
    """ Return list of Fields given path relative to model.

    e.g. (ModelX, "user__groups__name") -> [
        <django.db.models.fields.related.ForeignKey object at 0x...>,
        <django.db.models.fields.related.ManyToManyField object at 0x...>,
        <django.db.models.fields.CharField object at 0x...>,
    ]
    """
    pieces = path.split(LOOKUP_SEP)
    fields = []
    for piece in pieces:
        if fields:
            parent = get_model_from_relation(fields[-1])
        else:
            parent = model
        fields.append(parent._meta.get_field(piece))
    return fields 

def test_in_lookup_allows_F_expressions_and_expressions_for_datetimes(self):
        self.skipTest("TODO fix django.db.utils.OperationalError: Conversion failed when converting date and/or time from character string.")
        # (0.012) QUERY = 'SELECT [expressions_result].[id], [expressions_result].[experiment_id], [expressions_result].[result_time] FROM [expressions_result] INNER JOIN [expressions_experiment] ON ([expressions_result].[experiment_id] = [expressions_experiment].[id]) WHERE [expressions_result].[result_time] BETWEEN %s AND %s' - PARAMS = ('F(experiment__start)', 'F(experiment__end)'); args=('F(experiment__start)', 'F(experiment__end)')
        start = datetime.datetime(2016, 2, 3, 15, 0, 0)
        end = datetime.datetime(2016, 2, 5, 15, 0, 0)
        experiment_1 = Experiment.objects.create(
            name='Integrity testing',
            assigned=start.date(),
            start=start,
            end=end,
            completed=end.date(),
            estimated_time=end - start,
        )
        experiment_2 = Experiment.objects.create(
            name='Taste testing',
            assigned=start.date(),
            start=start,
            end=end,
            completed=end.date(),
            estimated_time=end - start,
        )
        Result.objects.create(
            experiment=experiment_1,
            result_time=datetime.datetime(2016, 2, 4, 15, 0, 0),
        )
        Result.objects.create(
            experiment=experiment_1,
            result_time=datetime.datetime(2016, 3, 10, 2, 0, 0),
        )
        Result.objects.create(
            experiment=experiment_2,
            result_time=datetime.datetime(2016, 1, 8, 5, 0, 0),
        )

        within_experiment_time = [F('experiment__start'), F('experiment__end')]
        queryset = Result.objects.filter(result_time__range=within_experiment_time)
        self.assertQuerysetEqual(queryset, ["<Result: Result at 2016-02-04 15:00:00>"])

        within_experiment_time = [F('experiment__start'), F('experiment__end')]
        queryset = Result.objects.filter(result_time__range=within_experiment_time)
        self.assertQuerysetEqual(queryset, ["<Result: Result at 2016-02-04 15:00:00>"]) 

def get_rules(self):
        """Get rules from tables"""
        connsuccess = False
        dbtries = 20
        while not connsuccess:
            try:
                rules = LimitRule.objects.all()
                connsuccess = True
            except LimitRule.DoesNotExist:
                msg = 'LogESP sentry thread for limit rule ' + \
                        self.rule.name + \
                        ' exiting. Rule no longer exists.'
                exit(0)
            except Exception:
                if dbtries == 20:
                    db.connections.close_all()
                    msg = 'LogESP parser thread for ' + filename + \
                            ' got a db error. Resetting conn. ' + \
                            'Event: ' + str(ourline[:160]) + \
                            '... Error: ' + str(err)
                    syslog.syslog(syslog.LOG_ERR, msg)
                elif dbtries == 0:
                    dbtries = 20
                    msg = 'LogESP sentry thread for ' + self.rule.name + \
                            ' got 20 db errors while retrieving rules. ' + \
                            'Error: ' + str(err)
                    syslog.syslog(syslog.LOG_ERR, msg)
                    exit(1)
                else:
                    sleep(0.2)
                dbtries -= 1
        for r in rules:
            if not r.id in self.rules:
                self.newrules.append(r)
        self.rules = [r.id for r in rules] 

def test_order_by_resetting(self):
        # Calling order_by() with no parameters removes any existing ordering on the
        # model. But it should still be possible to add new ordering after that.
        qs = Author.objects.order_by().order_by('name')
        self.assertIn('ORDER BY', qs.query.get_compiler(qs.db).as_sql()[0]) 

def test_ticket7813(self):
        # We should also be able to pickle things that use select_related().
        # The only tricky thing here is to ensure that we do the related
        # selections properly after unpickling.
        qs = Item.objects.select_related()
        query = qs.query.get_compiler(qs.db).as_sql()[0]
        query2 = pickle.loads(pickle.dumps(qs.query))
        self.assertEqual(
            query2.get_compiler(qs.db).as_sql()[0],
            query
        ) 

def allow_migrate(self, db, app_label, **hints):
        return db == DEFAULT_DB_ALIAS 

def test_pickling(self):
        for db in connections:
            Book.objects.using(db).create(title='Dive into Python', published=datetime.date(2009, 5, 4))
            qs = Book.objects.all()
            self.assertEqual(qs.db, pickle.loads(pickle.dumps(qs)).db) 

def test_nested_queries_sql(self):
        # Nested queries should not evaluate the inner query as part of constructing the
        # SQL (so we should see a nested query here, indicated by two "SELECT" calls).
        qs = Annotation.objects.filter(notes__in=Note.objects.filter(note="xyzzy"))
        self.assertEqual(
            qs.query.get_compiler(qs.db).as_sql()[0].count('SELECT'),
            2
        ) 

def _lookup_template(self, template_data):
        # This method should retrieve a template, given either a dict with a UUID
        # or a string/unicode reference. It should never save a new template.
        # This is expected to be called only once and should complete in a small,
        # finite number of db queries.
        try:
            template_data.get('uuid')
            try:
                return Template.objects.get(uuid=template_data.get('uuid'))
            except Template.DoesNotExist:
                raise serializers.ValidationError(
                    'No template found with UUID "%s"'
                    % template_data.get('uuid'))
        except AttributeError:
            pass

        # Not an object. Treat as a string reference.
        if not isinstance(template_data, (str, unicode)):
            raise serializers.ValidationError(
                'Invalid template. Expcted object or string but found "%s"'
                % template_data)

        matches = Template.filter_by_name_or_id_or_tag_or_hash(template_data)
        if len(matches) == 0:
            raise serializers.ValidationError(
                'No template found for identifier "%s"' % template_data)
        elif len(matches) > 1:
            raise serializers.ValidationError(
                'More than one template matched identifier "%s"' % template_data)
        else:
            return matches[0] 

def test_invalid_operator(self):
        self.skipTest("TODO fix django.db.utils.ProgrammingError: Incorrect syntax near '1000000'.")
        # throws django.db.utils.ProgrammingError instead of DatabaseError
        with self.assertRaises(DatabaseError):
            list(Experiment.objects.filter(start=F('start') * datetime.timedelta(0))) 

def _connect_tasks_to_active_task_attempts(self, tasks, task_attempts):
        params = []
        for task_uuid, task_attempt_uuid in self._task_to_task_attempt_relationships:
            task = filter(lambda t: t.uuid==task_uuid, tasks)[0]
            task_attempt = filter(
                lambda ta: ta.uuid==task_attempt_uuid, task_attempts)[0]
            params.append((task.id, task_attempt.id))
        if params:
            case_statement = ' '.join(
                ['WHEN id=%s THEN %s' % pair for pair in params])
            id_list = ', '.join(['%s' % pair[0] for pair in params])
            sql = 'UPDATE api_task SET task_attempt_id= CASE %s END WHERE id IN (%s)'\
                                                        % (case_statement, id_list)
            with django.db.connection.cursor() as cursor:
                cursor.execute(sql) 

def db_execute_hook(execute, sql, params, many, context):
    """
    Database instrumentation hook for Django 2.0+
    https://docs.djangoproject.com/en/2.0/topics/db/instrumentation/
    """
    if many:
        operation = "SQL/Many"
    else:
        operation = "SQL/Query"

    if sql is not None:
        tracked_request = TrackedRequest.instance()
        span = tracked_request.start_span(operation=operation)
        span.tag("db.statement", sql)

    try:
        return execute(sql, params, many, context)
    finally:
        if sql is not None:
            tracked_request.stop_span()
            if tracked_request.n_plus_one_tracker.should_capture_backtrace(
                sql=sql,
                duration=span.duration(),
                count=(1 if not many else len(params)),
            ):
                span.capture_backtrace() 

def settings_to_cmd_args(settings_dict):
    """
    Copied from django 1.8 MySQL backend DatabaseClient - where the runshell
    commandline creation has been extracted and made callable like so.
    """
    args = ["mysql"]
    db = settings_dict["OPTIONS"].get("db", settings_dict["NAME"])
    user = settings_dict["OPTIONS"].get("user", settings_dict["USER"])
    passwd = settings_dict["OPTIONS"].get("passwd", settings_dict["PASSWORD"])
    host = settings_dict["OPTIONS"].get("host", settings_dict["HOST"])
    port = settings_dict["OPTIONS"].get("port", settings_dict["PORT"])
    cert = settings_dict["OPTIONS"].get("ssl", {}).get("ca")
    defaults_file = settings_dict["OPTIONS"].get("read_default_file")
    # Seems to be no good way to set sql_mode with CLI.

    if defaults_file:
        args += ["--defaults-file=%s" % defaults_file]
    if user:
        args += ["--user=%s" % user]
    if passwd:
        args += ["--password=%s" % passwd]
    if host:
        if "/" in host:
            args += ["--socket=%s" % host]
        else:
            args += ["--host=%s" % host]
    if port:
        args += ["--port=%s" % port]
    if cert:
        args += ["--ssl-ca=%s" % cert]
    if db:
        args += [db]
    return args 

def get_last_logevent(self):
        """Set the last event id"""
        connsuccess = False
        dbtries = 20
        while not connsuccess:
            try:
                e = LogEvent.objects.last()
                connsuccess = True
            except Exception as err:
                if dbtries == 20:
                    db.connections.close_all()
                    msg = 'LogESP sentry thread for limit rule ' + \
                            self.rule.name + \
                            ' got a db error. Resetting conn. ' + \
                            'Error: ' + str(err)
                    syslog.syslog(syslog.LOG_ERR, msg)
                elif dbtries == 0:
                    dbtries = 20
                    msg = 'LogESP sentry thread for limit rule ' + \
                            self.rule.name + \
                            ' got 20 db errors. Error: ' + str(err)
                    syslog.syslog(syslog.LOG_ERR, msg)
                    exit(1)
                else:
                    sleep(0.2)
                dbtries -= 1
        if e:
            self.lasteventid = e.id
        else:
            self.lasteventid = 0 

def get_last_ruleevent(self):
        """Set the last event id"""
        connsuccess = False
        dbtries = 20
        while not connsuccess:
            try:
                self.lasteventid = RuleEvent.objects.last().id
                connsuccess = True
            except AttributeError:
                self.lasteventid = 0
                connsuccess = True
            except Exception as err:
                if dbtries in [5, 10, 15, 20] or dbtries == 10:
                    db.connections.close_all()
                    msg = 'LogESP sentry thread for limit rule ' + \
                            self.rule.name + \
                            ' got a db error. Resetting conn. ' + \
                            'Error: ' + str(err)
                    syslog.syslog(syslog.LOG_ERR, msg)
                    dbtries -= 1
                elif dbtries == 0:
                    dbtries = 20
                    msg = 'LogESP sentry thread for limit rule ' + \
                            self.rule.name + \
                            ' got 20 db errors. Exiting. Error: ' + str(err)
                    syslog.syslog(syslog.LOG_ERR, msg)
                    exit(1)
                else:
                    sleep(0.2)
                dbtries -= 1 

def get_last_logevent(self):
        """Set the last event id"""
        connsuccess = False
        dbtries = 20
        while not connsuccess:
            try:
                self.lasteventid = LogEvent.objects.last().id
                connsuccess = True
            except AttributeError:
                self.lasteventid = 0
                connsuccess = True
            except Exception as err:
                if dbtries in [5, 10, 15, 20] or dbtries == 10:
                    db.connections.close_all()
                    msg = 'LogESP sentry thread for limit rule ' + \
                            self.rule.name + \
                            ' got a db error. Resetting conn. ' + \
                            'Error: ' + str(err)
                    syslog.syslog(syslog.LOG_ERR, msg)
                    dbtries -= 1
                elif dbtries == 0:
                    dbtries = 20
                    msg = 'LogESP sentry thread for limit rule ' + \
                            self.rule.name + \
                            ' got 20 db errors. Exiting. Error: ' + str(err)
                    syslog.syslog(syslog.LOG_ERR, msg)
                    exit(1)
                else:
                    sleep(0.2)
                dbtries -= 1