Python實戰(zhàn)之數(shù)據(jù)庫應(yīng)用和數(shù)據(jù)獲取 課件 【Ch05】SQLAlchemy操作MySQL

SQLAlchemy操作MySQL第五章新工科建設(shè)之路·數(shù)據(jù)科學與大數(shù)據(jù)系列Python實戰(zhàn)之數(shù)據(jù)庫應(yīng)用和數(shù)據(jù)庫獲取1SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作1.1添加對象前面已經(jīng)創(chuàng)建了Session工廠，并可以通過工廠創(chuàng)建新的Session對象。有了Session對象，就可以將類實例對象存入數(shù)據(jù)庫。為了將Course對象存入數(shù)據(jù)庫，需要調(diào)用Session的add()函數(shù)。示例如下(insert_exp_01.py):執(zhí)行以上代碼，通過MySQL指令控制臺查看course表，結(jié)果如圖5-1所示。1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作由查詢結(jié)果可知，數(shù)據(jù)庫中的course表中并沒有插入記錄。在代碼中已經(jīng)執(zhí)行了add操作，為何在數(shù)據(jù)庫中看不到對應(yīng)的記錄呢？在Python中調(diào)用Session的add()方法添加Course實例時，調(diào)用add()方法后，所添加的Course實例的狀態(tài)為pending,就是目前實際上還沒有執(zhí)行SQL操作，在數(shù)據(jù)庫中還沒有產(chǎn)生與這個Course實例對應(yīng)的行，此時數(shù)據(jù)是保存在內(nèi)存中的。若讓添加的Course實例進入數(shù)據(jù)庫，還需要調(diào)用Session的commit()方法提交事務(wù)。將insert_exp_0lpy中代碼片段更改如下(insert_exp_02.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作由代碼片段可以看到，在insert_exp01.py代碼文件的最后加上一行事務(wù)提交的代碼即可。執(zhí)行代碼，通過MySQL指令控制臺查看course表，得到如圖5-2所示的結(jié)果。Course實例對應(yīng)的行已經(jīng)成功插入MySQL數(shù)據(jù)庫了。1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作執(zhí)行結(jié)果已經(jīng)得到了預(yù)期的結(jié)果，但是對比insert_exp_01.py和insert_exp_02.py中的代碼可以發(fā)現(xiàn)，在這兩個PY文件中存在大量相同的代碼段，并且這些代碼都放在一個PY文件中，使得代碼看上去頗為臃腫。那么，這些代碼是否可以抽取出來呢？先抽取Course類，為便于后續(xù)的操作與使用，在chapter5文件夾下創(chuàng)建一個名為common的文件夾，在common文件夾下創(chuàng)建一個course.py文件。文件的代碼如下(course.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作該示例將Couser類封裝進了一個PY文件，將insert_exp_02.py更改為調(diào)用封裝的Course類，代碼更改如下(insert_exp_03.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作由更改后的代碼可以看到，更改的insert_exp_03.py文件中的代碼量已經(jīng)比前面的insert_exp_02.py文件中少了很多，看起來也沒有那么臃腫了，不過還是有重復(fù)編寫的代碼，Session創(chuàng)建的代碼還在重復(fù)編寫。接下來繼續(xù)做代碼抽取和封裝。根據(jù)前面的示例代碼，抽取一個創(chuàng)建Session的公共代碼，與course.py一樣，在common文件夾下創(chuàng)建一個名為session_create.py的PY文件，文件中的代碼示例如下(session_create.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作由示例代碼可以看到，代碼中提供了一個get_session()方法，返回一個構(gòu)造好的Session對象，外部程序調(diào)用getsession()方法即可拿到Session對象?，F(xiàn)在結(jié)合course.py和session_create.py文件，將insert_exp_02.py更改為如下更為精簡的結(jié)構(gòu)(insert_cxp_04.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作代碼片段可以看到，現(xiàn)在的代碼結(jié)構(gòu)已經(jīng)變得非常簡單清晰了。此示例代碼也不是最精簡或不可再封裝的，可以進一步精簡和封裝，此處不再具體講解，有興趣的讀者可以繼續(xù)深入探究。后續(xù)的講解中，當需要用到Course類或Session的創(chuàng)建時，會直接使用本節(jié)創(chuàng)建好的course.py和session_create.py文件中的代碼片段，而不再重復(fù)編寫這些代碼段。若后續(xù)有可以抽取的公共代碼段，也會將對應(yīng)的PY文件放到common文件夾下，便于文件的統(tǒng)一管理。在Session中添加對象的方法，除了add()方法，還有addall()方法。add()方法一次只能添加一個對象，而addall()方法可以添加多個對象。addall()方法的調(diào)用方式如下：1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作其中，objectl、object2、object3都是一個Base的派生類。示例如(mult_insert_exp.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作執(zhí)行代碼，在日志輸出控制臺可以看到包含如下輸出信息：由輸出信息可以看到，在SQLAlchemy的操作中，執(zhí)行Session的addall()方法時，實際會將多個對象的操作轉(zhuǎn)化為一條條NSERT語句，逐條往數(shù)據(jù)庫中插入數(shù)據(jù)。1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作程序執(zhí)行后，通過MySQL指令控制臺查看執(zhí)行結(jié)果，可以看到如圖5-3所示的結(jié)果。由結(jié)果可以看到，對應(yīng)的幾個Couser對象都成功插入course表中了。在實際項目應(yīng)用中，能以面向?qū)ο蟮姆绞絼?chuàng)建對象是非常不錯的，可以避免在源代碼中加入SQL代碼。1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作1.2查詢對象在Session中添加對象的方法時，每次執(zhí)行完插入操作，都需要到MySQL指令控制臺查看數(shù)據(jù)，這樣操作很不方便。Session中提供了query(方法實現(xiàn)數(shù)據(jù)查詢。Session的quey0方法會返回一個Query對象。query()方法可以接受多種參數(shù)類型，可以是類或者類的屬性字段。如要通過Session的query()方法從course表中查找出所有的記錄，查找所有記錄的方式還需要在quey()方法后加上allO方法，代碼如下(query_exp_1.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作執(zhí)行該代碼段，在日志輸出控制臺可以看到包含如下輸出：1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作由結(jié)果可知，通過Session的query()方法和aIl()方法，可以拿到所有的表記錄，并且編譯器會將查詢語句轉(zhuǎn)換為對應(yīng)的SQL語句。由打印的日志結(jié)果同時可以看到，在Course類中添加repr()方法對于打印指定形式的結(jié)果的便捷性。這里可以拿到全部記錄，若只查找coursename為Python的記錄，查找方式怎樣呢？只查找course_name為Python的記錄，在SQL語句中需要使用WHERE條件語句實現(xiàn)，在SQLAlchemy中，可以使用query()方法中的filter()方法實現(xiàn)與WHERE等同的效果。具體使用看如下示例(query._cxp_2.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作執(zhí)行代碼文件，在日志輸出控制臺可以看到包含如下輸出：1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作由結(jié)果可知，查詢得到了想要的結(jié)果，并且編譯器將加了ltr()方法的查詢語句轉(zhuǎn)換為了SQL的WHERE條件查詢語句。以面向?qū)ο蟮姆绞竭M行對象查詢，在實際項目應(yīng)用中，對代碼的維護帶來很大的方便，也可以避免很多查詢風險，如SQL注入。若可能，在項目中多使用面向?qū)ο蟮姆绞骄帉懖樵冋Z句。1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作1.3更新對象在SQLAlchemy中，通過Session對象做數(shù)據(jù)的更新操作是先得到數(shù)據(jù)表對應(yīng)的類對象，若要更改某個字段值，找到字段映射到類對象對應(yīng)的屬性字段，對屬性字段賦值為需要更改的值，再執(zhí)行Session的a()方法并執(zhí)行commit()后，即實現(xiàn)更新。若需要將數(shù)據(jù)表course中，對course_name為Python的記錄，將teacher_name的值更改為“TeacherLi”，則代碼如下(update_cxp_1.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作執(zhí)行代碼文件，在日志控制臺中可以看到包含如下輸出：1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作在輸出日志信息中可以看到如下關(guān)鍵信息：這里，輸出查詢結(jié)果是為了更清晰地查看所查詢到的結(jié)果的形式是怎樣的，輸出itm對象的類型是為了清楚地了解item是什么結(jié)構(gòu)類型。打印更改前和更改后teachername的值，能得知更改操作是否發(fā)生，也免于再到MySQL指令控制臺去進行查驗。從輸出的日志可知，編譯器將Session發(fā)起的add()方法編譯成了UPDATE語句。):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作對于pdateexp_1.py文件中的示例代碼，可更改如下(update_exp_2.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作在代碼中，在filter()后有使用到first()方法，意為取得查詢結(jié)果中的第一個結(jié)果對象，如示例中拿到的就是一個Course對象。得到Course對象后就可以直接進行更改操作，如代碼中所示。直接運行代碼，即可看到更改的結(jié)果，此處不再展示更改結(jié)果。對于示例代碼，有興趣的讀者可以進一步簡化或封裝。1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作1.3刪除對象在SQLAlchemy中，通過Session對象做對象別除操作比較簡單。先查詢要別除的數(shù)據(jù)，得到的數(shù)據(jù)映射為類對象，再調(diào)用Session的delete()方法刪除類對象，執(zhí)行后即完成刪除操作。如將course表中coursename為“Python”的記錄別除，代碼如下(delete_exp_1.py):1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作執(zhí)行代碼文件，在日志輸出控制臺可以看到包含如下輸出日志：1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作執(zhí)行代碼文件，在日志輸出控制臺可以看到包含如下輸出日志：1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作由輸出日志可知，對應(yīng)記錄已經(jīng)成功刪除。同時，操作過程中，編譯器會將delete()方法對應(yīng)的語句轉(zhuǎn)換為SQL的DELETE語句。1.SQLAlchemy對MySQL數(shù)據(jù)庫的基本操作2SQLAlchemy的常用filter操作符equals操作符notequals操作符Iike操作符2.SQLAlchemy的常用filter操作符and操作符isnotnull操作符isnull操作符notin操作符or操作符in操作符2.1equals操作符在filter()方法中，equals操作符在query_exp_2.py中已有使用，即如下代碼行：代碼行中的“==”操作符就是equals操作符，意為等于操作，操作結(jié)果是Tnue或False。若操作符兩邊的值相等，則結(jié)果是True,否則結(jié)果是False.在實際項目應(yīng)用中，equals操作是很常用的一個操作，類似SQL語句中的等值操作2.SQLAlchemy的常用filter操作符2.2notequals操作符notequals操作符即不等于操作符，結(jié)果也是True或False.notequals的示例如下(not_equals_exp.py):2.SQLAlchemy的常用filter操作符該示例代碼用于從course表中查找coursename不等于‘MySQL’的記錄。執(zhí)行該示例代碼，可以看到如下輸出結(jié)果：由輸出結(jié)果可以看到，沒有返回course_name等于MySQL'的記錄，同時對象操作的語句轉(zhuǎn)換成了SQL查詢語句。后續(xù)的操作示例代碼中，由于與not_equals_exp.py文件中的代碼相差不大，后面的幾個示例只展示有差異的代碼，具體代碼可以根據(jù)給定的PY文件去查看，也會適當展示執(zhí)行結(jié)果。2.SQLAlchemy的常用filter操作符2.3Iike操作符在filter()方法中，Iike操作與SQL中的LIKE操作類似，作用也是模糊匹配，有全模糊匹配、左模糊匹配和右模糊匹配三種，示例代碼分別如下(Iike_exp.py).全模糊匹配：執(zhí)行代碼文件，可以看到如下輸出：2.SQLAlchemy的常用filter操作符右模糊匹配：執(zhí)行代碼文件，可以看到如下輸出：由輸出可以看到，沒有得到匹配的結(jié)果。左模糊匹配：2.SQLAlchemy的常用filter操作符執(zhí)行代碼文件，可以看到如下輸出：這里僅僅作為一個結(jié)果演示，更多可以自行操作，以觀察輸出結(jié)果的差異。2.SQLAlchemy的常用filter操作符2.4and操作符在filter()方法中，and操作符表示“并且”的意思，其前后的條件都滿足時，結(jié)果是True,否則結(jié)果是False.and操作符有如下3種表示方式(and_exp.py).123方法一：使用and_()(注意and后面還有一個下劃線“_”)。方法二：在filter()中設(shè)置多個表達式。方法三：使用多個filter()。2.SQLAlchemy的常用filter操作符2.SQLAlchemy的常用filter操作符2.5or操作符在filter()方法中，or操作符表示“或者”的意思，其前后的條件都不滿足時，結(jié)果是False,否則結(jié)果是True.or操作符的示例如下(or_exp.py):2.6isnull操作符在filter()方法中，isnull操作符用于判斷查詢的對象是否是空的，并且在Python中，None字符和數(shù)據(jù)庫表記錄中的null對應(yīng)。isnull操作符的示例如下（is_null_exp.py):2.SQLAlchemy的常用filter操作符2.7Isnotnull操作符在filter()方法中，isnotnull的意思與isnull相反，用于判斷一個查詢出來的對象不是空的。isnotnull操作符的示例如下(is_not_null_exp.py):2.SQLAlchemy的常用filter操作符2.8in操作符在filter()方法中，in操作符的作用類似or,一般后面為Iist類型的值。不過，不同于or操作符需要前后都有條件值，n中可以只有一個或多個元素。in操作符的示例如下(in_exp.py)(注意in后還有一個下劃線“_")：2.SQLAlchemy的常用filter操作符2.8notin操作符在filter()方法中，notin操作符的作用與in相反。在SQLAlchemy中，notin操作符使用“~”表示。notin操作符的示例如下(not_in_exp.py):2.SQLAlchemy的常用filter操作符3SQLAlchemy的更多操作3.SQLAlchemy的更多操作3.1返回列表和單項

5.1節(jié)中的查詢對象操作中用到了all()方法，更新對象操作中用到了fist()方法，對于all()方法的查詢結(jié)果，需要使用for循環(huán)遍歷，而first()方法的查詢結(jié)果不需要遍歷。那么，all()方法和first()方法分別返回的是什么類型的結(jié)果？在SQLAlchemy中，all()方法返回的是一個列表，列表中的每個元素對應(yīng)一個類對象或類的屬性對象。first()方法返回至多一個結(jié)果，而且以單個類對象或?qū)傩詫ο蟮男问?，而不是以只包含一個元素的Tuple的形式返回結(jié)果。在SQLAlchemy的返回查詢結(jié)果的方法中還有一個one()方法。先通過如下示例了解one()方法的使用(query._one_exp.py):文件中的查詢語句會返回多個查詢結(jié)果，所以執(zhí)行代碼文件后，會得到如下錯誤信息：把查詢語句更改如下：3.SQLAlchemy的更多操作執(zhí)行代碼，會得到如下錯誤信息：接著更改查詢語句為如下形式：執(zhí)行代碼，最終得到如下正確返回結(jié)果：由以上執(zhí)行結(jié)果可知：one()方法返回且僅返回一個查詢結(jié)果，當結(jié)果的數(shù)量不足一個或者多于一個時會報錯。3.SQLAlchemy的更多操作3.2嵌入使用SQL在SQLAlchemy中查詢對象時，可以在query()方法中通過使用text()使用SQL語句。使用text()方法需要先從SQLAlchemy中導入text。操作示例如下（query_text_exp_1.py):3.SQLAlchemy的更多操作執(zhí)行代碼段，打印日志中可以看到包含如下輸出信息：觀察輸出的打印語句，仔細閱讀查詢的SQL語句，WHERE條件后面的course_name前沒有加上“course.”字符，此處即使用了原生的SQL語句。除了這種直接將參數(shù)寫進字符串的方式，還可以通過params()方法來傳遞參數(shù)。示例如下(query_text_exp_2.py):3.SQLAlchemy的更多操作執(zhí)行代碼段，打印日志中可以看到包含如下輸出信息：觀察輸出的打印語句，仔細閱讀查詢的SQL語句，WHERE條件后面的teacher_name和course_name前都沒有加上“course.”字符，也是使用了原生的SQL語句。3.SQLAlchemy的更多操作text()方法中還可以直接使用完整的SQL語句。示例如下(quey_text_ex