Django 2.1 - Performing raw SQL queries

कच्चे एसक्यूएल प्रश्नों का प्रदर्शन




django

कच्चे एसक्यूएल प्रश्नों का प्रदर्शन

जब मॉडल क्वेरी API बहुत दूर नहीं जाती है, तो आप कच्चे SQL लिखने पर वापस आ सकते हैं। Django आपको कच्चे एसक्यूएल प्रश्नों के प्रदर्शन के दो तरीके देता है: आप कच्चे प्रश्नों को करने और मॉडल इंस्टेंसेस को वापस करने के लिए Manager.raw() का उपयोग कर सकते हैं, या आप मॉडल लेयर से पूरी तरह बच सकते हैं और कस्टम SQL को सीधे निष्पादित कर सकते हैं

चेतावनी

जब भी आप कच्ची एसक्यूएल लिखते हैं तो आपको बहुत सावधान रहना चाहिए। हर बार जब आप इसका उपयोग करते हैं, तो आपको SQL इंजेक्शन के हमलों से बचाने के लिए किसी भी पैरामीटर को ठीक से बचाना चाहिए, जिसे उपयोगकर्ता params का उपयोग करके नियंत्रित कर सकता है। कृपया SQL इंजेक्शन सुरक्षा के बारे में अधिक पढ़ें।

कच्चे प्रश्नों का प्रदर्शन

raw() प्रबंधक विधि का उपयोग कच्चे एसक्यूएल प्रश्नों को करने के लिए किया जा सकता है जो मॉडल उदाहरण लौटाते हैं:

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

यह विधि एक कच्ची SQL क्वेरी लेती है, इसे निष्पादित करती है, और django.db.models.query.RawQuerySet उदाहरण देता है। इस RawQuerySet उदाहरण वस्तु उदाहरण प्रदान करने के लिए एक सामान्य QuerySet तरह पुनरावृत्त किया जा सकता है।

यह एक उदाहरण के साथ सबसे अच्छा सचित्र है। मान लें कि आपके पास निम्न मॉडल है:

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

आप तब कस्टम SQL को निष्पादित कर सकते हैं जैसे:

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

बेशक, यह उदाहरण बहुत रोमांचक नहीं है - यह बिल्कुल वैसा ही है जैसा चल रहा Person.objects.all() । हालांकि, raw() में अन्य विकल्पों का एक गुच्छा होता है जो इसे बहुत शक्तिशाली बनाते हैं।

मॉडल तालिका के नाम

उस उदाहरण में Person तालिका का नाम कहां से आया?

डिफ़ॉल्ट रूप से, Django मॉडल के "ऐप लेबल" से जुड़कर एक डेटाबेस तालिका नाम का पता manage.py startapp - आपने जो नाम प्रबंधन में इस्तेमाल किया है manage.py startapp - मॉडल के वर्ग के नाम पर, उनके बीच एक अंडरस्कोर के साथ। उदाहरण में हमने मान लिया है कि Person मॉडल myapp नाम के ऐप में रहता है, इसलिए इसकी तालिका myapp_person होगी।

अधिक जानकारी के लिए db_table विकल्प के लिए दस्तावेज़ीकरण देखें, जो आपको डेटाबेस तालिका नाम मैन्युअल रूप से सेट करने देता है।

चेतावनी

.raw() में पास किए गए SQL कथन पर कोई जाँच नहीं की जाती है। Django को उम्मीद है कि बयान डेटाबेस से पंक्तियों का एक सेट लौटाएगा, लेकिन इसे लागू करने के लिए कुछ भी नहीं करता है। यदि क्वेरी पंक्तियों को वापस नहीं करती है, तो (संभवतः गुप्त) त्रुटि का परिणाम होगा।

चेतावनी

