您现在的位置：程式師世界 >> 編程語言 > >> 更多編程語言 >> Python

一文詳解python中的數據庫操作

編輯：Python

python中的數據庫操作

- 一、數據庫編程接口
- - 1. 連接對象
- 二、使用內置的SQLite
- - 1.創建數據庫文件
  - 2.操作SQLite
- 三、MySql數據庫的使用
- - 3.1 安裝MySql
  - 3.2 設置環境變量
  - 3.3 啟動MySql
  - 3.4 使用navicat for mysql 管理軟件
  - 3.5 安裝PyMysql模塊
  - 3.6 連接數據庫
- 四、創建數據表
- - 4.1 創建books 圖書表
  - 4.2 操作MySql數據表

最近做畢設，需要添加個數據庫，記錄一下。

一、數據庫編程接口

重點介紹數據庫API接口中的連接對象和游標對象

1. 連接對象

數據庫連接對象主要提供獲取數據庫游標對象和提交/回滾事務的方法，以及關閉數據庫連接。

使用connect函數獲取對象，該函數有多個參數：

host 主機名
database/db 數據庫名稱
user 用戶名
password 用戶密碼
charset 編碼方式

使用PyMySQL模塊連接MySQL數據庫。

conn = pymysql.connect(host='localhost',user='user',password='passwd',db='test',charset='utf-8')

connect函數返回連接對象，該對象表示當前與數據庫的會話。

連接對象支持的方法如下：

close() 關閉數據庫連接
commit() 提交事務
rollback() 回滾事務
cursor() 獲取游標對象，操作數據庫，如執行DML操作、調用存儲過程

對於這裡的commit方法，commit是用於提交事務，事務主要是處理數據量大、復雜度高的數據。如果操作的是一系列動作，比如張三給李四轉賬：那麼張三的賬戶余額減少，李四的賬戶余額增加，那麼使用事務可以維護數據庫的完整性，也就是保證兩個操作要麼全部執行，要麼都不執行。

對於游標對象，代表數據庫中的游標，用於指示抓取數據操作的上下文，主要是提供執行SQL語句，調用存儲過程，獲取查詢結果等方法。

使用連接對象的cursor方法可以獲取游標對象，游標對象的屬性如下：

description屬性：表示數據庫列類型以及值得描述信息
rowcount屬性：返回結果得行數統計信息，如SELECT、UPDATE、CALLPROC
callproc(procname[,parameters]) 調用存儲過程，需要數據庫支持
close() 關閉當前游標
execute(operation[,parameters]) 執行數據庫操作，SQL語句或者數據庫命令

二、使用內置的SQLite

SQLite是一種嵌入式數據庫，該數據庫本身就是一個文件。SQLite將整個數據庫（包括表、索引、數據本身）作為一個單獨的可跨平台使用文件存儲在主機中，由於SQLite本身是使用C語言開發的，而且體積小，經常被集成到各種應用程序中，Python中內置了SQLite,所以在python中使用SQLite數據庫，不需要安裝任何模塊，直接使用。

1.創建數據庫文件

創建SQLite數據庫文件：

import sqlite3
# 連接到SQLite數據庫
# 數據庫文件是mrsoft.db 如果文件不存在，會自動在當前目錄中創建
conn = sqlite3.connect('mrsoft.db')
# 創建一個cursor
cursor = conn.cursor()
# 使用游標對象執行SQL語句，創建user表
cursor.execute('create table user (id int(10) primary key, name varchar(20))')
# 關閉游標
cursor.close()
# 關閉Connection
conn.close()

在上面的代碼中，使用sqlite3.connect()方法連接SQLite數據庫文件mrsoft.db，由於mrsoft.db文件並不存在，所以會在本實例python代碼得同級目錄下創建mrsoft.db文件，該文件包含了user表的相關信息。

但是如果你再次執行上面的代碼，會提示錯誤信息，因為user表已經存在。

2.操作SQLite

向數據表中新增數據可以使用SQL中的insert語句。

語法如下：

**insert into 表名（字段名1，字段名2，字段名3） values(字段值1，字段值2，...字段值n)**

在上面創建得user表中，有兩個字段，字段名分別為id,name。而字段值需要根據字段的類型來賦值。

那執行SQL語句：

cursor.execute('insert into user(id,name) values(1,"MRSOFT")')
cursor.execute('insert into user(id,name) values(2,"Andy")')
cursor.execute('insert into user(id,name) values(3,"SiRi")')

案例1：向數據庫中插入三條記錄

