Python3快速入門（十一）——Python3數(shù)據(jù)庫操作

一、mysql-connector 驅(qū)動

1、mysql-connector?簡介

mysql-connector?是?MySQL?官方提供的驅(qū)動器。
使用pipenv進行包管理，安裝命令如下：
pipenv install mysql-connector

2、mysql-connector常用接口

mysql.connector.connect(*args, **kwargs)
創(chuàng)建一個數(shù)據(jù)庫連接，返回MySQLConnectionAbstract實例。connect接收可變參數(shù)和關(guān)鍵字可變參數(shù)，通常接收可變參數(shù)時使用可變參數(shù)指定默認參數(shù)，接收關(guān)鍵字可變參數(shù)時使用字典類型參數(shù)。常用關(guān)鍵字如下：
'host': '127.0.0.1',
'user': 'root',
'password': 'root',
'port': 3306,
'database': 'test',
'charset': 'utf8'

            
              import mysql.connector

MYSQL_CONFIG = {
    "host":"47.110.45.34",
    "port":"3306",
    "user":"root",
    "passwd":"xxxxxx"
}

if __name__ == '__main__':
    mysql_db = mysql.connector.connect(**MYSQL_CONFIG)
    print(mysql_db)
            
          

MySQLConnectionAbstract常用接口如下
mysql.connector.close()
斷開到MySQL服務(wù)器的連接
mysql.connector.disconnect()
斷開到MySQL服務(wù)器的連接
mysql.connector.reconnect(attempts=1, delay=0)
重新建立到MySQL服務(wù)器的連接
mysql.connector.ping(reconnect=False, attempts=1, delay=0)
檢查MySQL服務(wù)器的可用性
mysql.connector.commit()
提交當(dāng)前事務(wù)
mysql.connector.rollback()
回滾當(dāng)前事務(wù)
mysql.connector.start_transaction(consistent_snapshot=False,
isolation_level=None, readonly=None)
啟動事務(wù)
mysql.connector.cursor(buffered=None,raw=None,prepared=None, cursor_class=None,dictionary=None, named_tuple=None)
創(chuàng)建一個游標(biāo)
MySQLCursorAbstract常用接口如下：
MySQLCursorAbstract.close()
關(guān)閉游標(biāo)
MySQLCursorAbstract.execute(operation,params=(),multi=False)
執(zhí)行SQL操作
MySQLCursorAbstract.executemany(operation, seqparams)
執(zhí)行SQL操作多次
MySQLCursorAbstract.fetchone()
返回查詢結(jié)果集的下一行，返回一個tuple或None。
MySQLCursorAbstract.fetchmany(size=1)
返回查詢結(jié)果集的后續(xù)size行，返回一個tuple的鏈表或空鏈表。
MySQLCursorAbstract.fetchall()
返回查詢結(jié)果集的所有行，返回一個tuple的鏈表
MySQLCursorAbstract.reset(self, free=True):
重置游標(biāo)到默認狀態(tài)

3、mysql-connector數(shù)據(jù)庫編程

            
              # -*- coding:utf-8 -*-
import mysql.connector

if __name__ == '__main__':
    mysql_db = mysql.connector.connect(
        host="47.110.45.34",
        port="3306",
        user="root",
        passwd="YangYuLiang@123.com")
    mysql_cursor = mysql_db.cursor()
    try:
        # 創(chuàng)建數(shù)據(jù)庫
        mysql_cursor.execute("CREATE DATABASE sites;")
        # 使用數(shù)據(jù)庫
        mysql_cursor.execute("USE sites;")
        # 創(chuàng)建數(shù)據(jù)庫表
        mysql_cursor.execute("CREATE TABLE website (name VARCHAR(255), url VARCHAR(255));")
        # 創(chuàng)建主鍵
        mysql_cursor.execute("ALTER TABLE website ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;")
        # 插入數(shù)據(jù)
        sql = "INSERT INTO website (name, url) VALUES (%s, %s);"
        value = ("BaiDu", "https://www.baidu.com")
        mysql_cursor.execute(sql, value)
        mysql_db.commit()
    except:
        mysql_db.rollback()
    # 批量插入數(shù)據(jù)
    sql = "INSERT INTO website (name, url) VALUES (%s, %s);"
    value = [
        ('Google', 'https://www.google.com'),
        ('Github', 'https://www.github.com'),
        ('TaoBao', 'https://www.taobao.com'),
        ('HuaWei', 'https://www.huawei.com')
    ]
    try:
        mysql_cursor.executemany(sql, value)
        # 更新記錄
        sql = "UPDATE website SET name = 'GitHub' WHERE name = 'Github'"
        mysql_cursor.execute(sql)
        mysql_db.commit()
    except:
        mysql_db.rollback()
    # 查詢記錄
    try:
        mysql_cursor.execute("SELECT * FROM website;")
        result = mysql_cursor.fetchall()  # fetchall() 獲取所有記錄
        for x in result:
            print(x)
        # 刪除記錄
        mysql_cursor.execute("DELETE FROM website WHERE name='Google';")
        # 刪除數(shù)據(jù)庫
        mysql_cursor.execute("DROP DATABASE sites;")
        mysql_db.commit()
    except:
        # 回滾
        mysql_db.rollback()
    # 關(guān)閉數(shù)據(jù)庫連接
    mysql_db.close()
            
          

