How to use multiple database and raw SQL in Django – MySQL

Django is a powerful web framework that allows developers to build complex and dynamic web applications. One of the key features of Django is its support for working with multiple databases and executing raw SQL queries. In this article, we will explore how to use multiple databases and raw SQL in Django, specifically with MySQL.

Setting up Multiple Databases in Django

In Django, it is possible to work with multiple databases simultaneously. This is particularly useful when dealing with large-scale applications that require different databases for different purposes. To set up multiple databases in Django with MySQL, you can follow these steps:

1. First, you need to define the databases in your Django settings file. You can do this by specifying the database details for each database in the DATABASES setting. For example:

		DATABASES = {
		    'default': {
		        'ENGINE': 'django.db.backends.mysql',
		        'NAME': 'my_default_db',
		        'USER': 'myuser',
		        'PASSWORD': 'mypassword',
		    },
		    'other_db': {
		        'ENGINE': 'django.db.backends.mysql',
		        'NAME': 'my_other_db',
		        'USER': 'myuser',
		        'PASSWORD': 'mypassword',
		    }
		}
		

2. Then, you need to define the database router to instruct Django on how to route the database queries. You can create a custom router class that tells Django which database to use for each model. For example:

		class MyRouter:
		    def db_for_read(self, model, **hints):
		        if model._meta.app_label == 'myapp':
		            return 'other_db'
		        return 'default'
		

Executing Raw SQL Queries in Django with MySQL

Sometimes, the ORM (Object-Relational Mapping) provided by Django may not be sufficient for complex queries or specific database operations. In such cases, you can use raw SQL queries to interact directly with the database. In Django, you can execute raw SQL queries using the connections module. Here’s an example:

	from django.db import connections

	cursor = connections['other_db'].cursor()
	cursor.execute("SELECT * FROM my_table")
	rows = cursor.fetchall()
	

By using the connections module, you can specify which database to execute the raw SQL query on and fetch the results accordingly.

Conclusion

In this article, we have explored how to use multiple databases and execute raw SQL queries in Django with MySQL. By following the steps outlined above, you can effectively work with multiple databases and interact with them using raw SQL queries in your Django applications.