這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)碛嘘P(guān)sqlalchemy怎么在python中使用,文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述,閱讀完這篇文章希望大家可以有所收獲。
成都創(chuàng)新互聯(lián)堅(jiān)信:善待客戶,將會(huì)成為終身客戶。我們能堅(jiān)持多年,是因?yàn)槲覀円恢笨芍档眯刨嚒N覀儚牟缓鲇瞥踉L客戶,我們用心做好本職工作,不忘初心,方得始終。十年網(wǎng)站建設(shè)經(jīng)驗(yàn)成都創(chuàng)新互聯(lián)是成都老牌網(wǎng)站營銷服務(wù)商,為您提供成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、H5技術(shù)、網(wǎng)站制作、品牌網(wǎng)站建設(shè)、小程序定制開發(fā)服務(wù),給眾多知名企業(yè)提供過好品質(zhì)的建站服務(wù)。一.ORM簡介1. ORM(Object-Relational Mapping,對(duì)象關(guān)系映射):作用是在關(guān)系型數(shù)據(jù)庫和業(yè)務(wù)實(shí)體對(duì)象之間做一個(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的引擎會(huì)處理不同數(shù)據(jù)庫之間的差異,所以遷移數(shù)據(jù)庫時(shí)無需更改代碼.
應(yīng)用緩存優(yōu)化等技術(shù)有時(shí)可以提高數(shù)據(jù)庫操作的效率.
3. SQLALchemy:是python中最成熟的ORM框架,資源和文檔很豐富,大多數(shù)python web框架對(duì)其有很好的主持,能夠勝任大多數(shù)應(yīng)用場合,SQLALchemy被認(rèn)為是python事實(shí)上的ORM標(biāo)準(zhǔn).
二、代碼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() # 獲取游標(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ù),才會(huì)寫入表格 conn.commit() # 關(guān)閉游標(biāo)關(guān)閉數(shù)據(jù)庫 cursor.close() conn.close()
2. 數(shù)據(jù)存儲(chǔ)
""" Created on 19-10-22 @author: apple @requirement:Anaconda 4.3.0 (64-bit) Python3.6 @description:數(shù)據(jù)存儲(chǔ) """ 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對(duì)象 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)品編號(hào) :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)建會(huì)話 # 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對(duì)象 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) #打印對(duì)象屬性 # 查詢表關(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()
上述就是小編為大家分享的sqlalchemy怎么在python中使用了,如果剛好有類似的疑惑,不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí),歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
當(dāng)前名稱:sqlalchemy怎么在python中使用-創(chuàng)新互聯(lián)
文章分享:http://bm7419.com/article30/igipo.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供品牌網(wǎng)站建設(shè)、自適應(yīng)網(wǎng)站、網(wǎng)站排名、企業(yè)建站、搜索引擎優(yōu)化、商城網(wǎng)站
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)
猜你還喜歡下面的內(nèi)容