martsberger / django-pivot Goto Github PK

View Code? Open in Web Editor NEW

206.0 206.0 16.0 101 KB

A module for pivoting Django Querysets

License: MIT License

Python 100.00%

django-pivot's People

Contributors

Stargazers

Watchers

Forkers

jangeador ashraful-ic rafal-jaworski spark25 batisteo vpineda7 denhartog osvill pypi-buildability-project aficionado thethirdbearsolutions nata-water 0therguys htukiain yosephbernandus shanechin

django-pivot's Issues

Aggregation on annotated field fails if queryset ordered by other values + set default value

Hey Marts,

First of all thanks for the package, it simple but very useful :)

I have a couple of comments/questions:

1. Aggregation on annotated field
I have noticed that you do not reorder the QuerySet before aggregation, which causes problems when aggregating on an annotated field, if it is already ordered by another field.

For example, consider the model:

class Blog(models.Model):
    date = models.DateField()
    category = models.CharField(max_length=50)
    content = models.TextField(default='')
    
    class Meta:
        order_by = ['date']

If I pivot on an annotated field like:

qs = Blog.objects.annotate(period=Trunc('date', 'year'))
pivot(qs, 'period', 'category', 'content', Count)

Aggregation does not work because the 'date' field must be part of the query's group by for the ordering to work.

Reordering fixes the problem, so this will work:

qs = Blog.objects.annotate(period=Trunc('date', 'year')).order_by('period')
pivot(qs, 'period', 'category', 'content', Count)

I think it would make sense to implement this in the package.

2. Default value
The default column value is None atm. Why not using 0? Alternatively could one set the default value at module level? Something like:

def _get_annotations(column, column_values, data, aggregation):
    value = data if hasattr(data, 'resolve_expression') else F(data)
    return {
        display_value: aggregation(Case(When(Q(**{column: column_value}), then=value), default=DEFAULT_VALUE))
        for column_value, display_value in column_values
    }

3. Cumulative aggregation
It would be fairly easy to allow for cumulative aggregation as well, which can be useful. It can be done using the window function (+distinct):

window = Window(aggregator(data), order_by=row)

It does not work on sqlite though...
I'd be happy to have a look at it if you think it could be useful.

Possibility to attach queryset to number

Could it be possible to keep a queryset or some sort of way that we could make the pivot number be attached to the records that produced the number to be able to make the number a list that would allow to drill down to the records. I was thinking of maybe adding the data or queryset as an item to each records dict, or whatever other way may be more practical.

Currently I am generating drilldown lists using django-filter and get parameters like this:

def build_url(*args, **kwargs):
    params = kwargs.pop('params', {})
    url = reverse(*args, **kwargs)
    if not params: return url

    qdict = QueryDict('', mutable=True)
    for k, v in params.items():
        if type(v) is list:
            qdict.setlist(k, v)
        else:
            qdict[k] = v

    return url + '?' + qdict.urlencode()

def render_as_links(self, request, record):
        params = {'performance': record['performance'], 'grade': record['grade'],
                  **self.request.GET}
        href = build_url('afb:table_proficiency_model_detail', params=params)
        return format_html(
            '<a href={href}>{text}</a>',
            href=href,
            text=value
        )

But it would be a lot easier if the queryset was already attached to the record. I hope this is not too out of scope.

Thanks in advance.

TypeError: annotate() keywords must be strings + FIX

First of all, thanks for the awesome package! Its really usefull!

I found a little bug in the pivot.py line 52.
When i run pivot with the following parameters (models below):

pivot(Participant.objects.all(), 'language__name','languagelevel__level','id',Count)

I get the following error:

File "/django_pivot/pivot.py", line 41, in pivot
    values_list = queryset.values(*values).annotate(**annotations)
TypeError: annotate() keywords must be strings

This can be fixed by explicitly casting strings as keys for the returned dictionary:

def _get_annotations(column, column_values, data, aggregation, display_transform=lambda s: s, default=None):
    value = data if hasattr(data, 'resolve_expression') else F(data)
    return {
        str(display_transform(display_value)): Coalesce(aggregation(Case(When(Q(**{column: column_value}), then=value))), default)
        for column_value, display_value in column_values
    }

Should this be fixed in the code base or did I do something else wrong?

Best,

Cornelius

Relevant Models

class Participant(models.Model, ParticipantMailContextMixin):
    language = models.ManyToManyField(Language, through='LanguageLevel')

class LanguageLevel (models.Model):
    language = models.ForeignKey(Language, on_delete=models.CASCADE, verbose_name=_('Language'))
    participant = models.ForeignKey(Participant, on_delete=models.CASCADE)
    level = models.IntegerField(choices=LANGUAGELEVELCHOICES, blank=True, null=True, verbose_name=_('Level'))

class Language (models.Model):
    name = models.CharField(max_length=30, blank=True, null=True, verbose_name=_('Language'))

New version

Please release new version as master has this error fixed.
from django.utils.encoding import force_text

Causes error with Django 4.0 with latest version 1.8.1.

Thanks in advance!

force_text in django_pivot.utils.py is depricated for django >=3.0

First I want to thank you for this great package! I'm using it intensively!

As described in the title I'm encountering warnings from the imported method force_text from django.utils.encoding in django_pivot.utils.py in line 20.

It recommends to use the new method force_str introduced in django v3 in django.utils.encoding, see here .

Maybe a version check of django in django_pivot.utils.py in line 20 could help.

Total column

Hello,

Is it possible to have Total column creation?

thanks!

Django Rest FrameWorks

is it possible to serialize the result in DRF?

Blocker - No readme.srt found in package

Installing V1.8.0 of django-python. Package references readme.srt but is not part of package so installation is blocked.

[root@teardown ~]# pip install django-pivot
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. A future version of pip will drop support for Python 2.7. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support
Collecting django-pivot
Using cached django-pivot-1.8.0.tar.gz (13 kB)
ERROR: Command errored out with exit status 1:
command: /usr/bin/python2 -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-vkfQ21/django-pivot/setup.py'"'"'; file='"'"'/tmp/pip-install-vkfQ21/django-pivot/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(file);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, file, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-install-vkfQ21/django-pivot/pip-egg-info
cwd: /tmp/pip-install-vkfQ21/django-pivot/
Complete output (7 lines):
Traceback (most recent call last):
File "", line 1, in
File "/tmp/pip-install-vkfQ21/django-pivot/setup.py", line 13, in
long_description=read_file('README.rst'),
File "/tmp/pip-install-vkfQ21/django-pivot/setup.py", line 5, in read_file
with open(name) as fd:
IOError: [Errno 2] No such file or directory: 'README.rst'
----------------------------------------
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

get_FOO_display for ChoiceFields

Is there any way to make the field display the get_foo_display value for choices fields? Right now I am having to do this:

qs = self.get_queryset().annotate(
            performance_level=Case(
                When(performance='1', then=Value('MP')),
                When(performance='2', then=Value('PP')),
                When(performance='3', then=Value('P')),
                When(performance='4', then=Value('HP')),
                default=Value('blank'),
                output_field=CharField(),
            ))

where the values MP, PP, P, and HP already live in the Choices tuple.

Queryset and pivot table ordering

From issue #5

I have noticed that you do not reorder the QuerySet before aggregation, which causes problems when aggregating on an annotated field, if it is already ordered by another field.

For example, consider the model:

class Blog(models.Model):
    date = models.DateField()
    category = models.CharField(max_length=50)
    content = models.TextField(default='')
    
    class Meta:
        order_by = ['date']

If I pivot on an annotated field like:

qs = Blog.objects.annotate(period=Trunc('date', 'year'))
pivot(qs, 'period', 'category', 'content', Count)

Aggregation does not work because the 'date' field must be part of the query's group by for the ordering to work.

Reordering fixes the problem, so this will work:

qs = Blog.objects.annotate(period=Trunc('date', 'year')).order_by('period')
pivot(qs, 'period', 'category', 'content', Count)

By default we will remove ordering from querysets passed to pivot, which is useful for any case where there is default ordering on the model. In the case that the client wants to specify an ordering for the resulting pivot table, we provide an optional ordering parameter to the pivot function.

Example to render pivot table in template

As per title, what is the best/easiest way to render the columns of the pivot table dynamically?

Feature Request Discussion: a list of `Case` in place of `row` or `column`

Do you think it would be possible to specify a dict of when/then options in place of row/column. Use case below:

Data

customer_name	sales_rep	stage	is_archived
Cust1	Alice	Negotiating	False
Cust2	Bob	Closed	True

Result

sales_rep	Negotiating	Closed	Archived
Alice	1	0	0
Bob	0	0	1

So perhaps something like:

