Share
Embed
Django ORM vs SQLAlchemy (queries)
Este é uma anotação pessoal para me ajudar a fazer consultas utilizando Django ORM ou SQLAlchemy.
Qualquer contribuição será bem vinda!
Live Stream para o Pyjamas 2019
https://www.youtube.com/watch?v=OhdgBoMlvpQ
Roger Camargo
December 14, 2019
Transcript
9 slides
-
MODELS class Blog(models.Model): name = models.CharField(max_length=100) tagline = models.TextField() class
Author(models.Model): name = models.CharField(max_length=200) email = models.EmailField() class Entry(models.Model): blog = models.ForeignKey(Blog, on_delete=models.CASCADE) headline = models.CharField(max_length=255) body_text = models.TextField() pub_date = models.DateField() mod_date = models.DateField() authors = models.ManyToManyField(Author) n_comments = models.IntegerField() n_pingbacks = models.IntegerField() rating = models.IntegerField() -
#### 1 # Django # SQL Alchemy Entry.objects.all() Entry.query.all() ####
2 # Django # SQL Alchemy --> better idea? Entry.objects.filter(pub_date__year=2019) Entry.query.filter( Entry.pub_date >= datetime.date(2019, 1, 1), Entry.pub_date <= datetime.date(2019, 12, 31)).all() # Get records from Monday only (1=Sunday) Entry.objects.filter(pub_date__week_day=2) # TODO: ? # Get records made at 2:30pm Entry.objects.filter(pub_date__time=datetime.time(14, 30)) # TODO: ? #### 3 # Starts with “What” and were published after January 1 # Django # SQL Alchemy ----> how to be >= 2019, 1, 1? Entry.objects.filter( Entry.query.filter( headline__startswith='What' Entry.headline.like('What%') ).filter( ).filter( pub_date__gte=datetime.date(2019, 1, 1) Entry.pub_date >= datetime.date(2019, 1, 1) ) ).all() #### 4 # Starts with “What” and were published between January 1, and the current day # Django # SQL Alchemy Entry.objects.filter( Entry.query.filter( headline__startswith='What' Entry.headline.like('What%') ).exclude( .filter( pub_date__gte=datetime.date.today() ~(Entry.pub_date >= datetime.date.today()) ).filter( ).filter( pub_date__gte=datetime.date(2019, 1, 1) Entry.pub_date >= datetime.date(2019, 1, 1) ) ).all() -
#### 5 # Contains ‘food’ case insensitive in the body
# Django # SQL Alchemy Entry.objects.filter( Entry.query.filter( body_text__icontains='food' Entry.body_text.ilike('%food%') ) ) #### 6 # Find author by email # Django # SQL Alchemy Author.objects.filter( Author.query.filter( email__exact='[email protected]' Author.email == '[email protected]') ) ) #### 7 # Find name case insensitive # Django # SQL Alchemy Author.objects.filter( from sqlalchemy import func name__iexact='Roger Waters' Author.query.filter( ) func.lower(Author.name) == func.lower('Roger Waters') ) # OR Author.name.ilike('Roger Waters') #### 8 # Order by # Django # SQL Alchemy Author.objects.all().order_by('name') Author.query.order_by(Author.name).all() Author.objects.all().order_by('-name') Author.query.order_by(Author.name.desc()).all() #### 9 # Gets the most recent blog posts Blog.objects.latest('created') # TODO: ? #### 10 # Filter over a relation field # Django # SQL Alchemy Blog.objects.filter(entry__headline__contains='Python') Blog.query.join(Entry).filter(Entry.headline.like('%Python%')) Entry.objects.filter(pub_date__isnull=True) Entry.query.filter(pub_date == None) #OR Entry.query.filter(pub_date.is_(None)) -
#### 11 # Comparing fields # Django # SQL Alchemy
from django.db.models import F Entry.objects.filter(n_comments__gt=F('n_pingbacks')) Entry.query.filter(Entry.n_comments > Entry.n_pingbacks) #### 12 # SQL in # Django # SQL Alchemy Blog.objects.filter(pk__in=[1, 4, 7]) Blog.query.filter(Blog.pk.in_([1, 4, 7])) #### 13 # WHERE question LIKE 'Who%' OR question LIKE 'What%' # Django # SQL Alchemy from django.db.models import Q Entry.objects.filter(Q(headline__startswith='Who') | Q(headline__startswith='What')) Entry.query.filter( (Entry.headline.like('Who%')) | (Entry.headline.like('What%')) ) #### 14 # WHERE question LIKE 'Who%' OR question LIKE 'What%' # Django # SQL Alchemy Entry.objects.filter(pub_date__range=[dt_start, dt_end]) Entry.query.filter((pub_date >= dt_start) & (pub_date <= dt_end)) -
MODELS class Country(models.Model): name = models.CharField(max_length=32) class City(models.Model): name =
models.CharField(max_length=32) country = models.ForeignKey(Country) population = models.PositiveIntegerField() metadata = jsonfield.JSONField(default={}) -
#### 15 # Django # SQL Alchemy from django.db.models import
Sum from sqlalchemy import func City.objects.aggregate(Sum('population')) session.query(func.sum(City.population)).first() {'population__sum': 970880224} [(970880224,)] #### 16 # Django # SQL Alchemy from django.db.models import Sum from sqlalchemy import func City.objects.values('country__name').annotate(Sum('population')) session.query( func.sum(City.population), Country.name ).join(Country).group_by(Country.name).all() [ [ {'country__name': u'Angola', 'population__sum': 6542944}, (6542944, 'Angola'), {'country__name': u'Argentina', 'population__sum': 13074000}, (13074000, 'Argentina'), {'country__name': u'Australia', 'population__sum': 9650500}, (9650500, 'Australia'), ... ... ] ] #### 17 # Django # SQL Alchemy Entry.objects.count() Entry.query.count() 3600 3600 Entry.objects.distinct().count() Entry.query.distinct().count() 3600 3600 Entry.objects.values('blog').count() session.query(Entry.blog).group_by(Entry.blog).count() 2 2 #### 18 # Django # SQL Alchemy # JSONB Filters City.objects.filter(country__name='Brazil').extra( session.query(City).filter( where=["metadata->'size'->'terrain' is not null"] (City.name == 'Brazil') & (City.metadata[('size', 'terrain')].astext != None) ) ) from django.db.models.expressions import RawSQL City.objects.all().order_by(RawSQL("metadata->>%s", ("area",) )) TODO: ? -
#### 19 # Django # SQL Alchemy # Order by
a string field as Integer (avoid 999 after 1000) City.objects.all().extra( session.query(City).all().order_by(cast(City.identifier, Integer)) select={'identifier_int': 'CAST(myapp_city.identifier AS INTEGER)'} ).order_by('identifier_int') #### 19 # SQL Alchemy results_sub = db.session.query( QueueItem.image_id, db.func.count(QueueItem.result_at).label('total') ).group_by( QueueItem.image_id ).filter( QueueItem.result_at.isnot(None) ).order_by( db.func.count(QueueItem.result_at) ).subquery('results_sub') results = db.session.query( results_sub.c.total, db.func.count(results_sub.c.image_id) ).group_by( results_sub.c.total ).order_by(db.func.count(results_sub.c.total).desc())