pymysql模塊對(duì)數(shù)據(jù)庫(kù)的操作與備份

今天呢主要對(duì)pymysql模塊進(jìn)行使用講解一下：

https://www.cnblogs.com/lilidun/p/6041198.html

Linux系統(tǒng)上安裝pip3通過(guò)這個(gè)文檔查看

查詢操作：

import pymysql

db = pymysql.connect(host="localhost",user="root",password="",db="lxf",port=3306)
# 使用cursor（）獲取操作游標(biāo)
cur = db.cursor()

#查詢操作
sql = "select * from user"

try:
    cur.execute(sql)  # 執(zhí)行sql語(yǔ)句

    results = cur.fetchall()  # 獲取查詢的所有記錄
    print("id", "name", "password")
    # 遍歷結(jié)果
    for row in results:
        id = row[0]
        name = row[1]
        password = row[2]
        print(id, name, password)
except Exception as e:
    raise e
finally:
    db.close() #關(guān)閉連接

插入數(shù)據(jù)：

import pymysql
db = pymysql.connect(host='localhost',user='root',password='',db='lxf',port=3306)
cur = db.cursor()
#插入操作
sql_insert = """insert into user(id,name,pwd) values(5,'liu','123')"""

try:
    cur.execute(sql_insert)
    # 提交
    db.commit()
except Exception as e:
    # 錯(cuò)誤回滾
    db.rollback()
finally:
    db.close()

更新操作：

import pymysql

# 3.更新操作
db = pymysql.connect(host="localhost", user="root",
                     password="", db="lxf", port=3306)

# 使用cursor()方法獲取操作游標(biāo)
cur = db.cursor()

sql_update = "update user set name = '%s' where id = %d"

try:
    cur.execute(sql_update % ("lxf", 1))  # 像sql語(yǔ)句傳遞參數(shù)
    # 提交
    db.commit()
except Exception as e:
    # 錯(cuò)誤回滾
    db.rollback()
finally:
    db.close()

刪除操作：

import pymysql
db = pymysql.connect(host='localhost',user='root',password='',db='lxf',port=3306)
#使用游標(biāo)
cur = db.cursor()

sql_delete="delete from user where id = %d"
try:
    cur.execute(sql_delete %(1)) #傳遞參數(shù)
    #提交
    db.commit()
except Exception as e:
    #錯(cuò)誤回滾
    db.rollback()
finally:
    db.close()

數(shù)據(jù)庫(kù)備份：

#!/usr/bin/env python

import pymysql

import os

import time

TIME =time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())

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

conn = connect.cursor()

os.system("""mysqldump -uroot -p123456 test > /root/test.sql""")

 其實(shí)這里只是一個(gè)簡(jiǎn)單的備份，還可以加上time模塊備份每一天有日期，另外還可以加上hash模塊對(duì)密碼進(jìn)行加密，如果要想了解這兩個(gè)模塊的用發(fā)可以看看我的python分類中有相關(guān)的操作