Python x MySql

pip install pymysql

原碼:https://reurl.cc/bGkxKr

資料庫連線

import pymysql
# 資料庫設定
db_settings = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "資料庫管理員密碼",
    "db": "test2022",
    "charset": "utf8"
}
try:
    # 建立Connection物件
    conn = pymysql.connect(**db_settings)
except Exception as ex:
    print(ex)

資料庫的連線建立完成後，要進行相關的操作，需要建立Cursor(指標)物件來執行，這邊使用Python的with陳述式，當資料庫存取完成後，自動釋放連線。

import pymysql

# 資料庫設定
db_settings = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "資料庫管理員密碼",
    "db": "test2022",
    "charset": "utf8"
}
try:
    # 建立Connection物件
    conn = pymysql.connect(**db_settings)
    # 建立Cursor物件
    with conn.cursor() as cursor:
      #資料表相關操作
        print("ok")
except Exception as ex:
    print(ex)

INSERT

import pymysql

# 資料庫設定
db_settings = {
"host": "127.0.0.1",
"port": 3306,
"user": "root",
"password": "資料庫管理員密碼",
"db": "test2022",
"charset": "utf8"
}
try:
    # 建立Connection物件
    conn = pymysql.connect(**db_settings)
    # 建立Cursor物件
    with conn.cursor() as cursor:
      #資料表相關操作
         # 新增資料SQL語法
        command = "INSERT INTO member(m_name, m_mail)VALUES(%s, %s)"
        cursor.execute(command, ("test", "1234@cc"))
        # 儲存變更
        conn.commit()
except Exception as ex:
    print(ex)

SELECT

import pymysql

# 資料庫設定
db_settings = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
　　"password": "資料庫管理員密碼",
    "db": "test2022",
    "charset": "utf8"
}
try:
    # 建立Connection物件
    conn = pymysql.connect(**db_settings)
    # 建立Cursor物件
    with conn.cursor() as cursor:
        # 查詢資料SQL語法
        command = "SELECT * FROM member"
        # 執行指令
        cursor.execute(command)
        # 取得所有資料
        result = cursor.fetchall()
        print(result)
except Exception as ex:
    print(ex)

Select 取得單筆資料 fetchone()

import pymysql

# 資料庫設定
db_settings = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "資料庫管理員密碼",
    "db": "test2022",
    "charset": "utf8"
}
try:
    # 建立Connection物件
    conn = pymysql.connect(**db_settings)

    # 建立Cursor物件
    with conn.cursor() as cursor:
        # 新增資料指令
        command = "SELECT * FROM member"
        # 執行指令
        cursor.execute(command)
        # 取得第一筆資料
        #result = cursor.fetchone()
        # 取得前五筆資料
        result = cursor.fetchmany(5)
        print(result)
except Exception as ex:
    print(ex)

import pymysql

# 資料庫設定
db_settings = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "資料庫管理員密碼",
    "db": "test2022",
    "charset": "utf8"
}
try:
    # 建立Connection物件
    conn = pymysql.connect(**db_settings)

    # 建立Cursor物件
    with conn.cursor() as cursor:
        # 新增資料指令
        command = "SELECT * FROM member WHERE m_no = %s"
        # 執行指令
        cursor.execute(command, ("10",))
        # 取得所有資料
        result = cursor.fetchall()
        print(result)
except Exception as ex:
    print(ex)

UPDATE

import pymysql

# 資料庫設定
db_settings = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "資料庫管理員密碼",
    "db": "test2022",
    "charset": "utf8"
}
try:
    # 建立Connection物件
    conn = pymysql.connect(**db_settings)

    # 建立Cursor物件
    with conn.cursor() as cursor:
        # 修改資料SQL語法
        command = "UPDATE member SET m_name = %s WHERE m_no = %s"
        # 執行指令
        cursor.execute(command, ("cchaha", "10"))
      
        #儲存變更
        conn.commit()
except Exception as ex:
    print(ex)

DELETE

import pymysql

# 資料庫設定
db_settings = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "資料庫管理員密碼",
    "db": "test2022",
    "charset": "utf8"
}
try:
    # 建立Connection物件
    conn = pymysql.connect(**db_settings)

# 建立Cursor物件
    with conn.cursor() as cursor:
        # 刪除特定資料指令
        command = "DELETE FROM member WHERE m_no = %s"
        # 執行指令
        cursor.execute(command, ("10",))
        #儲存變更
        conn.commit()
except Exception as ex:
    print(ex)