Python PyMySQL操作MySQL資料庫

這裡主要介紹python 3中PyMySQL操作MySQL資料庫的方法

準備工作

在開始之前:

①確定已經安裝好了MySQL資料庫并保證能正常運作, 這裡我就不多說了, 不會的話自行百度

②確定安裝好PyMySQL庫

推薦pip安裝:

pip install pymsql

一、連接配接資料庫:

# 導入pymysql
import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306)

操作資料庫指令

定義光标

嗯~~ 插點東西, 下面會用到的(想了半天不知道插到哪, 就放這兒了)

實行sql語句
cursor.execute(sql)

資料插入
# 執行db對象的commit()方法才可以實作資料插入, 這個方法才是真正将語句送出到資料庫執行的方法, 對于資料插入、更新、删除操作, 都需要調用該方法才能生效
db.commit()

資料復原
# 如果執行失敗, 則調用rollback()執行資料復原, 相當于什麼都沒有發生過
db.rollback()

擷取目前MySQL版本

檢視已經存在的資料庫

實行SQL語句

cursor.execute(sql)

fetchone(): 傳回單個元組調用一次

fetchone()

方法, 指針就會指向下一條資料

fetchall(): 傳回所有元組會将結果以元祖形式全部傳回, 如果資料量很大, 那麼占用的開銷會非常高, 最好用

while

循環加

fetchone()

方法來擷取所有資料

cursor.execute(get_version_sql)

data = cursor.fetchone()
print('Database version:', data)
'''
我的傳回的是
> Database version: ('5.7.23-log',)
''''
cursor.execute(exist_sql)
tables = cursor.fetchall()
print(tables)
'''這個檢視資料庫的就不展示了!'''

關閉資料庫

二、建立/删除資料庫

建立資料庫

注意: MySQL自動将資料庫名的大寫字母替換為小寫字母(也沒什麼大不了的)

資料復原

db.rollback()

如果執行失敗, 則調用rollback()執行資料復原, 相當于什麼都沒有發生過

第一種方法: 直接建立資料庫

try:
	# 建立資料庫 預設字元集utf8
	sql = 'CREATE DATABASE 庫名 DEFAULT CHARACTER SET utf8'
	cursor.execute(sql)
except:
	print('Failure')
	db.rollback()

第二種方法: 判斷資料庫base_name是否存在, 如果不存在則建立資料庫base_name

import re
import pymysql

db = pymysql.connect(host='localhost', user='root', password='yao120', port=3306)
cursor = db.cursor()

# 檢視所有資料庫, 然後以清單形式傳回
cursor.execute('SHOW DATABASES')
tables = [cursor.fetchall()]
table_list = re.findall("\('(.*?)',\)", str(tables), re.I)
# 需要建立的資料庫
base_name = 'Once_Upon_a_Time'

if base_name.lower() not in table_list:
    # create dataase建立資料庫
    # default character set utf8 預設字元集utf8
    cursor.execute('CREATE DATABASE %s DEFAULT CHARACTER SET utf8' % base_name)
else:
    print(base_name + '資料庫已存在')
# 不要忘了close()一下
db.close()

删除資料庫

三、建立表

準備工作:

連結資料庫時指定資料庫 db=‘庫名’

import pymysql
# 連結mysql  db='庫名'
db = pymysql.connect(host='localhost', user='root', password='yao120', port=3306, db='Once_Upon_a_Time')
cursor = db.cursor()

建立表的格式:

第一種方法: 直接建立第一種方法: 直接建立

sql = '''CREATE TABLE 表名 (屬性名 資料類型 完整限制條件, 
							屬性名 資料類型 完整限制條件, 
  							...
    						屬性名 資料類型 完整限制條件)'''

第二種方法: 先判斷資料庫中是否有此表, 如果沒有則建立

sql = '''CREATE TABLE IF NOT EXISTS 表名(屬性名 資料類型 完整限制條件, 
										屬性名 資料類型 完整限制條件, 
  										...
    									屬性名 資料類型 完整限制條件)'''

上面提到的資料類型可以在菜鳥教程裡具體檢視。

完整限制條件:

PRIMARY KEY	辨別該屬性為該表的"主鍵", 可以唯一的辨別對應的元組
PRIMARY KEY	辨別該屬性為該表的"主鍵", 可以唯一的辨別對應的元組
FOREIGN KEY	辨別該屬性為該表的"外鍵", 是與之聯系某表的主鍵
NOT NULL	辨別該屬性"不能為空"
UNIQUE	辨別該屬性的值是"唯一"的
AUTO_INCREMENT	辨別該屬性的值是"自動增加", 這是MySQL的SQL語句的特色
DEFAULT	為該屬性設定預設值

