python录入数据_python通过sqlalchemy库操作mysql写入数据[ORM]

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)