tornado八：数据库

 

tornado没有自带的ORM，对于数据库需要自己去适配。目前python3.6+tornado，还没有比较完善的驱动。

一、创建数据库操作类

import pymysql
# class MySQL():
class MyCustomSQL(object):
    def __init__(self, host, user, passwd, dbName):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.dbName = dbName
    def connect(self):
        self.db = pymysql.connect(self.host, self.user, self.passwd, self.dbName)
        self.cursor = self.db.cursor()
    def close(self):
        self.cursor.close()
        self.db.close()
    def get_one(self, sql):
        res = None
        try:
            self.connect()
            self.cursor.execute(sql)
            res = self.cursor.fetchone()
            self.close()
        except:
            print "faile to select"
        return res
    def get_all(self, sql):
        res = None
        try:
            self.connect()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()
        except:
            print "faile to select"
        return res
    def get_all_obj(self, sql, tableName, *args):
        resList = []
        fieldList = []
        if len(args) > 0:
            for item in args:
                fieldList.append(item)
        else:
            fieldSql = "select COLUMN_NAME from information_schema.COLUMNS where tabl_name=%s and table_schema=%s" % (
                tableName, self.dbName
            )
            fields = self.get_all(fieldSql)
            for item in fields:
                fieldList.append(item[0])

        # select
        res = self.get_all(sql)
        for item in res:
            obj = {}
            count = 0
            for x in item:
                obj[fieldList[count]] = x
                count += 1
            resList.append(obj)
        return resList


    def insert(self, sql):
        return self.__edit(sql)
    def update(self, sql):
        return self.__edit(sql)
    def delete(self, sql):
        return self.__edit(sql)
    def __edit(self, sql):
        count = 0
        try:
            self.connect()
            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except:
            print " faile execute sql"
            self.db.rollback()
        return count

 

二、链接数据库：

在应用app启动的时侯，创建一个数据库连接实例，供各个RequestHandler使用。

在RequestHandler中通过self.application.db操作数据库。

　　1.在config配置中，添加数据库配置

# 数据库配置
mysql = {
    "host": "127.0.0.1",
    "user": "root",
    "passwd": "123456",
    "dbName": "test"
}

　　2.在application路由中，建立数据库连接。使用前面数据操作类，实例化此数据操作类。

#application.py
        self.db = MyCustomSQL(config.mysql["host"],
                              config.mysql["user"],
                              config.mysql["passwd"],
                              config.mysql["dbName"])

 

三、业务--数据库操作　　

　　3.创建查询某业务的路由

　　4.创建此业务的模板文件　　

#路由
            # 数据库
            (r'/stu', index.StuHandler),
#stu.html
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <ul>
        {% for stu in stus %}
        <li>{{ stu["name"]---stu["age"]</li>
        {% end %}
    </ul>
</body>
</html>

 

　　5.创建此业务的视图处理函数Handler

class StuHandler(RequestHandler):
    def get(self, *args, **kwargs):
        sql = "select * from students"
        # stus = self.application.db.get_all_obj(
        #     sql, "students"
        # )
        sql = "select name,age from students"
        stus = self.application.db.get_all_obj(
            sql, "students", "name", "age"
        )
        print stus
        self.render('stu.html', stus=stus)

 

　　

四、封装ORM

怎么实现save, filter等数据库操作？

在以上数据库操作类的外层，再包装一层映射即可。

1.创建orm目录结构：

创建models.py，用于存放创建数据库与python类的关系类。

创建ORM包，用于存放外层映射包装的类。

2.实现外层封装

import tornado.web
from mycustomsql import MyCustomSQL

class ORM(tornado.web.RequestHandler):
    # 为了让ORM使用到application实例中的selb.db实例，继承自tornado.web.RequestHandler；就可以使用self.application.db操作数据库
    def save1(self):
        # insert into students (name, age) values ("jerry", 23)
        sql = "" # todo，实现sql取得上行的sql语句，且通用，完成ORM最后一步。
        self.application.db.insert(sql)
    def delete(self):
        pass
    def update(self):
        pass
    def all(self):
        pass
    def filter(self):
        pass

    def save(self):
        # insert into students (name, age) values ("jerry", 23)
        tableName = (self.__class__.__name__).lower()
        fieldsStr = valuesStr = "("
        for field in self.__dict__:
            fieldsStr += (field + ",")
            if isinstance(self.__dict__[field], str):
                valuesStr += ("'" + self.__dict__[field] + "',")
            else:
                valuesStr += (str(self.__dict__[field]) + ",")
        fieldsStr = fieldsStr[:len(fieldsStr) -1]  + ")"
        valuesStr = valuesStr[:len(valuesStr) -1] + ")"
        sql = "insert into " + tableName + " " + fieldsStr + " values " + valuesStr

        db = MyCustomSQL()
        db.insert(sql)

 

 

3.在models.py中，创建数据库与python类的关系类

from ORM.orm import ORM

class Stu(ORM):
    def __init__(self, name, age):
        self.name = name
        self.age = age

 

4.在业务处理视图Handler中，使用3.的python类操作数据库。

class StuHandler(RequestHandler):
    def get(self, *args, **kwargs):
        from models import Stu
        stu = Stu("jerry", 23)
        stu.save()
        self.render('ok')

 5.从application中去掉self.db

不再需要从applciation中调用self.db

 

posted on 2018-07-29 21:20  myworldworld  阅读(506)  评论(0)    收藏  举报

导航