यदि आप MySQL पर क्वेरी कर रहे हैं, तो ध्यान दें कि टाइप करते समय MySQL का साइलेंट टाइप कॉर्शन अप्रत्याशित परिणाम दे सकता है। यदि आप एक स्ट्रिंग प्रकार के कॉलम पर क्वेरी करते हैं, लेकिन पूर्णांक मान के साथ, MySQL तुलना करने से पहले तालिका में सभी मानों के प्रकारों को एक पूर्णांक तक ले जाएगा। उदाहरण के लिए, यदि आपकी तालिका में 'abc' , 'def' मान हैं और आपने WHERE mycolumn=0 लिए क्वेरी की है, तो दोनों पंक्तियाँ मेल WHERE mycolumn=0 । इसे रोकने के लिए, क्वेरी में मान का उपयोग करने से पहले सही टाइपकास्टिंग करें।

क्वेरी फ़ील्ड को मॉडल फ़ील्ड में मैप करना

raw() स्वचालित रूप से मॉडल पर फ़ील्ड में क्वेरी से फ़ील्ड को मैप करता है।

आपकी क्वेरी में फ़ील्ड का क्रम मायने नहीं रखता है। दूसरे शब्दों में, निम्नलिखित दोनों प्रश्न समान रूप से काम करते हैं:

>>> 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')
...

नाम से मिलान किया जाता है। इसका अर्थ है कि आप क्वेरी से मॉडल फ़ील्ड में मैप करने के लिए SQL के AS क्लॉज़ का उपयोग कर सकते हैं। इसलिए यदि आपके पास कोई अन्य तालिका थी जिसमें व्यक्तिगत डेटा था, तो आप इसे व्यक्तिगत उदाहरणों में आसानी से मैप कर सकते हैं:

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

जब तक नाम से मेल खाते हैं, तब तक मॉडल उदाहरण सही तरीके से बनाए जाएंगे।

वैकल्पिक रूप से, आप क्वेरी में translations तर्क raw() उपयोग से फ़ील्ड को मॉडल फ़ील्ड में मैप कर सकते हैं। यह मॉडल में फ़ील्ड के नाम के लिए क्वेरी में फ़ील्ड के नाम का एक शब्द मैपिंग है। उदाहरण के लिए, उपरोक्त क्वेरी भी लिखी जा सकती है:

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

सूचकांक खोजते हैं

raw() अनुक्रमण का समर्थन करता है, इसलिए यदि आपको केवल पहला परिणाम चाहिए जो आप लिख सकते हैं:

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

हालाँकि, अनुक्रमण और स्लाइसिंग डेटाबेस स्तर पर नहीं किए जाते हैं। यदि आपके डेटाबेस में बड़ी संख्या में व्यक्तिगत ऑब्जेक्ट हैं, तो SQL स्तर पर क्वेरी को सीमित करना अधिक कुशल है:

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

मॉडल फ़ील्ड खोजें

फ़ील्ड भी छोड़ी जा सकती हैं:

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

