Python全棧之學習MySQL(3)

# ### python 操作mysql 
import pymysql
# ### 1.基本語法
"""
# (1) 創建連接對象 host user password database 這四個參數必寫
conn = pymysql.connect( host="127.0.0.1" , user="root" , password="123456" , database="db003" , charset="utf8" , port=3306 )
# (2) 創建遊標對象 (用來操作數據庫的增刪改查)
cursor = conn.cursor()
print(cursor)
# (3) 執行sql語句
sql = "select * from employee"
# 執行查詢語句返回的總條數
res = cursor.execute(sql)
print(res) 
# (4) 獲取數據 fetchone 獲取一條數據
# 返回的是元組,裡面包含的是第一條的完整數據
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
# (5) 釋放遊標對象
cursor.close()
# (6) 釋放連接對象
conn.close()
"""
# ### 2.創建/刪除 表操作
# conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003")
# cursor = conn.cursor()
# 1.創建一張表
sql = """
create table t1(
id int unsigned primary key auto_increment,
first_name varchar(255) not null,
last_name varchar(255) not null,
sex tinyint not null,
age tinyint unsigned not null,
money float
);
"""
# res = cursor.execute(sql)
# print(res) # 無意義返回值
# 2.查詢表結構
"""
sql = "desc t1"
res = cursor.execute(sql)
print(res) # 返回的是字段的個數
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
"""
# 3.刪除表
"""
try:
	sql = "drop table t1"
	res = cursor.execute(sql)
	print(res) # 無意義返回值
except:
	pass
"""
# ### 3.事務處理
"""pymysql 默認開啟事務的,所有增刪改的數據必須提交,否則默認回滾;rollback"""
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003")
cursor = conn.cursor()
sql1 = "begin"
sql2 = "update employee set emp_name='程咬鉆石' where id = 18 "
sql3 = "commit"
res1 = cursor.execute(sql1)
res1 = cursor.execute(sql2)
res1 = cursor.execute(sql3)
# 一般在查詢的時候,通過fetchone來獲取結果
res1 = cursor.fetchone()
print(res1)

cursor.close()
conn.close()

# ### sql 註入攻擊
import pymysql
# (1) sql註入的現象
''' 現象:繞開賬號密碼登錄成功 '''
'''
user = input("請輸入您的用戶名>>>")
pwd  = input("請輸入您的密碼>>>")
conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="db005")
cursor = conn.cursor()
sql1 = """
create table usr_pwd(
id int unsigned primary key auto_increment,
username varchar(255) not null,
password varchar(255) not null
)
"""
sql2 = "select * from usr_pwd where username='%s' and password='%s' " % (user,pwd)
print(sql2)
res = cursor.execute(sql2)
print(res) # 1查到成功 0沒查到失敗
# res=cursor.fetchone()
"""
select * from usr_pwd where username='2222' or 4=4 -- aaa' and password='' 
相當於 : select * from usr_pwd where 10=10; 繞開瞭賬戶和密碼的判斷 -- 代表的是註釋;
"""
if res:
	print("登錄成功")
else:
	print("登錄失敗")
cursor.close()
conn.close()
'''
# (2) 預處理機制
""" 在執行sql語句之前,提前對sql語句中出現的字符進行過濾優化,避免sql註入攻擊 """
""" execute( sql , (參數1,參數2,參數3 .... ) ) execute2個參數默認開啟預處理機制 """
""" 填寫 234234' or 100=100 -- sdfsdfsdfsdf  嘗試攻擊  """

user = input("請輸入您的用戶名>>>")
pwd  = input("請輸入您的密碼>>>")
conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="db005")
cursor = conn.cursor()
sql = "select * from usr_pwd where username=%s and password=%s"
res = cursor.execute(sql , (user,pwd)  )
print(res)

print(    "登錄成功"  if res else "登錄失敗"    )
cursor.close()
conn.close()

