Python語言實用教程第11章-數(shù)據(jù)庫應用課件

1、第11章 數(shù)據(jù)庫應用一般高級語言都支持數(shù)據(jù)庫操作，Python對多種數(shù)據(jù)庫操作提供了很好的支持。Python語言標準數(shù)據(jù)庫接口為Python DB-API，Python DB-API為開發(fā)人員提供了一致的數(shù)據(jù)庫應用編程接口。本章主要內(nèi)容包括：數(shù)據(jù)庫基本知識、Python數(shù)據(jù)庫編程技術、多種數(shù)據(jù)庫操作模塊的介紹與基本使用等內(nèi)容。第11章 數(shù)據(jù)庫應用學習重點或難點： 關系數(shù)據(jù)庫與SQL語言 Python數(shù)據(jù)庫編程簡介 通過DB-API訪問數(shù)據(jù)庫 Python數(shù)據(jù)庫編程實例學習本章后，將能利用Python數(shù)據(jù)庫訪問技術來編寫數(shù)據(jù)庫應用程序，實現(xiàn)對信息的高級管理功能。第11章 數(shù)據(jù)庫應用11.1 關系

2、數(shù)據(jù)庫概述11.2 Python數(shù)據(jù)庫編程概述11.3 Python與ODBC11.4 Python與SQLite311.5 Python與MySQL11.6 Python與SQL Server11.1 關系數(shù)據(jù)庫概述 從知識的完整性角度考慮，本節(jié)簡單介紹關系數(shù)據(jù)庫的基本知識。主要是關系數(shù)據(jù)庫的定義及關系數(shù)據(jù)庫的國際標準SQL語言。11.1.1 關系數(shù)據(jù)庫數(shù)據(jù)庫(Database)是指長期存儲在計算機內(nèi)的、有組織的、可共享的數(shù)據(jù)集合。數(shù)據(jù)以記錄(Record)和字段(Field)的形式存儲在數(shù)據(jù)表(Table)中，由若干個相關聯(lián)的數(shù)據(jù)表構成一個數(shù)據(jù)庫。數(shù)據(jù)庫國際標準操作語言SQL語言的操作對象

