MySQL và Python Tương Tác

Chuẩn bị dữ liệu Tạo bảng dữ liệu

-- Tạo cơ sở dữ liệu "jing_dong"
CREATE DATABASE jing_dong CHARACTER SET utf8;

-- Sử dụng cơ sở dữ liệu "jing_dong"
USE jing_dong;

-- Tạo bảng "goods"
CREATE TABLE goods (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(150) NOT NULL,
    cate_name VARCHAR(40) NOT NULL,
    brand_name VARCHAR(40) NOT NULL,
    price DECIMAL(10,3) NOT NULL DEFAULT 0,
    is_show BIT NOT NULL DEFAULT 1,
    is_saleoff BIT NOT NULL DEFAULT 0
);

Chèn dữ liệu

-- Chèn dữ liệu vào bảng "goods"

INSERT INTO goods (id, name, cate_name, brand_name, price, is_show, is_saleoff) VALUES
(0, 'r510vc 15.6 inch laptop', 'Laptop', 'Asus', 3399, DEFAULT, DEFAULT),
(0, 'y400n 14.0 inch laptop', 'Laptop', 'Lenovo', 4999, DEFAULT, DEFAULT),
(0, 'g150th 15.6 inch gaming laptop', 'Gaming Laptop', 'Thunderobot', 8499, DEFAULT, DEFAULT),
(0, 'x550cc 15.6 inch laptop', 'Laptop', 'Asus', 2799, DEFAULT, DEFAULT),
(0, 'x240 ultrabook', 'Ultrabook', 'Lenovo', 4880, DEFAULT, DEFAULT),
(0, 'u330p 13.3 inch ultrabook', 'Ultrabook', 'Lenovo', 4299, DEFAULT, DEFAULT),
(0, 'svp13226scb touch ultrabook', 'Ultrabook', 'Sony', 7999, DEFAULT, DEFAULT),
(0, 'ipad mini 7.9 inch tablet', 'Tablet', 'Apple', 1998, DEFAULT, DEFAULT),
(0, 'ipad air 9.7 inch tablet', 'Tablet', 'Apple', 3388, DEFAULT, DEFAULT),
(0, 'ipad mini with retina display', 'Tablet', 'Apple', 2788, DEFAULT, DEFAULT),
(0, 'ideacentre c340 20 inch all-in-one pc', 'Desktop', 'Lenovo', 3499, DEFAULT, DEFAULT),
(0, 'vostro 3800-r1206 desktop', 'Desktop', 'Dell', 2899, DEFAULT, DEFAULT),
(0, 'imac me086ch/a 21.5 inch all-in-one pc', 'Desktop', 'Apple', 9188, DEFAULT, DEFAULT),
(0, 'at7-7414lp desktop (linux)', 'Desktop', 'Acer', 3699, DEFAULT, DEFAULT),
(0, 'z220sff f4f06pa workstation', 'Server/Workstation', 'HP', 4288, DEFAULT, DEFAULT),
(0, 'poweredge ii server', 'Server/Workstation', 'Dell', 5388, DEFAULT, DEFAULT),
(0, 'mac pro professional desktop', 'Server/Workstation', 'Apple', 28888, DEFAULT, DEFAULT),
(0, 'hmz-t3w head-mounted display', 'Laptop Accessories', 'Sony', 6999, DEFAULT, DEFAULT),
(0, 'business backpack', 'Laptop Accessories', 'Sony', 99, DEFAULT, DEFAULT),
(0, 'x3250 m4 rack server', 'Server/Workstation', 'IBM', 6888, DEFAULT, DEFAULT),
(0, 'business backpack', 'Laptop Accessories', 'Sony', 99, DEFAULT, DEFAULT);

Luyện tập SQL Cải thiện câu lệnh SQL

Truy vấn tên và giá của sản phẩm loại "Ultrabook"

SELECT name, price FROM goods WHERE cate_name = 'Ultrabook';

Hiển thị các loại sản phẩm

SELECT cate_name FROM goods GROUP BY cate_name;

Tính giá trung bình của tất cả các sản phẩm và làm tròn đến 2 chữ số thập phân

SELECT ROUND(AVG(price), 2) AS avg_price FROM goods;

Hiển thị giá trung bình của mỗi loại sản phẩm

SELECT cate_name, AVG(price) FROM goods GROUP BY cate_name;

Truy vấn giá cao nhất, thấp nhất, trung bình, và số lượng của mỗi loại sản phẩm

SELECT cate_name, MAX(price), MIN(price), AVG(price), COUNT(*) FROM goods GROUP BY cate_name;

Truy vấn tất cả các sản phẩm có giá cao hơn giá trung bình và sắp xếp theo giá giảm dần

SELECT id, name, price FROM goods 
WHERE price > (SELECT ROUND(AVG(price), 2) AS avg_price FROM goods) 
ORDER BY price DESC;

