Connecting an external mysql database to django application and querying it

Question

I need to pull data from an external MySQL database to my application. I have added the below lines to my databases dictionary in settings.py file.

'cust': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'cust_data',
        'HOST': '',
        'USER': '',
        'PASSWORD': ''
    }

Is the engine name right? I have added all other details.

Now is this it? How can I get the data from this database now? Can I simply use an SQL query in my applications views.py?

Now when I enter shell using below command

python manage.py shell

When I execute a query to the MySQL database, it doesn't work. I am sure I am missing something here.

I am stuck here, what am I doing wrong here? How can I run a command to access data from a particular table in the above DB?

EsmaeelE · Accepted Answer · 2024-02-06 18:23:59Z

5

It seems that your external MySQL database does not map to your Django models. In that case you have to use django.db.connection object to connect the database and execute the raw SQL query directly. You can use the query result in views.py but it is best to consider whether views.py is the right place to perform the query action.

To test the query in python shell you can try in this way:

>>> from django.db import connections
>>> cursor = connections['cust'].cursor() # Replace 'cust' to other defined databases if necessary.
>>> cursor.execute("YOUR SQL QUERY HERE")
>>> cursor.fetchall()

For the method fetchall() it should return your result in tuple form.

You can refer more information in the official site from here.

edited Feb 6, 2024 at 18:23

EsmaeelE

2,7557 gold badges28 silver badges36 bronze badges

answered Jun 22, 2017 at 16:31

Lee Elton

863 bronze badges

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

2 Comments

moki Over a year ago

This looks perfect, so once after configuring my settings.py, can I directly use the above lines of code as mentioned by Lee? Or should I need to configure something else?

Lee Elton Over a year ago

Yes. You can try the code as mentioned above in the shell (shell by the command python manage.py shell) to see if it can get the result from your database. If it returns the expected result you can simply use the same code in the py file.

Erich · Accepted Answer · 2017-06-21 19:38:15Z

1

The constant variable is wrong. You have to declare the DATABASES variable.

In your settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mydatabase',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': 'myhost'            
    }
}

Just replace the placeholders with the correct values.

answered Jun 21, 2017 at 19:38

Erich

1,84817 silver badges20 bronze badges

1 Comment

moki Over a year ago

I think you are mistaken here, I am using multiple databases for my application, I already have a default db set and this is my second database. Refer this link for detailed documentation docs.djangoproject.com/en/1.11/topics/db/multi-db

Collectives™ on Stack Overflow

Connecting an external mysql database to django application and querying it

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related