programming python

Cách nhập dữ liệu từ Python sang SQL Server

Tìm hiểu cách viết mã Python thực thi tiện ích SQL Server BULK INSERT để tải dữ liệu từ tệp CSV vào bảng cơ sở dữ liệu ở tốc độ cao

Giới thiệu

Thu thập dữ liệu là một phần lớn của nhiều dự án phân tích dữ liệu và vòng đời phát triển hệ thống. Bài viết này sẽ chỉ cho bạn cách viết một chương trình Python đơn giản sử dụng tiện ích BULK INSERT để chèn nhanh dữ liệu từ tệp CSV vào bảng cơ sở dữ liệu SQL Server

Tiện ích SQL Server BULK INSERT có thể được thực thi trực tiếp từ Python để nhanh chóng chèn dữ liệu từ tệp CSV vào bảng cơ sở dữ liệu. Hình ảnh của tác giả. Tại sao sử dụng phương pháp tiếp cận này?

Có nhiều cách để tải dữ liệu từ tệp CSV vào bảng SQL Server. Dưới đây là một số phương pháp

Chạy tiện ích BULK INSERT từ dòng lệnh
Chạy tiện ích BULK INSERT từ SQL Server Management Studio [SSMS]
Sử dụng trình hướng dẫn Nhập tệp phẳng của SQL Server Management Studio [SSMS]
Viết chương trình mở tệp CSV, đọc từng bản ghi của nó và gọi câu lệnh SQL INSERT để chèn các hàng vào bảng cơ sở dữ liệu

Nếu có rất nhiều cách để lấy dữ liệu từ CSV vào cơ sở dữ liệu SQL Server, tại sao phải viết chương trình Python gọi tiện ích BULK INSERT để tải dữ liệu vào bảng?

Chương trình Python có thể thực hiện các bước của quy trình trước khi thực thi BULK INSERT
Chương trình Python có thể đảm bảo rằng nó không ghi dữ liệu trùng lặp vào bảng đích
Chương trình có thể làm sạch hoặc chuyển đổi dữ liệu sau BULK INSERT
Nó có thể thực hiện các chức năng xử lý lỗi
Nó có thể gửi thông báo, qua email hoặc các phương thức khác, về hành động của nó

Phần mềm được sử dụng trong bài viết này

Tôi sử dụng môi trường Windows 10 và nhiều công cụ của Microsoft cũng như các công cụ khác để phát triển phần mềm, cơ sở dữ liệu và phân tích dữ liệu của tôi. Dưới đây là danh sách các phần mềm mà tôi đã sử dụng cho bài viết này

Windows 10 Home — Trong khi tôi sử dụng Windows, một số công cụ này có thể hoạt động trong các hệ điều hành khác
Notepad — Notepad rất hữu ích để xem dữ liệu tệp CSV ở dạng thô
Microsoft Excel — Excel rất hữu ích để xem dữ liệu từ tệp CSV được định dạng theo cột và hàng
Microsoft SQL Server 2019 Express — Có nhiều phiên bản SQL Server. Express và SQL Server Developer là phiên bản miễn phí
Microsoft SQL Server Management Studio [SSMS] — Sử dụng công cụ giao diện người dùng này để quản lý và truy vấn cơ sở dữ liệu SQL Server. Nó miễn phí và hoạt động tốt
Microsoft Visual Studio Community Edition — Tôi đã dùng thử môi trường phát triển tích hợp Microsoft Visual Studio Code [VS Code] [IDE]. Tuy nhiên, tôi vẫn thích viết mã trong Visual Studio hơn vì tôi đã sử dụng nó trong nhiều năm và tôi thấy nó dễ cấu hình và sử dụng, ngoại trừ vô số lựa chọn menu sâu của nó
Trăn 3. 9. 2 — Đây là bản phát hành Python mới nhất tính đến thời điểm viết bài này, nhưng bất kỳ phiên bản nào gần đây cũng sẽ hoạt động

Tạo dữ liệu thử nghiệm

