Tornado 操作数据库

2022-09-14 19:02:57 浏览数 (2)

一、概述

与Django或者Flask相比 Tornado没有自带的ORM 对于数据库需要去适配 我们使用MySQL数据库

二、torndb

说明

在Tornado3.0版本一起 提供tornado.database模块用来操作MySQL数据库 而从3.0版本开始 此模块就被独立出来 作为torndb包单独提供 torndb只是对MySQLdb的简单封装 不支持python3（torndb是支持python2.x.x版本 不支持Python3 先正常安装 然后在进行修改

更改步骤：

  • 先正常安装 pip install torndb
  • pip show torndb 查看安装的路径
  • 把lucky老师的torndb替换到我们torndb安装的路径的位置

连接初始化

我们需要在应用启动时创建一个数据库连接实例 供各个RequestHandler进行使用 我们可以在构造Application的时候 创建一个数据库实例 并作为其属性 而RequestHandler可以通过self.application获取其属性 进而操作数据库实例

代码如下：

代码语言：javascript复制
<span class="hljs-keyword">import</span> tornado.web
<span class="hljs-keyword">import</span> tornado.ioloop
<span class="hljs-keyword">import</span> torndb


<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">IndexHandler</span><span class="hljs-params">(tornado.web.RequestHandler)</span>:</span>
    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">get</span><span class="hljs-params">(self)</span>:</span>
        self.write(<span class="hljs-string">'操作MySQL数据库'</span>)


<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Application</span><span class="hljs-params">(tornado.web.Application)</span>:</span>
    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">__init__</span><span class="hljs-params">(self,handlers,**settings)</span>:</span>
        super(Application, self).__init__(handlers,**settings)

        <span class="hljs-comment"># 创建一个全局MySQL链接实例 供handler使用</span>
        self.db = torndb.Connection(
            host=<span class="hljs-string">'127.0.0.1'</span>,  <span class="hljs-comment"># 主机</span>
            database=<span class="hljs-string">'torndb'</span>,  <span class="hljs-comment"># 数据库名称</span>
            user=<span class="hljs-string">'lucky'</span>,  <span class="hljs-comment"># 用户名</span>
            password=<span class="hljs-string">'123456'</span>, <span class="hljs-comment"># 密码</span>
        )


<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">'__main__'</span>:
    app = Application([
        (<span class="hljs-string">r'/'</span>,IndexHandler),
    ],
        debug=<span class="hljs-keyword">True</span>,
        autoreload=<span class="hljs-keyword">True</span>
    )
    app.listen(<span class="hljs-number">8000</span>)
    tornado.ioloop.IOLoop.current().start()

创建操作使用的表

语句如下

代码语言：javascript复制
mysql> create table if not exists user(
    -> id int unsigned primary key auto_increment,
    -> username varchar(10) default 'lucky',
    -> age tinyint default 18,
    -> info varchar(100) default '个人简介'
    -> );

执行语句

  • execute(query) 返回最后一条自增字段值
  • execute_rowcount(query) 返回影响的行数

示例

execute的使用：

代码语言：javascript复制
<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">IndexHandler</span><span class="hljs-params">(tornado.web.RequestHandler)</span>:</span>
    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">get</span><span class="hljs-params">(self)</span>:</span>
        <span class="hljs-comment"># 执行语句</span>
        res = self.application.db.execute(<span class="hljs-string">"insert into user values(null,'lucky',18,'我是帅气的lucky老师')"</span>)
        print(res)
        self.write(<span class="hljs-string">'操作MySQL数据库'</span>)

execute_rowcount的使用：

代码语言：javascript复制
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">get</span><span class="hljs-params">(self)</span>:</span>
    <span class="hljs-comment"># 执行语句</span>
    res = self.application.db.execute_rowcount(<span class="hljs-string">"select * from user"</span>)
    print(res)
    self.write(<span class="hljs-string">'操作MySQL数据库'</span>)

查询语句

  • get(query) 返回单行结果或None，如果出现多行则报错 返回值是一个字典的对象 支持对象属性的获取和关键字索引
  • query(query) 返回多行结果 torndb.Row的列表

示例

get的使用：

代码语言：javascript复制
<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">IndexHandler</span><span class="hljs-params">(tornado.web.RequestHandler)</span>:</span>
    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">get</span><span class="hljs-params">(self)</span>:</span>
        <span class="hljs-comment"># 查询语句</span>
        res = self.application.db.get(<span class="hljs-string">"select * from user where id=1"</span>)
        print(res[<span class="hljs-string">'info'</span>])
        print(res.username)
        self.write(<span class="hljs-string">'操作MySQL数据库'</span>)

query的使用：

代码语言：javascript复制
<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">IndexHandler</span><span class="hljs-params">(tornado.web.RequestHandler)</span>:</span>
    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">get</span><span class="hljs-params">(self)</span>:</span>
        <span class="hljs-comment"># 查询语句</span>
        res = self.application.db.query(<span class="hljs-string">"select * from user"</span>)
        print(res)
        self.write(<span class="hljs-string">'操作MySQL数据库'</span>)

