python 操作 MySQL

基本增删改查操作

import pymysql
from sqlalchemy import create_engine

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

pymysql.install_as_MySQLdb()

# 连接数据库
engine = create_engine('mysql://root:123@localhost:3306/mess?charset=utf8')

# # 获取基类
Base = declarative_base()


class TestTable(Base):  # 继承基类
    __tablename__ = 'test_table'
    id = Column(Integer, primary_key=True)
    name = Column(String(255))


TestTable.metadata.create_all(engine)

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

# 新增
new_obj = TestTable(
    id=2,
    name='321'
)
session.add(new_obj)
session.commit()

# 查询
print(session.query(TestTable).get(1))
data_list = session.query(TestTable).filter(TestTable.id >= 5)

# 删除
session.delete(new_obj)
session.commit()

生成实体

1. 安装sqlacodegen

pip install  sqlacodegen 

2. 运行生成命令

sqlacodegen --outfile db.py --tables table1,table2 mysql+pymysql://root:passwd@127.0.0.1:3306/db_name