python orm 框架中sqlalchemy用法实例详解

本文实例讲述了python orm 框架中sqlalchemy用法。分享给大家供大家参考，具体如下：

一.ORM简介

1. ORM(Object-Relational Mapping,对象关系映射):作用是在关系型数据库和业务实体对象之间做一个映射.

2. ORM优点:

向开发者屏蔽了数据库的细节,使开发者无需与SQL语句打交道,提高了开发效率;

便于数据库的迁移,由于每种数据库的SQL语法有差别,基于Sql的数据访问层在更换数据库时通过需要花费时间调试SQL时间,而ORM提供了独立于SQL的接口,ORM的引擎会处理不同数据库之间的差异,所以迁移数据库时无需更改代码.

应用缓存优化等技术有时可以提高数据库操作的效率.

3. SQLALchemy:是python中最成熟的ORM框架,资源和文档很丰富,大多数python web框架对其有很好的主持,能够胜任大多数应用场合,SQLALchemy被认为是python事实上的ORM标准.

二、代码

1.建表

"""Created on 19-10-22@author: apple@description:建表"""import pymysqlserver = '127.0.0.1'user = 'root'# devpassword = '123456'conn = pymysql.connect(server, user, password, database='DataSave') # 获取连接cursor = conn.cursor() # 获取游标# "**ENGINE=InnoDB DEFAULT CHARSET=utf8**"-创建表的过程中增加这条，中文就不是乱码# 创建表cursor.execute ("""CREATE TABLE if not exists lamp_result(  result_id INT NOT NULL auto_increment primary key,  product_number VARCHAR(100),  record_time VARCHAR(100),  lamp_color INT NOT NULL,  detect_result VARCHAR(100),  old_pic_path VARCHAR(100),  result_pic_path VARCHAR(100)  )  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4""")# 查询数据cursor.execute('SELECT * FROM lamp_result')row = cursor.fetchone()print(row)# cursor.execute("INSERT INTO user VALUES('%d', '%s','%s','%s','%s')" % ('xiaoming','qwe','ming','@163.com'))# 提交数据，才会写入表格conn.commit()# 关闭游标关闭数据库cursor.close()conn.close()

2. 数据存储

"""Created on 19-10-22@author: apple@requirement:Anaconda 4.3.0 (64-bit) Python3.6@description:数据存储"""from sqlalchemy.exc import SQLAlchemyErrorfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, String, Integer, create_enginefrom sqlalchemy.orm import sessionmakerBase = declarative_base()# 连接数据库# alter table students convert to character set utf8;conn = "mysql+pymysql://root:password@0.0.0.0:3306/DataSave"engine = create_engine(conn, encoding='UTF8', echo=False) # echo=True 打印日志# 创建session对象Session = sessionmaker(bind=engine)session = Session()# 数据库表模型ORMclass DataSaveSystem(Base):  """  员工自助信息采集系统  """  __tablename__ = 'lamp_result' # 定义表名  # 定义列名  result_id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)  product_number = Column(String(50), nullable=True)  record_time = Column(String(50), nullable=False)  lamp_color = Column(Integer, nullable=False)  detect_result = Column(String(100), nullable=False)  old_pic_path = Column(String(100), nullable=False)  result_pic_path = Column(String(100), nullable=False)  def __repr__(self):    """    引用该类别，输出结果    :return:    """    return str(self.__dict__)    # return '<detect_result:{}>'.format(self.detect_result)# 插入数据def insert_to_db(product_number=None, record_time=None, lamp_color=None,         detect_result=None, old_pic_path=None, result_pic_path=None):  '''  :param product_number: 产品编号  :param record_time: 取原图时间  :param lamp_color: 灯的颜色:1 2 3 4  :param detect_result: 检测结果  :param old_pic_path: 原图路径  :param result_pic_path: 结果图路径  :return: 数据是否写入成功  '''  information_system_instance = DataSaveSystem(    product_number=product_number,    record_time=record_time,    lamp_color=lamp_color,    detect_result=detect_result,    old_pic_path=old_pic_path,    result_pic_path=result_pic_path)  # session.add_all([  #   lamp_result(id=2, name="张2", age=19),  #   lamp_result(id=3, name="张3", age=20)  # ])  session.add(information_system_instance)  try:    session.commit() # 尝试提交数据库事务    # print('数据库数据提交成功')    return {      "code": 200,      "status": True,      "message": "写入数据库成功",    }  except SQLAlchemyError as e:    session.rollback()    print(e)    return {      "code": 500,      "status": False,      "message": str(e)    }# url = "mysql+pymysql://root:password@0.0.0.1:3306/DataSave"# # echo为True时，打印sql,可用于调试# engine = create_engine(url, echo=False, encoding='utf-8', pool_size=5)# sessionClass = sessionmaker(bind=engine)# # 创建会话# session = sessionClass()# # 查所有,并排序# stuList = session.query(DataSaveSystem).order_by(DataSaveSystem.result_id).all()# print(stuList)#stu = DataSaveSystem(product_number='id1',    record_time='20191022170400',    lamp_color='1',    detect_result='ok',    old_pic_path='picture/',    result_pic_path='d')# session.add(stu)stuList = [DataSaveSystem(product_number='id1',    record_time='20191022170400',    lamp_color='1',    detect_result='ok',    old_pic_path='picture/',    result_pic_path='d'),      DataSaveSystem(product_number='id1',    record_time='20191022170400',    lamp_color='1',    detect_result='ok',    old_pic_path='picture/',    result_pic_path='d')]# session.add_all(stuList)# session.commit()# print('数据成功')if __name__ == '__main__':  result = insert_to_db(stu)  print(result)