django sql .raw filtering on a string not working-CodePudding

I am trying to filter on a foreign key but getting an error. Current code is:

Views.py
def kingmailboxcodesshow(request):
    lname = "King"
    lockbox_list = MailBoxCodes.objects.raw('SELECT * FROM mailboxcodes WHERE Address_id__contains %s',[lname])
    return render(request,"users/mailboxcodesshow.html",{'MailBoxCodes':lockbox_list})

receiving this error:

django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''King'' at line 1")

I am still really new to django and python, looking at the error I am thinking i need a few less ' around the King, but I am not sure how to make that happen.

I have a bunch of addresses in the Address_id and I just want to retrieve all the address with the work King in their street address.

I would greatly appreciate any and all assistance.

Thank you in advance.

CodePudding user response：

The great thing with Django is that you don't need raw SQL queries, unless you really really know what you're doing. Please follow the official tutorial as it explains the fundamentals of Django and will guide you through this over here: Django tutorial

To help you out with this particular issue:

def kingmailboxcodesshow(request):
    lname = "King"
    lockbox_list = MailBoxCodes.objects.filter(address__name__contains=lname)
    return render(request,"users/mailboxcodesshow.html",{'MailBoxCodes':lockbox_list})

CodePudding user response：

Well there is no such thing as a __contains in SQL, that is just some Django logic that transforms it to a query, you can work with:

def kingmailboxcodesshow(request):
    lname = 'King'
    lockbox_list = MailBoxCodes.objects.raw(
        'SELECT * FROM mailboxcodes WHERE Address_id LIKE %%%s%%', (lname,)
    )
    return render(
        request, 'users/mailboxcodesshow.html', {'MailBoxCodes': lockbox_list}
    )

That being said, using raw queries should be a last resort: the entire idea of Django's ORM is, as you actually found out with this question to make abstraction of query logic. You can run this with:

def kingmailboxcodesshow(request):
    lname = 'King'
    lockbox_list = MailBoxCodes.objects.filter(Address_id__contains=lname)
    return render(
        request, 'users/mailboxcodesshow.html', {'MailBoxCodes': lockbox_list}
    )