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 db_utils.py:

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):
        self.connect()

    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):
        self.connect()
        return self.Session()
       
    def close(self):
        self.Session.remove()



# 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):
        self.name = name

        self.value = value
       
    def __repr__(self):
        return "<Project2_Table (%s-%s)>" % (self.name, 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'
    session.commit()
...


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



(source: http://westmarch.sjsoft.com/2012/10/introduction-to-sqlalchemy-tutorial/)