how to pass the dynamic variable in raw sql query using django mysql

Question

How to archive the following...

i need to pass two variable date which comes as long format and apply this variable in a subquery using raw sql django

def duplicatephoneno(request):

    cursor = connection.cursor()
    payload = json.loads(request.body.decode('utf-8'))
    print(payload)
    if payload:
        startdate=payload['from']
        enddate=payload['to']
        with connection.cursor() as cursor:
            cursor.execute(MYSQLdb.escape_string("SELECT * FROM `allergy` WHERE `allergy`.`patient_n_key` IN (SELECT `patient_masters`.`patient_n_key` FROM `patient_masters` WHERE `patient_masters`.`created_on`between %s AND %s)",[startdate], [enddate]))
            row = dictfetchall(cursor)
            patientuser=serializers.serialize('json', row)
        return HttpResponse(patientuser, content_type='application/json;charset=utf8')

So startdate and enddate are strings? Can you give an example of the format? — Will Keeling
– Will Keeling, Commented Sep 5, 2018 at 11:52
Tq Mr will keeling.Yes I need to give string only Ex.Varchar field.where am i going wrong. — Micheal
– Micheal, Commented Nov 27, 2018 at 10:45
Hi Mr pandelis i have run the above code which you edited but I am getting error MysqlDB is not defined but i have imported in my local but again i am getting the same error. — Micheal
– Micheal, Commented Nov 27, 2018 at 15:58

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Try this

def duplicatephoneno(request):
    payload = json.loads(request.body.decode('utf-8'))
    print(payload)

    if payload:
        startdate=payload['from']
        enddate=payload['to']
        with connection.cursor() as cursor:

            queries="SELECT * FROM `allergy` WHERE `allergy`.`patient_n_key` IN (SELECT `patient_masters`.`patient_n_key` FROM `patient_masters` WHERE `patient_masters`.`created_on`between %s AND %s)"
            data_tuple=(startdate,enddate)
            cursor.execute(queries,data_tuple)
            connection.commit()
            row = cursor.fetchall()
            patientuser=serializers.serialize('json', row)
            return HttpResponse(patientuser, content_type='application/json;charset=utf8')

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 15, 2020 at 9:31

ASHUTOSH ASHIRBAD TRIPATHY

315 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rosey · Accepted Answer · 2018-09-04 14:12:10Z

0

The docs mention how to do this. Note you will not be able to use this method if you are using an SQLite database.

As a side note, using raw queries should be your last resort. The Django ORM is pretty good these days and you should really only use raw queries if there is a type of query that is unsupported by the ORM (rare) or the ORM generated querystring has a significant performance cost compared to your custom raw query. Querysets (the objects returned by ORM queries) are also pretty easy to turn into JSON like you seem to be doing here.

answered Sep 4, 2018 at 14:12

Rosey

8712 gold badges14 silver badges31 bronze badges

Comments

Kebab Programmer · Accepted Answer · 2023-02-01 17:05:00Z

0

def duplicatephoneno(request):

    cursor = connection.cursor()
    payload = json.loads(request.body.decode('utf-8'))
    print(payload)
    if payload:
        startdate=payload['from']
        enddate=payload['to']
        with connection.cursor() as cursor:
            cursor.execute(MYSQLdb.escape_string("SELECT * FROM `allergy` WHERE `allergy`.`patient_n_key` IN (SELECT `patient_masters`.`patient_n_key` FROM `patient_masters` WHERE `patient_masters`.`created_on`between %s AND %s)",[startdate, enddate]))
            row = dictfetchall(cursor)
            patientuser=serializers.serialize('json', row)
        return HttpResponse(patientuser, content_type='application/json;charset=utf8')

edited Feb 1, 2023 at 17:05

Kebab Programmer

1,2192 gold badges22 silver badges37 bronze badges

answered Jan 27, 2023 at 13:55

Hemasai Nagothi

1

Collectives™ on Stack Overflow

how to pass the dynamic variable in raw sql query using django mysql

3 Answers 3

Try this

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Try this

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related