horizon对sql表的增删改查操作

首先根据horizon/doc/source/tutorials/dashboard.rst新加目录结构mydashboard->mypanel
一,从数据库中取数据，显示在mypanel的index页面
1.tables.py

  class InstancesTable(tables.DataTable): 
   定义index页面的字段为数据库里表的字段 
   这里定义了，name、age字段   （数据库中的id字段是自增的主键）

2.urls.py

  url(r'^$', views.IndexView.as_view(), name='index'),

3.views.py

from openstack_dashboard.dashboards.mydashboard.testpanel import tabs as mydashboard_tabs
  class IndexView(tabs.TabbedTableView):
      tab_group_class =mydashboard_tabs.YourpanelTabs
      template_name = 'mydashboard/mypanel/index.html'

4.tabs.py

from openstack_dashboard.dashboards.mydashboard.mypanel import usemysql  
  class YourpanelTabs(tabs.TabGroup):  
      slug = "yourpanel_tabs"  
      tabs = (UsersTab,)  //需要修改
      tabs = (InstanceTab,BooksTab,)  
      sticky = True  
  新加UsersTab
   class UsersTab(tabs.TableTab):
    name = _("Users Tab")
    slug = "users_tab"
    table_classes = (tables.InstancesTable,)
    template_name = ("horizon/common/_detail_table.html")
    preload = False
    def has_more_data(self, table):
        return []
    def get_instances_data(self):
        sql=usemysql.sql()
        instances=sql.list()
        return instances

5.新加usemysql.py文件，在这里对数据库进行操作

import MySQLdb
from openstack_dashboard import api


class sql():
    db = MySQLdb.connect(user='', db='', passwd='', host=' ')
    cursor = db.cursor()
    def list(self):
        self.cursor.execute('SELECT * FROM users ORDER BY id')
        myarr=self.cursor.fetchall()
        aa=[]
        for i in range(len(myarr)):
            aa.append(api.nova.Server({},False))  //要使从数据库取得的数据显示在mypanel的表格中，数据必须符合horizon定义的格式
            aa[i].id=myarr[i][0]
            aa[i].name=myarr[i][0+1]
            aa[i].age=myarr[i][0+2]
        instances=aa
        return instances

二，删除user
1.tables.py

from openstack_dashboard.dashboards.mydashboard.mypanel import usemysql
from openstack_dashboard import policy
from django.utils.translation import ungettext_lazy
class InstancesTable(tables.DataTable): 
    class Meta: 
         row_actions = (DeleteUser,)   //需要新加

添加DeleteUser类
class DeleteUser(policy.PolicyTargetMixin, tables.DeleteAction):
    @staticmethod
    def action_present(count):
        return ungettext_lazy(
            u"Delete User",
            u"Delete Users",
            count
        )
    @staticmethod
    def action_past(count):
        return ungettext_lazy(
            u"Deleted User",
            u"Deleted Users",
            count
        )
    def allowed(self, request, datum):
        return True
    def delete(self, request, obj_id):
        sql=usemysql.sql()
        sql.delete(obj_id)
        return []

2.usemysql.py新加如下内容

class sql():
    def delete(self,obj_id):
        self.cursor.execute("DELETE FROM users WHERE id = '"+obj_id+"'")
        self.db.commit()

三，增加user
1.tables.py

class InstancesTable(tables.DataTable):
      class Meta:  
          table_actions = (CreateUser,)
  新加CreateUser 类
  class CreateUser(tables.LinkAction):
      name = "User"
      verbose_name = _("Create User")
      url = "horizon:mydashboard:mypanel:create"
      classes = ("ajax-modal",)
      icon = "plus"
      def allowed(self, request, instance=None):
          return True

2.urls.py

 url(r'^create/$', views.CreateUserView.as_view(), name='create'),

3.views.py新加CreateUserView类

from horizon import forms
from openstack_dashboard.dashboards.mydashboard.mypanel import forms as mydashboard_forms
from django.utils.translation import ugettext_lazy as _
from django.core.urlresolvers import reverse_lazy
  class CreateUserView(forms.ModalFormView):  
    form_class = mydashboard_forms.CreateUser
    form_id = "create_image_form"
    modal_header = _("Create An User")
    submit_label = _("Create User")
    submit_url = reverse_lazy("horizon:mydashboard:mypanel:create")  
    template_name = 'mydashboard/mypanel/create.html'  
    context_object_name = 'user'
    success_url = reverse_lazy("horizon:mydashboard:mypanel:index")  
    page_title = _("Create An User")
    def get_initial(self):
        initial = {}
        for name in [
            'name',
            'age',
        ]:
            tmp = self.request.GET.get(name)
            if tmp:
                initial[name] = tmp
        return initial

4.forms.py 新加CreateUser类

from openstack_dashboard.dashboards.mydashboard.mypanel import usemysql
from django.utils.translation import ugettext_lazy as _
  class CreateUser(forms.SelfHandlingForm):  
    id = forms.CharField(label=_("Person ID"),  
                                  widget=forms.HiddenInput(),  
                                  required=False)
    name = forms.CharField(max_length=255, label=_("Person Name"))
    age = forms.CharField(max_length=255, label=_("Person Age"))

    def __init__(self, request, *args, **kwargs):
        super(CreateUser, self).__init__(request, *args, **kwargs)
    def handle(self,request, data):
        sql=usemysql.sql()
        sql.create(data)
        return [] 

5.usermysql.py新加create方法

def create(self,data):
        aa="insert into users(name,age) values('"+data['name']+"','"+data['age']+"')"
        self.cursor.execute(aa) 
        self.db.commit() 

6.在templates/mypanel下新加create.html与_create.html
create.html

 {% extends 'base.html' %}
    {% load i18n %}
    {% block title %}{% trans "Create An User" %}{% endblock %}
    {% block main %}
    {% include 'project/images/images/_create.html' %}
    {% endblock %}
    _create.html
    {% extends "horizon/common/_modal_form.html" %}
    {% load i18n %}
    {% block ng_controller %}ImageFormController as ctrl{% endblock %}
    {% block form_attrs %}enctype="multipart/form-data"{% endblock %}
    {% block modal-body-right %}
      <h3>{% trans "Description:" %}</h3>
    {% endblock %}

四、修改user
1.tables.py

from horizon import forms
from openstack_dashboard import api
 class InstancesTable(tables.DataTable):  
    name = tables.Column("name", verbose_name=_("Name"),form_field=forms.CharField(max_length=64),update_action=UpdateCell)  
    age = tables.Column("age", verbose_name=_("Age1"),form_field=forms.CharField(max_length=64),update_action=UpdateCell)  
    在字段后面新加：form_field=forms.CharField(max_length=64),update_action=UpdateCell
  class Meta:
      row_class = UpdateRow   //新加

新加UpdateCell与UpdateRow
class UpdateRow(tables.Row):  
    ajax = True  
    def get_data(self, request, id):  
        user=api.nova.Server({},False)
        user.id=id
        return user  
class UpdateCell(tables.UpdateAction):  
    def allowed(self, request, instance, cell):  
        policy_rule = (("identity", "identity:update_project"),)  
        return True  
    def update_cell(self, request, datum, id,cell_name, new_value):  
        aa=datum
        sql=usemysql.sql()   
        sql.online(id,cell_name,new_value)
        return True  

2.usemysql.py 新加online函数

 def online(self,id,cell_name,new_value):
        if cell_name=='name':
            self.cursor.execute("update users set name='"+new_value+"' where id='"+id+"'")
        elif cell_name=='age':
            self.cursor.execute("update users set age='"+new_value+"' where id='"+id+"'")
        self.db.commit()