Utföra SQL-frågor i råformat

Django ger dig två sätt att utföra råa SQL-frågor: du kan använda Manager.raw() för att utföra råa frågor och returnera modellinstanser, eller så kan du undvika modellskiktet helt och utföra anpassad SQL direkt.

Utforska ORM innan du använder rå SQL!

Django ORM tillhandahåller många verktyg för att uttrycka frågor utan att skriva rå SQL. Till exempel:

• Den QuerySet API är omfattande.

• Du kan annotera och aggregera med hjälp av många inbyggda databasfunktioner. Utöver dessa kan du skapa custom query expressions.

Innan du använder rå SQL bör du utforska ORM. Fråga på en av supportkanalerna för att se om ORM stöder ditt användningsfall.

Varning

Du bör vara mycket försiktig när du skriver rå SQL. Varje gång du använder det bör du på rätt sätt undkomma alla parametrar som användaren kan kontrollera genom att använda params för att skydda mot SQL-injektionsattacker. Läs gärna mer om SQL injection protection.

Utföra råa förfrågningar

Hanteringsmetoden raw() kan användas för att utföra råa SQL-frågor som returnerar modellinstanser:

Manager.raw(raw_query, params=(), translations=None)

Den här metoden tar en rå SQL-fråga, kör den och returnerar en django.db.models.query.RawQuerySet-instans. Denna RawQuerySet-instans kan itereras över som en normal QuerySet för att tillhandahålla objektinstanser.

Detta illustreras bäst med ett exempel. Anta att du har följande modell:

class Person(models.Model):
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)

Du kan sedan köra anpassad SQL på följande sätt:

>>> for p in Person.objects.raw("SELECT * FROM myapp_person"):
...     print(p)
...
John Smith
Jane Jones

Det här exemplet är inte särskilt spännande - det är exakt samma sak som att köra Person.objects.all(). Men raw() har en massa andra alternativ som gör den mycket kraftfull.

Namn på modelltabeller

Varifrån kom namnet på tabellen Person i det exemplet?

Som standard räknar Django ut ett databastabellnamn genom att ansluta modellens ”appetikett” - det namn du använde i manage.py startapp - till modellens klassnamn, med ett understreck mellan dem. I exemplet har vi antagit att modellen Person lever i en app som heter myapp, så dess tabell skulle vara myapp_person.

Mer information finns i dokumentationen för alternativet db_table, där du också kan ange databasens tabellnamn manuellt.

Varning

Ingen kontroll görs på SQL-satsen som skickas in till .raw(). Django förväntar sig att satsen kommer att returnera en uppsättning rader från databasen, men gör ingenting för att genomdriva det. Om frågan inte returnerar rader, kommer ett (eventuellt kryptiskt) fel att uppstå.

Varning

Om du utför frågor på MySQL, observera att MySQL: s tysta typcoercion kan orsaka oväntade resultat när du blandar typer. Om du ställer en fråga på en kolumn av strängtyp, men med ett heltalsvärde, kommer MySQL att tvinga typerna för alla värden i tabellen till ett heltal innan jämförelsen utförs. Om din tabell till exempel innehåller värdena 'abc', 'def' och du frågar efter WHERE mycolumn=0, kommer båda raderna att matcha. För att förhindra detta måste du utföra korrekt typecasting innan du använder värdet i en fråga.

Mappning av frågefält till modellfält

raw() mappar automatiskt fält i frågan till fält i modellen.

Ordningen på fälten i din fråga spelar ingen roll. Med andra ord fungerar båda följande frågor på samma sätt:

>>> Person.objects.raw("SELECT id, first_name, last_name, birth_date FROM myapp_person")
>>> Person.objects.raw("SELECT last_name, birth_date, first_name, id FROM myapp_person")

Matchningen görs med hjälp av namn. Det innebär att du kan använda SQL:s AS-klausuler för att mappa fält i frågan till modellfält. Så om du hade någon annan tabell som hade Person-data i den, kan du enkelt mappa den till Person-instanser:

>>> Person.objects.raw(
...     """
...     SELECT first AS first_name,
...            last AS last_name,
...            bd AS birth_date,
...            pk AS id,
...     FROM some_other_table
...     """
... )

Så länge namnen stämmer överens kommer modellinstanserna att skapas på rätt sätt.