三、sqlalchemy

安装sqlalchemy和pymysql

  • pip install sqlalchemy
  • pip install pymysql

连接数据库

代码语言：javascript复制
<span class="hljs-keyword">from</span> sqlalchemy <span class="hljs-keyword">import</span> create_engine


config = {
    <span class="hljs-string">'HOST'</span>: <span class="hljs-string">''</span>,
    <span class="hljs-string">'USERNAME'</span>: <span class="hljs-string">''</span>,
    <span class="hljs-string">'PASSWORD'</span>: <span class="hljs-string">''</span>,
    <span class="hljs-string">'PORT'</span>: <span class="hljs-string">''</span>,
    <span class="hljs-string">'DATABASE'</span>: <span class="hljs-string">''</span>,
    <span class="hljs-string">'PARAMS'</span>: <span class="hljs-string">''</span>
}


DB_URL = <span class="hljs-string">'mysql pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}?{PARAMS}'</span>

engine = create_engine(DB_URL.format(**config))



<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">'__main__'</span>:
    conc = engine.connect()
    result = conc.execute(<span class="hljs-string">'select 1'</span>)
    print(result.fetchone())

如果连接成功，则控制台会出现（1，）的结果

数据库建模

建立base类

代码语言：javascript复制
<span class="hljs-keyword">from</span> sqlalchemy <span class="hljs-keyword">import</span> create_engine
<span class="hljs-keyword">from</span> sqlalchemy.ext.declarative <span class="hljs-keyword">import</span> declarative_base


config = {
    <span class="hljs-string">'HOST'</span>: <span class="hljs-string">''</span>,
    <span class="hljs-string">'USERNAME'</span>: <span class="hljs-string">''</span>,
    <span class="hljs-string">'PASSWORD'</span>: <span class="hljs-string">''</span>,
    <span class="hljs-string">'PORT'</span>: <span class="hljs-string">''</span>,
    <span class="hljs-string">'DATABASE'</span>: <span class="hljs-string">''</span>,
    <span class="hljs-string">'PARAMS'</span>: <span class="hljs-string">''</span>
}

<span class="hljs-comment"># 连接数据连接 URL</span>
DB_URL = <span class="hljs-string">'mysql pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}?{PARAMS}'</span>
<span class="hljs-comment"># 连接数据库</span>
engine = create_engine(DB_URL.format(**DB_CONFIG))
<span class="hljs-comment"># 模型类基类</span>
Base = declarative_base(engine)

使用base类并建立表

代码语言：javascript复制
<span class="hljs-keyword">from</span> settings <span class="hljs-keyword">import</span> Base
<span class="hljs-keyword">from</span> sqlalchemy <span class="hljs-keyword">import</span> Column, Integer, String, DateTime, Boolean
<span class="hljs-keyword">from</span> datetime <span class="hljs-keyword">import</span> datetime


<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">User</span><span class="hljs-params">(Base)</span>:</span>
    __tablename__ = <span class="hljs-string">'user'</span>  <span class="hljs-comment"># 指定表名</span>
    id = Column(Integer, autoincrement=<span class="hljs-keyword">True</span>, primary_key=<span class="hljs-keyword">True</span>)
    username = Column(String(<span class="hljs-number">20</span>))
    password = Column(String(<span class="hljs-number">20</span>))
    create_time = Column(DateTime, default=datetime.now())
    is_login = Column(Boolean, default=<span class="hljs-keyword">False</span>, nullable=<span class="hljs-keyword">False</span>)




<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">'__main__'</span>:
    Base.metadata.create_all()  <span class="hljs-comment"># 创建表</span>

创建的表名必须为该类的__tablename__属性，Colum新建一个字段，然后给Colum传参来进行约束

Column的常用参数：

  • default: 默认值，可以传一个函数体，default的值等于这个函数体执行后返回的值
  • nullable：是否可为空
  • primary_key：是否为主键
  • unique：是否唯一
  • autoincrement：是否自增长
  • onupdate：更新的时候执行的函数，和default一样，可以传一个函数体
  • name：该属性在数据库中的字段的映射，默认是属性名

常用的数据类型

  • Integer：整形
  • Float：浮点类型
  • Boolean：布尔
  • DECIMAL：定点类 DECIMAL第一个参数为整数位的个数，第二位参数为小数位的个数
  • Enum：枚举类型 Enum可以借助python3自带的enum包来实现更加简便
  • Date：传递datetime.date()
  • DateTime：传递datetime.datetime()
  • Time：传递datetime.time() 进去
  • String：字符串型, 使用时需要制定长度
  • Text：文本类型
  • LONGTEXT：长文本类型

简单的增删改查

增

