tornado 数据库

tornado+pymysql数据库操作

import tornado.web
import tornado.ioloop
import tornado.options
import json
import os
import pymysql

tornado.options.define('port',type=int,default=8005,help='服务器端口')

db=pymysql.Connect(host='127.0.0.1',port=3306,user='root',password='mysql123',db='itcast')
cursor=db.cursor()

class IndexHandler(tornado.web.RequestHandler):
    def get(self):
        uid=self.get_argument('uid')
        sql='select ui_name,ui_mobile,hi_name,hi_address,hi_price from it_user_info inner join it_house_info on ui_user_id=hi_user_id where ui_user_id=%s'
        print(sql)
        try:
            cursor.executemany(sql,[(uid,)])  #防止SQL注入
            data=cursor.fetchall()
            # db.close()   #别关，不然只能执行一次get
            print(data)
        except Exception as e:
            print(e)
            return self.write({'errono':1,'errmsg':'db error'})
        houses=[]
        if data:
            for l in data:
                house={
                    'uname': l[0],
                    'umobile': l[1],
                    'hname':l[2],
                    'haddress':l[3],
                    'hprice':l[4]
                }
                houses.append(house)
        self.write({'errono':0,'errmsg':'ok','data':houses})

    def post(self):
        name=self.get_argument('username')
        password=self.get_argument('password')
        mobile=self.get_argument('mobile')
        sql='insert into it_user_info(ui_name,ui_password,ui_mobile) values(%s,%s,%s)'   #防止SQL注入
        # sql='insert into it_user_info(ui_name,ui_password,ui_mobile) values("{}","{}","{}")'.format(name,password,mobile)  #导致SQL注入
        print(sql)
        try:
            '''
            # user_id=Application().cusor.execute(sql)
            # user_id=Application().cusor.execute(sql,name=name,password=password,mobile=mobile)
            # Application().db.commit()
            # Application().db.close()  
            '''

            # db.cursor().execute(sql)  #导致SQL注入
            cursor.executemany(sql,[(name,password,mobile)])   # 执行SQL，并返回受影响行数（使用pymysql的参数化语句防止SQL注入）
            db.commit()
            # db.close()   #别关，不然只能执行一次post
        except Exception as e:
            print(e)
        else:
            self.write('hello')


class Application(tornado.web.Application):
    def __init__(self,*args,**kwargs):
        super().__init__(*args,**kwargs)
        '''Application这个类是初始化一些全局变量，按照道理说里边的self.db 也应该能够被其他类或者派生类调用的，但是db这个属性就是不行无奈只好创建了一个全局的db句柄'''
        # self.db=pymysql.Connect(host='127.0.0.1',port=3306,user='root',password='mysql123',db='itcast')
        # self.cusor=self.db.cursor()


if __name__ == '__main__':
    tornado.options.parse_command_line()

    current_path = os.path.dirname(__file__)

    settings=dict(
        static_path=os.path.join(current_path,'statics')  #通过向web.Application类的构造函数传递一个名为static_path的参数来告诉Tornado从文件系统的一个特定位置提供静态文件
        ,template_path=os.path.join(current_path,'templates')
        ,debug=True)

    app=Application(
        [(r'/',IndexHandler),
         ]
        ,**settings)

    app.listen(tornado.options.options.port)
    tornado.ioloop.IOLoop().current().start()