Trong bài đăng này, chúng tôi sẽ thực hiện một bài tập trong đó chúng tôi sẽ đọc dữ liệu từ tệp Excel và chèn dữ liệu này vào bảng cơ sở dữ liệu SQL Server
Xem xét tình huống mà bạn muốn mở một tệp excel để thực hiện một số phép toán và sau đó chèn kết quả vào bảng cơ sở dữ liệu
Lưu ý rằng trong ví dụ này, chúng ta sẽ không tập trung vào các phép toán phức tạp, chúng ta sẽ chỉ thực hiện một phép toán ngớ ngẩn như nhân với 10. Mục tiêu của chúng tôi ở đây là hiển thị sự tích hợp giữa tệp bên ngoài, chương trình python và bảng cơ sở dữ liệu
Tệp Excel
Chúng tôi sẽ xem xét một tệp excel đơn giản chỉ có một trang tính và năm cột chứa các số
Lưu tệp vào một vị trí sẽ được giới thiệu bởi mã python. Bạn không cần chỉ định cùng một đường dẫn, có một lý do cho đường dẫn này bên dưới sẽ được giải thích trong bài đăng tiếp theo. Hiện tại, chỉ cần lưu tệp vào một vị trí mà bạn sẽ nhớ
Bảng cơ sở dữ liệu
Tạo bảng vào cơ sở dữ liệu SQL Server để lưu kết quả
Chương trình Python
Bây giờ cuối cùng chúng ta sẽ “liên kết các điểm”. Chúng ta sẽ thực hiện thuật toán được biểu diễn trên lưu đồ bên dưới
1 – Nhập kiện hàng
Chúng tôi sẽ nhập gói XLRD để thao tác excel và PYODBC để tương tác với cơ sở dữ liệu
2 – Kết nối chuỗi cơ sở dữ liệu
Chúng tôi sẽ cung cấp các cài đặt và thông tin đăng nhập để kết nối với Cơ sở dữ liệu SQL Server
3 – Excel – đọc, nhân với 10 và lưu trữ
Tôi biết, nhân với 10 nghe có vẻ ngớ ngẩn, nhưng như tôi đã nói trước đây, trọng tâm ở đây là thể hiện sự tích hợp giữa các nền tảng. Tôi chắc chắn rằng bạn có thể nâng cao ví dụ này bằng các phép toán thống kê thông minh
Trong đoạn mã dưới đây, bạn sẽ mở tệp excel đã tạo trước đó và lưu trữ nội dung của nó vào các cấu trúc dữ liệu để sử dụng sau này
4 – Chèn vào bảng cơ sở dữ liệu
Sau khi tải các giá trị từ tệp excel vào một vectơ của vectơ, chúng ta phải đọc từng dòng vectơ này để chèn nội dung của nó vào bảng cơ sở dữ liệu
Lưu mã này ở đâu đó bạn có thể nhớ sau này
thực hiện chương trình
Bây giờ tất cả những gì bạn cần làm là thực hiện chương trình của mình
… và đó là kết quả bạn có thể thấy trên bảng điều khiển
Hãy kiểm tra cơ sở dữ liệu để xem dữ liệu có ở đó không
Thế là xong. Bạn có thể sử dụng ví dụ này cho các hoạt động phức tạp hơn.
Ở bài tiếp theo chúng ta sẽ sử dụng giải pháp này để tích hợp với giải pháp khác. Chúng tôi sẽ có thể tạo một giao diện để chúng tôi có thể tải tệp lên và xem kết quả cuối cùng trong một trang web.
Tôi hy vọng điều này có thể hữu ích cho bạn. Chúc bạn ngày mới tốt lành
Xem mã cho giải pháp này bên dưới
ĐỐI TƯỢNG SQL
CREATE_TABLETải xuống
MÃ TRĂN
Tôi xin lỗi vì điều đó nhưng tôi không thể tải tệp có mã python lên do chính sách bảo mật của dịch vụ lưu trữ. Vui lòng xem mã bên dưới và chú ý đến vết lõm
Khi mọi người lưu dữ liệu ở định dạng JSON hoặc CSV, họ đang có ý định truy cập dữ liệu đó theo chương trình. Nhưng phần lớn dữ liệu của thế giới được lưu trữ trong các tệp bảng tính và nhiều tệp trong số đó ở định dạng Excel. Excel được sử dụng vì mọi người có thể thao tác với nó một cách dễ dàng và bản thân nó là một công cụ mạnh mẽ. Tuy nhiên, có rất nhiều tự động hóa có thể được thực hiện bằng cách trích xuất dữ liệu từ bảng tính và quy trình này cũng cho phép bạn đưa dữ liệu từ nhiều loại nguồn vào một chương trình
Trước tiên, chúng ta sẽ xem nhanh cách lưu tệp Excel dưới dạng tệp CSV. Đây đôi khi là cách nhanh nhất và dễ dàng nhất để trích xuất dữ liệu. Nhưng đó là quy trình thủ công, vì vậy bạn phải mở tệp trong Excel và lưu lại dưới dạng CSV mỗi khi tệp được cập nhật. Trong nhiều trường hợp, tốt hơn là chỉ trích xuất dữ liệu trực tiếp từ Excel
Ví dụ chúng tôi sẽ sử dụng là dữ liệu bạn có thể tải xuống từ Lập bản đồ Cảnh sát Bạo lực. Nếu vì lý do nào đó bạn không thể tải xuống tệp này từ trang web, bạn cũng có thể tìm thấy ảnh chụp nhanh của bảng tính này từ ngày 19/6/2020 trong thư mục
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
Chuyển đổi tệp Excel sang CSV
Bạn có thể tạo tệp CSV từ bất kỳ trang tính nào trong sổ làm việc Excel. Để thực hiện việc này, trước tiên hãy bấm vào tab của trang tính mà bạn muốn tập trung vào. Sau đó, chọn Tệp > Lưu dưới dạng và trong menu thả xuống Định dạng tệp, chọn CSV UTF-8 [Được phân cách bằng dấu phẩy] [. csv]. Bạn sẽ nhận được thông báo rằng không thể lưu toàn bộ sổ làm việc ở định dạng này, nhưng nếu bạn bấm vào OK, bạn sẽ nhận được một bản sao của trang tính hiện tại ở định dạng CSV
Để xem tệp và đảm bảo tệp chứa dữ liệu bạn mong đợi, hãy định vị tệp CSV mới trong trình duyệt tệp và mở tệp đó bằng trình chỉnh sửa văn bản. Nếu bạn mở tệp bằng ứng dụng bảng tính như Excel, nó sẽ không khác gì tệp Excel thông thường
Cài đặt openpyxl
Chúng tôi sẽ sử dụng thư viện openpyxl để truy cập dữ liệu trong tệp Excel. Bạn có thể cài đặt thư viện này với pip
$ pip install --user openpyxl
Mở một tệp Excel
Để làm theo hướng dẫn này, hãy tạo một thư mục ở đâu đó trên hệ thống của bạn có tên là extracting_from_excel. Tạo một thư mục dữ liệu bên trong thư mục này; . Tôi đã lưu tệp mapping_police_violence_snapshot_061920. xlsx trong thư mục dữ liệu của tôi; . xls hoặc. xlsx mà bạn quan tâm
Đoạn mã sau sẽ mở tệp Excel và in tên của tất cả các trang tính trong tệp
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
Đầu tiên ta import hàm
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
Tên của tất cả các trang tính trong tệp được lưu trữ trong thuộc tính
Found the following worksheets:
2013-2019 Police Killings
2013-2019 Killings by PD
2013-2019 Killings by State
Police Killings of Black Men
Found the following worksheets:
2013-2019 Police Killings
2013-2019 Killings by PD
2013-2019 Killings by State
Police Killings of Black Men
Truy cập dữ liệu trong một Worksheet
Chúng tôi muốn truy cập dữ liệu thực tế trong một trang tính cụ thể. Để làm điều này, chúng tôi lấy trang tính mà chúng tôi quan tâm, sau đó trích xuất dữ liệu từ tất cả các hàng trong trang tính
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# Load one worksheet.
ws = wb['2013-2019 Killings by State']
all_rows = list[ws.rows]
print[f"Found {len[all_rows]} rows of data."]
print["\nFirst rows of data:"]
for row in all_rows[:5]:
print[row]
Trang tính được truy cập theo tên thông qua đối tượng sổ làm việc. Ở đây chúng tôi chỉ định một trang tính cho
Found the following worksheets:
2013-2019 Police Killings
2013-2019 Killings by PD
2013-2019 Killings by State
Police Killings of Black Men
Found the following worksheets:
2013-2019 Police Killings
2013-2019 Killings by PD
2013-2019 Killings by State
Police Killings of Black Men
$ pip install --user openpyxl
Trong bảng tính này, chúng tôi đã tìm thấy 55 hàng dữ liệu. Mỗi hàng dữ liệu được tạo thành từ một loạt các đối tượng ô
Truy cập dữ liệu từ các ô
Cho đến nay, chúng tôi đã truy cập tệp Excel, một trang tính riêng lẻ và một loạt các hàng. Bây giờ chúng ta có thể truy cập dữ liệu thực tế trong các ô
Để bắt đầu, chúng ta sẽ chỉ xem xét dữ liệu ở hàng đầu tiên
$ pip install --user openpyxl
Chúng tôi lặp qua tất cả các ô trong hàng và in giá trị của từng ô. Điều này được truy cập thông qua thuộc tính
Found the following worksheets:
2013-2019 Police Killings
2013-2019 Killings by PD
2013-2019 Killings by State
Police Killings of Black Men
$ pip install --user openpyxl
Trích xuất dữ liệu từ các ô cụ thể
Ví dụ trước có thể hữu ích khi xem danh sách các tiêu đề cho một trang tính qua kết nối từ xa. Nhưng thông thường khi phân tích dữ liệu từ bảng tính, chúng ta chỉ cần mở tệp trong Excel, tìm kiếm thông tin mình muốn rồi viết mã để trích xuất thông tin đó. Tuy nhiên, chúng tôi thường không quan tâm đến từng ô trong một hàng. Chúng tôi thường quan tâm đến các ô được chọn trong mỗi hàng trong trang tính
Ví dụ sau lấy dữ liệu từ ba cột cụ thể trong mỗi hàng trong tệp chứa dữ liệu mà chúng tôi quan tâm
$ pip install --user openpyxl
Ở đây chúng tôi lặp qua tất cả các hàng chứa dữ liệu của các trạng thái. Đối với mỗi hàng, chúng tôi kéo các giá trị tại chỉ mục 0, 3 và 4 và gán mỗi giá trị này cho một tên biến thích hợp. Sau đó, chúng tôi in một câu lệnh tóm tắt ý nghĩa của những giá trị này
Đầu ra không hoàn toàn như những gì chúng ta mong đợi
$ pip install --user openpyxl
Các giá trị trong các ô này thực sự là các công thức. Nếu chúng ta muốn các giá trị được tính toán từ các công thức này, chúng ta cần chuyển cờ
Found the following worksheets:
2013-2019 Police Killings
2013-2019 Killings by PD
2013-2019 Killings by State
Police Killings of Black Men
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
Bây giờ chúng tôi thấy đầu ra giống như những gì chúng tôi mong đợi
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
Phân tích dữ liệu hầu như luôn liên quan đến một số mức độ định dạng lại. Đối với kết quả này, chúng tôi sẽ làm tròn tỷ lệ phần trăm đến hai chữ số thập phân và biến chúng thành số nguyên được định dạng gọn gàng để hiển thị
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
Đây là đầu ra sạch hơn
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
Hãy cẩn thận về việc làm tròn dữ liệu trong giai đoạn xử lý. Nếu bạn định chuyển dữ liệu này đến thư viện đồ thị, có thể bạn muốn thực hiện phép làm tròn trong mã đồ thị. Điều này có thể ảnh hưởng đến hình dung của bạn. Ví dụ: nếu hai tỷ lệ phần trăm làm tròn thành cùng một giá trị ở hai chữ số thập phân nhưng chúng khác nhau ở chữ số thập phân thứ ba, thì bạn sẽ mất khả năng sắp xếp chính xác các mục. Trong tình huống này, điều quan trọng là phải hỏi liệu vị trí thập phân thứ ba có ý nghĩa hay không
Ngoài ra, lưu ý rằng bạn sẽ thường xuyên cần xác định các hàng cụ thể cần được lặp lại. Bảng tính đẹp và có cấu trúc, nhưng mọi người cũng có thể tự do viết bất cứ thứ gì họ muốn trong bất kỳ ô nào. Nhiều bảng tính có một số ghi chú trong một vài ô sau tất cả các hàng dữ liệu. Đây có thể là ghi chú về nguồn dữ liệu thô, ngày thu thập dữ liệu, tác giả, v.v. Có thể bạn sẽ cần phải loại trừ các hàng này, bằng cách lặp qua một lát cắt như minh họa ở đây hoặc sử dụng khối thử/ngoại trừ để chỉ trích xuất dữ liệu nếu thao tác cho mỗi hàng thành công
Cuối cùng, bạn nên lưu ý rằng mọi người có thể sửa đổi các giá trị được mã hóa cứng trong bảng tính mà không cần cập nhật các giá trị bắt nguồn từ các công thức sử dụng các giá trị đó. Nếu bạn có bất kỳ nghi ngờ nào về việc liệu bảng tính bạn đang làm việc đã được cập nhật hay chưa, bạn nên tự chạy lại công thức trước khi sử dụng cờ ________ 75 khi tải sổ làm việc
tái cấu trúc
Điều đó có thể đủ để giúp bạn bắt đầu làm việc với dữ liệu được lưu trữ trong các tệp Excel, nhưng cũng đáng để hiển thị một chút tái cấu trúc chương trình mà chúng ta đang sử dụng trong hướng dẫn này. Đây là mã trông như thế nào vào thời điểm này
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
Nếu tất cả những gì chúng tôi muốn làm là tạo một bản tóm tắt văn bản về dữ liệu này, thì mã này có thể sẽ ổn. Nhưng có lẽ chúng tôi sẽ thực hiện một số công việc trực quan hóa và có thể chúng tôi muốn đưa vào một số dữ liệu bổ sung từ một tệp khác. Nếu chúng ta định làm bất cứ điều gì xa hơn, thì đáng để chia nó thành một vài chức năng. Đây là cách chúng tôi có thể tổ chức mã này
from openpyxl import load_workbook
data_file = 'data/mapping_police_violence_snapshot_061920.xlsx'
# Load the entire workbook.
wb = load_workbook[data_file]
# List all the sheets in the file.
print["Found the following worksheets:"]
for sheetname in wb.sheetnames:
print[sheetname]
Chúng tôi tổ chức mã thành hai chức năng, một để truy xuất dữ liệu và một để tổng hợp dữ liệu. Hàm
Found the following worksheets:
2013-2019 Police Killings
2013-2019 Killings by PD
2013-2019 Killings by State
Police Killings of Black Men
Found the following worksheets:
2013-2019 Police Killings
2013-2019 Killings by PD
2013-2019 Killings by State
Police Killings of Black Men
Đọc thêm
Còn rất nhiều điều bạn có thể làm với các tệp Excel trong chương trình Python của mình. Ví dụ: bạn có thể sửa đổi dữ liệu trong tệp Excel hiện có hoặc bạn có thể trích xuất dữ liệu mà bạn quan tâm và tạo một tệp Excel hoàn toàn mới. Để tìm hiểu thêm về những khả năng này, hãy xem tài liệu openpyxl. Bạn cũng có thể trích xuất dữ liệu từ Excel và viết lại ở bất kỳ định dạng dữ liệu nào khác, chẳng hạn như JSON hoặc CSV