Làm cách nào để TẠO BẢNG và chèn các giá trị trong SQL bằng Python?

Một trong những vai trò chính của nhà khoa học dữ liệu là trích xuất các mẫu và thông tin chi tiết từ dữ liệu thô. Vì phần lớn dữ liệu của chính phủ và doanh nghiệp trên thế giới được tổ chức trong cơ sở dữ liệu quan hệ, nên các nhà khoa học dữ liệu cần biết cách làm việc với các cấu trúc cơ sở dữ liệu này. Viết các truy vấn SQL để chèn, trích xuất và lọc dữ liệu trong cơ sở dữ liệu là một kỹ năng quan trọng đối với bất kỳ ai quan tâm đến phân tích dữ liệu hoặc khoa học dữ liệu

SQL (Ngôn ngữ truy vấn có cấu trúc) dựa trên E. F. Mô hình quan hệ và đại số của Codd để quản lý cơ sở dữ liệu quan hệ. Đó là ngôn ngữ truy vấn cơ sở dữ liệu được sử dụng để tạo, chèn, truy vấn và thao tác cơ sở dữ liệu quan hệ và được sử dụng bởi một số lượng lớn ứng dụng

Mặc dù đã tồn tại hàng thập kỷ, nhưng việc học SQL vẫn là một kỹ năng quan trọng đối với các nhà khoa học dữ liệu hiện đại và thực sự là bất kỳ ai làm việc với dữ liệu, bởi vì SQL được sử dụng trong tất cả các loại phần mềm cơ sở dữ liệu quan hệ, bao gồm MySQL, SQL Server, Oracle,

Trong hướng dẫn này, chúng ta sẽ tìm hiểu chi tiết về các thao tác chèn SQL. Đây là danh sách các chủ đề mà chúng ta sẽ học trong hướng dẫn này

• Chèn SQL
• Chèn bản ghi vào cơ sở dữ liệu
• Chèn Pandas DataFrames vào cơ sở dữ liệu bằng lệnh chèn
• Chèn Pandas DataFrames vào cơ sở dữ liệu bằng lệnh to_sql()
• Đọc bản ghi từ cơ sở dữ liệu
• Cập nhật bản ghi trong cơ sở dữ liệu

Bạn muốn đạt đến trình độ kỹ năng SQL cao hơn?

Chèn SQL

Chèn SQL là một hoạt động thiết yếu để nhân viên dữ liệu hiểu. Chèn dữ liệu bị thiếu hoặc thêm dữ liệu mới là một phần chính của quy trình làm sạch dữ liệu trên hầu hết các dự án khoa học dữ liệu

Chèn cũng là cách hầu hết dữ liệu được đưa vào cơ sở dữ liệu ngay từ đầu, vì vậy điều quan trọng là bất cứ khi nào bạn thu thập dữ liệu cũng vậy. Ví dụ: khi công ty của bạn nhận được dữ liệu mới về một khách hàng, rất có thể một phần chèn SQL sẽ là cách dữ liệu đó được đưa vào cơ sở dữ liệu khách hàng hiện tại của bạn

Trên thực tế, cho dù bạn có biết hay không, dữ liệu luôn được chuyển vào cơ sở dữ liệu bằng cách sử dụng các phần chèn SQL. Khi bạn điền vào một cuộc khảo sát tiếp thị, hoàn thành một giao dịch, gửi biểu mẫu chính phủ trực tuyến hoặc thực hiện bất kỳ việc nào trong số hàng nghìn việc khác, dữ liệu của bạn có thể được chèn vào cơ sở dữ liệu ở đâu đó bằng SQL

Hãy đi sâu vào cách chúng ta thực sự có thể sử dụng SQL để chèn dữ liệu vào cơ sở dữ liệu. Chúng ta có thể chèn dữ liệu theo từng hàng hoặc thêm nhiều hàng cùng lúc

Chèn bản ghi vào cơ sở dữ liệu

Trong SQL, chúng tôi sử dụng lệnh cursor = connection.cursor()1 để thêm bản ghi/hàng vào dữ liệu bảng. Lệnh này sẽ không sửa đổi cấu trúc thực tế của bảng mà chúng tôi đang chèn vào, nó chỉ thêm dữ liệu

