tornado的ORM

 

---

• 安装sqlalchemy和pymysql
  • pip install sqlalchemy
  • pip install pymysql
• 连接数据库
  • 1 from sqlalchemy import create_engine 2  3  4  5 config = { 6     'HOST': '', 7     'USERNAME': '', 8     'PASSWORD': '', 9     'PORT': '',10     'DATABASE': '',11     'PARAMS': ''12 }13 14 15 DB_URL = 'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}?{PARAMS}'16 17 engine = create_engine(DB_URL.format(**config))18 19 20 21 if __name__ == '__main__':22     conc = engine.connect()23     result = conc.execute('select 1')24     print(result.fetchone())

    如果连接成功，则控制台会出现（1，）的结果
• 数据库建模
  • 建立base类
    1 from sqlalchemy import create_engine 2 from sqlalchemy.ext.declarative import declarative_base 3  4  5 config = { 6     'HOST': '', 7     'USERNAME': '', 8     'PASSWORD': '', 9     'PORT': '',10     'DATABASE': '',11     'PARAMS': ''12 }13 14 15 DB_URL = 'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}?{PARAMS}'16 17 engine = create_engine(DB_URL.format(**config))18 19 Base = declarative_base(engine)
  • 使用base类并建立表格
    1 from settings import Base 2 from sqlalchemy import Column, Integer, String, DateTime, Boolean 3 from datetime import datetime 4  5  6 class User(Base): 7     __tablename__ = 'user'  # 指定表名 8     id = Column(Integer, autoincrement=True, primary_key=True) 9     username = Column(String(20))10     password = Column(String(20))11     create_time = Column(DateTime, default=datetime.now())12     is_login = Column(Boolean, default=False, nullable=False)13 14 15 16 17 if __name__ == '__main__':18     Base.metadata.create_all()  # 创建表格

    创建的表名必须为该类的__tablename__属性，Colum新建一个字段，然后给Colum传参来进行约束
• 简单的增删改查
  • 增
    • 1 from sqlalchemy import create_engine 2 from settings import DB_CONFIG 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy.orm import sessionmaker 5  6  7 DB_URL = 'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}?{PARAMS}' 8  9 engine = create_engine(DB_URL.format(**DB_CONFIG))10 11 Base = declarative_base(engine)12 13 Session = sessionmaker(engine)14 15 session = Session()

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

    • 1 from config import Base 2 from sqlalchemy import Column, Integer, String, DateTime, Boolean 3 from datetime import datetime 4  5 class User(Base): 6     __tablename__ = 'user' 7     id = Column(Integer, primary_key=True, autoincrement=True) 8     username = Column(String(20)) 9     password = Column(String(20))10     create_time = Column(DateTime, default=datetime.now())11     is_login = Column(Boolean, default=False, nullable=False)12 13

      表结构

    • 1 from wechat import User 2 from config import session 3  4 def add(): 5     user = User(username='ivy', password='123456') 6     session.add(user) 7     session.commit() 8  9 10 if __name__ == '__main__':11     add()

      添加一条

    • 1 from wechat import User 2 from config import session 3  4 def add(): 5  6     session.add_all([ 7         User(username='ivy', password='123456'), 8         User(username='ivy1', password='123456'), 9         User(username='ivy2', password='123456'),10     ])11     session.commit()12 13 14 if __name__ == '__main__':15     add()

      添加多条
  • 查
    • 1 from wechat import User 2 from config import session 3  4 def search(): 5     session.query(User).all()  # 查找所有 6     result = session.query(User).first().username  # 查找第一个 7     result = session.query(User).filter(User.username=='ivy').first().password 8     print(result) 9 10 11 12 if __name__ == '__main__':13     search()
  • 改
    • 1 from wechat import User 2 from config import session 3  4  5 def update(): 6     session.query(User).filter(User.username == 'ivy').update( 7         { 8             User.username: 'bob', 9             User.password: '123654',10         }11     )12     session.commit()13 14 15 if __name__ == '__main__':16     update()

      更改的数据以字典的键值对的形式传入
  • 删
    • 1 from wechat import User 2 from config import session 3  4  5 def delete(): 6     result = session.query(User).filter(User.username == 'bob').first() 7  8     session.delete(result) 9     session.commit()10 11 12 if __name__ == '__main__':13     delete()

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