Đối với ví dụ được trình bày trong bài viết này, tôi đã tạo một tệp CSV với 10.000 bản ghi dữ liệu bịa đặt từ Trình tạo dữ liệu trực tuyến miễn phí. Tệp được gọi là c. \test_data_folder\person. csv. Nó chứa các cột ID, Chức danh công việc, Địa chỉ email và FirstName LastName. Một chương trình Python sẽ thực thi câu lệnh SQL Server BULK INSERT để tải dữ liệu từ tệp vào bảng

Nội dung của người. tệp csv. Hình ảnh của tác giả. Tạo cơ sở dữ liệu và bảng

Lưu ý quan trọng về các phiên bản máy chủ SQL

Microsoft đã giới thiệu khả năng sử dụng BULK INSERT để chèn dữ liệu từ tệp CSV trong SQL Server 2017. Vì vậy, bạn sẽ cần phiên bản đó hoặc mới hơn để sử dụng khả năng này

Tạo cơ sở dữ liệu

Dưới đây là các bước được sử dụng để tạo cơ sở dữ liệu có tên HR [dành cho Nhân sự]

Kết nối với máy chủ SQL

Khởi chạy SSMS
Kết nối với máy chủ cơ sở dữ liệu. Trong trường hợp này, tôi đã sử dụng xác thực Windows để kết nối với phiên bản SQL Server Express được cài đặt cục bộ

Kết nối với công cụ cơ sở dữ liệu SQL Server trong SQL Server Management Studio [SSMS]. Hình ảnh của tác giả

Tạo cơ sở dữ liệu nhân sự

Mở rộng nút [+ Cơ sở dữ liệu] trong Object Explorer. Nhấp chuột phải vào [+ Cơ sở dữ liệu] và nhấp vào [Cơ sở dữ liệu mới…]
Trong hộp thoại Cơ sở dữ liệu mới, nhập “HR” vào hộp văn bản Tên cơ sở dữ liệu. Để nguyên tất cả các cài đặt. Nhấp vào [OK] để tạo cơ sở dữ liệu

Tạo cơ sở dữ liệu nhân sự với SSMS. Hình ảnh của tác giả

Xác minh rằng cơ sở dữ liệu nhân sự xuất hiện trong Object Explorer. Nếu không, nhấp chuột phải vào Cơ sở dữ liệu và nhấp vào [Làm mới]. Nó sẽ đi vào xem

Object Explorer liệt kê các cơ sở dữ liệu. Hình ảnh của tác giả

Tạo bảng

Tại thời điểm này, cơ sở dữ liệu nhân sự sẽ không chứa bất kỳ bảng hoặc đối tượng nào khác, chẳng hạn như các thủ tục được lưu trữ. Mặc dù BULK INSERT có thể tạo bảng khi nó chạy, nhưng tôi nhận thấy rằng việc tạo bảng trước thời hạn mang lại nhiều lợi thế. Ví dụ: tôi có thể chỉ định [các] cột chính của bảng cũng như loại và độ dài của từng cột. Nhìn lại dữ liệu mẫu trong tệp CSV, hãy tạo các cột có cùng tên và với các loại dữ liệu này

Mã số — INT
Chức danh — NCHAR[60]
Địa chỉ email — NCHAR[120]
Tên Họ — NCHAR[80]

Vì tất cả các hàng sẽ có giá trị cho tất cả các cột, hãy đặt từng cột thành NOT NULL. Ngoài ra, vì ID là mã định danh duy nhất cho mỗi hàng, hãy chọn nó làm khóa

Làm theo các bước sau để tạo bảng

Trong Object Explorer, nhấp vào [+ HR] để xem lựa chọn trong cơ sở dữ liệu
Nhấp chuột phải vào [+ Bảng] và nhấp vào [Mới] rồi nhấp vào [Bảng…]
Nhập dữ liệu như hình bên dưới. Sau khi nhập các giá trị Tên cột, Loại dữ liệu và Cho phép Nulls, nhấp chuột phải vào tên cột ID và nhấp vào [Đặt khóa chính]. Đặt ID làm khóa sẽ đảm bảo rằng chỉ một hàng trong bảng có thể chứa bất kỳ giá trị ID nào

Thông tin cột cho bảng Người. Hình ảnh của tác giả

