Python x MySql

pip install pymysql

資料庫連線

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)

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)