import sqlite3
# 連接到SQLite數據庫
# 數據庫文件是mrsoft.db 如果文件不存在，會自動在當前目錄中創建
conn = sqlite3.connect('mrsoft.db')
# 創建一個cursor
cursor = conn.cursor()
# 使用游標對象執行SQL語句，創建user表
# cursor.execute('create table user (id int(10) primary key, name varchar(20))')
# 執行插入語句 引號 外單內雙
cursor.execute('insert into user(id,name) values(1,"MRSOFT")')
cursor.execute('insert into user(id,name) values(2,"Andy")')
cursor.execute('insert into user(id,name) values(3,"Siri")')
# 關閉游標
cursor.close()
# 提交事務 使用數據庫連接對象進行提交事務
conn.commit()
# 關閉Connection
conn.close()

案例2：查看用戶信息

查找數據表中的數據可以使用SQL中的select語句：

select 字段名1，字段名2，字段名3， from 表名 where 查詢條件

這個是查詢符合條件的數據集，但是我們可能只需要某種數據，所以還需要具體的查詢語句。

fetchone():獲取查詢結果集中的下一條記錄
fetchmany(size):獲取指定數量的記錄
fetchall():獲取結構集所有記錄
import sqlite3
con = sqlite3.connect(‘mrsoft.db’) # 連接數據庫
創建一個cursor 獲取游標對象
cursor = con.cursor()
執行查詢語句
cursor.execute(‘select * from user’)
獲取查詢結果獲取的是第一條記錄
fetchone 返回的是一個元組
result1 = cursor.fetchone()
print(result1)
關閉游標
cursor.close()
關閉連接
con.close()
import sqlite3
con = sqlite3.connect(‘mrsoft.db’) # 連接數據庫
創建一個cursor 獲取游標對象
cursor = con.cursor()
執行查詢語句
cursor.execute(‘select * from user’)
獲取查詢結果獲取的是第一條記錄
fetchone 返回的是一個元組
result1 = cursor.fetchone()
print(result1)
使用fetchmany查詢多條數據
返回一個列表列表中有兩個元組
result2 = cursor.fetchmany(2)
print(result2)
result3 = cursor.fetchall() # 獲取所有的數據
print(result3)
關閉游標
cursor.close()
關閉連接
con.close()
import sqlite3
con = sqlite3.connect(‘mrsoft.db’) # 連接數據庫
創建一個cursor 獲取游標對象
cursor = con.cursor()
執行查詢語句查詢id > 1 所有元組
cursor.execute(‘select * from user where id > 1’)
獲取查詢結果獲取的是第一條記錄
fetchone 返回的是一個元組
result1 = cursor.fetchone()
print(result1)
使用fetchmany查詢多條數據
返回一個列表列表中有兩個元組
result2 = cursor.fetchmany(2)
print(result2)
result3 = cursor.fetchall() # 獲取所有的數據
print(result3)
關閉游標
cursor.close()
關閉連接
con.close()

案例3：修改用戶數據信息

語法如下：

update 表名 set 字段名 = 字段值 where 查詢條件
import sqlite3
# 連接數據庫
con = sqlite3.connect("mrsoft.db")
# 創建游標對象
cursor = con.cursor()
# 執行數據庫更新操作
cursor.execute('update user set name = "xiaxuefei" where id = 1')
cursor.execute('select * from user')
result = cursor.fetchall()
print(result)
cursor.close()
con.commit() # 提交事務
con.close()

案例4：刪除用戶信息

刪除數據庫表中的數據可以使用SQL中的delete語句，語法如下：

delete from 表名 where 查詢條件
import sqlite3
# 連接數據庫
con = sqlite3.connect('mrsoft.db')
# 創建一個游標對象
cursor = con.cursor()
# 刪除ID是1的用戶
cursor.execute('delete from user where id = 1')
# 獲取所有的用戶信息
cursor.execute('select * from user')
# 記錄查詢結果
result = cursor.fetchall()
print(result)
# 關閉游標
cursor.close()
# 提交事務
con.commit()
# 關閉connection
con.close()

三、MySql數據庫的使用

3.1 安裝MySql

進入選擇設置類型界面。五種類型：

Developer Default: 安裝MySql服務器以及開發MySql應用所需要的工具，工具包括開發和管理服務器的GUI平台，訪問操作數據的EXCEL插件，與Visual Studio 集成開發的插件、通過NET/Java/C/C++/ODBC 等訪問數據的連接器、官方示例教程、開發文檔。
Server only: 僅安裝MySql服務器，適用於部署MySql服務器
Client only: 僅安裝客戶端，適用於基於已經存在的mySql服務器進行MySql應用開發的情況
Full：安裝MySql所有的可用組件
Custom:自定義所需要的組件