# ### python 操作mysql 數據庫 (增刪改查)
import pymysql
"""
	python 操作mysql增刪改時,默認是開啟事務的,
	必須最後commit提交數據,才能產生變化
	提交數據: commit 
	默認回滾: rollback
"""
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db005")
# 默認獲取查詢結果時是元組,可以設置返回字典;  cursor=pymysql.cursors.DictCursor
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 執行對mysql 的操作
# 1.增
"""
sql = "insert into t1(first_name,last_name,sex,age,money) values(%s,%s,%s,%s,%s)"
# (1) 一次插入一條
res = cursor.execute( sql , ("孫","健",0,15,20000)  )
print(res) # 1
# 獲取最後插入這條數據的id號
print(cursor.lastrowid)
# (2) 一次插入多條
res = cursor.executemany(  sql , [  ("安","曉東",0,18,30000) , ("劉","玉波",1,20,50000) ,("張","光旭",0,80,60000) , ("李","是元",0,10,10) , ("高","大奧",1,20,80000)   ]   )
print(res) # 返回插入的條數
# 插入5條數據中的第一條數據的id
print(cursor.lastrowid)
# 獲取最後一個數據的id
sql = "select id from t1 order by id desc limit 1"
res = cursor.execute(sql)
print(res)
# 獲取結果,返回元組
res = cursor.fetchone()
print(res["id"])
# 默認元組 : (57, '高', '大奧', 1, 20, 80000.0)
# 返回字典 : {'id': 51, 'first_name': '高', 'last_name': '大奧', 'sex': 1, 'age': 20, 'money': 80000.0}
"""
# 2.刪
"""
sql = "delete from t1 where id in (%s,%s,%s)"
res = cursor.execute(sql , (3,4,5) )
print(res) # 返回的是3,代表刪除瞭3條
if res:
	print("刪除成功")
else:
	print("刪除失敗")
"""
# 3.改
"""
sql = "update t1 set first_name = '王' where id = %s"
sql = "update t1 set first_name = '王' where id in (%s,%s,%s,%s)"
res = cursor.execute(sql , (6,7,8,9))
print(res) # 返回的是4,代表修改瞭4條
if res:
	print("修改成功")
else:
	print("修改失敗")
"""
# 4.查
"""
fetchone  獲取一條
fetchmany 獲取多條
fetchall  獲取所有
"""	
sql = "select * from t1"
res = cursor.execute(sql)
print(res) # 針對於查詢語句來說,返回的res是總條數;
# (1) fetchone 獲取一條
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
# (2) fetchmany 獲取多條
res = cursor.fetchmany() # 默認獲取的是一條數據,返回列表,裡面裡面是一組一組的字典;
data = cursor.fetchmany(3)
print(data)
"""
[
	{'id': 9, 'first_name': '王', 'last_name': '是元', 'sex': 0, 'age': 10, 'money': 10.0}, 
	{'id': 10, 'first_name': '孫', 'last_name': '健', 'sex': 0, 'age': 15, 'money': 20000.0}, 
	{'id': 11, 'first_name': '安', 'last_name': '曉東', 'sex': 0, 'age': 18, 'money': 30000.0}
]
"""
for row in data:
	first_name = row["first_name"]
	last_name = row["last_name"]
	sex = row["sex"]
	if sex == 0:
		sex = "男性"
	else:
		sex = "女性"
	age = row["age"]
	money = row["money"]
	strvar = "姓:{},名:{},性別:{},年齡:{},收入:{}".format(first_name,last_name,sex,age,money)
print(strvar)
# (3) fetchall 獲取所有
# data = cursor.fetchall()
# print(data)
# (4) 自定義搜索查詢的位置
print("<==================>")
# 1.相對滾動 relative
"""相對於上一次查詢的位置往前移動(負數),或者往後移動(正數)"""
"""
cursor.scroll(-1,mode="relative")
# cursor.scroll(5,mode="relative")
res = cursor.fetchone()
print(res)
"""
# 2.絕對滾動 absolute
"""永遠從數據的開頭起始位置進行移動,不能向前滾"""
cursor.scroll(0,mode="absolute")
res = cursor.fetchone()
print(res)
conn.commit()
cursor.close()
conn.close()

# ### (1) 導入導出 不要加分號
導出數據庫
1.退出mysql
2.選擇要導出的默認路徑
3.mysqldump -uroot -p db001 > db001.sql
mysqldump -uroot -p123456 db001 表1 表2 > ceshi01.sql
導入數據庫
1.登錄到mysql之後
2.創建新的數據庫
# 註意：source db001.sql 後面沒有分號";"
3.source 路徑+文件
# ### (2) 如果服務器宕機瞭，有沒有備份數據，如何恢復數據
myisam：
	直接新建一個數據庫，然後把之前庫中的三個文件，粘貼復制到新的數據庫中，直接就恢復瞭
innodb:
# innodb 在隻有frm和ibd文件的情況下,如何恢復數據;
安裝 MySQL Utilities
https://downloads.mysql.com/archives/utilities/ 
cmd中找到frm那個文件,執行如下命令:
切換到對應目錄,執行下面語句,不要加分號
mysqlfrm --diagnostic ./文件目錄/t1.frm
查出建表語句,復制查詢出來的建表語句在mysql中創建的新數據中使用(新建一個數據庫，然後執行下面的建表語句)
CREATE TABLE `innodb1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB;

#對已創建的表進行表空間卸載 刪除ibd文件
mysql> alter table innodb1 discard tablespace;
把要恢復的idb文件替換進去
#對已創建的表進行空間裝載
mysql> alter table innodb1 import tablespace;

# ### (3) 配置linux下的編碼集
!includedir  /etc/mysql/conf.d/       客戶端的修改
# 設置mysql客戶端默認字符集
default-character-set=utf8
!includedir  /etc/mysql/mysql.conf.d/ 服務端的修改
# 服務端使用的字符集默認為8比特編碼的latin1字符集
character-set-server=utf8
# 重啟數據庫
service mysql restart

-- 是mysql的註釋  # 在sql註入那塊用到瞭
默認python連接mysql的時候，都是開啟瞭一個事務，尤其是增刪改，
在改變數據的時候，一定要提交數據，不提交數據(默認回滾)，不會真正的改變
\s 看一下服務器的數據

(1) 避免使用select *,
(2) 不確定表大小時候,先用count(*)查下數據.
(3) 創建表時盡量使用 char 代替 varchar
(4) 定長的字段放前面,變長的字段放後面.(盡可能小的改變樹狀結構高度)
(5) 組合索引代替多個單列索引
   （由於mysql中每次隻能使用一個索引，所以經常使用多個條件查詢時更適     合使用組合索引）
(6) 盡量使用短索引(小數據值)
(7) 重復少的字段值不適合做索引，例：性別不適合
(8) 使用連接（JOIN）來代替子查詢(Sub-Queries)