Truy vấn thông tin về sản phẩm đắt nhất của mỗi loại

SELECT * FROM goods
INNER JOIN 
    (SELECT cate_name, MAX(price) AS max_price, MIN(price) AS min_price, AVG(price) AS avg_price, COUNT(*) FROM goods GROUP BY cate_name) AS goods_new_info 
ON goods.cate_name = goods_new_info.cate_name AND goods.price = goods_new_info.max_price;

Tạo bảng "Danh mục Sản phẩm"

-- Tạo bảng danh mục sản phẩm
CREATE TABLE IF NOT EXISTS goods_cates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL
);

Truy vấn các loại sản phẩm từ bảng "goods"

SELECT cate_name FROM goods GROUP BY cate_name;

Ghi kết quả nhóm vào bảng "goods_cates"

INSERT INTO goods_cates (name) SELECT cate_name FROM goods GROUP BY cate_name;

Đồng bộ dữ liệu bảng

Cập nhật bảng "goods" thông qua bảng "goods_cates"

UPDATE goods AS g INNER JOIN goods_cates AS c ON g.cate_name = c.name SET g.cate_name = c.id;

Tạo bảng "Nhãn hiệu Sản phẩm"

Tạo bảng và chèn dữ liệu cùng một lúc

-- Tạo bảng nhãn hiệu sản phẩm
CREATE TABLE goods_brands (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL
) SELECT brand_name AS name FROM goods GROUP BY brand_name;

Đồng bộ dữ liệu

Cập nhật bảng "goods" thông qua bảng "goods_brands"

UPDATE goods AS g INNER JOIN goods_brands AS b ON g.brand_name = b.name SET g.brand_name = b.id;

Sửa cấu trúc bảng

Kiểm tra cấu trúc bảng "goods"

DESCRIBE goods;

Sửa cấu trúc bảng

ALTER TABLE goods 
CHANGE cate_name cate_id INT UNSIGNED NOT NULL,
CHANGE brand_name brand_id INT UNSIGNED NOT NULL;

KRAINT

Chèn dữ liệu vào bảng "goods_cates" và "goods_brands"

INSERT INTO goods_cates (name) VALUES ('Router'), ('Switch'), ('Network Card');
INSERT INTO goods_brands (name) VALUES ('Haier'), ('Tsinghua Tongfang'), ('Hasee');

Chèn dữ liệu vào bảng "goods"

INSERT INTO goods (name, cate_id, brand_id, price) VALUES ('LaserJet Pro P1606dn Black and White Laser Printer', 12, 4, 1849);

Truy vấn thông tin chi tiết của tất cả các sản phẩm (sử dụng INNER JOIN)

SELECT g.id, g.name, c.name, b.name, g.price FROM goods AS g
INNER JOIN goods_cates AS c ON g.cate_id = c.id
INNER JOIN goods_brands AS b ON g.brand_id = b.id;

Truy vấn thông tin chi tiết của tất cả các sản phẩm (sử dụng LEFT JOIN)

SELECT g.id, g.name, c.name, b.name, g.price FROM goods AS g
LEFT JOIN goods_cates AS c ON g.cate_id = c.id
LEFT JOIN goods_brands AS b ON g.brand_id = b.id;

Thêm ràng buộc khóa ngoại

-- Thêm ràng buộc khóa ngoại cho brand_id
ALTER TABLE goods ADD FOREIGN KEY (brand_id) REFERENCES goods_brands(id);

-- Thêm ràng buộc khóa ngoại cho cate_id (có thể lỗi nếu đã có giá trị không hợp lệ)
ALTER TABLE goods ADD FOREIGN KEY (cate_id) REFERENCES goods_cates(id);

Tạo bảng với ràng buộc khóa ngoại

CREATE TABLE goods (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(40) DEFAULT '',
    price DECIMAL(5,2),
    cate_id INT UNSIGNED,
    brand_id INT UNSIGNED,
    is_show BIT DEFAULT 1,
    is_saleoff BIT DEFAULT 0,
    FOREIGN KEY (cate_id) REFERENCES goods_cates(id),
    FOREIGN KEY (brand_id) REFERENCES goods_brands(id)
);

Xóa ràng buộc khóa ngoại

-- Xem tạo bảng để lấy tên ràng buộc
SHOW CREATE TABLE goods;

-- Xóa ràng buộc theo tên
ALTER TABLE goods DROP FOREIGN KEY tên_ràng_buộc;

Thiết kế cơ sở dữ liệu Tạo bảng "Danh mục Sản phẩm"

CREATE TABLE goods_cates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(40) NOT NULL
);

Tạo bảng "Nhãn hiệu Sản phẩm"

CREATE TABLE goods_brands (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(40) NOT NULL
);

