Tornado之ORM（sqlalchemy）

/connect.py

#### ORM  对象关系映射  (只是一个概念)
### Python里面使用非常广的orm ： SQLAlchemy 连接数据库

#导入模块
from sqlalchemy import create_engine

#数据库数据
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'mydb'
USERNAME = 'xps'
PASSWORD = 'qwe123'

##连接数据连接 URL
Db_url = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(
    USERNAME,
    PASSWORD,
    HOSTNAME,
    PORT,
    DATABASE,

)
## 连接数据库
engine = create_engine(Db_url)

##创建Moudle

## 表
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(engine)  ####基类


##增删改查
#创建会话
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
session = Session()  #实例



##测试
if __name__ == "__main__":
    conection = engine.connect()
    result = conection.execute('select 1')
    print(result.fetchone())

user_modules.py

from datetime import datetime
from sqlalchemy import Column, Integer, String, DateTime,Boolean
from connect import Base

#####我们用类来表示数据库里面的表！！！
class User(Base):
    __tablename__ = 'user'  ##表名字
    id = Column(Integer, primary_key=True,autoincrement=True)
    username = Column(String(20))
    password = Column(String(50))
    creatime = Column(DateTime, default=datetime.now)
    _locked = Column(Boolean,default=False,nullable=False)

    def __repr__(self):    ##
        return """<User(id=%s,username=%s,password=%s,creatime=%s,_locked=%s)>

        """%(
            self.id,
            self.username,
            self.password,
            self.creatime,
            self._locked
        )


##创建表
if __name__ =="__main__":
    Base.metadata.create_all()    ##去数据库里面创建所有的表

/*test.py*

from connect import session
from User_moudules import User

##增
def add_user():
    #person = User(username = 'xps',password='123')
    #session.add(person)  #增加一条数据
    session.add_all([    #增加多条数据
        User(username = 'xpss',password='123'),
        User(username = 'xpss',password='123'),
        User(username = 'xpsss',password='123'),
    ])
    session.commit() #提交



##查
def search__user():
    rows = session.query(User).all()  ###查询所有
    #rows = session.query(User).first()  ###查询一条
    print(rows) ##打印出的是内存地址,  重写__repr__之后就可以直接读了

##改
def update_user():
    rows = session.query(User).filter(User.username == 'xpss').update({User.password:1}) ##字典
    session.commit()

##删除
def delete_user():
    rows = session.query(User).filter(User.username =='xpss')[0]
    print(rows)
    session.delete(rows)   #删除第一条记录
    #rows.deleted()   #批量删除
    session.commit()

def chaxun():
    row = session.query(User).filter(User.username == 'xpss')
    for i in row:
        print(i)



if __name__ =="__main__":
    add_user()
    search__user()
    update_user()
    delete_user()
    #chaxun()