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:


- 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:


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

+ define a class which stand for MyGeniusTable using SQLAlchemy module. Please read the article about using SQLAlchemy here:

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

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

+ 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 = ''
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,\

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

def __init__(self):

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):
return MyDBUtil.Session()

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

return value

def set_data(db, name, value):
session = db.get_session()
setting = session.query(MyGeniusTable).filter_by(name=name).first()
setting = Settings(name=name, value=value)
setting.value = value
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:

3. Create 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

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():
            return redirect('home')

        my_form = MyGeniusForm()

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

That's it!