代码语言：javascript复制
<span class="hljs-keyword">from</span> sqlalchemy <span class="hljs-keyword">import</span> create_engine
<span class="hljs-keyword">from</span> settings <span class="hljs-keyword">import</span> DB_CONFIG
<span class="hljs-keyword">from</span> sqlalchemy.ext.declarative <span class="hljs-keyword">import</span> declarative_base
<span class="hljs-keyword">from</span> sqlalchemy.orm <span class="hljs-keyword">import</span> sessionmaker

<span class="hljs-comment"># 连接数据连接 URL</span>
DB_URL = <span class="hljs-string">'mysql pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}?{PARAMS}'</span>
<span class="hljs-comment"># 连接数据库</span>
engine = create_engine(DB_URL.format(**DB_CONFIG))
<span class="hljs-comment"># 模型基类</span>
Base = declarative_base(engine)
<span class="hljs-comment"># 增删改查 创建会话</span>
Session = sessionmaker(engine)
<span class="hljs-comment"># 实例</span>
session = Session()

与上面不同的是,新建了一个Session对象，然后将这个对象实例化，接下来的数据库操作都用到这个实例对象来操作

表结构

代码语言：javascript复制
<span class="hljs-keyword">from</span> config <span class="hljs-keyword">import</span> Base
<span class="hljs-keyword">from</span> sqlalchemy <span class="hljs-keyword">import</span> Column, Integer, String, DateTime, Boolean
<span class="hljs-keyword">from</span> datetime <span class="hljs-keyword">import</span> datetime

<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">User</span><span class="hljs-params">(Base)</span>:</span>
    __tablename__ = <span class="hljs-string">'user'</span>
    id = Column(Integer, primary_key=<span class="hljs-keyword">True</span>, autoincrement=<span class="hljs-keyword">True</span>)
    username = Column(String(<span class="hljs-number">20</span>))
    password = Column(String(<span class="hljs-number">20</span>))
    create_time = Column(DateTime, default=datetime.now())
    is_login = Column(Boolean, default=<span class="hljs-keyword">False</span>, nullable=<span class="hljs-keyword">False</span>)

添加

添加一条

代码语言：javascript复制
<span class="hljs-keyword">from</span> models <span class="hljs-keyword">import</span> User
<span class="hljs-keyword">from</span> config <span class="hljs-keyword">import</span> session

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">add</span><span class="hljs-params">()</span>:</span>
    user = User(username=<span class="hljs-string">'lucky'</span>, password=<span class="hljs-string">'123456'</span>)
    session.add(user)
    session.commit()


<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">'__main__'</span>:
    add()

添加多条

代码语言：javascript复制
<span class="hljs-keyword">from</span> models <span class="hljs-keyword">import</span> User
<span class="hljs-keyword">from</span> config <span class="hljs-keyword">import</span> session

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">add</span><span class="hljs-params">()</span>:</span>

    session.add_all([
        User(username=<span class="hljs-string">'lucky1'</span>, password=<span class="hljs-string">'123456'</span>),
        User(username=<span class="hljs-string">'lucky2'</span>, password=<span class="hljs-string">'123456'</span>),
        User(username=<span class="hljs-string">'lucky3'</span>, password=<span class="hljs-string">'123456'</span>),
    ])
    session.commit()


<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">'__main__'</span>:
    add()

查

代码语言：javascript复制
<span class="hljs-keyword">from</span> models <span class="hljs-keyword">import</span> User
<span class="hljs-keyword">from</span> config <span class="hljs-keyword">import</span> session

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">search</span><span class="hljs-params">()</span>:</span>
    session.query(User).all()  <span class="hljs-comment"># 查找所有</span>
    result = session.query(User).first().username  <span class="hljs-comment"># 查找第一个</span>
    result = session.query(User).filter(User.username==<span class="hljs-string">'lucky'</span>).first().password
    print(result)



<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">'__main__'</span>:
    search()

改

更改的数据以字典的键值对的形式传入

代码语言：javascript复制
<span class="hljs-keyword">from</span> models <span class="hljs-keyword">import</span> User
<span class="hljs-keyword">from</span> config <span class="hljs-keyword">import</span> session


<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">update</span><span class="hljs-params">()</span>:</span>
    session.query(User).filter(User.username == <span class="hljs-string">'ivy'</span>).update(
        {
            User.username: <span class="hljs-string">'lucky_boy'</span>,
            User.password: <span class="hljs-string">'123654'</span>,
        }
    )
    session.commit()


<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">'__main__'</span>:
    update()

删

代码语言：javascript复制
<span class="hljs-keyword">from</span> models <span class="hljs-keyword">import</span> User
<span class="hljs-keyword">from</span> config <span class="hljs-keyword">import</span> session


<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">delete</span><span class="hljs-params">()</span>:</span>
    result = session.query(User).filter(User.username == <span class="hljs-string">'lucky'</span>).first()

    session.delete(result)
    session.commit()


<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">'__main__'</span>:
    delete()

先查询结果，再将查询的结果删除，如果查询的结果为空，则删除会报错

linux 数据库 sqlalchemy sql 云数据库SQLServer

0 人点赞