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!
1
-
1Try pandas library especially pandas.read_sql() along with pyodbc. It also has to_sql() method you can call on DataFrame (equivalent of table)Bartek Malysz– Bartek Malysz2018-10-15 09:34:42 +00:00Commented Oct 15, 2018 at 9:34
Add a comment
|
1 Answer
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/