Tạo bảng "Sản phẩm"

CREATE TABLE goods (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(40) DEFAULT '',
    price DECIMAL(5,2),
    cate_id INT UNSIGNED,
    brand_id INT UNSIGNED,
    is_show BIT DEFAULT 1,
    is_saleoff BIT DEFAULT 0,
    FOREIGN KEY (cate_id) REFERENCES goods_cates(id),
    FOREIGN KEY (brand_id) REFERENCES goods_brands(id)
);

Tạo bảng "Khách hàng"

CREATE TABLE customer (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(30) NOT NULL,
    addr VARCHAR(100),
    tel VARCHAR(11) NOT NULL
);

Tạo bảng "Đơn hàng"

CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    order_date_time DATETIME NOT NULL,
    customer_id INT UNSIGNED,
    FOREIGN KEY (customer_id) REFERENCES customer(id)
);

Tạo bảng "Chi tiết Đơn hàng"

CREATE TABLE order_detail (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    order_id INT UNSIGNED NOT NULL,
    goods_id INT UNSIGNED NOT NULL,
    quantity TINYINT UNSIGNED NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (goods_id) REFERENCES goods(id)
);

Thao tác MySQL trong Python Nhập mô-đun

from pymysql import *

Đối tượng Connection

Tạo kết nối với cơ sở dữ liệu

conn = connect(host='localhost', port=3306, database='jing_dong', user='root', password='mysql', charset='utf8')

Các phương thức
close(): đóng kết nối
commit(): xác nhận thay đổi
cursor(): trả về đối tượng Cursor

Đối tượng Cursor

Thực hiện câu lệnh SQL

cs1 = conn.cursor()

Các phương thức
close(): đóng
execute(operation, [parameters]): thực thi câu lệnh, trả về số dòng ảnh hưởng
fetchone(): lấy một dòng kết quả
fetchall(): lấy tất cả các dòng kết quả

CRUD

from pymysql import *

def main():
    # Tạo kết nối
    conn = connect(host='localhost', port=3306, database='jing_dong', user='root', password='mysql', charset='utf8')
    # Tạo đối tượng Cursor
    cs1 = conn.cursor()
    
    # Thêm
    count = cs1.execute('INSERT INTO goods_cates (name) VALUES ("Hard Disk")')
    print(count)
    count = cs1.execute('INSERT INTO goods_cates (name) VALUES ("Optical Disk")')
    print(count)
    
    # Xác nhận thay đổi
    conn.commit()
    
    # Đóng
    cs1.close()
    conn.close()

if __name__ == '__main__':
    main()

Truy vấn một dòng

from pymysql import *

def main():
    # Tạo kết nối
    conn = connect(host='localhost', port=3306, user='root', password='mysql', database='jing_dong', charset='utf8')
    # Tạo đối tượng Cursor
    cs1 = conn.cursor()
    
    # Thực hiện truy vấn
    count = cs1.execute('SELECT id, name FROM goods WHERE id >= 4')
    print(f"Truy vấn {count} dòng dữ liệu:")
    
    for i in range(count):
        result = cs1.fetchone()
        print(result)
    
    # Đóng
    cs1.close()
    conn.close()

if __name__ == '__main__':
    main()

Truy vấn nhiều dòng

from pymysql import *

def main():
    # Tạo kết nối
    conn = connect(host='localhost', port=3306, user='root', password='mysql', database='jing_dong', charset='utf8')
    # Tạo đối tượng Cursor
    cs1 = conn.cursor()
    
    # Thực hiện truy vấn
    count = cs1.execute('SELECT id, name FROM goods WHERE id >= 4')
    print(f"Truy vấn {count} dòng dữ liệu:")
    
    result = cs1.fetchall()
    print(result)
    
    # Đóng
    cs1.close()
    conn.close()

if __name__ == '__main__':
    main()

Tham số hóa

Tham số hóa câu lệnh SQL để tránh SQL injection

from pymysql import *

def main():
    find_name = input("Nhập tên sản phẩm: ")
    
    # Tạo kết nối
    conn = connect(host='localhost', port=3306, user='root', password='mysql', database='jing_dong', charset='utf8')
    # Tạo đối tượng Cursor
    cs1 = conn.cursor()
    
    # Cách an toàn
    params = [find_name]
    count = cs1.execute('SELECT * FROM goods WHERE name = %s', params)
    print(f"Truy vấn {count} dòng dữ liệu:")
    
    result = cs1.fetchall()
    print(result)
    
    # Đóng
    cs1.close()
    conn.close()

if __name__ == '__main__':
    main()

Thẻ: mysql python PyMySQL sql CRUD

Đăng vào ngày 7 tháng 6 lúc 23:21

Thành phố Cuồng loạn

MySQL và Python Tương Tác

Thẻ Phổ Biến