Nhấp vào biểu tượng Lưu trong menu ruy-băng và trong hộp thoại Chọn Tên, nhập tên “Người. ” Bấm vào [OK] để lưu bảng
Trong Object Explorer, nhấp vào [+ Tables] để mở rộng nút. Sau đó, nhấp chuột phải vào [- Tables] và nhấp vào [Refresh]. Bảng Person bây giờ sẽ được xem
Nhấp vào [+dbo. Person] và sau đó vào [+ Columns] để kiểm tra cấu trúc của bảng. Nó sẽ trông giống như hình ảnh sau đây

Cột bảng người được hiển thị trong Object Explorer. Hình ảnh của tác giả. Chương trình Python

Bây giờ cơ sở dữ liệu nhân sự và bảng Person đã tồn tại, hãy kiểm tra một chương trình Python đơn giản sử dụng tiện ích BULK INSERT. Nó chỉ đơn giản là chèn tất cả các bản ghi từ tệp CSV vào bảng Person

Mô-đun mã

Chương trình Python này bao gồm hai mô-đun hoặc tệp

c_bulk_insert. py chứa lớp c_bulk_insert. Nó bao gồm các hàm để kết nối với cơ sở dữ liệu, xây dựng và thực thi câu lệnh BULK INSERT để chèn dữ liệu từ tệp CSV vào bảng cơ sở dữ liệu
sql_server_bulk_insert. py chỉ cần khởi tạo lớp c_bulk_insert và gọi nó với thông tin cần thiết để thực hiện công việc của nó

Mã logic

Khi chương trình khởi tạo lớp c_bulk_insert, nó sẽ thực hiện các bước sau

Kết nối với cơ sở dữ liệu SQL Server
Tạo truy vấn BULK INSERT với tên của bảng đích, tệp CSV đầu vào và một số cài đặt
Mở một con trỏ cơ sở dữ liệu
Thực hiện truy vấn
Dọn dẹp. Thực hiện các giao dịch CHÈN SỐ LƯỢNG LỚN, đóng con trỏ và đóng kết nối cơ sở dữ liệu

Mật mã

Lớp Python c_bulk_insert trong mô-đun c_bulk_insert. py thực hiện logic được mô tả trong phần Code Logic ở trên

""" 
    Name:           c_bulk_insert.py
    Author:         Randy Runtsch
    Date:           March 17, 2021
    Description:    This module contains the c_bulk_insert class that connect to a SQL Server database
                    and executes the BULK INSERT utility to insert data from a CSV file into a table.
    Prerequisites:  1. Create the database data table.
                    2. Create the database update_csv_log table.
"""import pyodbcclass c_bulk_insert:def __init__[self, csv_file_nm, sql_server_nm, db_nm, db_table_nm]:# Connect to the database, perform the insert, and update the log table.conn = self.connect_db[sql_server_nm, db_nm]
        self.insert_data[conn, csv_file_nm, db_table_nm]
        conn.closedef connect_db[self, sql_server_nm, db_nm]:# Connect to the server and database with Windows authentication.conn_string = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + sql_server_nm + ';DATABASE=' + db_nm + ';Trusted_Connection=yes;'
        conn = pyodbc.connect[conn_string]return conndef insert_data[self, conn, csv_file_nm, db_table_nm]:# Insert the data from the CSV file into the database table.# Assemble the BULK INSERT query. Be sure to skip the header row by specifying FIRSTROW = 2.qry = "BULK INSERT " + db_table_nm + " FROM '" + csv_file_nm + "' WITH [FORMAT = 'CSV', FIRSTROW = 2]"# Execute the querycursor = conn.cursor[]
        success = cursor.execute[qry]
        conn.commit[]
        cursor.close

Mô-đun sql_server_bulk_insert. py khởi tạo c_bulk_insert. Nó gọi nó với

tên tệp CSV
Tên công cụ phiên bản SQL Server
Tên cơ sở dữ liệu
Tên bảng đích

""" 
    Name:           sql_server_bulk_insert.py
    Author:         Randy Runtsch
    Date:           March 17, 2021
    Description:    This program is the controller that uses the Microsoft Transact-SQL BULK INSERT
                    statement to quickly insert the rows from a CSV file into
                    a SQL Server table.
    Prerequisites:  1. Create the database data table.
                    2. Create the database update_csv_log table.
"""from c_bulk_insert import c_bulk_insertbulk_insert = c_bulk_insert[r'c:\\test_data\\person.csv', 'xxxxx-DESKTOP-\\SQLEXPRESS', 'HR', 'Person']

