python orm 框架中sqlalchemy用法實例詳解

更新時間：2020年02月02日 10:54:56 作者：Dawn__Z

這篇文章主要介紹了python orm 框架中sqlalchemy用法,結(jié)合實例形式詳細分析了Python orm 框架基本概念、原理及sqlalchemy相關(guān)使用技巧,需要的朋友可以參考下

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

一.ORM簡介

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

2. ORM優(yōu)點:

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

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

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

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

二、代碼

1.建表

"""
Created on 19-10-22
@author: apple
@description:建表
"""
import pymysql
server = '127.0.0.1'
user = 'root'
# dev
password = '123456'
conn = pymysql.connect(server, user, password, database='DataSave') # 獲取連接
cursor = conn.cursor() # 獲取游標
# "**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)閉游標關(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 SQLAlchemyError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.orm import sessionmaker
Base = 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ù)庫表模型ORM
class 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: 取原圖時間
  :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時，打印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)

3.數(shù)據(jù)函數(shù)調(diào)用

"""
Created on 19-10-31
@author: apple
@requirement:Anaconda 4.3.0 (64-bit) Python3.6
@description:調(diào)取函數(shù)基類
"""
from data_sql.airconditioning_lamp_datasave.datasave import DataSaveSystem
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
# 連接數(shù)據(jù)庫
# alter table students convert to character set utf8;
conn = "mysql+pymysql://root:password@0.0.0.1:3306/DataSave"
engine = create_engine(conn, encoding='UTF8', echo=False) # echo=True 打印日志
# 創(chuàng)建session對象
Session = sessionmaker(bind=engine)
session = Session()
stuList = [DataSaveSystem(product_number='id1',
    record_time='20191022170400',
    lamp_color='1',
    detect_result='ok',
    old_pic_path='picture/',
    result_pic_path='F'),
      DataSaveSystem(product_number='id1',
    record_time='20191022170400',
    lamp_color='1',
    detect_result='ok',
    old_pic_path='picture/',
    result_pic_path='F'),DataSaveSystem(product_number='id1',
    record_time='20191022170400',
    lamp_color='1',
    detect_result='ok',
    old_pic_path='picture/',
    result_pic_path='F'),DataSaveSystem(product_number='id1',
    record_time='20191022170400',
    lamp_color='1',
    detect_result='ok',
    old_pic_path='picture/',
    result_pic_path='F')]
session.add_all(stuList)
session.commit()
print('數(shù)據(jù)成功')
# # 根據(jù)主建查詢數(shù)據(jù)
# result = session.query(DataSaveSystem).get(3)
# print(result.old_pic_path)
# # 查詢第一條
# result = session.query(DataSaveSystem).first()
# print(result) #打印對象屬性
# 查詢表關(guān)鍵字的數(shù)據(jù)
result = session.query(DataSaveSystem).filter_by(result_pic_path='a/').first()
print(result)
#修改
session.query(DataSaveSystem).filter(DataSaveSystem.result_pic_path=='a/').update({"detect_result":"不合格"})
session.commit()

更多關(guān)于Python相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《Python常見數(shù)據(jù)庫操作技巧匯總》、《Python數(shù)學運算技巧總結(jié)》、《Python數(shù)據(jù)結(jié)構(gòu)與算法教程》、《Python函數(shù)使用技巧總結(jié)》、《Python字符串操作技巧匯總》、《Python入門與進階經(jīng)典教程》及《Python文件與目錄操作技巧匯總》

希望本文所述對大家Python程序設(shè)計有所幫助。

您可能感興趣的文章: