azure-blob2cdn

pymysql 同步 mysql

python + mysql —-> pymysql

'''
数据库连接处理
1. 通过程序操作数据库会自动开启事务
2， 如果数据表不支持事务则执行语句后立即生效
3. 如果数据表支持事务，则需要提交才能修改，否则默认结束为回滚
'''
import pymysql

# 连接数据库  连接本机库可以不写host port
db = pymysql.connect(
    host="localhost",
    port=3306,
    user='root',
    password='200371',
    database='stu',
    charset='utf8'
)

# 生成一个游标：调用sql语句得到执行结果集的对象
cur = db.cursor()

# 数据库写操作  增删改
# 执行sql语句  class -> InnoDB  hobby -> MyISAM
try:
    sql = 'update class set score=100 where id=2;'
    cur.execute(sql)
    sql = 'delete from class where socre<60;'
    cur.execute(sql)
    db.commit() # 事务提交
except Exception as e:
    print(e)
    db.rollback() # 事务回滚

# 关闭游标和数据库连接
cur.close()
db.close()

SQLAlchemy ORM

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

engine = create_engine('mysql+pymysql://root:200371@localhost/stu', echo=True)
Base = declarative_base()

class Class(Base):
    __tablename__ = 'class'
    id = Column(Integer, primary_key=True)
    score = Column(Integer)

Session = sessionmaker(bind=engine)
session = Session()

# 更新
session.query(Class).filter_by(id=2).update({'score': 100})
# 删除
session.query(Class).filter(Class.score < 60).delete()
session.commit()

aiomysql 异步mysql

Tortoise ORM

python异步本版的ORM框架，以mysql为例子

pip install tortoise-orm[aiomysql]

import asyncio
from tortoise import Model, fields, Tortoise, run_async

class Class(Model):
    id = fields.IntField(pk=True)
    score = fields.IntField()

async def main():
    await Tortoise.init(
        db_url="mysql://root:200371@127.0.0.1:3306/stu",
        modules={"models": ["models"]}
    )
    await Tortoise.generate_schemas()
    # ORM事务处理
    try:
        async with Tortoise.get_connection("default").transaction():
            # update class set score=100 where id=2;
            await Class.filter(id=2).update(score=100)
            # delete from class where score<60;
            await Class.filter(score__lt=60).delete()
            # 事务：commit在离开async with块时自动完成
    except Exception as e:
        print("数据库操作异常:", e)
        # 回滚由transaction管理
    await Tortoise.close_connections()
if __name__ == "__main__":
    run_async(main())