Hãy tưởng tượng chúng ta có một bảng dữ liệu như bên dưới, được sử dụng để lưu trữ một số thông tin về nhân viên của công ty

Bây giờ, hãy tưởng tượng chúng ta có nhân viên mới cần đưa vào hệ thống

Bảng cursor = connection.cursor()2 này có thể được tạo bằng lệnh cursor = connection.cursor()3, vì vậy chúng tôi có thể sử dụng lệnh đó để tạo một bảng hoàn toàn mới. Nhưng sẽ rất kém hiệu quả nếu tạo một bảng hoàn toàn mới mỗi khi chúng ta muốn thêm dữ liệu. Thay vào đó, hãy sử dụng lệnh cursor = connection.cursor()1 để thêm dữ liệu mới vào bảng hiện có của chúng ta

Đây là cú pháp cơ bản để sử dụng cursor = connection.cursor()1 trong SQL

Chúng tôi bắt đầu với lệnh cursor = connection.cursor()6 theo sau là tên của bảng mà chúng tôi muốn chèn dữ liệu vào. Sau tên bảng, chúng tôi liệt kê các cột dữ liệu mới mà chúng tôi đang chèn từng cột, bên trong dấu ngoặc đơn. Sau đó, ở dòng tiếp theo, chúng ta sử dụng lệnh cursor = connection.cursor()7 cùng với các giá trị mà chúng ta muốn chèn (theo thứ tự bên trong dấu ngoặc đơn

Vì vậy, đối với bảng cursor = connection.cursor()2 của chúng tôi, nếu chúng tôi đang thêm một nhân viên mới tên là Kabir, lệnh cursor = connection.cursor()1 của chúng tôi có thể trông như thế này

Chèn bản ghi vào cơ sở dữ liệu từ Python

Vì chúng ta thường làm việc với dữ liệu của mình bằng Python khi thực hiện khoa học dữ liệu, hãy chèn dữ liệu từ Python vào cơ sở dữ liệu MySQL. Đây là một nhiệm vụ phổ biến có nhiều ứng dụng trong khoa học dữ liệu

Chúng ta có thể gửi và nhận dữ liệu đến cơ sở dữ liệu MySQL bằng cách thiết lập kết nối giữa Python và MySQL. Có nhiều cách khác nhau để thiết lập kết nối này;

Dưới đây là các bước chung mà chúng tôi cần thực hiện để kết nối # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()0, chèn dữ liệu của chúng tôi và sau đó trích xuất dữ liệu từ MySQL

Hãy đi qua quá trình này từng bước

Bước 1. Nhập mô-đun pymysql

# Import pymysql module import pymysql

Bước 2. Tạo kết nối đến cơ sở dữ liệu MySQL

Tạo kết nối bằng hàm # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()3 của # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()0 với các tham số máy chủ, người dùng, tên cơ sở dữ liệu và mật khẩu

(Các tham số bên dưới chỉ nhằm mục đích trình diễn; bạn sẽ cần điền vào các chi tiết truy cập cụ thể cần thiết cho cơ sở dữ liệu MySQL mà bạn đang truy cập. )

# Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee')

Bước 3. Tạo con trỏ sử dụng hàm cursor()

Điều này sẽ cho phép chúng tôi thực hiện truy vấn SQL sau khi chúng tôi viết nó

cursor = connection.cursor()

Bước 4. Thực hiện truy vấn SQL được yêu cầu

Thực hiện các thay đổi bằng cách sử dụng hàm # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()4 và kiểm tra các bản ghi đã chèn. Lưu ý rằng chúng tôi có thể tạo một biến có tên là # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()5, gán cú pháp truy vấn của chúng tôi cho nó, sau đó chuyển sql và dữ liệu cụ thể mà chúng tôi muốn chèn làm đối số cho # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()6

Sau đó, chúng tôi sẽ cam kết những thay đổi này bằng cách sử dụng # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()4

# Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()

Hãy kiểm tra nhanh xem bản ghi mà chúng ta muốn chèn đã thực sự được chèn chưa

Chúng tôi có thể làm điều này bằng cách truy vấn cơ sở dữ liệu cho toàn bộ nội dung của cursor = connection.cursor()2, sau đó tìm nạp và in các kết quả đó

# Create a new query that selects the entire contents of `employee` sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records and use a for loop to print them one line at a time result = cursor.fetchall() for i in result: print(i)(1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')

Nó đã làm việc. Ở trên, chúng ta có thể thấy bản ghi mới đã được chèn vào và hiện là hàng cuối cùng trong cơ sở dữ liệu MySQL của chúng ta

Bước 5. Đóng kết nối cơ sở dữ liệu

Bây giờ chúng ta đã hoàn tất, chúng ta nên đóng kết nối cơ sở dữ liệu bằng phương pháp # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()9

# Close the connection connection.close()

Tất nhiên, sẽ tốt hơn nếu viết mã này theo cách có thể xử lý các ngoại lệ và lỗi tốt hơn. Chúng tôi có thể làm điều này bằng cách sử dụng # Create a new query that selects the entire contents of `employee` sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records and use a for loop to print them one line at a time result = cursor.fetchall() for i in result: print(i)0 để chứa phần thân của mã của chúng tôi và ngoại trừ các lỗi in nếu có phát sinh. Sau đó, chúng ta có thể sử dụng # Create a new query that selects the entire contents of `employee` sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records and use a for loop to print them one line at a time result = cursor.fetchall() for i in result: print(i)1 để đóng kết nối sau khi kết thúc, bất kể # Create a new query that selects the entire contents of `employee` sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records and use a for loop to print them one line at a time result = cursor.fetchall() for i in result: print(i)0 thành công hay thất bại

Đây là những gì trông giống như tất cả cùng nhau

import pymysql try: # Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee') cursor=connection.cursor() # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" cursor.execute(sql, (1009,'Morgan',1,4000,'HR','Mumbai')) # connection is not autocommit by default. So we must commit to save our changes. connection.commit() # Execute query sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records result = cursor.fetchall() for i in result: print(i) except Error as e: print(e) finally: # close the database connection using close() method. connection.close()((1001, 'John', 2, 4000, 'IT', 'New Delhi'), (1002, 'Anna', 1, 3500, 'HR', 'Mumbai'), (1003, 'James', 1, 2500, 'HR', 'Mumbai'), (1004, 'David', 2, 5000, 'IT', 'New Delhi'), (1005, 'Mark', 2, 3000, 'IT', 'New Delhi'), (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai'), (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai'), (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi'), (1009, 'Morgan', 1, 4000, 'HR', 'Mumbai'), (1009, 'Morgan', 1, 4000, 'HR', 'Mumbai'))

Chèn Pandas DataFrames vào cơ sở dữ liệu bằng INSERT

Khi làm việc với dữ liệu trong Python, chúng tôi thường sử dụng # Create a new query that selects the entire contents of `employee` sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records and use a for loop to print them one line at a time result = cursor.fetchall() for i in result: print(i)3 và chúng tôi thường lưu trữ dữ liệu của mình dưới dạng DataFrame của gấu trúc. Rất may, chúng tôi không cần thực hiện bất kỳ chuyển đổi nào nếu muốn sử dụng SQL với DataFrames của mình;

Một lần nữa, chúng tôi sẽ thực hiện từng bước

Bước 1. Tạo DataFrame bằng từ điển

Chúng tôi cũng có thể nhập dữ liệu từ CSV hoặc tạo DataFrame theo bất kỳ cách nào khác, nhưng với mục đích của ví dụ này, chúng tôi sẽ tạo một DataFrame nhỏ lưu tên sách và giá của một số sách giáo khoa về khoa học dữ liệu

# Import pandas import pandas as pd # Create dataframe data = pd.DataFrame({ 'book_id':[12345, 12346, 12347], 'title':['Python Programming', 'Learn MySQL', 'Data Science Cookbook'], 'price':[29, 23, 27] }) data

book_idtitleprice012345Python Programming29112346Learn MySQL23212347Data Science Cookbook27

Bước 2. Tạo một bảng trong cơ sở dữ liệu MySQL của chúng tôi

Trước khi chèn dữ liệu vào MySQL, chúng tôi sẽ tạo một bảng # Create a new query that selects the entire contents of `employee` sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records and use a for loop to print them one line at a time result = cursor.fetchall() for i in result: print(i)5 trong # Create a new query that selects the entire contents of `employee` sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records and use a for loop to print them one line at a time result = cursor.fetchall() for i in result: print(i)6 để chứa dữ liệu của chúng tôi. Nếu một bảng như vậy đã tồn tại, chúng ta có thể bỏ qua bước này

Chúng tôi sẽ sử dụng câu lệnh CREATE TABLE để tạo bảng của mình, theo sau đó là tên bảng của chúng tôi (trong trường hợp này là # Create a new query that selects the entire contents of `employee` sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records and use a for loop to print them one line at a time result = cursor.fetchall() for i in result: print(i)7), sau đó liệt kê từng cột và kiểu dữ liệu tương ứng của nó

Bước 3. Tạo kết nối đến cơ sở dữ liệu

Khi chúng tôi đã tạo bảng đó, một lần nữa chúng tôi có thể tạo kết nối đến cơ sở dữ liệu từ Python bằng cách sử dụng # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()0

# Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee')0

Bước 4. Tạo danh sách cột và chèn hàng

Tiếp theo, chúng tôi sẽ tạo một danh sách cột và chèn từng hàng trong khung dữ liệu của chúng tôi vào cơ sở dữ liệu bằng cách lặp qua từng hàng và sử dụng cursor = connection.cursor()6 để chèn các giá trị của hàng đó vào cơ sở dữ liệu

(Cũng có thể chèn toàn bộ DataFrame cùng một lúc và chúng ta sẽ xem xét cách thực hiện điều đó trong phần tiếp theo, nhưng trước tiên hãy xem cách thực hiện từng hàng một)

# Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee')1

Bước 5. Truy vấn cơ sở dữ liệu để kiểm tra công việc của chúng tôi

Một lần nữa, hãy truy vấn cơ sở dữ liệu để đảm bảo rằng dữ liệu đã chèn của chúng tôi đã được lưu chính xác

# Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee')2_______14_______3

Khi chúng tôi hài lòng rằng mọi thứ đều ổn, chúng tôi có thể đóng kết nối

# Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee')4

Chèn Pandas DataFrames vào cơ sở dữ liệu bằng hàm to_sql()

Bây giờ, hãy thử làm điều tương tự - chèn một DataFrame của gấu trúc vào cơ sở dữ liệu MySQL - sử dụng một kỹ thuật khác. Lần này, chúng tôi sẽ sử dụng mô-đun (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')0 để tạo kết nối của chúng tôi và chức năng (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')1 để chèn dữ liệu của chúng tôi

Cách tiếp cận này hoàn thành cùng một kết quả cuối cùng theo cách trực tiếp hơn và cho phép chúng tôi thêm toàn bộ khung dữ liệu vào cơ sở dữ liệu MySQL cùng một lúc

# Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee')5

book_idtitleprice012345Python Programming29112346Learn MySQL23212347Data Science Cookbook27

Nhập mô-đun (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')0 và tạo một công cụ với các tham số người dùng, mật khẩu và tên cơ sở dữ liệu. Đây là cách chúng tôi kết nối và đăng nhập vào cơ sở dữ liệu MySQL

# Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee')6

Khi chúng tôi đã kết nối, chúng tôi có thể xuất toàn bộ DataFrame sang MySQL bằng cách sử dụng hàm (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')1 với tên bảng tham số, tên công cụ, if_exists và chunksize

Chúng ta sẽ xem xét kỹ hơn về ý nghĩa của từng tham số này trong giây lát, nhưng trước tiên, hãy xem việc chèn một DataFrame của gấu trúc vào cơ sở dữ liệu MySQL bằng phương pháp này đơn giản hơn nhiều như thế nào. Chúng ta có thể làm điều đó chỉ với một dòng mã

# Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee')7

Bây giờ, hãy xem xét kỹ hơn những gì từng tham số này đang thực hiện trong mã của chúng ta

• # Create a new query that selects the entire contents of `employee` sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records and use a for loop to print them one line at a time result = cursor.fetchall() for i in result: print(i)7 là tên của bảng mà chúng tôi muốn chèn Khung dữ liệu của mình vào
• (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')5 cung cấp chi tiết kết nối (hãy nhớ rằng chúng ta đã tạo công cụ bằng cách sử dụng chi tiết xác thực ở bước trước)
• (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')6 kiểm tra xem bảng chúng tôi chỉ định đã tồn tại hay chưa, sau đó nối thêm dữ liệu mới (nếu nó tồn tại) hoặc tạo một bảng mới (nếu nó không tồn tại)
• (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')7 ghi các bản ghi theo lô có kích thước nhất định tại một thời điểm. Theo mặc định, tất cả các hàng sẽ được viết cùng một lúc

Đọc bản ghi từ cơ sở dữ liệu

Khi chúng tôi đã sử dụng các phần chèn SQL để đưa dữ liệu của mình vào cơ sở dữ liệu, chúng tôi sẽ muốn có thể đọc lại dữ liệu đó. Cho đến giờ trong hướng dẫn này, chúng tôi đã kiểm tra các phần chèn SQL của mình bằng cách in toàn bộ cơ sở dữ liệu, nhưng rõ ràng đây không phải là một tùy chọn khả thi với các cơ sở dữ liệu lớn hơn, nơi bạn sẽ in hàng nghìn hàng (hoặc nhiều hơn). Vì vậy, hãy xem xét sâu hơn về cách chúng tôi có thể đọc lại các bản ghi mà chúng tôi đã tạo hoặc chèn vào cơ sở dữ liệu SQL của mình

Chúng ta có thể đọc các bản ghi từ cơ sở dữ liệu SQL bằng cách sử dụng lệnh (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')8. Chúng tôi có thể chọn các cột cụ thể hoặc sử dụng (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')9 để chọn mọi thứ từ một bảng nhất định. Chúng tôi cũng có thể chọn chỉ trả lại các bản ghi đáp ứng một điều kiện cụ thể bằng cách sử dụng lệnh # Close the connection connection.close()0

Đây là cách cú pháp cho các lệnh này trông như thế nào

Chúng tôi bắt đầu với mệnh đề (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')8, theo sau là danh sách các cột hoặc (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')9 nếu chúng tôi muốn chọn tất cả các cột. Sau đó, chúng tôi sẽ sử dụng mệnh đề # Close the connection connection.close()3 để đặt tên cho bảng mà chúng tôi muốn xem xét. Có thể sử dụng # Close the connection connection.close()0 để lọc các bản ghi và theo sau là điều kiện lọc và chúng ta cũng có thể sử dụng # Close the connection connection.close()5 để sắp xếp các bản ghi. (Mệnh đề # Close the connection connection.close()0 và # Close the connection connection.close()5 là tùy chọn)

Với cơ sở dữ liệu lớn hơn, # Close the connection connection.close()0 rất hữu ích khi chỉ trả về dữ liệu chúng tôi muốn xem. Vì vậy, ví dụ: nếu chúng tôi vừa chèn một số dữ liệu mới về một bộ phận cụ thể, chúng tôi có thể sử dụng # Close the connection connection.close()0 để chỉ định bộ phận import pymysql try: # Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee') cursor=connection.cursor() # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" cursor.execute(sql, (1009,'Morgan',1,4000,'HR','Mumbai')) # connection is not autocommit by default. So we must commit to save our changes. connection.commit() # Execute query sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records result = cursor.fetchall() for i in result: print(i) except Error as e: print(e) finally: # close the database connection using close() method. connection.close()0 trong truy vấn của mình và nó sẽ chỉ trả về các bản ghi có ID bộ phận khớp với ID bộ phận mà chúng tôi đã chỉ định

Ví dụ: so sánh kết quả của hai truy vấn này bằng cách sử dụng bảng cursor = connection.cursor()2 của chúng tôi từ trước đó. Đầu tiên, chúng tôi sẽ trả lại tất cả các hàng. Trong trường hợp thứ hai, chúng tôi chỉ lấy lại những hàng mà chúng tôi đã yêu cầu. Điều này có thể không tạo ra sự khác biệt lớn khi bảng của chúng ta có bảy hàng, nhưng khi bạn đang làm việc với bảy nghìn hàng hoặc thậm chí bảy triệu hàng, việc sử dụng # Close the connection connection.close()0 để chỉ trả lại kết quả bạn muốn là rất quan trọng

Nếu chúng ta muốn làm điều này từ bên trong Python, chúng ta có thể sử dụng cùng một tập lệnh mà chúng ta đã sử dụng trước đó trong hướng dẫn này để truy vấn các bản ghi này. Sự khác biệt duy nhất là chúng ta sẽ yêu cầu # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" # Execute the query cursor.execute(sql, (1008,'Kabir',2,5000,'IT','New Delhi')) # the connection is not autocommited by default. So we must commit to save our changes. connection.commit()0 thực thi lệnh (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')8 thay vì lệnh cursor = connection.cursor()1 mà chúng ta đã sử dụng trước đó

# Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee')8# Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee')9

Ở trên, chúng tôi đã chọn và in toàn bộ cơ sở dữ liệu, nhưng nếu chúng tôi muốn sử dụng # Close the connection connection.close()0 để thực hiện lựa chọn hạn chế, cẩn thận hơn, thì cách tiếp cận vẫn như vậy

cursor = connection.cursor()0

Cập nhật bản ghi trong cơ sở dữ liệu

Thông thường, chúng ta sẽ cần sửa đổi các bản ghi trong bảng sau khi tạo chúng

Ví dụ: hãy tưởng tượng rằng một nhân viên trong bảng nhân viên của chúng tôi được thăng chức. Chúng tôi muốn cập nhật dữ liệu lương của họ. Lệnh cursor = connection.cursor()6 sẽ không giúp chúng tôi ở đây, vì chúng tôi không muốn thêm một hàng hoàn toàn mới

Để sửa đổi các bản ghi hiện có trong bảng, chúng ta cần sử dụng lệnh import pymysql try: # Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee') cursor=connection.cursor() # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" cursor.execute(sql, (1009,'Morgan',1,4000,'HR','Mumbai')) # connection is not autocommit by default. So we must commit to save our changes. connection.commit() # Execute query sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records result = cursor.fetchall() for i in result: print(i) except Error as e: print(e) finally: # close the database connection using close() method. connection.close()8. import pymysql try: # Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee') cursor=connection.cursor() # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" cursor.execute(sql, (1009,'Morgan',1,4000,'HR','Mumbai')) # connection is not autocommit by default. So we must commit to save our changes. connection.commit() # Execute query sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records result = cursor.fetchall() for i in result: print(i) except Error as e: print(e) finally: # close the database connection using close() method. connection.close()8 được sử dụng để thay đổi nội dung của bản ghi hiện có. Chúng tôi có thể chỉ định các cột và giá trị cụ thể để thay đổi bằng cách sử dụng ((1001, 'John', 2, 4000, 'IT', 'New Delhi'), (1002, 'Anna', 1, 3500, 'HR', 'Mumbai'), (1003, 'James', 1, 2500, 'HR', 'Mumbai'), (1004, 'David', 2, 5000, 'IT', 'New Delhi'), (1005, 'Mark', 2, 3000, 'IT', 'New Delhi'), (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai'), (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai'), (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi'), (1009, 'Morgan', 1, 4000, 'HR', 'Mumbai'), (1009, 'Morgan', 1, 4000, 'HR', 'Mumbai'))0 và chúng tôi cũng có thể thực hiện các thay đổi có điều kiện bằng # Close the connection connection.close()0 để chỉ áp dụng những thay đổi đó cho các hàng đáp ứng điều kiện đó

Bây giờ, hãy cập nhật các bản ghi từ bảng nhân viên của chúng tôi và hiển thị kết quả. Trong trường hợp này, giả sử David được thăng chức — chúng ta sẽ viết một truy vấn bằng cách sử dụng import pymysql try: # Connect to the database connection = pymysql.connect(host='localhost', user='root', password='12345', db='employee') cursor=connection.cursor() # Create a new record sql = "INSERT INTO `employee` (`EmployeeID`, `Ename`, `DeptID`, `Salary`, `Dname`, `Dlocation`) VALUES (%s, %s, %s, %s, %s, %s)" cursor.execute(sql, (1009,'Morgan',1,4000,'HR','Mumbai')) # connection is not autocommit by default. So we must commit to save our changes. connection.commit() # Execute query sql = "SELECT * FROM `employee`" cursor.execute(sql) # Fetch all the records result = cursor.fetchall() for i in result: print(i) except Error as e: print(e) finally: # close the database connection using close() method. connection.close()8 chỉ đặt ((1001, 'John', 2, 4000, 'IT', 'New Delhi'), (1002, 'Anna', 1, 3500, 'HR', 'Mumbai'), (1003, 'James', 1, 2500, 'HR', 'Mumbai'), (1004, 'David', 2, 5000, 'IT', 'New Delhi'), (1005, 'Mark', 2, 3000, 'IT', 'New Delhi'), (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai'), (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai'), (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi'), (1009, 'Morgan', 1, 4000, 'HR', 'Mumbai'), (1009, 'Morgan', 1, 4000, 'HR', 'Mumbai'))3 thành ((1001, 'John', 2, 4000, 'IT', 'New Delhi'), (1002, 'Anna', 1, 3500, 'HR', 'Mumbai'), (1003, 'James', 1, 2500, 'HR', 'Mumbai'), (1004, 'David', 2, 5000, 'IT', 'New Delhi'), (1005, 'Mark', 2, 3000, 'IT', 'New Delhi'), (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai'), (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai'), (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi'), (1009, 'Morgan', 1, 4000, 'HR', 'Mumbai'), (1009, 'Morgan', 1, 4000, 'HR', 'Mumbai'))4 trong các cột có ID nhân viên là 1004 (ID của David)

Hãy cẩn thận — không có mệnh đề # Close the connection connection.close()0, truy vấn này sẽ cập nhật mọi bản ghi trong bảng, vì vậy đừng quên điều đó

Sau khi thực hiện truy vấn trên, bảng được cập nhật sẽ như thế này

Phần kết luận

Trong hướng dẫn này, chúng ta đã xem xét các phần chèn SQL và cách chèn dữ liệu vào cơ sở dữ liệu MySQL từ Python. Chúng tôi cũng đã học cách chèn Pandas DataFrames vào cơ sở dữ liệu SQL bằng hai phương pháp khác nhau, bao gồm phương pháp (1001, 'John', 2, 4000, 'IT', 'New Delhi') (1002, 'Anna', 1, 3500, 'HR', 'Mumbai') (1003, 'James', 1, 2500, 'HR', 'Mumbai') (1004, 'David', 2, 5000, 'IT', 'New Delhi') (1005, 'Mark', 2, 3000, 'IT', 'New Delhi') (1006, 'Steve', 3, 4500, 'Finance', 'Mumbai') (1007, 'Alice', 3, 3500, 'Finance', 'Mumbai') (1008, 'Kabir', 2, 5000, 'IT', 'New Delhi')1 hiệu quả cao

Tất nhiên, đây chỉ là phần nổi của tảng băng khi nói đến các truy vấn SQL. Nếu bạn thực sự muốn trở thành bậc thầy về SQL, hãy đăng ký miễn phí và tham gia một trong các khóa học SQL tương tác của Dataquest để nhận hướng dẫn tương tác và kinh nghiệm thực hành viết tất cả các truy vấn bạn cần để thực hiện công việc khoa học dữ liệu chuyên nghiệp, hiệu quả

Làm cách nào để chèn dữ liệu vào bảng SQL từ Python?

Chúng ta có thể tạo bảng trong SQL bằng Python không?

Làm cách nào để chèn dữ liệu vào SQL từ DataFrame Python?

Làm cách nào để chèn dữ liệu hàng loạt vào SQL bằng Python?