Thursday, July 11, 2013

Django + SQLAlchemy - Make use of Django Forms

Imagine, you have a MySQL database table which is used by a web application (let's call it App-A). And you want to create a web interface to modify data of that table, but you don't want to tough the source code of App-A. In this situation, I will choose Django and SQLAlchemy.

Your table in App-A is something like:

MyGeniusTable:

- id: INT(10)
- name: VARCHAR(255)
- value: VARCHAR(255)

and we already have 2 records in MyGeniusTable:

+ name = 'my_first_data_name' / value = 'super'
+ name = 'my_second_data_name' / value = 'genius'

We will build a Django app to modify those records above:

1. Create a Django project named MyGeniusProject, and an app named MyGeniusApp. The directories structure is:


MyGeniusProject
----MyGeniusProject
--------MyGeniusApp
------------views.py
------------models.py
------------__init__.py
------------test.py
--------settings.py
--------urls.py
--------wsgi.py
--------__init__.py
----manage.py



2. Create a folder named utils inside MyGeniusProject (in the same location with MyGeniusApp). In utils, create:

+ my_models.py: define a class which stand for MyGeniusTable using SQLAlchemy module. Please read the article about using SQLAlchemy here: http://iambusychangingtheworld.blogspot.com/2013/06/sqlalchemy-python-module-with-mysql.html

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String



Base = declarative_base()

class MyGeniusTable(Base):
__tablename__ = 'MyGeniusTable'

id = Column(Integer, primary_key=True)
name = Column(String(100))
value = Column(String(100))

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

def __repr__(self):
return "<Settings(%s, %s)>" % (self.name, self.value)


+ my_db_utils.py: define a utility class to help you connect to App-A's database and MyGeniusTable table and get/set data from/to that table:

from sqlalchemy import *
from sqlalchemy.orm import *
import os
from datetime import datetime

from MyGeniusProject.utils.my_db_models import MyGeniusTable


DB_HOST = 'mydomain.com'
DBMS = 'mysql'
DB_PORT = '3306'
DB_NAME = 'my_genius_db'
DB_USER = 'genius'
DB_PWD = 'Sup3rM@n'

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

# moodle database connect
class MyDBUtil(object):
engine = None
Session = None

def __init__(self):
self.connect()

def connect(self):
if MyDBUtil.engine is None:
MyDBUtil.engine = create_engine(DB_ENGINE_URL, echo=False)

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

def get_session(self):
self.connect()
return MyDBUtil.Session()



def get_data(db, name):
value = ''
session = db.get_session()
try:
value = session.query(MyGeniusTable).filter_by(name=name).first().value
except:
session.rollback()
raise
finally:
session.close() # !important

return value


def set_data(db, name, value):
session = db.get_session()
try:
setting = session.query(MyGeniusTable).filter_by(name=name).first()
except:
setting = Settings(name=name, value=value)
session.add(setting)
else:
setting.value = value
finally:
session.commit()
session.close() # !important


* Note: 

+ remember to close session after get or set data to release the MySQL connection or your app will dead after a couple of requests.

+ If you face the error "MySQL has gone away", please read this article: http://iambusychangingtheworld.blogspot.com/2013/07/sqlalchemy-mysql-has-gone-away.html

3. Create forms.py in MyGeniusApp folder. We will override 3 methods of Django's Form:

+ __init__(self, *args, **kwargs) : get the current data of MyGeniusTable

+ clean(self): custom validation method

+ save(self): save submitted data to MyGeniusTable

from django import forms
from MyGeniusProject.utils import my_models, my_db_utils


class MyGeniusForm(forms.Form):
    field1 = forms.CharField(max_length=255)
    field2 = forms.CharField(max_length=255)

    def __init__(self, *args, **kwargs):
        super(MyGeniusForm, self).__init__(*args, **kwargs)

        db = my_db_utils.MyDBUtil()
        self.fields['field1'].initial = my_db_utils.get_data(db, 'my_first_data_name')
        self.fields['field2'].initial = my_db_utils.get_data(db, 'my_second_data_name')

    def clean(self):
        cleaned_data = super(MyGeniusForm, self).clean()                

       # do your custom validation here

        return cleaned_data

    def save(self):
        db = my_db_utils.MyDBUtil()
        my_db_utils.set_data(db, 'my_first_data_name', self.cleaned_data['field1'])
        my_db_utils.set_data(db, 'my_second_data_name', self.cleaned_data['field2'])



4. And in your views.py:

from django.shortcuts import render_to_response, redirect
from django.template import RequestContext
from MyGeniusProject.MyGeniusApp.forms import MyGeniusForm

def home(request):
    if request.method == 'POST':
        my_form = MyGeniusForm(request.POST, request.FILES)
        if my_form.is_valid():
            my_form.save()
            return redirect('home')

    else:
        my_form = MyGeniusForm()

    return render_to_response('home.html', locals(), context_instance=RequestContext(request))



That's it!