In this article, we’ll look at how we can extract data from & insert data into our models. Below, we have a simple select all query. It will return an unfiltered dataset from the skills model. This should be used with care, expecially if your dataset is quite large.
skill_list = skills.objects
Next, we may want to apply filters to the data we extract, keeping only the data relevant to us. In the below, we have two examples. Example 1, simply filters based on the userID and example 2 filters based on userID and the status field.
#single filter
profile = user_profile.objects.filter(creator=request.user)
#multiple filters
skill_list = skills.objects.filter(creator=request.user, status='open')
Now, what if we wanted to return just a single number, rather than an iteritable dataset. Well, in this scenario we can use the aggregation function, as below.
pts = skills.objects.filter(creator=request.user, status='closed').aggregate(total=Sum(Cast('points', output_field=IntegerField())))['total']
We may also want to include a new field in our dataset. In this example, I have casted the milestone_points field to an integer as a new field (called milestone_points_int).
goals = milestones.objects.filter(creator=request.user).extra({'milestone_points_int': "CAST(milestone_points as int)"}).order_by('milestone_points_int')
The final bit of extraction logic – we may want to write our own SQL query. With this, we need to be careful, as we could open ourselves up to the risk of SQL injection. As you can see from the Django website, there are a couple of big no-no’s.
Here is an example of how I have queried my dataset using the raw function, which should conform to the requirements stated on the Django website.
totals = skills.objects.raw('''
SELECT *, closed_count/total_count*100 as closed_pct, round((closed_points/total_points)*100,0) as points_pct FROM (
SELECT x.id, total_points, total_count, coalesce(closed_points,0) as closed_points, coalesce(closed_count,0) as closed_count FROM (SELECT category as id, sum(cast(points as int)) as total_points, count(*) as total_count
FROM voxi_skills
WHERE creator = %s
group by category)x left join
(SELECT category as id, sum(cast(points as int)) as closed_points, count(*) as closed_count
FROM voxi_skills
WHERE creator = %s and status != 'open'
group by category)y on x.id = y.id)p
''', [request.user.username, request.user.username])
These are just a few examples of the ways we can interact with our data. In future articles we will look further into this topic.
