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

Transcript

2. 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()
3. #### 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()
4. #### 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@provider.com' ​Author.email == 'email@provider.com') ) ) #### 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))
5. #### 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))
6. 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={})
7. #### 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: ?
8. #### 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())