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!
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!
Comments
Post a Comment