Friday, September 6, 2013

Django & SQLAlchemy - DB Interacting between 2 projects running on 2 different environments

Here is the case:

* I have 2 Django projects running on 2 different environments (virtualenv)

* I want to update some data of project2 from project1

I did some research and found a solution:

Using SQLAlchemy to create a middle database interaction layer between 2 projects (both sides)

So, I wrote this utility class called

import os
from datetime import datetime

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime

DB_HOST = 'localhost'
DBMS = 'mysql'
DB_PORT = '3306'
DB_NAME = 'project2'
DB_USER = 'project2_user'
DB_PWD = 'project2_pwd'

DB_ENGINE_URL = '%s://%s:%s@%s:%s/%s' % (DBMS, DB_USER, DB_PWD, DB_HOST,\
                                                    DB_PORT, DB_NAME)

# database connection
class Project2(object):
    engine = None
    Session = None

    def __init__(self):

    def connect(self):
        if self.engine is None:
            self.engine = create_engine(DB_ENGINE_URL, echo=False, \
                                    pool_size = 100, pool_recycle=3600)

        if self.Session is None:
            self.Session = scoped_session(sessionmaker(bind=self.engine))

    def get_session(self):
        return self.Session()
    def close(self):

# table mapping
Base = declarative_base()
class Project2_Table(Base):
    __tablename__ = 'project2_table'

    id = Column(String(50), primary_key=True)
    name = Column(String(50))
    value = Column(String(75))

    def __init__(self, name, value): = name

        self.value = value
    def __repr__(self):
        return "<Project2_Table (%s-%s)>" % (, self.value)

And in the views of project1, I can manipulate project2 table data. For example:

from db_utils import *

def project1_view(request):
    prj2_db = Project2()
    session = prj2_db.get_session()
    prj2_table_obj = session.query(Project2_Table).filter(name='genius').first()
    prj2_table_obj.value = 'Trinh Nguyen'

Awesomenesss!!! \m/\m/\m/