3.2 設置環境變量

安裝完成之後，默認路徑肯定是“C:Program FilesMySQL Server 8.0in”。新建一個環境變量，寫入路徑。

3.3 啟動MySql

使用以下兩個命令：

net start mysql # 啟動mysql服務器
mysql -u root -p # 進入MySQL服務器，需要輸入密碼

注意輸入以上兩個命令，需要管理員權限。

3.4 使用navicat for mysql 管理軟件

這裡我安裝破解版navicat for mysql.

可以自定義安裝路徑：

然後使用PatchNavicat.exe進行破解。可以正常使用

鏈接：https://pan.baidu.com/s/1SeCf0BCIO1LHB5-tjPD0OQ?pwd=8x2u
提取碼：8x2u
--來自百度網盤超級會員V2的分享

然後新建連接：

輸入連接名，主機名或者IP地址填寫：localhost或者127.0.0.1,輸入MYsql數據庫的登陸密碼，這裡是root

下面使用Navicat創建一個Mrsoft的數據庫，右鍵單擊mr->選擇新建數據庫-> 輸入數據庫信息。

3.5 安裝PyMysql模塊

由於MySql服務器以獨立的進程運行，並通過網絡對外服務，所以需要支持Python的MySql驅動來連接到MySQl服務器，在python中支持MySql數據庫的模塊有很多，這裡選擇使用PyMySql。

安裝命令如下：

pip install PyMySQL

3.6 連接數據庫

import pymysql
# 打開數據庫連接 參數1：數據庫域名或者IP；參數2：數據庫賬號；參數3：數據庫密碼 參數4：數據庫名稱
db = pymysql.connect(host="localhost",user="root",password="123",database="mrsoft")
# 創建一個游標對象
cursor = db.cursor()
# 執行SQL查詢
cursor.execute("SELECT VERSION()")
# 使用fetchone() 獲取單條數據
data = cursor.fetchone()
print("DataBase version : %s " % data)
# 關閉游標對象連接
cursor.close()
# 關閉數據庫連接
db.close()

上面的代碼只是用來查看數據庫的版本。

運行結果如下：DataBase version : 5.1.32-community

四、創建數據表

下面通過execute()創建數據庫表。

4.1 創建books 圖書表

books表包含id(主鍵)、name(圖書的名稱)、category(圖書分類)、price(圖書價格)和publish_time(出版時間)共五個字段。

sql語句如下：

CREATE TABLE books(
id int(8) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
category varchar(50) NOT NULL,
price decimal(10,2) DEFAULT NULL,
publish_time date DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

創建數據庫表之前，先測試是否已經存在這個表：

drop table if exists books

表示如果mrsoft數據庫已經存在表books,那麼先刪除books,然後在創建books數據表。具體代碼如下：

import pymysql
# 打開數據庫
db = pymysql.connect(host='localhost',user='root',password='123',database='mrsoft')
# 使用cursor() 方法執行SQL，如果表存在則刪除
cursor = db.cursor()
# 使用execute 執行SQL，如果表存在 就刪除表
cursor.execute('DROP TABLE IF EXISTS books')
# 使用預處理語句創建表
sql = """
CREATE TABLE books(
id int(8) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
category varchar(50) NOT NULL,
price decimal(10,2) DEFAULT NULL,
publish_time date DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
"""
# 執行SQL語句
cursor.execute(sql)
db.close()

4.2 操作MySql數據表

MySQL數據表的操作主要是CRUD。

案例：批量添加圖書數據：

插入數據，可以使用execute()添加一條記錄，也可以使用executemany()方法批量添加多條記錄，executemany()語法如下：

executemany(operation,seq_of_params)

operation:操作的SQL語句
seq_of_params:參數序列

使用executemany() 批量添加多條記錄。

import pymysql
# 打開數據庫
db = pymysql.connect(host='localhost',user='root',password='123',database='mrsoft')
# 使用cursor() 方法執行SQL，如果表存在則刪除
cursor = db.cursor()
data = [("零基礎學習python",'python','1',"2018-09-10"),("零基礎學習java",'java','11',"2018-09-11")]
try:
# 執行sql語句 插入多條數據 第二個參數是 要插入的數據
cursor.executemany("insert into books(name,category,price,publish_time) values(%s,%s,%s,%s)",data)
# 提交事務
db.commit()
except:
# 發生錯誤時，回滾
db.rollback()
db.close()