二、PyMySQL驅(qū)動

1、PyMySQL簡介

PyMySQL 是在 Python3.x 版本中用于連接 MySQL 服務(wù)器的一個庫，Python2中則使用mysqldb。
PyMySQL 遵循 Python 數(shù)據(jù)庫 API v2.0 規(guī)范，并包含了 pure-Python MySQL 客戶端庫。
使用pipenv進行包管理，安裝命令如下：
pipenv install pymysql

2、PyMySQL常用接口

pymysql.Connect(*args, **kwargs)
連接到MySQL數(shù)據(jù)庫，建立一個socket連接并返回Connection對象。Connection接收的關(guān)鍵字參數(shù)如下：
Connection（host=None, user=None, password="",
database=None, port=0, unix_socket=None,
charset='', sql_mode=None,
read_default_file=None, conv=None, use_unicode=None,
client_flag=0, cursorclass=Cursor, init_command=None,
connect_timeout=10, ssl=None, read_default_group=None,
compress=None, named_pipe=None,
autocommit=False, db=None, passwd=None, local_infile=False,
max_allowed_packet=16 1024 1024, defer_connect=False,
auth_plugin_map=None, read_timeout=None, write_timeout=None,
bind_address=None, binary_prefix=False, program_name=None,
server_public_key=None)
Connection常用接口如下：
Connection.close()
發(fā)送退出消息，關(guān)閉到MySQL數(shù)據(jù)庫的socket連接
Connection.begin()
開啟事務(wù)
Connection.commit()
提交變化到穩(wěn)定存儲
Connection.rollback()
回滾當(dāng)前事務(wù)
Connection.select_db(self, db):
設(shè)置當(dāng)前數(shù)據(jù)庫，db為數(shù)據(jù)庫名稱
Connection.cursor(self, cursor=None)
創(chuàng)建一個用于SQL操作的游標(biāo)
Cursor常用接口如下：
Cursor.close()
關(guān)閉游標(biāo)
Cursor.execute(query, args=None)
執(zhí)行SQL查詢操作，返回受影響的行的數(shù)量
Cursor.executemany(query, args)
批量執(zhí)行SQL查詢操作，返回受影響的行的數(shù)量
Cursor.fetchone()
Fetch下一行
Cursor.fetchmany(size=None)
Fetch多行
Cursor.fetchall()
Fetch所有的行

3、PyMySQL數(shù)據(jù)庫編程

            
              import pymysql

if __name__ == '__main__':
    mysql_db = pymysql.Connect(
        host="47.110.45.34",
        port=3306,
        user='root',
        password='YangYuLiang@123.com')
    mysql_cursor = mysql_db.cursor()
    try:
        # 創(chuàng)建數(shù)據(jù)庫
        mysql_cursor.execute("CREATE DATABASE sites;")
        # 使用數(shù)據(jù)庫
        mysql_cursor.execute("USE sites;")
        # 創(chuàng)建數(shù)據(jù)庫表
        mysql_cursor.execute("CREATE TABLE website (name VARCHAR(255), url VARCHAR(255));")
        # 創(chuàng)建主鍵
        mysql_cursor.execute("ALTER TABLE website ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;")
        # 插入數(shù)據(jù)
        sql = "INSERT INTO website (name, url) VALUES (%s, %s);"
        value = ("BaiDu", "https://www.baidu.com")
        mysql_cursor.execute(sql, value)
        mysql_db.commit()
    except:
        mysql_db.rollback()
    # 批量插入數(shù)據(jù)
    sql = "INSERT INTO website (name, url) VALUES (%s, %s);"
    value = [
        ('Google', 'https://www.google.com'),
        ('Github', 'https://www.github.com'),
        ('TaoBao', 'https://www.taobao.com'),
        ('HuaWei', 'https://www.huawei.com')
    ]
    try:
        mysql_cursor.executemany(sql, value)
        # 更新記錄
        sql = "UPDATE website SET name = 'GitHub' WHERE name = 'Github'"
        mysql_cursor.execute(sql)
        mysql_db.commit()
    except:
        mysql_db.rollback()
    # 查詢記錄
    try:
        mysql_cursor.execute("SELECT * FROM website;")
        result = mysql_cursor.fetchall()  # fetchall() 獲取所有記錄
        for x in result:
            print(x)
        # 刪除記錄
        mysql_cursor.execute("DELETE FROM website WHERE name='Google';")
        # 刪除數(shù)據(jù)庫
        mysql_cursor.execute("DROP DATABASE sites;")
        mysql_db.commit()
    except:
        # 回滾
        mysql_db.rollback()
    # 關(guān)閉數(shù)據(jù)庫連接
    mysql_db.close()