cases = [
    {fieldname: 'Negotiating', when: Q(stage='negotiating') & Q(is_archived=False), then: 1},
    {fieldname: 'Closed', ...},
    {fieldname: 'Archived', ...},
]
pivot_table = pivot(Lead, 'sales_rep', cases, 'id', aggregation=Count)

What do you think?

P.S. Thanks for this package! Was looking for something lightweight for a lightweight use case, and this is great.

is it possible to remove doublescore for lookup columns?

for example

stock = pivot(Transaction, 'product__name',
                  'location__name', 'quantity', default=0)

I want response to be {'product_name' : 'Shirt'} instead of { 'product__name' : 'Shirt' }

Choice Field on row display not working

The fix for 1.6 that you described in #1 is not working. Here is a failing test showing the behavior. It says that the field (get_FOO_display()) is not found.

    def test_choice_field_on_row(self):
        pt = pivot(ShirtSales.objects.all(), 'gender', 'style', 'units')
        self.assertIsNotNone(pt)

I wish I knew how to fix it, but my knowledge of how your module works is limited. Otherwise I would be happy to send you a PR.

And this is the error itself when I run the test above:

$ python runtests.py --settings=django_pivot.tests.test_postgres_settings
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
E........
======================================================================
ERROR: test_choice_field_on_row (django_pivot.tests.pivot.test.Tests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/delio/Projects/django-pivot/django_pivot/tests/pivot/test.py", line 104, in test_choice_field_on_row
    pt = pivot(ShirtSales.objects.all(), 'gender', 'style', 'units')
  File "/Users/delio/Projects/django-pivot/django_pivot/pivot.py", line 30, in pivot
    queryset = queryset.annotate(row_display=row_display)
  File "/Users/delio/.virtualenvs/django_pivot/lib/python3.6/site-packages/django/db/models/query.py", line 945, in annotate
    clone.query.add_annotation(annotation, alias, is_summary=False)
  File "/Users/delio/.virtualenvs/django_pivot/lib/python3.6/site-packages/django/db/models/sql/query.py", line 973, in add_annotation
    summarize=is_summary)
  File "/Users/delio/.virtualenvs/django_pivot/lib/python3.6/site-packages/django/db/models/expressions.py", line 865, in resolve_expression
    c.cases[pos] = case.resolve_expression(query, allow_joins, reuse, summarize, for_save)
  File "/Users/delio/.virtualenvs/django_pivot/lib/python3.6/site-packages/django/db/models/expressions.py", line 799, in resolve_expression
    c.result = c.result.resolve_expression(query, allow_joins, reuse, summarize, for_save)
  File "/Users/delio/.virtualenvs/django_pivot/lib/python3.6/site-packages/django/db/models/expressions.py", line 471, in resolve_expression
    return query.resolve_ref(self.name, allow_joins, reuse, summarize)
  File "/Users/delio/.virtualenvs/django_pivot/lib/python3.6/site-packages/django/db/models/sql/query.py", line 1477, in resolve_ref
    self.get_initial_alias(), reuse)
  File "/Users/delio/.virtualenvs/django_pivot/lib/python3.6/site-packages/django/db/models/sql/query.py", line 1417, in setup_joins
    names, opts, allow_many, fail_on_missing=True)
  File "/Users/delio/.virtualenvs/django_pivot/lib/python3.6/site-packages/django/db/models/sql/query.py", line 1352, in names_to_path
    "Choices are: %s" % (name, ", ".join(available)))
django.core.exceptions.FieldError: Cannot resolve keyword 'Boy' into field. Choices are: gender, id, price, shipped, store, store_id, style, units

----------------------------------------------------------------------
Ran 9 tests in 1.110s

FAILED (errors=1)
Destroying test database for alias 'default'...

row_range not working

I’m trying to pivot my data:

trackings = pivot(trackings, 'project', 'date', ‘time’)
for record in trackings:
    print(record)

Result:

{'project': 1, '2022-03-28': datetime.timedelta(seconds=14400), '2022-03-29': None, '2022-03-30': None}
{'project': 2, '2022-03-28': datetime.timedelta(seconds=90), '2022-03-29': None, '2022-03-30': datetime.timedelta(seconds=3900)}
{'project': 3, '2022-03-28': None, '2022-03-29': datetime.timedelta(seconds=21600), '2022-03-30': None}

Now with row_range:

row_range = [date(2022, 3, 28) + timedelta(days) for days in range(8)]
trackings = pivot(trackings, 'project', 'date', 'time', row_range=row_range)