設定表的主鍵

1.單字段主鍵格式:
 屬性名  資料類型 PRIMARY KEY
 執行個體:
   # id 為主鍵
   sql = 'CREATE TABLE 表名(id int PRIMARY KEY, name varchar(20))'
   
2.多字段主鍵格式:
 PRIMARY KEY(屬性名1, 屬性名2..., 屬性名n)
 執行個體:
   # id, stu_id為主鍵
   sql = 'CREATE TABLE 表名(id int, stu_id int, name varchar(20), PRIMARY KEY(id, stu_id))'

設定表的外鍵

格式:
  CONSTRAINT 外鍵别名 FOREIGN KEY(屬性1, 屬性2..., 屬性n) REFERENCES 表名(屬性1, 屬性2..., 屬性n)
  執行個體:
     sql = '''CREATE TABLE 表名1 (id int RPIMARY KEY,
    							stu_id int,
      							name varchar(20),
        						CONSTRAINT 外鍵别名 FOREIGN KEY(stu_id) REFERENCES 表名2(id))'''
	# 建立表1, 将表1的stu_id與表2的id連結

設定非空限制

格式:
  # 此屬性不能為空, 不填的話會"報錯"
  屬性名 資料類型 NOT NULL

設定表的唯一限制

格式:
 # 此屬性的值不能重複
 屬性名 資料類型 UNIQUE

設定表的屬性值自動增加

格式:
 # AUTO_INCREMNT限制的字段可以使任何整數類型(TINYINT, SMALLINT, INT和BIGINT), 在預設的情況下, 該字段是從1開始自增
 屬性名 資料類型 AUTO_INCREMNT

設定表的屬性的預設值

格式:
屬性名 資料類型 DEFAULT 預設值

綜合示例

sql = '''CREATE TABLE hero(id int PRIMARY KEY AUTO_INCREMENT,
  							name varchar(20) NOT NULL,
  							sex varchar(10) DEFAULT "male")'''
# 建立hero表, id為主鍵和自動增加, name為非空, sex預設是'male'

檢視表結構

通過檢視表的結構, 就很明确的對表進行解讀, 而且可以檢視一下自己建立的表有沒有錯誤

格式:
  DESCRIBE 表名

檢視表詳細結構

通過這個SQL語句可以檢視表的詳細定義, 除了字段名, 字段的資料類型, 限制條件外, 還可以檢視表的預設存儲引擎和字元編碼

格式:
  SHOW CREATE TABLE 表名

四、修改表

1.修改表名:
  格式:
    ALTER TABLE 原表名 RENAME 新表明
    
2.修改字段的資料類型:
  格式:
    ALTER TABLE 表名 MODIFY 屬性名 資料類型

3.修改字段名:
  格式:
    ALTER TABLE 表名 CHANGE 原屬性名 新屬性名 新資料類型

4.增加字段:
  格式:
    ALTER TABLE 表名 ADD 屬性名1 資料類型 完整性限制條件 [FIRST | AFTER 屬性名2]
    # 其中, "屬性名1"參數指需要增加的字段的名稱, "FIRST"參數是可選參數, 起作用是将新增字段設定為表的第一個字段, "AFTER"參數也是可選的參數, 其作用是将新增字段添加到已有的"屬性名2"字段的後面.

5.删除字段:
  格式:
    ALTER TABLE 表名 DROP 屬性名

6.更改表的存儲引擎:
  格式:
    ALTER TABLE 表名 ENGINE = 存儲引擎名
    
7.删除表的外鍵限制:
  格式:
    ALTER TABLE 表名 DROP FOREIGN KEY 外鍵别名

五、删除表

格式:
  # 删除沒有被關聯的普通表
  DROP TABLE 表名
  # 删除被其他表關聯的父表
  1. 先删除子表, 再删除父表
  2. 删除父表的外鍵限制, 再删該表

六、查詢表

sql = 'select table_name from information_schema.tables where table_schema="Once_Upon_a_Time" and table_type="base table"'
cursor.execute(sql)
 # 表的總數
count = cursor.rowcount
print(count)
 # 周遊所有表
row = cursor.fetchone()
rows = []
while row:
	rows += row
	row = cursor.fetchone()
print(rows)

七、插入資料

格式:
  INSERT INTO 表名 (屬性名1, 屬性名2..., 屬性名n) values(屬性值1, 屬性值2..., 屬性值n)

執行個體:

~~注意: 如果資料類型是字元串的, 需要加上雙引号, 不然報錯

