Python：mysql-connector-python子產品對MySQL資料庫進行增删改查

MySQL文檔： https://dev.mysql.com/doc/connector-python/en/ PYPI： https://pypi.org/project/mysql-connector-python/

mysql-connector-python 是MySQL官方的Python語言MySQL連接配接子產品

安裝

$ pip install mysql-connector-python      

代碼示例

連接配接管理

# -*- coding: utf-8 -*-

import mysql.connector

db_config = {
    "database": "mydata",
    "user": "root",
    "password": "123456",
    "host": "127.0.0.1",
    "port": 3306,
}

# 連接配接資料庫擷取遊标，可以設定傳回資料的格式，元組，指令元組，字典等...
connect = mysql.connector.Connect(**db_config)

cursor = connect.cursor(dictionary=True)

# 關閉遊标和連接配接
cursor.close()
connect.close()      

寫操作

資料 寫入 和 更新 都可以使用

execute

和

executemany

删除就使用

execute

寫操作都需要

commit

才會生效

# 插入元組資料
insert_tuple_sql = "insert into student(name, age) values(%s, %s)"
data = ("Tom", 23)
cursor.execute(insert_tuple_sql, data)
connect.commit()
print(cursor.lastrowid)   # 一般插入一條時使用，擷取插入id

# 插入多條元組資料
insert_tuple_sql = "insert into student(name, age) values(%s, %s)"
data = [
    ("Tom", 23),
    ("Jack", 25),
]
cursor.executemany(insert_tuple_sql, data)
connect.commit()


# 插入字典資料
insert_dict_sql = "insert into student(name, age) values(%(name)s, %(age)s)"
data = {
    "name": "Tom",
    "age": 25
}

cursor.execute(insert_dict_sql, data)
connect.commit()

# 插入多條字典資料
insert_dict_sql = "insert into student(name, age) values(%(name)s, %(age)s)"
data = [
    {
        "name": "Tom",
        "age": 26
    },
    {
        "name": "Jack",
        "age": 27
    }
]

cursor.executemany(insert_dict_sql, data)
connect.commit()
      

讀操作

cursor.execute("select * from student where name=%s limit 3", ("Tom",))
rows = cursor.fetchall()
print(rows)

# 因為開始設定的傳回資料格式是字典 dictionary ，是以直接傳回字典資料
# [
#  {'id': 1, 'name': 'Tom', 'age': 23},
#  {'id': 2, 'name': 'Tom', 'age': 23},
#  {'id': 3, 'name': 'Tom', 'age': 25}
#]

# 擷取一些中繼資料資訊
print(cursor.column_names)  # ('id', 'name', 'age')

print(cursor.description)
# [
#  ('id', 3, None, None, None, None, 0, 16899),
#  ('name', 253, None, None, None, None, 1, 0),
#  ('age', 3, None, None, None, None, 1, 0)
#]

print(cursor.rowcount)  # 3

print(cursor.statement)
# select * from student where name='Tom' limit 3

print(cursor.with_rows)
# True