python 利用sqlalchemy+pymsql 操作mysql，实现创建表，新增查询修改数据

python 利用sqlalchemy+pymsql 操作mysql，实现创建表，新增查询修改数据
首先安装mysql ，创建myclass数据库
pip install pymsql和pip install sqlalchemy

#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   sql.py    
@data    ：2021/7/15 10:23
@Desciption :
@Version  :
@License : 
'''
# HOST = 'localhost'
# PORT = 3306
# USERNAME = 'root'
# PASSWORD = '123456'
# DB = 'myclass'

# dialect + driver://username:passwor@host:port/database
DB_URI = f'mysql+pymysql://root:123456@localhost:3306/myclass'

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker


engine = create_engine(DB_URI)
Base = declarative_base(engine)  # SQLORM基类
#创建会话
session = sessionmaker(engine)()  # 构建session对象

#表类
class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    sex = Column(String(10))


class Devcode(Base):
    __tablename__ = 'devcode'  # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    dev = Column(String(50))
    ip = Column(String(50))
    xy = Column(Integer)

Base.metadata.create_all()  # 将模型映射到数据库中

#新增数据
student = Student(name='Tony', age=18, sex='male')  # 创建一个student对象
devcode  = Devcode(dev='123', ip='182.168.1.20', xy=50)  # 创建一个student对象
session.add(student)  # 添加到session
session.add(devcode)  # 添加到session
session.commit()  # 提交到数据库


#查询数据
item_list = session.query(Devcode).all()
print(item_list)
for item in item_list:
    print(item.dev, item.ip)


#修改数据
# 修改Tony的age为22
session.query(Student).filter(Student.name == 'Tony').update({'age': 22})
session.commit()

item = session.query(Student.name, Student.age).filter(Student.name == 'Tony').first()
print(item)