for record in trackings:
    print(record)

Result:

{'project': datetime.date(2022, 3, 28), datetime.date(2022, 3, 28): None, datetime.date(2022, 3, 29): None, datetime.date(2022, 3, 30): None}
{'project': datetime.date(2022, 3, 29), datetime.date(2022, 3, 28): None, datetime.date(2022, 3, 29): None, datetime.date(2022, 3, 30): None}
{'project': datetime.date(2022, 3, 30), datetime.date(2022, 3, 28): None, datetime.date(2022, 3, 29): None, datetime.date(2022, 3, 30): None}
{'project': datetime.date(2022, 3, 31), datetime.date(2022, 3, 28): None, datetime.date(2022, 3, 29): None, datetime.date(2022, 3, 30): None}
{'project': datetime.date(2022, 4, 1), datetime.date(2022, 3, 28): None, datetime.date(2022, 3, 29): None, datetime.date(2022, 3, 30): None}
{'project': datetime.date(2022, 4, 2), datetime.date(2022, 3, 28): None, datetime.date(2022, 3, 29): None, datetime.date(2022, 3, 30): None}
{'project': datetime.date(2022, 4, 3), datetime.date(2022, 3, 28): None, datetime.date(2022, 3, 29): None, datetime.date(2022, 3, 30): None}
{'project': datetime.date(2022, 4, 4), datetime.date(2022, 3, 28): None, datetime.date(2022, 3, 29): None, datetime.date(2022, 3, 30): None}

What am I doing wrong? Seems row_range is not working

Pivot on Union Queryset : Calling QuerySet.distinct() after union() is not supported.

Hi there,

I'm trying to get some dashboarded datas on some models, and even between models.

issue description :
When I union 2 queryset of models objects, and then try to pivot with pivot_table, I've got the issue :

django.db.utils.NotSupportedError: Calling QuerySet.distinct() after union() is not supported.

Step to reproduce :

define a model
add some new instances
:
a=models.MYModel.objects.filter(pk=1)
b=models.MYModel.objects.filter(pk=2)
c=a.union(b)
pivot_table = pivot(c, 'field1', 'field2', 'field3')

=> raise the error above

is there anything I missed ?

infos :
django v4.1.2
python v3.9.2
django-pivot v1.9.0

Pivot returns List instead of ValuesQueryset

Hello!
I the revision 1.9.0 Pivot returned ValuesQueryset, but in the revision 1.10.0 it returns List:

#rev 1.9.0
..
values_list = queryset.values(*values).annotate(**annotations)
..

#rev 1.10.0
..
values_list = [_swap_dictionary_keys(result, column_alias_map)
                   for result in queryset.values(*values).annotate(**annotations)]
..

Is it posible to return Queryset again?

unexpected type conversion

Hi,
I am using this library to pivot my tables, but without any sort of data aggregation. I noticed that when I use a CharField as the 4th parameter (data) I get some unexpected type conversion in my results. Take the following sample data. This is what my DB looks like:

id	label	value	category_id	sku_id
1	weight	40lbs	1	5
3	height	10in	1	5
5	power cord	3 prong	3	5
2	weight	20lbs	1	6
4	weight	10lbs	1	8

When I run this command:
pivot_table = pivot(SkuData, 'sku_id', 'label', 'value')

The output seems to be forcing all of my strings into a double:

{'sku': 5, 'height': 10.0, 'power cord': 3.0, 'weight': 40.0},
{'sku': 6, 'height': None, 'power cord': None, 'weight': 20.0},
{'sku': 8, 'height': None, 'power cord': None, 'weight': 10.0}

The issue is especially visible for the value of "power cord." which is changed from the string "3 prong" to the double 3.0.

Is this intended functionality?

Add Support for multiples data fields

I have used the library and It just works fine with one data field ("mark1" in my case). However It fail for multiples data fields. For examples I would like to display mark1, mark2 and mark3 for each student and for each course. This is my code:

from django.db.models import Sum, F

# Defining the queryset (student_marks) and other parameters

rows = "enrolled_student__student__user__username"
column = "teaching_course__course__name"

# Creating separate aggregation expressions for mark1, mark2, and mark3
data_mark1 = Sum(F('mark1'))
data_mark2 = Sum(F('mark2'))
data_mark3 = Sum(F('mark3'))