3、主要是數(shù)據(jù)表或視圖。SQL語言可分為：數(shù)據(jù)定義語言DDL、數(shù)據(jù)操縱語言DML、數(shù)據(jù)查詢語言DQL和數(shù)據(jù)控制語言DCL四大類。11.1.2 SQL語言1.創(chuàng)建數(shù)據(jù)表CREATE TABLE 表名(字段名l 數(shù)據(jù)類型 列級約束條件,字段名2 數(shù)據(jù)類型 列級約束條件,字段名n 數(shù)據(jù)類型 列級約束條件 , 表級完整性約束)創(chuàng)建學生信息表studentcreate table student(sno char(10) primary key,sname char(20),sage integer,sdept char(10)11.1.2 SQL語言 2.修改數(shù)據(jù)表 ALTER TABLE 表名 ADD

4、字段名 數(shù)據(jù)類型 約束條件 在學生表student中添加一個性別字段ssex其SQL語句為： ALTER TABLE student add ssex char(2) not null 刪除字段使用的格式為：ALTER TABLE 表名 DROP 字段名 在學生信息表student中刪除一個字段性別ssex其SQL語句為： ALTER TABLE student DROP ssex11.1.2 SQL語言3.刪除數(shù)據(jù)表 在SQL語言中使用DROP TABLE語句刪除某個表格及表格中的所有記錄，其命令格式為：DROP TABLE 表名 在test數(shù)據(jù)庫中刪除學生表student其SQL語句為：D

5、ROP TABLE student11.1.2 SQL語言4.向數(shù)據(jù)表中插入數(shù)據(jù) INSERT語句實現(xiàn)向數(shù)據(jù)庫表格中插入或增加新的數(shù)據(jù)行，其格式如下： INSERT INTO 表名(字段名l,字段名n) VALUES(值l,值n) 例如：在學生表student中插入一條記錄，其SQL語句為： insert into student(sno,sname,sage,sdept) values(20160305,董華,19, cs)11.1.2 SQL語言5.數(shù)據(jù)更新語句 UPDATE語句實現(xiàn)更新或修改滿足規(guī)定條件的現(xiàn)有記錄，使用格式如下： UPDATE 表名 SET 字段名l=新值l ,字段名2=

6、新值2 WHERE 條件表達式 例如：學生表student中的sage加1歲，其SQL語句為：Update student set sage=sage+111.1.2 SQL語言6.刪除記錄語句 DELETE語句刪除數(shù)據(jù)庫表格中的行或記錄，其命令格式如下： DELETE FROM 表名 WHERE 條件表達式 例如：刪除學生表student中的sage字段的值超過24的記錄，其語句為： DELETE FROM student where sage2411.1.2 SQL語言7.數(shù)據(jù)查詢語言 最基本的SELECT查詢語句格式： SELECT DISTINCT 字段名l,字段名2, FROM 表名

7、 WHERE 條件表達式 例如查詢出學生表student中的所有姓王的學生信息，其語句為： Select * from student where sname like 王%11.2 Python數(shù)據(jù)庫編程概述 關系型數(shù)據(jù)庫擁有共同的規(guī)范Python Database API Specification V2.0(Python DB-API)，MySQL、Oracle等都實現(xiàn)了此規(guī)范，然后增加自己的擴展。為了增強自己數(shù)據(jù)庫操作的性能與效率等，不同數(shù)據(jù)庫還會提供操作自己數(shù)據(jù)庫的專門接口模塊。 sqlite3：sqlite3模塊提供了SQLite數(shù)據(jù)庫訪問的接口。SQLite數(shù)據(jù)庫是以一個文件或內(nèi)

8、存的形式存在的自包含的關系型數(shù)據(jù)庫。11.2 Python數(shù)據(jù)庫編程概述 DBM-style數(shù)據(jù)庫模塊：python提供了多個modules來支持UNIX DBM-style數(shù)據(jù)庫文件。dbm模塊用來讀取標準的UNIX-dbm數(shù)據(jù)庫文件;gdbm用來讀取GNU dbm數(shù)據(jù)庫文件;dbhash用來讀取Berkeley DB數(shù)據(jù)庫文件。所有的這些模塊提供了一個對象實現(xiàn)了基于字符串的持久化的字典，它與字典dict非常相似，但是它的keys和values都必須是字符串。11.2 Python數(shù)據(jù)庫編程概述 這里主要介紹Python標準數(shù)據(jù)庫接口(Python DB-API)。Python DB-API

9、為開發(fā)人員提供了數(shù)據(jù)庫應用編程接口。DB-API是一個規(guī)范,它定義了一系列必須的對象和數(shù)據(jù)庫存取方式,以便為各種各樣的底層數(shù)據(jù)庫系統(tǒng)和多種多樣的數(shù)據(jù)庫接口程序提供一致的訪問接口。11.2 Python數(shù)據(jù)庫編程概述 Python 數(shù)據(jù)庫接口支持非常多的數(shù)據(jù)庫，可以選擇適合你項目的數(shù)據(jù)庫：GadFly、mSQL、MySQL、PostgreSQL、Microsoft SQL Server、Informix、Interbase、Oracle、Sybase等。可以訪問Python數(shù)據(jù)庫接口及API查看詳細的支持數(shù)據(jù)庫列表。11.2 Python數(shù)據(jù)庫編程概述 不同的數(shù)據(jù)庫需要下載不同的DB-API模塊

10、，例如需要訪問Oracle數(shù)據(jù)庫和Mysql數(shù)據(jù)，你需要下載Oracle和MySQL數(shù)據(jù)庫DB-API模塊。Python的DB-API，為大多數(shù)的數(shù)據(jù)庫實現(xiàn)了接口，使用它連接各數(shù)據(jù)庫后，就可以用相同的方式操作各種數(shù)據(jù)庫。 Python DB-API使用流程：1）引入DB-API 模塊；2）獲取與數(shù)據(jù)庫的連接；3）執(zhí)行SQL語句和存儲過程；4）關閉數(shù)據(jù)庫連接。11.3 Python與ODBC pyodbc封裝了ODBC API，通過它可以訪問各種有ODBC驅(qū)動的數(shù)據(jù)庫。Pyodbc提供的相關對象方法說明如下：1、connection 對象方法 1）close()：關閉數(shù)據(jù)庫； 2）commit(

11、)：提交當前事務； 3）rollback()：取消當前事務； 4）cursor()：獲取當前連接的游標； 5）errorhandler()：作為已給游標的句柄。 2.cursor游標對象和方法（具體略）11.3 Python與ODBC 【例11-1】 查詢某個ODBC數(shù)據(jù)源的某個表#encoding=gbkimport pyodbc, sys#conn= pyodbc.connect(DSN=mysqldsn;UID=root;PWD=root) # 連接MySQL#conn= pyodbc.connect(DRIVER=MySQL ODBC 5.3 ANSI Driver;SERVER=lo

12、calhost;PORT=3306;DATABASE=Jxgl;USER=root;PASSWORD=root)#conn= pyodbc.connect(DRIVER=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;PORT=3306;DATABASE=Jxgl;USER=root;PASSWORD=root)conn = pyodbc.connect(DRIVER=SQL Server;DATABASE=%s;SERVER=%s;UID=%s;PWD=%s%(Jxgl, , sa, sasasasa) # 連接SQL Servercursor=

