python通过sqlalchemy库操作mysql写入数据[O

ORM是什么,我这里就不说啦,相信在看的都应该已经清楚了,这里我直接上使用方法。

首先python自带的库是不支持orm的,所以这里我们需要安装sqlalchemy库进行支持,通过pip就可以安装了

pip install sqlalchemy
pip install pymysql

如果pymysql已经安装就不用装了,如果是使用其它类库连接mysql数据库,sqlalchemy也是支持的

这里是演示示例的目录结构:

sample_project
    - model
        base_model.py
        green_food_model.py
    - common
        db_helpers.py
    main.py

base_model.py (model基类,这里要不要创建一个类大家看情况决定了,大家也可以直接申明engine\base)

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine

# 实际业务可以封装到settings中去
DB_USER = root
DB_PASSWORD = root
DB_HOST = 'xxx.xxx.xxx.xxx'

class BaseModel:
    """
    DB_USER:数据库用户名
    DB_PASSWORD:数据库用户密码
    DB_HOST:数据库连接地址
    """
    # echo参数为False时,表示不打印sqlalchemy日志,True则打印 ( 建议设置为False关闭掉,不然会有非常多的日志信息 )
    engine = create_engine(f'mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}?charset=utf8mb4', echo=False)
    base = declarative_base()

green_food_model.py (业务数据表)

from .BaseModel import BaseModel
from sqlalchemy import Index, Column
from sqlalchemy import VARCHAR, Integer

class GreenFoodModel(BaseModel.base):
    __tablename__ = 'green_food'
    __table_args__ = (Index('index(id)', 'id'), {'comment': '绿色食品食材'})
    _id = Column(Integer, name='id', primary_key=True, autoincrement=True)
    area_name = Column(VARCHAR(18), comment='所在省')
    applicant_full_name = Column(VARCHAR(50), comment='申请人全称')
    industry_type_name = Column(VARCHAR(20), comment='行业类型')
    product_name = Column(VARCHAR(50), comment='产品名称')
    annual_output = Column(VARCHAR(10), comment='年产量(吨)')
    certificate_id = Column(VARCHAR(50), comment='证书编号')
    certificate_date_str = Column(VARCHAR(50), comment='证书有效期')

    def __init__(self, **items):
        for key in items:
            if hasattr(self, key):
                setattr(self, key, items[key])

db_helpers.py (就是靠它来操作数据库,这里我只是实现了写入数据)

from sqlalchemy.orm import sessionmaker
from model.base_model import BaseModel

class MysqlHelpers:
    def __init__(self,model):
        self.session = sessionmaker(bind=BaseModel.engine)()
        self.model = model
        self.model.metadata.create_all(BaseModel.engine)

    def write(self, items):
        try:
            data = self.model(**items)
            self.session.add(data)
            self.session.commit()
        except():
            self.session.rollback()
        finally:
            self.session.close() 

main.py (具体操作实例)

from model.green_food_model import GreenFoodModel
from utils.db_helpers import MysqlHelpers


if __name__ == '__main__':
    # 因为字段id是自增主键,所以这里可以不用传递id属性
    items = {
        'area_name ': 'xx省',
        'applicant_full_name ': 'xx人',
        'industry_type_name ': 'xx行业',
        'product_name ': 'xx产品',
        'annual_output ': '年产量',
        'certificate_id ': 'xx编号',
        'certificate_date_str': 'xx有效期'
    }
    db = MysqlHelpers(GreenFoodModel)
    db.write(items)