本文實(shí)例講述了python orm 框架中sqlalchemy用法。分享給大家供大家參考，具體如下：

一.ORM簡介

1. ORM(Object-Relational Mapping,對象關(guān)系映射):作用是在關(guān)系型數(shù)據(jù)庫和業(yè)務(wù)實(shí)體對象之間做一個(gè)映射.

2. ORM優(yōu)點(diǎn):

向開發(fā)者屏蔽了數(shù)據(jù)庫的細(xì)節(jié),使開發(fā)者無需與SQL語句打交道,提高了開發(fā)效率;

便于數(shù)據(jù)庫的遷移,由于每種數(shù)據(jù)庫的SQL語法有差別,基于Sql的數(shù)據(jù)訪問層在更換數(shù)據(jù)庫時(shí)通過需要花費(fèi)時(shí)間調(diào)試SQL時(shí)間,而ORM提供了獨(dú)立于SQL的接口,ORM的引擎會處理不同數(shù)據(jù)庫之間的差異,所以遷移數(shù)據(jù)庫時(shí)無需更改代碼.

應(yīng)用緩存優(yōu)化等技術(shù)有時(shí)可以提高數(shù)據(jù)庫操作的效率.

3. SQLALchemy:是python中最成熟的ORM框架,資源和文檔很豐富,大多數(shù)python web框架對其有很好的主持,能夠勝任大多數(shù)應(yīng)用場合,SQLALchemy被認(rèn)為是python事實(shí)上的ORM標(biāo)準(zhǔn).

二、代碼

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() # 獲取游標(biāo)# "**ENGINE=InnoDB DEFAULT CHARSET=utf8**"-創(chuàng)建表的過程中增加這條，中文就不是亂碼# 創(chuàng)建表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""")# 查詢數(shù)據(jù)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'))# 提交數(shù)據(jù)，才會寫入表格conn.commit()# 關(guān)閉游標(biāo)關(guān)閉數(shù)據(jù)庫cursor.close()conn.close()

2. 數(shù)據(jù)存儲

"""Created on 19-10-22@author: apple@requirement:Anaconda 4.3.0 (64-bit) Python3.6@description:數(shù)據(jù)存儲"""from sqlalchemy.exc import SQLAlchemyErrorfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, String, Integer, create_enginefrom sqlalchemy.orm import sessionmakerBase = declarative_base()# 連接數(shù)據(jù)庫# 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 打印日志# 創(chuàng)建session對象Session = sessionmaker(bind=engine)session = Session()# 數(shù)據(jù)庫表模型ORMclass DataSaveSystem(Base):  """  員工自助信息采集系統(tǒng)  """  __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):    """    引用該類別，輸出結(jié)果    :return:    """    return str(self.__dict__)    # return '<detect_result:{}>'.format(self.detect_result)# 插入數(shù)據(jù)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: 產(chǎn)品編號  :param record_time: 取原圖時(shí)間  :param lamp_color: 燈的顏色:1 2 3 4  :param detect_result: 檢測結(jié)果  :param old_pic_path: 原圖路徑  :param result_pic_path: 結(jié)果圖路徑  :return: 數(shù)據(jù)是否寫入成功  '''  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() # 嘗試提交數(shù)據(jù)庫事務(wù)    # print('數(shù)據(jù)庫數(shù)據(jù)提交成功')    return {      "code": 200,      "status": True,      "message": "寫入數(shù)據(jù)庫成功",    }  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時(shí)，打印sql,可用于調(diào)試# engine = create_engine(url, echo=False, encoding='utf-8', pool_size=5)# sessionClass = sessionmaker(bind=engine)# # 創(chuàng)建會話# 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('數(shù)據(jù)成功')if __name__ == '__main__':  result = insert_to_db(stu)  print(result)