13、conn.cursor()cursor.execute(SELECT * FROM student)while True: row=cursor.fetchone() if not row: break print(row)cursor.close()conn.close()11.4 Python與SQLite3 sqlite3是一個很優(yōu)秀的輕量級數(shù)據(jù)庫。sqlite3從Python 2.5版本開始加入到標準庫中。通過它，可以很方便地操作SQLite數(shù)據(jù)庫。操作SQLite數(shù)據(jù)庫的方法或語句與上面操作ODBC數(shù)據(jù)源數(shù)據(jù)的基本相同。下面直接舉例說明： 【例11-2】 創(chuàng)建一個內(nèi)存數(shù)據(jù)庫，建表并插

14、入記錄后，再顯示出來import sqlite3conn=sqlite3.connect(:memory:)cursor=conn.cursor()cursor.execute(CREATE TABLE person (name text, age int)cursor.execute(INSERT INTO person VALUES (TOM,20)cursor.execute(INSERT INTO person VALUES (Jhon,22)mit()cursor.execute(SELECT * FROM person)while True: row=cursor.fetchone

15、() if not row: break print(row)cursor.close(); conn.close()11.5 Python與MySQL Python和MySQL交互的模塊有 MySQLdb和PyMySQL(pymysql)等。Python操作MySQL數(shù)據(jù)庫的方法首先可選MySQL for Python，即MySQLdb，原因是這個是C寫的，速度快。MySQLdb是用于Python連接Mysql數(shù)據(jù)庫的接口，它實現(xiàn)了 Python 數(shù)據(jù)庫 API 規(guī)范V2.0，是基于 MySQL C API上建立的。但Python3不再支持MySQLdb。若想在Linux和Windows中都

16、同時能夠運行，恐怕更得考慮采用PyMySQL。11.5 Python與MySQL PyMySQL是一個全Python寫的Python操作MySQL數(shù)據(jù)庫的第三方庫。使用時可以把它放入代碼工程中調(diào)用，當然也可以在系統(tǒng)里先安裝后使用。PyMySQL的性能和MySQLdb幾乎相當，如果對性能要求不是特別的強，使用PyMySQL將更加方便。 PyMySQL的使用方法和MySQLdb幾乎一樣，如果你以前使用過MySQLdb，只需要將import MySQLdb修改為 import pymysql就可以了。11.5.2 使用MySQLdb操MySQL 1、數(shù)據(jù)庫連接 連接數(shù)據(jù)庫前，請先確認以下事項：1）已

17、經(jīng)創(chuàng)建了數(shù)據(jù)庫，如：Company。2）在Company數(shù)據(jù)庫中已經(jīng)創(chuàng)建了表 EMPLOYEE。EMPLOYEE表字段有：FIRST_NAME,LAST_NAME,AGE,SEX和INCOME。3）連接數(shù)據(jù)庫Company使用的用戶名為testuser，密碼為test123，也可自己設定或者直接使用root用戶名及其密碼，Mysql數(shù)據(jù)庫用戶授權請使用Grant命令。4）在機器上已經(jīng)安裝了Python MySQLdb 模塊。11.5.2 使用MySQLdb操MySQL 2、創(chuàng)建數(shù)據(jù)庫表 如果數(shù)據(jù)庫連接存在，可以使用execute()方法來為數(shù)據(jù)庫創(chuàng)建表。 【例11-4】 創(chuàng)建表EMPLOYEE

18、。import MySQLdb db = MySQLdb.connect(localhost,root,root,Company)cursor = db.cursor() # 使用cursor()方法獲取操作游標# 如果數(shù)據(jù)表已經(jīng)存在使用 execute()方法刪除表。cursor.execute(DROP TABLE IF EXISTS EMPLOYEE)11.5.2 使用MySQLdb操MySQL# 創(chuàng)建數(shù)據(jù)表SQL語句sql = CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE IN

19、T, SEX CHAR(1), INCOME FLOAT )cursor.execute(sql)db.close() # 關閉數(shù)據(jù)庫連接11.5.2 使用MySQLdb操MySQL 3、數(shù)據(jù)庫插入操作 以下例題使用執(zhí)行 SQL INSERT 語句向表 EMPLOYEE 插入記錄：【例11-5】 在EMPLOYEE表中添加記錄。import MySQLdbdb = MySQLdb.connect(localhost,root,root,Company)cursor = db.cursor()sql = INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE

20、, SEX, INCOME)11.5.2 使用MySQLdb操MySQL VALUES (Mac, Mohan, 20, M, 2000) # SQL 插入語句 try: cursor.execute(sql) # 執(zhí)行sql語句 mit() # 提交到數(shù)據(jù)庫執(zhí)行 except: db.rollback() # 執(zhí)行中遇到錯誤回滾前面已做的操作 db.close()11.5.2 使用MySQLdb操MySQL 以上例子中的SQL語句也可以寫成如下形式：sql = INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME) VALUES (

21、%s, %s, %d, %c, %d ) % (Mac, Mohan, 20, M, 2000) # SQL 插入語句 以下代碼使用變量向SQL語句中傳遞參數(shù)：user_id = root; password = rootcon.execute(insert into Login values(%s,%s) % (user_id,password)11.5.2 使用MySQLdb操MySQL 4、數(shù)據(jù)庫查詢操作 Python查詢Mysql使用 fetchone()方法獲取單條數(shù)據(jù)，使用fetchall()方法獲取多條數(shù)據(jù)。說明：1）fetchone()：該方法獲取下一個查詢結(jié)果集。結(jié)果集是一個

