Python3快速入門(十一)——Python3數據庫操作

一、MySQL-connector 驅動

1、mysql-connector?簡介

mysql-connector?是?MySQL?官方提供的驅動器。使用pipenv進行包管理,安裝命令如下:pipenv install mysql-connector

2、mysql-connector常用接口

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

import mysql.connectorMYSQL_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服務器的連接mysql.connector.disconnect()斷開到MySQL服務器的連接mysql.connector.reconnect(attempts=1, delay=0)重新建立到MySQL服務器的連接mysql.connector.ping(reconnect=False, attempts=1, delay=0)檢查MySQL服務器的可用性mysql.connector.commit()提交當前事務mysql.connector.rollback()回滾當前事務mysql.connector.start_transaction(consistent_snapshot=False,isolation_level=None, readonly=None)啟動事務mysql.connector.cursor(buffered=None,raw=None,prepared=None, cursor_class=None,dictionary=None, named_tuple=None)創建一個游標MySQLCursorAbstract常用接口如下:MySQLCursorAbstract.close()關閉游標MySQLCursorAbstract.execute(operation,params=(),multi=False)執行SQL操作MySQLCursorAbstract.executemany(operation, seqparams)執行SQL操作多次MySQLCursorAbstract.fetchone()返回查詢結果集的下一行,返回一個tuple或None。MySQLCursorAbstract.fetchmany(size=1)返回查詢結果集的后續size行,返回一個tuple的鏈表或空鏈表。MySQLCursorAbstract.fetchall()返回查詢結果集的所有行,返回一個tuple的鏈表MySQLCursorAbstract.reset(self, free=True):重置游標到默認狀態

3、mysql-connector數據庫編程

# -*- coding:utf-8 -*-import mysql.connectorif __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:        # 創建數據庫        mysql_cursor.execute("CREATE DATABASE sites;")        # 使用數據庫        mysql_cursor.execute("USE sites;")        # 創建數據庫表        mysql_cursor.execute("CREATE TABLE website (name VARCHAR(255), url VARCHAR(255));")        # 創建主鍵        mysql_cursor.execute("ALTER TABLE website ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;")        # 插入數據        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()    # 批量插入數據    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';")        # 刪除數據庫        mysql_cursor.execute("drop DATABASE sites;")        mysql_db.commit()    except:        # 回滾        mysql_db.rollback()    # 關閉數據庫連接    mysql_db.close()

二、PyMySQL驅動

1、PyMySQL簡介

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

2、PyMySQL常用接口

pymysql.Connect(*args, **kwargs)連接到MySQL數據庫,建立一個socket連接并返回Connection對象。Connection接收的關鍵字參數如下: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=1610241024, 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()發送退出消息,關閉到MySQL數據庫的socket連接Connection.begin()開啟事務Connection.commit()提交變化到穩定存儲Connection.rollback()回滾當前事務Connection.select_db(self, db):設置當前數據庫,db為數據庫名稱Connection.cursor(self, cursor=None)創建一個用于SQL操作的游標Cursor常用接口如下:Cursor.close()關閉游標Cursor.execute(query, args=None)執行SQL查詢操作,返回受影響的行的數量Cursor.executemany(query, args)批量執行SQL查詢操作,返回受影響的行的數量Cursor.fetchone()Fetch下一行Cursor.fetchmany(size=None)Fetch多行Cursor.fetchall()Fetch所有的行

3、PyMySQL數據庫編程

import pymysqlif __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:        # 創建數據庫        mysql_cursor.execute("CREATE DATABASE sites;")        # 使用數據庫        mysql_cursor.execute("USE sites;")        # 創建數據庫表        mysql_cursor.execute("CREATE TABLE website (name VARCHAR(255), url VARCHAR(255));")        # 創建主鍵        mysql_cursor.execute("ALTER TABLE website ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;")        # 插入數據        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()    # 批量插入數據    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';")        # 刪除數據庫        mysql_cursor.execute("drop DATABASE sites;")        mysql_db.commit()    except:        # 回滾        mysql_db.rollback()    # 關閉數據庫連接    mysql_db.close()