使用SQLAlchemy

参考：https://www.liaoxuefeng.com/wiki/0014316089557264a6b348958f449949df42a6d3a2e542c000/0014320114981139589ac5f02944601ae22834e9c521415000

# coding: utf-8
import pymysql
pymysql.install_as_MySQLdb()
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer

Base = declarative_base()

# 定义User对象:
class BaaUser(Base):
    # 表的名字:
    __tablename__ = 'baa_user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    age = Column(Integer)
    work=Column(String(100))
    def __str__(self):
        return 'User(id={}, name={}, age={}, work={})'.format(self.id, self.name, self.age, self.work)

#连接数据库
def getEngine():
    engine = create_engine('mysql://root:123456@127.0.0.1/baa?charset=utf8')
    #print(engine)
    return engine

def getSession():
    engine = getEngine()
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    return session

#创建
def createUser():
    usr = BaaUser(id='05', name='ts', age=35, work="teacher")
    # session.add(usr)
    session=getSession()
    session.add(usr)

    try:
        session.commit()
    except Exception as e:
        session.rollback()
        raise e

#查询
def getUser():
    # 创建Session:
    session = getSession()
    # 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
    user = session.query(BaaUser).filter(BaaUser.id == '03').one()
    # 打印类型和对象的name属性:
    print('type:', type(user))
    print('name:', user.name)
    # 关闭Session:
    session.close()

# createUser()
getUser()