22、對象。2）fetchall()：接收全部的返回結(jié)果行。3）rowcount：這是一個只讀屬性，并返回執(zhí)行execute()方法后影響的行數(shù)。11.5.2 使用MySQLdb操MySQL 【例11-6】 查詢EMPLOYEE表中salary（工資）字段大于1000的所有數(shù)據(jù)。import MySQLdb # 打開連接db = MySQLdb.connect(localhost,root,root,Company) cursor = db.cursor() # 使用cursor()方法獲取操作游標sql = SELECT * FROM EMPLOYEE WHERE INCOME %d % (100

23、0)11.5.2 使用MySQLdb操MySQL try: cursor.execute(sql) results = cursor.fetchall() # 獲取所有記錄列表 for row in results: fname = row0;lname = row1; age = row2; sex = row3; income = row4 print(fname=%s,lname=%s,age=%d,sex=%s,income=%d % (fname, lname, age, sex, income ) # 打印結(jié)果 except: print(錯誤: 不能獲取到數(shù)據(jù)。) db.clos

24、e() # 結(jié)果如下： fname=Mac,lname=Mohan,age=20,sex=M,income=200011.5.2 使用MySQLdb操MySQL 5、數(shù)據(jù)庫更新操作 更新操作用于更新數(shù)據(jù)表的的數(shù)據(jù)，例如： 【例11-7】 將 EMPLOYEE表中所有記錄的SEX 字段改為M，AGE 字段遞增1。 import MySQLdbdb=MySQLdb.connect(localhost,root,root,Company )cursor = db.cursor()11.5.2 使用MySQLdb操MySQLsql = UPDATE EMPLOYEE SET AGE=AGE + 1 W