Alternativt kan du mappa fält i frågan till modellfält med hjälp av argumentet translations till raw(). Detta är en ordbok som mappar namn på fält i frågan till namn på fält i modellen. Till exempel kan ovanstående fråga också skrivas:

>>> name_map = {"first": "first_name", "last": "last_name", "bd": "birth_date", "pk": "id"}
>>> Person.objects.raw("SELECT * FROM some_other_table", translations=name_map)

Indexuppslagningar

raw() stöder indexering, så om du bara behöver det första resultatet kan du skriva:

>>> first_person = Person.objects.raw("SELECT * FROM myapp_person")[0]

Indexeringen och skivningen utförs dock inte på databasnivå. Om du har ett stort antal Person-objekt i din databas är det mer effektivt att begränsa frågan på SQL-nivå:

>>> first_person = Person.objects.raw("SELECT * FROM myapp_person LIMIT 1")[0]

Uppskjutande av modellfält

Fält kan också utelämnas:

>>> people = Person.objects.raw("SELECT id, first_name FROM myapp_person")

Objekten Person som returneras av denna fråga kommer att vara uppskjutna modellinstanser (se defer()). Detta innebär att de fält som utelämnas från frågan kommer att laddas på begäran. Till exempel:

>>> for p in Person.objects.raw("SELECT id, first_name FROM myapp_person"):
...     print(
...         p.first_name,  # This will be retrieved by the original query
...         p.last_name,  # This will be retrieved on demand
...     )
...
John Smith
Jane Jones

Utåt sett ser det ut som om frågan har hämtat både förnamnet och efternamnet. Men i det här exemplet ställdes faktiskt tre frågor. Endast förnamnen hämtades av raw()-frågan - efternamnen hämtades båda på begäran när de skrevs ut.

Det finns bara ett fält som du inte kan utelämna - fältet för primärnyckeln. Django använder primärnyckeln för att identifiera modellinstanser, så den måste alltid inkluderas i en rå fråga. Ett FieldDoesNotExist undantag kommer att uppstå om du glömmer att inkludera den primära nyckeln.

Lägga till anteckningar

Du kan också köra frågor som innehåller fält som inte är definierade i modellen. Vi kan till exempel använda PostgreSQL’s age() function för att få en lista över personer med deras åldrar beräknade av databasen:

>>> people = Person.objects.raw("SELECT *, age(birth_date) AS age FROM myapp_person")
>>> for p in people:
...     print("%s is %s." % (p.first_name, p.age))
...
John is 37.
Jane is 42.
...

Du kan ofta undvika att använda rå SQL för att beräkna annoteringar genom att istället använda ett Func()-uttryck.

Överföra parametrar till raw()

Om du behöver utföra parametriserade frågor kan du använda argumentet params till raw():

>>> lname = "Doe"
>>> Person.objects.raw("SELECT * FROM myapp_person WHERE last_name = %s", [lname])

params är en lista eller en ordbok med parametrar. Du använder %s platshållare i frågesträngen för en lista, eller %(key)s platshållare för en ordbok (där key ersätts av en ordboksnyckel), oavsett vilken databasmotor du använder. Sådana platshållare kommer att ersättas med parametrar från argumentet params.

Observera

Dictionary-parametrar stöds inte med SQLite-backend; med denna backend måste du skicka parametrar som en lista.

Varning

Använd inte strängformatering på råa frågor eller citattecken i dina SQL-strängar!

Det är frestande att skriva ovanstående fråga som:

>>> query = "SELECT * FROM myapp_person WHERE last_name = %s" % lname
>>> Person.objects.raw(query)

Du kanske också tycker att du ska skriva din fråga så här (med citationstecken runt %s):

>>> query = "SELECT * FROM myapp_person WHERE last_name = '%s'"

Gör inte något av dessa misstag.

Som diskuterats i Skydd mot SQL-injektion, genom att använda params argumentet och lämna platshållarna ociterade skyddar du dig från SQL injection attacks, en vanlig exploatering där angripare injicerar godtycklig SQL i din databas. Om du använder stränginterpolering eller citerar platshållaren löper du risk för SQL-injektion.

Exekvera anpassad SQL direkt

Ibland räcker inte ens Manager.raw(): du kan behöva utföra frågor som inte mappar rent till modeller, eller direkt köra UPDATE, INSERT eller DELETE-frågor.