Kết quả

Sau khi chương trình chạy, việc thực thi truy vấn CHỌN trong SSMS cho thấy rằng nó đã ghi các bản ghi từ tệp CSV vào bảng Person

Chạy truy vấn CHỌN trong SSMS cho thấy chương trình Python đã thực thi thành công tiện ích BULK INSERT để ghi các bản ghi từ tệp CSV vào bảng Person. Hình ảnh của tác giả. Đi đâu từ đây

Có thể có nhiều lý do và cách để nâng cao chương trình. Dưới đây là một vài ý tưởng

Thêm xử lý lỗi vào kết nối cơ sở dữ liệu, thực thi truy vấn và các phần khác của chương trình. Ví dụ, chương trình có thể sử dụng xử lý lỗi để tắt máy, thử lại một số lần đã đặt và thông báo cho các bên thích hợp qua email
Tự động hóa chương trình chèn dữ liệu vào cơ sở dữ liệu khi cần. Ví dụ: lên lịch cho chương trình chạy định kỳ với Trình lập lịch tác vụ Windows để chèn bản ghi người từ tệp CSV mới
Tạo và ghi vào bảng nhật ký để nắm bắt thời gian bắt đầu và kết thúc chương trình cũng như các sự kiện quan trọng khác. Thêm các thông báo khác, chẳng hạn như chi tiết lỗi, để xác định các vấn đề cần khắc phục sự cố

Phần kết luận

Như bạn có thể thấy, sử dụng Python để gọi BULK INSERT là một cách để tự động hóa một phần quy trình công việc nhằm nhanh chóng chèn dữ liệu từ tệp CSV vào bảng cơ sở dữ liệu SQL Server. Nó có thể chứng minh là một kỹ thuật hữu ích để thêm vào bộ công cụ phân tích dữ liệu hoặc phát triển phần mềm của bạn

Giới thiệu về tác giả

Randy Runtsch là nhà phân tích dữ liệu, nhà phát triển phần mềm, nhà văn, nhiếp ảnh gia, người đi xe đạp và nhà thám hiểm. Ông và vợ sống ở đông nam Minnesota, U. S. A

Theo dõi các bài viết sắp tới của Randy về bộ dữ liệu công khai để thúc đẩy các giải pháp phân tích dữ liệu, lập trình, phân tích dữ liệu, nhiếp ảnh, du lịch bằng xe đạp, bia, v.v.

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

Các bước để chèn giá trị vào bảng SQL Server bằng Python .

Bước 1. Cài đặt gói Pyodbc. .

Bước 2. Kết nối Python với máy chủ SQL. .

Bước 3. Chèn các giá trị vào bảng SQL Server bằng Python. .

Bước 4. Xác minh kết quả

Làm cách nào để kết nối Python với Microsoft SQL Server?

Các bước kết nối Python với SQL Server bằng pyodbc .

Bước 1. Cài đặt pyodbc. Để bắt đầu, hãy cài đặt gói pyodbc sẽ được sử dụng để kết nối Python với SQL Server. .

Bước 2. Lấy tên máy chủ. Tiếp theo, truy xuất tên máy chủ của bạn. .

Bước 3. Kết nối Python với máy chủ SQL

Làm cách nào để kết nối Python với cơ sở dữ liệu SQL?

Để tạo kết nối giữa cơ sở dữ liệu MySQL và Python, phương thức connect[] của mysql. mô-đun trình kết nối được sử dụng . Chúng tôi chuyển các chi tiết cơ sở dữ liệu như Tên máy chủ, tên người dùng và mật khẩu trong lệnh gọi phương thức, sau đó phương thức trả về đối tượng kết nối.

Làm cách nào để nhập tệp CSV trong SQL Server bằng truy vấn?

Nhập tệp CSV vào máy chủ SQL bằng quản lý máy chủ SQL. .

Đăng nhập vào cơ sở dữ liệu của bạn bằng SQL Server Management Studio

Nhấp chuột phải vào cơ sở dữ liệu và chọn Nhiệm vụ -> Nhập dữ liệu