這篇會來介紹如何使用 python 連接 MySQL 資料庫

安裝mysql-connector-python套件

mysql-connector-python套件為MySQL官方針對Python出的，只需要pip裝完後即可連線至MySQL Server，不需要在電腦上安裝其他東西。

$pip install mysql-connector-python

連線至MySQL資料庫程式

# 載入套件
import mysql.connector

# 建立MySQL連線
db = mysql.connector.connect(
    host='localhost',           # 連線主機名稱
    user='root',                # 登入帳號
    password='password',        # 登入密碼
    database = "testdatabase"   # 連接指定的 DATABASE
)

mycursor = db.cursor()


# 創建一個 DATABASE -> 建立後就不需要這句，直接在db內  database = "testdatabase" 連接指定的 DATABASE
# mycursor.execute("CREATE DATABASE testdatabase")

創建 TABLE

mycursor.execute("CREATE TABLE Person (name VARCHAR(50), age smallint UNSIGNED, personID int PRIMARY KEY AUTO_INCREMENT)")

獲取 Person 結構

mycursor.execute("DESCRIBE Person")

for x in mycursor:
    print(x)

添加資料庫資料

mycursor.execute("INSERT INTO Person (name, age) VALUES (%s,%s)",("AAA",19))
mycursor.execute("INSERT INTO Person (name, age) VALUES (%s,%s)",("BBB",91))
db.commit()    # 儲存變更

選擇資料

我們選擇全部資料，所以會顯示剛剛添加進資料庫的資料

mycursor.execute("SELECT * FROM Person")

for x in mycursor:
    print(x)

再建立一個 TABLE (Test)

# gender ENUM('M', 'F', 'O') => MALE or FEMALE or OTHER
mycursor.execute("CREATE TABLE Test (name VARCHAR(50) NOT NULL, created datetime NOT NULL, gender ENUM('M', 'F', 'O') NOT NULL, id  int PRIMARY KEY NOT NULL AUTO_INCREMENT)")

更改名稱

mycursor.execute("ALTER TABLE CHANGE name first_name varchar(50)")

mycursor.execute("DESCRIBE Test")
for x in mycursor:
    print(x)

添加資料庫資料(再增加一欄)

mycursor.execute("ALTER TABLE Test ADD COLUMN food varchar(50) NOT NULL")

mycursor.execute("DESCRIBE Test")
for x in mycursor:
    print(x)

刪除特定資料

mycursor.execute("DELETE FROM Test WHERE first_name = %s", ('BBB',))
db.commit()

參考資料 :
https://suyenting.github.io/post/python-connect-to-mysql/
Python MySQL Tutorial - Setup & Basic Queries (w/ MySQL Connector)
Python MySQL Tutorial - Creating Tables, Inserting & Selecting