I dessa fall kan du alltid komma åt databasen direkt och helt undvika modellagret.

Objektet django.db.connection representerar standardanslutningen till databasen. För att använda databasanslutningen, anropa connection.cursor() för att få ett cursor-objekt. Anropa sedan cursor.execute(sql, [params]) för att köra SQL och cursor.fetchone() eller cursor.fetchall() för att returnera de resulterande raderna.

Till exempel:

from django.db import connection


def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
        cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
        row = cursor.fetchone()

    return row

För att skydda mot SQL-injektion får du inte inkludera citattecken runt platshållarna %s i SQL-strängen.

Observera att om du vill inkludera bokstavliga procenttecken i frågan måste du dubbla dem i det fall du skickar parametrar:

cursor.execute("SELECT foo FROM bar WHERE baz = '30%'")
cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])

Om du använder fler än en databas kan du använda django.db.connections för att hämta anslutningen (och markören) för en specifik databas. django.db.connections är ett ordboksliknande objekt som gör att du kan hämta en specifik anslutning med hjälp av dess alias:

from django.db import connections

with connections["my_db_alias"].cursor() as cursor:
    # Your code here
    ...

Som standard kommer Python DB API att returnera resultat utan deras fältnamn, vilket innebär att du får en ”lista” med värden, snarare än ett ”dict”. Med en liten prestanda- och minneskostnad kan du returnera resultat som en dict genom att använda något liknande detta:

def dictfetchall(cursor):
    """
    Return all rows from a cursor as a dict.
    Assume the column names are unique.
    """
    columns = [col[0] for col in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]

Ett annat alternativ är att använda collections.namedtuple() från Pythons standardbibliotek. En namedtuple är ett tuple-liknande objekt som har fält som är tillgängliga genom attributuppslagning; det är också indexerbart och itererbart. Resultaten är oföränderliga och tillgängliga med fältnamn eller index, vilket kan vara användbart:

from collections import namedtuple


def namedtuplefetchall(cursor):
    """
    Return all rows from a cursor as a namedtuple.
    Assume the column names are unique.
    """
    desc = cursor.description
    nt_result = namedtuple("Result", [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

Exemplen dictfetchall() och namedtuplefetchall() förutsätter unika kolumnnamn, eftersom en markör inte kan skilja på kolumner från olika tabeller.

Här är ett exempel på skillnaden mellan de tre:

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
>>> cursor.fetchall()
((54360982, None), (54360880, None))

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
>>> dictfetchall(cursor)
[{'parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}]

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
>>> results = namedtuplefetchall(cursor)
>>> results
[Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)]
>>> results[0].id
54360982
>>> results[0][0]
54360982

Anslutningar och markörer

connection och cursor implementerar mestadels standard Python DB-API som beskrivs i PEP 249 - förutom när det gäller transaktionshantering.

Om du inte är bekant med Python DB-API, notera att SQL-satsen i cursor.execute() använder platshållare, "%s", snarare än att lägga till parametrar direkt i SQL. Om du använder den här tekniken kommer det underliggande databasbiblioteket automatiskt att escape dina parametrar vid behov.

Observera också att Django förväntar sig platshållaren "%s", inte platshållaren "?", som används av SQLite Python-bindningar. Detta är för konsekvensens och sanitetens skull.

Använda en markör som kontexthanterare:

with connection.cursor() as c:
    c.execute(...)

är likvärdig med:

c = connection.cursor()
try:
    c.execute(...)
finally:
    c.close()

Anropa lagrade procedurer

CursorWrapper.callproc(procname, params=None, kparams=None)

Anropar en databaslagrad procedur med det angivna namnet. En sekvens (params) eller ett lexikon (kparams) med inparametrar kan anges. De flesta databaser stöder inte kparams. Av Djangos inbyggda backends är det bara Oracle som stöder det.

Till exempel, med den här lagrade proceduren i en Oracle-databas:

CREATE PROCEDURE "TEST_PROCEDURE"(v_i INTEGER, v_text NVARCHAR2(10)) AS
    p_i INTEGER;
    p_text NVARCHAR2(10);
BEGIN
    p_i := v_i;
    p_text := v_text;
    ...
END;

Detta kommer att kalla det:

with connection.cursor() as cursor:
    cursor.callproc("test_procedure", [1, "test"])