25、HERE SEX=%c % (M)try: cursor.execute(sql); mit()except: db.rollback() # 發(fā)生錯誤時回滾db.close()11.5.2 使用MySQLdb操MySQL 6、執(zhí)行事務 事務機制可以確保數(shù)據(jù)一致性。事務應該具有4個屬性： 原子性、一致性、隔離性、持久性。這四個屬性通常稱為ACID特性。【例11-8】 基于事務機制實現(xiàn)記錄刪除的程序段。sql = DELETE FROM EMPLOYEE WHERE AGE %d % (20) # SQL刪除語句try: cursor.execute(sql) # 執(zhí)行SQL語句 mit() #

26、 向數(shù)據(jù)庫提交except: db.rollback() # 發(fā)生錯誤時回滾11.5.3 PyMySQL的安裝 PyMySQL(pymysql)是一個純Python寫的MySQL客戶端，它的目標是最終替代MySQLdb，可以在CPython、PyPy、IronPython和Jython環(huán)境下運行，PyMySQL在MIT許可下發(fā)布。在開發(fā)基于Python語言的項目中，為了以后系統(tǒng)能兼容Python3，建議使用PyMySQL替換MySQLdb。 通過如下例子來說明使用PyMySQL操作mysql。 【例11-9】 對student表插入一條記錄。11.5.5 MySQL-connector安裝使用

27、Python與MySQL服務器通信，還可以使用MySQL-connector-python，一個Python模塊MySQL/Connector。試著使用pip安裝，若不行可到/downloads/connector/python/手動下載，譬如下載unzip mysql-connector-python-2.1.3.zip后，解壓到mysql-connector-python-2.1.3目錄，進入該目錄后，利用“sudo python setup.py install”來安裝。11.5.5 MySQL-connector安裝使用 安裝成功后，就可以在python程序里正常使用“import m

28、ysql.connector as mysql”導入MySQL的connector模塊了，使用也很簡單。 【例11-10】 一個簡單查詢一個數(shù)據(jù)表數(shù)據(jù)的實例。11.5.6 中文亂碼問題處理 通過Python查詢數(shù)據(jù)庫中數(shù)據(jù)時，數(shù)據(jù)會呈現(xiàn)亂碼，只需要在連接字符串中指定數(shù)據(jù)編碼，如“utf8”、“GBK”等就可以。注意：Python 3.x才支持Unicode編碼。# -*- coding:gbk -*-import pymysqldb=pymysql.connect(host=localhost,user=root,password=root,database=Jxgl,charset=gbk)

29、cursor=db.cursor(); sqlstr=SELECT * FROM course;try: cursor.execute(sqlstr); courses=cursor.fetchall()except: print(u數(shù)據(jù)讀取錯誤)else: print(courses)db.close()11.6 Python與SQL Server python連接微軟的SQL Server數(shù)據(jù)庫用的第三方模塊叫做pymssql（官方文檔：/en/stable/index.html）。 在官方文檔可以看到，pymssql是基于_mssql模塊做的封裝，是為了遵守python的DB-API規(guī)范接口。11.6 Python與SQL Server pymssql的安裝可以通過：pip install pymssql 或 pip3 install pymssql 另外，pymssql 2.1.3可由網(wǎng)址：/pypi/pymssql/2.1.3獲取。從該網(wǎng)