def insert_into(table_name, name, age):
  insert_sql = 'INSERT INTO %s(name, age) values("%s", %s)'
  try:
      cursor.execute(insert_sql % (table_name, name, age))
      # commit() 送出
      db.commit()
      print('資料添加成功!')

  except:
      '''
      注意: 如果執行失敗, 則調用rollback()執行資料復原, 相當于什麼都沒有發生過.
      '''
      db.rollback()

八、删除資料

格式:
	DELETE FROM 表名 WHERE 屬性名="屬性值"

def delete_field(table_name, del_id):
    # 删除一條資料
    '''
    delete 删除    from 從哪裡    where 删除的資料資訊
    table_name 表名
    del_id 想要删除的id
    '''
    delete_sql = 'DELETE FROM %s where %s="%s"'
    try:
        cursor.execute(delete_sql % (table_name, 'id', del_id))
        db.commit()
        print('id:%s 删除成功!' % del_id)
    except:
        db.rollback()
        print('delete Failed')

九、更新資料

格式:
	UPDATE 表名 SET 更新的屬性名1 = 更新的屬性值1 WHERE 屬性名2 = 屬性值2
  屬性名2最好是有唯一限制的, 不會重複

執行個體

def update(table_name, name, age):
    # 更新清單中, name為%s的age屬性為%s
    update_sql = 'UPDATE %s SET age = %s WHERE name = %s'
    try:
        cursor.execute(update_sql, (table_name, age, name))
        db.commit()
    except:
        db.rollback()
        print('Failed')

十、更新+插入資料(結合了更新和插入的操作)

判斷有着

唯一限制的

id

是否存在如果存在則更新資料, 如果不存在則插入資料

def insert_or_update(table_name, id, name, age):
    table_name = tuple([table_name])
    data = id, name, age
    sql = 'INSERT INTO %s(id, name, age) values (%s, "%s", %s) ON DUPLICATE KEY UPDATE id = %s, name = "%s", age = %s'
    try:
        cursor.execute(sql % (table_name + data * 2))
        db.commit()
        print('資料更新成功!')
    except:
        db.rollback()
        print('Failed')

十一、查詢資料

格式:
	SELECT * FROM 表名 WHERE 屬性名=屬性值

執行個體:

sql = 'SELECT * FROM hero WHERE name = "Snow"'
try:
	cursor.execute(sql)
	count = cursor.rowcount
	print('Snow共有%d條資料' % count)
	row = cursor.fetchone()
	while row:
		row_list = list(row)
		row = cursor.fetchone()
	print(row_list)
except:
	print('Failed')

Python PyMySQL操作MySQL資料庫Python PyMySQL操作MySQL資料庫

Python PyMySQL操作MySQL資料庫

準備工作

一、連接配接資料庫:

操作資料庫指令

二、建立/删除資料庫

建立資料庫

删除資料庫

三、建立表

準備工作:

四、修改表

五、删除表

六、查詢表

七、插入資料

八、删除資料

九、更新資料

十、更新+插入資料(結合了更新和插入的操作)

十一、查詢資料

以上就是我對MySQL的一點心得,

第一次寫部落格, 如果哪裡有錯, 或是頁面布局又不舒服的, 歡迎在評論區留言。

繼續閱讀

Small tricks

libsvm for python 安裝

學習軟體測試基礎測試第七天

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Zeppelin 配置通路 REST APIApache Zeppelin Configuration REST API

【Torch】最簡潔logging使用指南

27. Remove Element(清單)題目代碼

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

Cloud Studio初體驗

使用 ctypes 進行 Python 和 C 的混合程式設計

【python】【資料處理】畫多元資料分布圖

mysql使用source指令導入.sql檔案

【python】netconf協定對接管理裝置

「Python 網絡自動化」NETCONF —— Python 使用 NETCONF 管理配置 H3C 網絡裝置

在python中建立excel并寫入

Python PyMySQL操作MySQL資料庫Python PyMySQL操作MySQL資料庫

Python PyMySQL操作MySQL資料庫

準備工作

一、連接配接資料庫:

操作資料庫指令

二、建立/删除資料庫

建立資料庫

删除資料庫

三、建立表

準備工作:

四、修改表

五、删除表

六 、查詢表

七、插入資料

八、删除資料

九、更新資料

十、更新+插入資料(結合了更新和插入的操作)

十一、查詢資料

以上就是我對MySQL的一點心得,

第一次寫部落格, 如果哪裡有錯, 或是頁面布局又不舒服的, 歡迎在評論區留言。

繼續閱讀

六、查詢表