django orm uses sql statement to perform multi-table joint query (join on, left join)

created at 07-16-2021 views: 1

background

Because the use of Manager.raw () can not meet the needs of executing SQL statements, because I want to execute the query statement that is not explicitly mapped to the model.

The object django.db.connection represents the default database connection. To use this database connection, call connection.cursor() to obtain a pointer object. Then, call cursor.execute(sql, [params]) to execute the SQL and cursor.fetchone(), or cursor.fetchall() to get the result data.

for example:

from django.db import connectiondef 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

Avoid sql input method:

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

note: To include the percent sign of the text in the query, you need to use two percent signs in the incoming parameters.

Use multiple database solutions in django

You can use django.db.connections to get the connection (and cursor) of the specified database. django.db.connections is a dictionary-like object, which allows you to obtain specific connections through connection aliases:

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

Note: my_db_alias is the key name in DATABASES

DATABASES = {
    # Store basic information
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'basic.sqlite3'),
    },
}

solution when returned result is a dictionary

By default, the results returned by the Python DB API will not contain field names, which means you will eventually receive a list instead of a dict. To pursue less calculation and memory consumption, you can return the result in dict, using the following code:

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()
    ]

Another method is to use collections.namedtuple() from the Python standard library. A namedtuple is a tuple-like object, which can be accessed through attribute search; it can also be indexed and iterated. The results are immutable, but can be accessed by field name or index, which is very practical:

from collections import namedtupledef 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()]

The differences between the three are introduced:

>>> 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
Please log in to leave a comment.