2

I am using two mysql schemas X and Y, both contains multiple tables but there is one table that has same name in both the schemas.

Both the schemas are as follows:

+--------------+--+--------------+
| X            |  | Y            |
+--------------+--+--------------+
| name         |  | album_info   |
+--------------+--+--------------+
| invite       |  | photo_info   |
+--------------+--+--------------+
|   photo      |  |   photo      |
+--------------+--+--------------+
| user_details |  | temp         |
+--------------+--+--------------+

Now, I want to query on both the tables but when I write the table structure in models.py file with same name it throws error/exception. I declared both table in routers.py file as below:

  modelDatabaseMap = {
    .
    'photo': 'default',
    .
    .
    .
    'photo': 'y',
}

(X is my default schema). Declared i models.py as below:

 class Photo(models.Model):
     id = models.AutoField(db_column='ID', primary_key=True)
     has_tagged_with = models.IntegerField()
     has_reposted_with = models.IntegerField()
     .
     .

     class Meta:
        managed = False
        db_table = 'photo'

 class Photo(models.Model):
     id = models.AutoField(db_column='ID', primary_key=True)
     account_id = models.IntegerField()
     p_id = models.IntegerField()
     is_profile = models.IntegerField()
     .
     .

     class Meta:
        managed = False
        db_table = 'photo'

Now, the ambiguity is first in name, in declaration in models.py and secondly in querying. I am stuck at how to query on both the tables separately through orm. Any help/lead about this would be helpful. Thanks in advance.

Shrey
  • 145
  • 1
  • 7
  • Do you have a connection for each schema? How is your `DATABASES` configuration? – alfonso.kim Jun 14 '17 at 13:14
  • DATABASES configuration is as follows: `DATABASES = { 'default': { 'ENGINE': 'django.contrib.gis.db.backends.mysql', 'NAME': 'X', 'USER': DATABASE_USERNAME, 'PASSWORD': DATABASE_PASSWORD, 'HOST': DATABASE_HOST_NAME, 'PORT': '3306', 'OPTIONS': {'charset': 'utf8mb4'}, } }` Same way I added schema Y into the dictionary. – Shrey Jun 14 '17 at 13:47
  • that's the point. You have two database configurations? one `default` and other `y`, for example? – alfonso.kim Jun 14 '17 at 14:04
  • I don't have two db configurations, default and Y both schemas are mentioned inside DATABASES = { } I have only one DATABASES config – Shrey Jun 14 '17 at 14:06
  • DATABASES = { 'default': { 'ENGINE': 'django.contrib.gis.db.backends.mysql', 'NAME': 'X', 'USER': , 'PASSWORD': , 'HOST': , 'PORT': '3306', 'OPTIONS': {'charset': 'utf8mb4'}, }, 'y': { 'ENGINE': 'django.contrib.gis.db.backends.mysql', 'NAME': 'Y', 'USER': , 'PASSWORD': , 'HOST': , 'PORT': '3306', 'OPTIONS': {'charset': 'utf8mb4'}, } } – Shrey Jun 14 '17 at 14:08

1 Answers1

0

Given your DATABASES configuration, you can try:

  1. Change your Models name or create a new app with a different models module for each schema:

    class YPhoto(models.Model):
    ...

class XPhoto(models.Model):
    ...

  2. Create a router.py module:

    class MyRouter(object):

def db_for_read(self, model, **hints):
    if model.__name__ == 'YPhoto':
        return 'Y'
    return None

def db_for_write(self, model, **hints):
    if model.__name__ == 'YPhoto':
        return 'Y'
    return None

def allow_relation(self, obj1, obj2, **hints):
    # Maybe you want to prevent relations between different schemas, it's up to you
    return True

def allow_syncdb(self, db, model):
    return True

  3. Add the router to the settings.py:

    DATABASE_ROUTERS = ['myapp.models.MyRouter',]

Check the docs about database routing.

alfonso.kim
  • 2,844
  • 4
  • 32
  • 37
  • Oops, I've noticed that you must check for all tables within `Y` schema in the `if`s. Maybe you are better creating a new app for the tables in the `Y` schema – alfonso.kim Jun 14 '17 at 14:50
  • Thanks, will try this. – Shrey Jun 15 '17 at 05:59
  • I would request to answer another question of mine, link is below: https://stackoverflow.com/questions/44496101/how-to-insert-emoji-into-mysql-5-5-and-higher-using-django-orm?noredirect=1#comment75996316_44496101 – Shrey Jun 15 '17 at 06:01