Writing python-written SQL code to interact with Django database

Question

Currently, I'm having a Django project that has an API to let users interact with my database. This project is deployed on Google App Engine, connecting to Cloud SQL (MySQL core).

I would like to add some SQL-based functions, written in python, into this project to

perform tasks on the database, such as querying, predicting, calculating, etc.
automate (1)

Currently, I'm thinking of using mysql.connector.connect() to get the database from the Cloud SQL like below

mydb = mysql.connector.connect(  
            host="/cloudsql/project_name:region_name:instance_name",
            user="username",
            password="password",
            database="database",
            port = "3306", # port should not be specified in deployment?   
        )

and after getting the database, I can do whatever SQL logic, written in Python.

Could anybody suggest a way to achieve these? Thanks a lot.

See this documentation: docs.djangoproject.com/en/3.0/topics/db/sql — chrislondon
– chrislondon, Commented Jul 7, 2020 at 3:18
Ah, I see. thanks chrislondon. Does the code connection.cursor() work as the mydb variable above? — Violet Evergarden
– Violet Evergarden, Commented Jul 7, 2020 at 4:06

chrislondon · Accepted Answer · 2020-07-07 16:13:01Z

To be able to make raw SQL queries in Django you must first set your database connection information in your Django Settings file like so:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'database',
        'USER': 'username',
        'PASSWORD': 'password',
        'HOST': '/cloudsql/project_name:region_name:instance_name',
        'PORT': '3306',
    }
}

Then in your file where you want to run the raw SQL you would do the following:

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

For more information see the Django documentation here https://docs.djangoproject.com/en/3.0/topics/db/sql/

Collectives™ on Stack Overflow

Writing python-written SQL code to interact with Django database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related