Transform data with django ORM
0
I have the following model:
class Entry(models.Model):
name = models.CharField(max_length=100)
date = models.DateTimeField(default=timezone.now)
material = models.CharField(max_length=20])
price = models.FloatField(null=True)
With data as follows:
john, 2011-01-21, GOLD, 10.00
blair, 2011-01-21, GOLD, 20.00
peter, 2011-01-21, SILVER, 21.00
peter, 2011-01-22, GOLD, 11.00
john, 2011-01-22, SILVER, 12.00
I would like to:
- aggregate (addition) by material per date
- produce an entry per day, with all the available materials (not known in advance)
As follows:
DATE GOLD SILVER
2011-01-21 30.00 21.00
2011-01-22 11.00 12.00
(dates not present in the input data will not get an output row)
How can this be achieved in SQL? With Django ORM?
Note: my database backend is Postgres
sql django orm
asked Nov 14 '18 at 14:57
dangonfastdangonfast
13.4k25122228
add a comment |
0
I have the following model:
class Entry(models.Model):
name = models.CharField(max_length=100)
date = models.DateTimeField(default=timezone.now)
material = models.CharField(max_length=20])
price = models.FloatField(null=True)
With data as follows:
john, 2011-01-21, GOLD, 10.00
blair, 2011-01-21, GOLD, 20.00
peter, 2011-01-21, SILVER, 21.00
peter, 2011-01-22, GOLD, 11.00
john, 2011-01-22, SILVER, 12.00
I would like to:
- aggregate (addition) by material per date
- produce an entry per day, with all the available materials (not known in advance)
As follows:
DATE GOLD SILVER
2011-01-21 30.00 21.00
2011-01-22 11.00 12.00
(dates not present in the input data will not get an output row)
How can this be achieved in SQL? With Django ORM?
Note: my database backend is Postgres
sql django orm
asked Nov 14 '18 at 14:57
dangonfastdangonfast
13.4k25122228
Are you concerned about the timezones of the datetime field? It's possible that one entry was for a different date locally than another entry.
– schillingt
Nov 14 '18 at 15:01
@schillingt: no, let's assume timezones are no issue
– dangonfast
Nov 14 '18 at 15:04
add a comment |
0
0
0
I have the following model:
class Entry(models.Model):
name = models.CharField(max_length=100)
date = models.DateTimeField(default=timezone.now)
material = models.CharField(max_length=20])
price = models.FloatField(null=True)
With data as follows:
john, 2011-01-21, GOLD, 10.00
blair, 2011-01-21, GOLD, 20.00
peter, 2011-01-21, SILVER, 21.00
peter, 2011-01-22, GOLD, 11.00
john, 2011-01-22, SILVER, 12.00
I would like to:
- aggregate (addition) by material per date
- produce an entry per day, with all the available materials (not known in advance)
As follows:
DATE GOLD SILVER
2011-01-21 30.00 21.00
2011-01-22 11.00 12.00
(dates not present in the input data will not get an output row)
How can this be achieved in SQL? With Django ORM?
Note: my database backend is Postgres
sql django orm
asked Nov 14 '18 at 14:57
dangonfastdangonfast
13.4k25122228
I have the following model:
class Entry(models.Model):
name = models.CharField(max_length=100)
date = models.DateTimeField(default=timezone.now)
material = models.CharField(max_length=20])
price = models.FloatField(null=True)
With data as follows:
john, 2011-01-21, GOLD, 10.00
blair, 2011-01-21, GOLD, 20.00
peter, 2011-01-21, SILVER, 21.00
peter, 2011-01-22, GOLD, 11.00
john, 2011-01-22, SILVER, 12.00
I would like to:
- aggregate (addition) by material per date
- produce an entry per day, with all the available materials (not known in advance)
As follows:
DATE GOLD SILVER
2011-01-21 30.00 21.00
2011-01-22 11.00 12.00
(dates not present in the input data will not get an output row)
How can this be achieved in SQL? With Django ORM?
Note: my database backend is Postgres
sql django orm
sql django orm
asked Nov 14 '18 at 14:57
dangonfastdangonfast
13.4k25122228
asked Nov 14 '18 at 14:57
dangonfastdangonfast
13.4k25122228
asked Nov 14 '18 at 14:57
dangonfastdangonfast
13.4k25122228
asked Nov 14 '18 at 14:57
dangonfastdangonfast
13.4k25122228
asked Nov 14 '18 at 14:57
dangonfastdangonfast
13.4k25122228
13.4k25122228
Are you concerned about the timezones of the datetime field? It's possible that one entry was for a different date locally than another entry.
– schillingt
Nov 14 '18 at 15:01
@schillingt: no, let's assume timezones are no issue
– dangonfast
Nov 14 '18 at 15:04
add a comment |
Are you concerned about the timezones of the datetime field? It's possible that one entry was for a different date locally than another entry.
– schillingt
Nov 14 '18 at 15:01
@schillingt: no, let's assume timezones are no issue
– dangonfast
Nov 14 '18 at 15:04
Are you concerned about the timezones of the datetime field? It's possible that one entry was for a different date locally than another entry.
– schillingt
Nov 14 '18 at 15:01
Are you concerned about the timezones of the datetime field? It's possible that one entry was for a different date locally than another entry.
– schillingt
Nov 14 '18 at 15:01
@schillingt: no, let's assume timezones are no issue
– dangonfast
Nov 14 '18 at 15:04
@schillingt: no, let's assume timezones are no issue
– dangonfast
Nov 14 '18 at 15:04
add a comment |
1 Answer
1
active
oldest
votes
0
This should do the trick for you. As a heads up, if you need to respect timezones, this becomes a much more difficult thing to do via the ORM.
from django.db.models import DateField
from django.db.models.functions import Cast
Entry.objects.annotate(
d=Cast('date', DateField())
).values('d', 'material').annotate(
total=Sum('price')
)
answered Nov 14 '18 at 15:15
schillingtschillingt
5,61211822
Would this produce entries with three rows,DATE,GOLDandSILVER?
– dangonfast
Nov 14 '18 at 15:33
No. This will return the entries annotated on date and material with the total price. Use.first().__dict__to see how it's exactly working with your data.
– schillingt
Nov 14 '18 at 15:43
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53303053%2ftransform-data-with-django-orm%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
This should do the trick for you. As a heads up, if you need to respect timezones, this becomes a much more difficult thing to do via the ORM.
from django.db.models import DateField
from django.db.models.functions import Cast
Entry.objects.annotate(
d=Cast('date', DateField())
).values('d', 'material').annotate(
total=Sum('price')
)
answered Nov 14 '18 at 15:15
schillingtschillingt
5,61211822
Would this produce entries with three rows,DATE,GOLDandSILVER?
– dangonfast
Nov 14 '18 at 15:33
No. This will return the entries annotated on date and material with the total price. Use.first().__dict__to see how it's exactly working with your data.
– schillingt
Nov 14 '18 at 15:43
add a comment |
0
This should do the trick for you. As a heads up, if you need to respect timezones, this becomes a much more difficult thing to do via the ORM.
from django.db.models import DateField
from django.db.models.functions import Cast
Entry.objects.annotate(
d=Cast('date', DateField())
).values('d', 'material').annotate(
total=Sum('price')
)
answered Nov 14 '18 at 15:15
schillingtschillingt
5,61211822
Would this produce entries with three rows,DATE,GOLDandSILVER?
– dangonfast
Nov 14 '18 at 15:33
No. This will return the entries annotated on date and material with the total price. Use.first().__dict__to see how it's exactly working with your data.
– schillingt
Nov 14 '18 at 15:43
add a comment |
0
0
0
This should do the trick for you. As a heads up, if you need to respect timezones, this becomes a much more difficult thing to do via the ORM.
from django.db.models import DateField
from django.db.models.functions import Cast
Entry.objects.annotate(
d=Cast('date', DateField())
).values('d', 'material').annotate(
total=Sum('price')
)
answered Nov 14 '18 at 15:15
schillingtschillingt
5,61211822
This should do the trick for you. As a heads up, if you need to respect timezones, this becomes a much more difficult thing to do via the ORM.
from django.db.models import DateField
from django.db.models.functions import Cast
Entry.objects.annotate(
d=Cast('date', DateField())
).values('d', 'material').annotate(
total=Sum('price')
)
answered Nov 14 '18 at 15:15
schillingtschillingt
5,61211822
answered Nov 14 '18 at 15:15
schillingtschillingt
5,61211822
answered Nov 14 '18 at 15:15
schillingtschillingt
5,61211822
answered Nov 14 '18 at 15:15
schillingtschillingt
5,61211822
5,61211822
Would this produce entries with three rows,DATE,GOLDandSILVER?
– dangonfast
Nov 14 '18 at 15:33
No. This will return the entries annotated on date and material with the total price. Use.first().__dict__to see how it's exactly working with your data.
– schillingt
Nov 14 '18 at 15:43
add a comment |
Would this produce entries with three rows,DATE,GOLDandSILVER?
– dangonfast
Nov 14 '18 at 15:33
No. This will return the entries annotated on date and material with the total price. Use.first().__dict__to see how it's exactly working with your data.
– schillingt
Nov 14 '18 at 15:43
Would this produce entries with three rows,
DATE, GOLD and SILVER?– dangonfast
Nov 14 '18 at 15:33
Would this produce entries with three rows,
DATE, GOLD and SILVER?– dangonfast
Nov 14 '18 at 15:33
No. This will return the entries annotated on date and material with the total price. Use
.first().__dict__ to see how it's exactly working with your data.– schillingt
Nov 14 '18 at 15:43
No. This will return the entries annotated on date and material with the total price. Use
.first().__dict__ to see how it's exactly working with your data.– schillingt
Nov 14 '18 at 15:43
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53303053%2ftransform-data-with-django-orm%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are you concerned about the timezones of the datetime field? It's possible that one entry was for a different date locally than another entry.
– schillingt
Nov 14 '18 at 15:01
@schillingt: no, let's assume timezones are no issue
– dangonfast
Nov 14 '18 at 15:04