CODEWITHSUNDEEP
pythonsql-serverdjangopostgresql
Gent Bytyqi
Gent Bytyqi

Reputation: 419

Django read and write from multiple databases

Hello guys I'm developing a web app in django and I'm using the postgresql database. I must be able also to grab some data from another app which uses a sqlserver database. The tables that I'm trying to get have lots of data so maybe it is not wise to use a direct link. Which is the best approach regarding this issue? Can I use a sql-odbc connection to get the data, also how can I populate the tables lets say I create a local table and migrate data from sql to postgresql schedualy. Would like to understand how you dealt with this issue and your experiences. Thank you!

Upvotes: 1

Views: 4256

Answers (1)

Carlos Herrera
Carlos Herrera

Reputation: 143

In your settings.py edit this code (for multiple databases)

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'django',
        'USER': 'postgres',
        'PASSWORD': '12345678',
        'HOST': 'localhost',
        'PORT': '5432',
    },
    'connection_other_db': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mi_db',
        'USER': 'root',
        'PASSWORD': 'root',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

Apply migrations command:

For default database

$ ./manage.py migrate

For other database (connection_other_db)

$ ./manage.py migrate --database=connection_other_db

In you views, For using ORM, use this:

Mi_Model.objects.using('connection_other_db').all() # For mysql database
Mi_Model.objects.all() # For default database (postgresql)

For create object:

s = Mi_Model()
s._state.adding = False
s._state.db = 'connection_other_db'
s.field_1 = 'A value'
s.save()

or

s = Mi_Model.objects.using('connection_other_db').create(
   field_1='A value'
   # ....
)

For use transactions:

with transaction.atomic(using='connection_other_db'):
   # Your code here

For use cursors

with connections['connection_other_db'].cursor() as cursor:
   cursor.execute('your query')

Django documentation: https://docs.djangoproject.com/es/2.1/topics/db/multi-db/

Upvotes: 5

Related Questions