# Use the pivot function with multiple data columns
result = pivot(
    queryset=student_marks,
    rows=rows,
    column=column, 
    data={
        'mark1': data_mark1,
        'mark2': data_mark2,
        'mark3': data_mark3,
    },)

Column values that contain spaces don't work (and doc might improve a smidgeon)

I really like that's been done here.

I gave it a quick spin though on some test models and immediately had it crash. Turns out that is the column values have spaces in them if bombs with:

ValueError: Column aliases cannot contain whitespace characters, quotation marks, semicolons, or SQL comments.

which happens when you try to apply the annotations.

Row values can tolerate spaces.

The small improvement I'd make to the doc is in the basic intro:

The pivot function

Pivot tables are generated by the pivot function, which takes a Model and 3 attribute names, to make a pivot table like the example above:

Where I'd simply describe what each argument actually does. I'm sort of inferring at present so would not call thus definitive but on suggestion:

The pivot function

Pivot tables are generated by the pivot function, which takes a Model and 3 attribute names (the first provides values for the row keys, the second values for the column keys and the third the values in the table), to make a pivot table like the example above:

I mean it is inferred, I'm a fan of more explicit statement.

Allow default values if no records are found

This point 2 from issue #5

The default column value is None atm. Why not using 0?

We will add a default value as a keyword argument to the pivot function.

ModuleNotFoundError: No module named 'django_pivot'

After install the module i set the app in settings, but i become an error:
ModuleNotFoundError: No module named 'django_pivot'

Missing files in sdist

It appears that the manifest is missing at least one file necessary to build
from the sdist for version 1.8.1. You're in good company, about 5% of other
projects updated in the last year are also missing files.

+ /tmp/venv/bin/pip3 wheel --no-binary django-pivot -w /tmp/ext django-pivot==1.8.1
Looking in indexes: http://10.10.0.139:9191/root/pypi/+simple/
Collecting django-pivot==1.8.1
  Downloading http://10.10.0.139:9191/root/pypi/%2Bf/718/4d3e3f5e96003/django-pivot-1.8.1.tar.gz (14 kB)
    ERROR: Command errored out with exit status 1:
     command: /tmp/venv/bin/python3 -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-wheel-n9vzbwx0/django-pivot/setup.py'"'"'; __file__='"'"'/tmp/pip-wheel-n9vzbwx0/django-pivot/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-wheel-n9vzbwx0/django-pivot/pip-egg-info
         cwd: /tmp/pip-wheel-n9vzbwx0/django-pivot/
    Complete output (7 lines):
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-wheel-n9vzbwx0/django-pivot/setup.py", line 13, in <module>
        long_description=read_file('Readme.rst'),
      File "/tmp/pip-wheel-n9vzbwx0/django-pivot/setup.py", line 5, in read_file
        with open(name) as fd:
    FileNotFoundError: [Errno 2] No such file or directory: 'Readme.rst'
    ----------------------------------------
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

Cumulative aggregation

It might be useful to some people to have the aggregation be cumulative.

From issue #5 (PJCampi)
It would be fairly easy to allow for cumulative aggregation as well, which can be useful. It can be done using the window function (+distinct):

window = Window(aggregator(data), order_by=row)

This would be a feature or option in addition to the current aggregation.

Bug with binary fields

Hi! I have detected what I think is a bug with this library when using binary fields on a django model. The problem is that answers aren't shown right, just as 0.0 values
Code to reproduce this bug:
Models:

from django.db import models
class Student(models.Model):
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name


class Subject(models.Model):
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name


class Grades(models.Model):
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    subject = models.ForeignKey(Subject, on_delete=models.CASCADE)
    marks = models.BinaryField()

    def __str__(self):
        return f"{self.student}, {self.subject}, {self.marks}"

Code to fill models with django-shell

from core.models import *
from django_pivot.pivot import pivot

students = ['Jacob', 'Ameliee']
subjects = ['Math', 'Science']
marks = [[b'Bilbao', b'Mariano'], [b'Murcia', b'Concha']]

for i, student_name in enumerate(students):
	st = Student(name=student_name)
	st.save()
	for j, subject_name in enumerate(subjects):
		su = Subject(name=subject_name)
		su.save()
		g = Grades(student=st, subject=su, marks=marks[i][j])
		g.save()

pivot(Grades, 'student__name', 'subject__name', 'marks')

When you change Grades.marks to a SmallIntegerField (in example) it works fine. Could you help me?