tornado一起来-动态数据(mysql)

前面有个mongo版的简单实现了增删改查功能。

但是，我一直用的mysql。怎么能没有个mysql的呢！

文件基本和上篇mongodb一致。

代码走起

main.py

# tornado 数据库（增删改查） + 模版  （mysql）
import os.path
import tornado.httpserver
import tornado.ioloop
import tornado.options
import tornado.web
import pymysql
import re


from tornado.options import define, options

define("port", default=8000, help="run on the given port", type=int)


class Application(tornado.web.Application):
    def __init__(self):
        handlers = [(r'/', RecommendedHandler), (r"/edit/([0-9Xx\-]+)", BookEditHandler),(r"/add", BookEditHandler), (r"/del/([0-9Xx\-]+)", BookDelHandler)]
        settings = dict(
            template_path=os.path.join(os.path.dirname(__file__), "templates"),
            static_path=os.path.join(os.path.dirname(__file__), "static"),
            ui_modules={"Book": BookModule},
        )
        conn = pymysql.connect(host="localhost", user="root", passwd="root", db="bookstore",cursorclass=pymysql.cursors.DictCursor)
        self.cursor = conn.cursor()
        self.conn = conn
        tornado.web.Application.__init__(self, handlers, **settings)


class RecommendedHandler(tornado.web.RequestHandler):
    def get(self):
        self.application.cursor.execute("select * from book")
        books = self.application.cursor.fetchall()
        self.render(
            "index.html",
            page_title="Burt's Books | Recommended Reading",
            header_text="Recommended Reading",
            books=books
        )


class BookEditHandler(tornado.web.RequestHandler):
    def get(self, isbn=None):
        book = dict()
        if isbn:
            self.application.cursor.execute("select * from book WHERE isbn= %s ",isbn)
            book = self.application.cursor.fetchone()
            print(book)
        self.render("book_edit.html",
                    page_title="Burt's Books",
                    header_text="Edit book",
                    book=book)

    def post(self, isbn=None):
        import time
        book_fields = ['isbn', 'title', 'subtitle', 'image', 'author',
                       'date_released', 'description']
        #coll = self.application.db.books
        book = dict()


        for key in book_fields:
            book[key] = self.get_argument(key, None)

        if isbn:
            #book = dict()
            #coll.save(book)

            sql_prefix = ['UPDATE  book  set ']
            sql_suffix = [' where isbn = %s']
            data = []
            for i, k in enumerate(book):
                data.append("%s = '%s'" % (k,re.sub("'", r"\'", book[k])))          #增加过滤单引号

            sql_prefix.append(','.join(data))
            sql_prefix.extend(sql_suffix)
            sql = ''.join(sql_prefix)
            print(sql)

            self.application.cursor.execute(sql, isbn)
            self.application.conn.commit()

        else:
            book['date_added'] = int(time.time())
            sql_prefix = ['insert into book (']
            sql_suffix = [') values(']
            data = []
            for i, k in enumerate(book):
                sql_prefix.append(', %s' % k if i > 0 else k)
                sql_suffix.append(',%s'if i > 0 else '%s')
                data.append(re.sub("'", r"\'", book[k]))      #增加过滤单引号
            sql_suffix.append(')')
            sql_prefix.extend(sql_suffix)
            sql = ''.join(sql_prefix)
            print(sql)
            self.application.cursor.execute(sql,tuple(data))
            self.application.conn.commit()

        self.redirect("/")

class BookDelHandler(tornado.web.RequestHandler):
    def get(self, isbn):
         self.application.cursor.execute("delete  from book WHERE isbn= %s ", isbn)
         self.application.conn.commit()
         self.redirect("/")


class BookModule(tornado.web.UIModule):
    def render(self, book):
        print(book)
        return self.render_string('modules/book.html', book=book)


if __name__ == '__main__':
    tornado.options.parse_command_line()
    http_server = tornado.httpserver.HTTPServer(Application())
    http_server.listen(options.port)
    tornado.ioloop.IOLoop.instance().start()

其他的就不贴了。

新建bookstore数据库

CREATE TABLE `book` (
  `isbn` varchar(255) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `subtitle` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `author` varchar(255) DEFAULT NULL,
  `date_released` varchar(11) DEFAULT NULL,
  `description` text,
  `date_added` int(11) DEFAULT NULL,
  PRIMARY KEY (`isbn`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

所有文件 https://pan.baidu.com/s/1nv3jl01 密码：y9qw