इस क्वेरी द्वारा लौटाए गए व्यक्तिगत ऑब्जेक्ट मॉडल के उदाहरण ( defer() देखें defer() आस्थगित हो जाएंगे। इसका मतलब है कि क्वेरी से हटाए गए फ़ील्ड मांग पर लोड किए जाएंगे। उदाहरण के लिए:

>>> 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

बाहरी दिखावे से, ऐसा लगता है कि क्वेरी ने पहले नाम और अंतिम नाम दोनों को पुनः प्राप्त किया है। हालाँकि, इस उदाहरण ने वास्तव में 3 प्रश्न जारी किए। केवल पहले नामों को कच्चे () क्वेरी द्वारा पुनर्प्राप्त किया गया था - अंतिम नाम दोनों मांग पर पुनर्प्राप्त किए गए थे जब वे मुद्रित किए गए थे।

केवल एक फ़ील्ड है जिसे आप बाहर नहीं छोड़ सकते हैं - प्राथमिक कुंजी फ़ील्ड। Django मॉडल उदाहरणों की पहचान करने के लिए प्राथमिक कुंजी का उपयोग करता है, इसलिए इसे हमेशा एक कच्ची क्वेरी में शामिल किया जाना चाहिए। यदि आप प्राथमिक कुंजी को शामिल करना भूल जाते हैं तो एक InvalidQuery अपवाद उठाया जाएगा।

एनोटेशन जोड़ना

आप उन फ़ील्ड्स को भी निष्पादित कर सकते हैं जो मॉडल पर परिभाषित नहीं हैं। उदाहरण के लिए, हम डेटाबेस द्वारा गणना की गई आयु के साथ लोगों की सूची प्राप्त करने के लिए PostgreSQL की आयु () फ़ंक्शन का उपयोग कर सकते हैं:

>>> 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.
...

raw() में पासिंग पैरामीटर raw()

यदि आपको पैरामीटरयुक्त प्रश्न करने की आवश्यकता है, तो आप params तर्क को raw() उपयोग कर सकते हैं:

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

params मापदंडों की एक सूची या शब्दकोश है। आप एक सूची के लिए क्वेरी स्ट्रिंग में %s प्लेसहोल्डर्स का उपयोग करेंगे, या एक शब्दकोश के लिए %(key)s प्लेसहोल्डर्स (जहाँ key को डिक्शनरी कुंजी द्वारा प्रतिस्थापित किया जाता है), आपके डेटाबेस इंजन की परवाह किए बिना। ऐसे प्लेसहोल्डर्स को params तर्क से मापदंडों के साथ बदल दिया जाएगा।

ध्यान दें

शब्दकोश परमेट्स SQLite बैकएंड के साथ समर्थित नहीं हैं; इस बैकएंड के साथ, आपको एक सूची के रूप में पैरामीटर पास करना होगा।

चेतावनी

अपने SQL स्ट्रिंग्स में कच्चे प्रश्नों या बोली प्लेसहोल्डर्स पर स्ट्रिंग स्वरूपण का उपयोग न करें!

उपरोक्त क्वेरी को इस प्रकार लिखना मोहक है:

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

आप यह भी सोच सकते हैं कि आपको अपनी क्वेरी को इस तरह लिखना चाहिए (लगभग %s उद्धरण के साथ):

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

इन गलतियों में से कोई भी मत करो।

जैसा कि SQL इंजेक्शन सुरक्षा में चर्चा की गई है, params तर्क का उपयोग करते हुए और प्लेसहोल्डर्स को अछूता छोड़कर आपको SQL इंजेक्शन हमलों से बचाता है, एक आम शोषण जहां हमलावर आपके डेटाबेस में मनमाने ढंग से SQL इंजेक्षन करते हैं। यदि आप स्ट्रिंग प्रक्षेप का उपयोग करते हैं या प्लेसहोल्डर को उद्धृत करते हैं, तो आपको SQL इंजेक्शन के लिए जोखिम है।

सीधे कस्टम SQL निष्पादित करना

कभी-कभी Manager.raw() भी पर्याप्त नहीं होता है: आपको उन प्रश्नों को करने की आवश्यकता हो सकती है जो मॉडल के लिए साफ-साफ मैप नहीं करते हैं, या सीधे UPDATE , INSERT , या DELETE प्रश्नों को निष्पादित करते हैं।

इन मामलों में, आप हमेशा पूरी तरह से मॉडल लेयर के आसपास रूटिंग कर सकते हैं।

ऑब्जेक्ट django.db.connection डिफ़ॉल्ट डेटाबेस कनेक्शन का प्रतिनिधित्व करता है। डेटाबेस कनेक्शन का उपयोग करने के लिए, कर्सर ऑब्जेक्ट प्राप्त करने के लिए connection.cursor() पर कॉल करें। फिर, cursor.execute(sql, [params]) को SQL और cursor.fetchone() को निष्पादित करने के लिए कॉल करें। cursor.execute(sql, [params]) cursor.fetchone() या cursor.fetchall() परिणामी पंक्तियों को वापस करने के लिए।

उदाहरण के लिए:

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

SQL इंजेक्शन से बचाने के लिए, आपको SQL स्ट्रिंग में %s प्लेसहोल्डर्स के आसपास उद्धरण शामिल नहीं करने चाहिए।

ध्यान दें कि यदि आप क्वेरी में शाब्दिक प्रतिशत संकेत शामिल करना चाहते हैं, तो आपको उन्हें उस मामले में दोगुना करना होगा जब आप पैरामीटर पारित कर रहे हैं:

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

यदि आप एक से अधिक डेटाबेस का उपयोग कर रहे हैं, तो आप विशिष्ट डेटाबेस के लिए कनेक्शन (और कर्सर) प्राप्त करने के लिए django.db.connections का उपयोग कर सकते हैं। django.db.connections एक शब्दकोश जैसी वस्तु है, जो आपको इसके उपनाम का उपयोग करके एक विशिष्ट कनेक्शन प्राप्त करने की अनुमति देती है:

from django.db import connections
with connections['my_db_alias'].cursor() as cursor:
    # Your code here...

डिफ़ॉल्ट रूप से, पायथन डीबी एपीआई अपने क्षेत्र के नाम के बिना परिणाम लौटाएगा, जिसका अर्थ है कि आप एक dict बजाय मूल्यों की एक list के साथ समाप्त होते हैं। एक छोटे से प्रदर्शन और स्मृति लागत पर, आप कुछ इस तरह का उपयोग करके एक परिणाम के रूप में वापस आ सकते हैं:

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

एक अन्य विकल्प पायथन मानक पुस्तकालय से संग्रह.नामटुपल collections.namedtuple() का उपयोग करना है। एक namedtuple एक टपल-जैसी वस्तु है जिसमें विशेषता लुकअप द्वारा सुलभ फ़ील्ड हैं; यह भी अनुक्रमित और चलने योग्य है। क्षेत्र के नाम या सूचकांक से परिणाम अपरिवर्तनीय और सुलभ हैं, जो उपयोगी हो सकते हैं:

from collections import namedtuple

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

यहाँ तीन के बीच अंतर का एक उदाहरण है:

>>> 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

कनेक्शन और अभिशाप

connection और cursor ज्यादातर पीईपी 249 में वर्णित मानक पायथन डीबी-एपीआई को लागू करते हैं - जब यह लेनदेन से निपटने के लिए आता है।

यदि आप Python DB-API से परिचित नहीं हैं, तो ध्यान दें कि SQL में cursor.execute() जोड़ने के बजाय cursor.execute() . cursor.execute() में SQL स्टेटमेंट, "%s" का उपयोग करता है। यदि आप इस तकनीक का उपयोग करते हैं, तो अंतर्निहित डेटाबेस लाइब्रेरी स्वचालित रूप से आवश्यक के रूप में आपके मापदंडों से बच जाएगी।

यह भी ध्यान दें कि Django "%s" प्लेसहोल्डर की अपेक्षा करता है, कि "?" प्लेसहोल्डर, जो SQLite पायथन बाइंडिंग द्वारा उपयोग किया जाता है। यह संगति और पवित्रता के लिए है।

संदर्भ प्रबंधक के रूप में कर्सर का उपयोग करना:

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

के बराबर है:

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

संग्रहीत प्रक्रियाओं को कॉल करना

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

किसी डेटाबेस संग्रहीत कार्यविधि को दिए गए नाम के साथ कॉल करता है। इनपुट पैरामीटर्स का एक अनुक्रम ( kparams ) या शब्दकोश ( kparams ) प्रदान किया जा सकता है। अधिकांश डेटाबेस kparams समर्थन नहीं करते हैं। Django के बिल्ट-इन बैकेंड्स में से केवल Oracle इसका समर्थन करता है।

उदाहरण के लिए, इस संग्रहीत कार्यविधि को Oracle डेटाबेस में दिया गया है:

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;

यह इसे कहेगा:

with connection.cursor() as cursor:
    cursor.callproc('test_procedure', [1, 'test'])
Django 2.0 में बदला:

kparams तर्क जोड़ा गया।