Làm cách nào để chèn dữ liệu CSV vào MySQL bằng Python?

Đối với hướng dẫn này, chương trình làm việc của tôi là

1. Lấy dữ liệu từ tệp CSV
2. Thêm dữ liệu vào danh sách
3. Chèn dữ liệu danh sách vào cơ sở dữ liệu MySQL

Đây là tệp CSV của tôi

Bây giờ, tôi sẽ viết mã để thêm dữ liệu này vào danh sách có các từ điển như thế này,

dict_list = [{'empid': '1', 'name': 'john', 'phone': '5123'}, {'empid': '2', 'name': 'jane', 'phone': '5124'}, {'empid': '3', 'name': 'doe', 'phone': '5678'}, {'empid': '4', 'name': 'adam', 'phone': '7427'}]

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

Vì vậy, hãy bắt đầu với hướng dẫn ngày hôm nay. Đảm bảo có dữ liệu. csv vào cùng thư mục với tệp python của bạn
Tôi cho rằng bạn đã thiết lập và chạy mysql

Bước 1]. Nhập mô-đun cần thiết

Trong bước này tôi sẽ nhập các mô-đun quan trọng

import pathlib
import csv
import mysql.connector

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

Bước 2]. Chỉ định đường dẫn tệp

Đảm bảo giữ tệp csv của bạn trong thư mục làm việc hiện tại

csv_path = pathlib.Path.cwd() / "data.csv"

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

Bước 3]. Tìm nạp dữ liệu từ tệp csv và thêm vào từ điển

Điều này sẽ tạo ra một danh sách với từ điển như hình trên

dict_list = list()
with csv_path.open(mode="r") as csv_reader:
    csv_reader = csv.reader(csv_reader)
    for rows in csv_reader:
        dict_list.append({'empid':rows[0], 'name':rows[1], 'phone':rows[2]})

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

Bước 4]. Thêm một kết nối MySQL và con trỏ

Sử dụng kết nối chúng tôi kết nối với cơ sở dữ liệu và con trỏ sẽ giúp thực hiện các thao tác trên đó

mydatabase = mysql.connector.connect(
    username = 'root',
    password = 'adminuser',
    host = 'localhost',
)

mycursor = mydatabase.cursor()

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

Bước [5]. Tạo một cơ sở dữ liệu và gán nó cho kết nối

Chúng tôi đang tạo tên cơ sở dữ liệu "myempdb"

mycursor.execute("CREATE DATABASE myempdb")
# add below line inside your connect method 
# database = 'myempdb',

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

Bước [6]. Tạo bảng 'emptable' trong myempdb

Bảng sẽ chứa 3 hàng "empid", "name", "phone"

mycursor.execute("CREATE TABLE emptable (empid INT PRIMARY KEY, name VARCHAR(255), phone INT")

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

Bước [7]. Thêm dữ liệu vào cơ sở dữ liệu

Thêm dữ liệu list_dict vào cơ sở dữ liệu bằng vòng lặp và đóng cơ sở dữ liệu

for item in dict_list:
    sql = "INSERT INTO emptable(empid, name, phone) VALUES (%s, %s, %s)"
    val = item['empid'], item['name'], item['phone']
    mycursor.execute(sql, val)
mydatabase.commit()

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

Bước [8]. Lấy dữ liệu và hiển thị nó trên thiết bị đầu cuối

Điều này sẽ hiển thị tất cả dữ liệu được lưu trữ trong "emptable" vào thiết bị đầu cuối

________số 8_______

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

Bước [9]. Lấy dữ liệu và hiển thị nó trên thiết bị đầu cuối

Điều này sẽ ngắt kết nối với máy chủ MySQL

mydatabase.close()

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

import pathlib
import csv
import mysql.connector

Vào chế độ toàn màn hình Thoát chế độ toàn màn hình

Bạn có thể sử dụng Python để lấy dữ liệu từ nhiều tệp trong một thư mục và nhập dữ liệu đó vào cơ sở dữ liệu MySQL cùng một lúc. Sử dụng thư viện Pandas để tạo khung dữ liệu của dữ liệu từ tất cả các tệp trong một thư mục, sau đó sử dụng thư viện SQLAlchemy để tạo kết nối với cơ sở dữ liệu của chúng tôi, chúng tôi có thể chèn dữ liệu vào cơ sở dữ liệu

Giả sử bạn đã cài đặt MySQL trên máy của mình, hãy sử dụng terminal để khởi động MySQL shell

Nhập lệnh sau vào thiết bị đầu cuối

`cd /usr/local/mysql/bin`

Tiếp theo, gõ lệnh sau để đảm bảo thay thế root bằng tên người dùng phù hợp nếu không sử dụng root

`. /mysql -u root -p`

Bạn sẽ được nhắc nhập mật khẩu MySQL của mình

Sau khi đăng nhập thành công, bây giờ bạn sẽ ở trong MySQL shell (mysql>)

Từ đây, gõ lệnh. `HIỂN THỊ CƠ SỞ DỮ LIỆU;`

Điều này sẽ cho bạn thấy tất cả các cơ sở dữ liệu trên máy của bạn

Bây giờ hãy thêm một cơ sở dữ liệu mới bằng cách gõ lệnh. `TẠO CƠ SỞ DỮ LIỆU my_db;`

Nếu chúng ta gõ lại lệnh `SHOW DATABASES`, chúng ta sẽ thấy cơ sở dữ liệu mới của mình trong danh sách

Trước khi tạo bảng, hãy chọn ‘my_db’ làm cơ sở dữ liệu mà chúng ta muốn sử dụng bằng cách gõ lệnh. `SỬ DỤNG my_db;`

Bây giờ chúng ta có thể tạo một bảng mà chúng ta sẽ gọi là 'triển vọng'. Bảng này sẽ có năm cột cho ID, chức danh công việc, địa chỉ email, tên và họ. Câu lệnh MySQL của chúng tôi để tạo bảng sẽ là

`TẠO BẢNG NẾU KHÔNG TỒN TẠI khách hàng tiềm năng (

ID INT KHÔNG NULL,

JobTitle VARCHAR(50) NOT NULL,

EmailAddress VARCHAR(255) NOT NULL,

Họ VARCHAR(50) NOT NULL,

FirstName VARCHAR(50) NOT NULL,

Tên tệp VARCHAR(255) NOT NULL

);`

Bây giờ gõ lệnh `DESC triển vọng;`

Điều này cho phép bạn xác thực sự tồn tại và kiểu dữ liệu của các cột chúng tôi vừa tạo trong bảng 'triển vọng' của chúng tôi

Bây giờ chúng ta có thể chuyển sang phát triển tập lệnh Python của mình. Để thoát khỏi dấu nhắc mysql, chỉ cần gõ 'exit' và nhấn enter. Điều này sẽ đưa bạn trở lại thư mục cuối cùng bạn đang ở

Đảm bảo bạn thoát khỏi thư mục ‘bin’ bằng cách nhập ‘cd. ’ hoặc quay lại thư mục gốc bằng cách gõ ‘cd ~’

Bây giờ, điều hướng đến thư mục mà bạn muốn đặt dự án của mình vào. Tôi sẽ tạo một thư mục mới trong thư mục 'Phát triển' của mình có tên là 'text-to-db' và sau đó cd vào thư mục mới đó

Tôi đang sử dụng Python3. Bạn có thể tìm tài liệu về Python 3 tại đây

https. //www. con trăn. tổ chức/

Chỉ cần vào Tải xuống

Tôi cũng đang sử dụng pip, trình cài đặt gói Python. Tài liệu cho pip tại đây

https. //pip. pypa. io/vi/ổn định/

Cái này có hướng dẫn cài đặt cho cả Mac và Windows. Bạn có thể kiểm tra xem bạn đã cài đặt pip trên hệ thống của mình hay chưa bằng cách nhập 'pip --version' hoặc 'pip3 --version'

Tôi sẽ sử dụng một môi trường ảo, vì vậy nếu bạn chưa cài đặt, hãy cài đặt pipenv bằng cách nhập lệnh sau vào thiết bị đầu cuối của bạn - 'pip3 install pipenv'

Bây giờ bạn sẽ có thể tạo và kích hoạt shell môi trường ảo bằng cách gõ lệnh 'pipenv shell'.  

Bây giờ chúng tôi sẽ cài đặt các gói của chúng tôi cho tập lệnh này vào môi trường ảo

3. mysql-connector-python

Bây giờ mình sẽ tạo một file mới bằng cách gõ lệnh ‘touch import_files. py'. Tôi có thể chắc chắn rằng tệp đã được tạo thành công bằng cách gõ lệnh 'ls' để liệt kê tất cả các tệp trong thư mục hiện tại

Bây giờ tôi có thể gõ lệnh 'mã. ’ để mở tệp này trong Visual Studio Code. Nhưng bạn có thể sử dụng bất kỳ mã hoặc trình soạn thảo văn bản nào bạn thích. Bây giờ chúng ta bắt tay vào công việc viết tập lệnh sẽ nhập dữ liệu từ tệp văn bản vào cơ sở dữ liệu MySQL của chúng ta

Các bước tạo kịch bản

Bước 1 - Nhập gói

Sau khi Mã VS được mở, bây giờ chúng ta cần nhập các gói của mình trong tệp import_files. tập tin py

Dưới đây là tóm tắt nhanh về hàng nhập khẩu của chúng tôi

1. os là một mô-đun cho phép chúng ta truy cập vào một phương thức, đường dẫn. tham gia, tham gia đường dẫn của chúng tôi với các tệp của chúng tôi cho cửa sổ. chức năng di chuyển ()
2. dbconfig là một tệp khác mà chúng tôi sẽ tạo để lưu trữ thông tin xác thực cơ sở dữ liệu của mình nhằm che dấu chúng trong tệp import_files. tập tin py
3. Shutil là một mô-đun cung cấp các chức năng cho các hành động của tệp như di chuyển tệp mà chúng tôi sẽ sử dụng
4. glob là một mô-đun cung cấp phương thức glob() sẽ giúp chúng ta liệt kê các tệp (trong trường hợp này là csv) trong các thư mục thư mục mà chúng ta sẽ gán cho một vài biến bên dưới
5. create_engine là một phương thức được nhập từ bộ công cụ sqlalchemy để kết nối với cơ sở dữ liệu của chúng tôi
6. gấu trúc là thư viện chúng tôi sẽ sử dụng để tạo một khung dữ liệu chứa dữ liệu của chúng tôi sẽ được nhập vào MySQL

Bước 2 - Tạo kết nối cơ sở dữ liệu và gán cho một biến Lưu ý rằng chúng tôi sử dụng các biến ở đây thay cho các giá trị bằng chữ cho người dùng, mật khẩu và cơ sở dữ liệu. Điều này là do trong bước tiếp theo, chúng ta sẽ tạo một tệp khác (dbconfig) để lưu trữ những tệp đó nhằm duy trì quyền riêng tư trong tệp import_files. tập tin py

Bước 3 - Tạo tệp dbconfig Tạo tệp mới trong cùng thư mục và đặt tên là dbconfig. py. Sau đó đặt đoạn mã sau vào đó (đảm bảo thay thế các giá trị mà các biến được đặt thành theo tên cơ sở dữ liệu, tên người dùng và mật khẩu của bạn. 

Bước 4 - Tạo kết nối db Tiếp theo chúng ta sử dụng phương thức create_engine từ sqlalchemy để tạo kết nối đến cơ sở dữ liệu.  

Bước 5 - Đặt đường dẫn tệp Điều tiếp theo chúng ta sẽ làm là đặt đường dẫn tệp csv của mình. Trên thực tế, chúng tôi sẽ thiết lập hai đường dẫn. Đầu tiên (biến nguồn) sẽ là đường dẫn nơi tệp csv của chúng tôi bắt nguồn. Đường dẫn thứ hai (biến 'dest') sẽ là thư mục 'archive', chúng tôi sẽ yêu cầu tập lệnh của chúng tôi di chuyển các tệp đến khi dữ liệu của chúng đã được nhập vào cơ sở dữ liệu của chúng tôi

Tiếp theo, chúng tôi sẽ gán một biến có tên 'import_files' cho tất cả các tệp csv trong 'nguồn' chứa dữ liệu để cuối cùng được gán cho một khung dữ liệu

Bước 6 - Tạo danh sách các tệp nhập và đếm số tệp đó Trước tiên, chúng tôi sử dụng mô-đun toàn cục để tạo danh sách các tệp trong đường dẫn tệp 'nguồn' và gán nó cho một biến mà tôi sẽ gọi là 'import_files'. Sau đó, chúng tôi sẽ sử dụng hàm len và lấy số lượng tệp trong danh sách 'import_files' và gán giá trị đó cho một biến mà chúng tôi sẽ gọi là 'filecount'.  

Bước 7 - Thêm dữ liệu từ các tệp vào khung dữ liệu Bây giờ chúng ta có thể sử dụng giá trị của 'filecount' trong câu lệnh if để kiểm tra giá trị của biến và nếu nó nhỏ hơn một (có nghĩa là không có tệp nào trong thư mục 'nguồn') . Nhưng nếu giá trị của 'filecount' lớn hơn 0, chúng tôi sẽ chuyển sang phần khác của câu lệnh if của chúng tôi

Duy trì thụt lề thích hợp bên dưới if và other của bạn trong phần này để đảm bảo mã chạy đúng theo phương pháp kiểm soát luồng này. Thụt lề thực sự là một phần của cú pháp Python, vì vậy nếu bạn chưa quen với điều này, bạn có thể muốn thực hiện một số nghiên cứu về điều đó

Trong phần khác này, chúng tôi đặt mã sẽ nối dữ liệu trong mỗi csv tồn tại trong đường dẫn 'nguồn' bằng cách sử dụng cấu trúc dữ liệu pandas dataframe. Chúng tôi cũng sẽ thêm mã để di chuyển các tệp đến đường dẫn 'dest' (thư mục lưu trữ của chúng tôi) sau khi dữ liệu được thêm vào khung dữ liệu. Sau đó, chúng tôi có thể in khung dữ liệu của mình lên bảng điều khiển để xác thực nhanh

Pandas cung cấp một phương thức gọi là 'concat' sẽ nối thêm dữ liệu từ mỗi tệp trong đường dẫn nguồn. Và phương thức 'read_csv' đọc từng tệp. Đây là những gì tôi sẽ sử dụng để đọc từng tệp trong thư mục nguồn dưới dạng khung dữ liệu gấu trúc.  

Một điều thú vị khác cần lưu ý ở đây là tôi đã thêm '. gán (tên tệp = tệp)' để thêm tên của tệp, mỗi hàng dữ liệu đến từ khung dữ liệu (và cuối cùng là cơ sở dữ liệu). Ngoài ra, theo mặc định, Pandas luôn thêm một chỉ mục vào mỗi hàng của khung dữ liệu. Tôi không cần điều đó trong trường hợp này, vì vậy tôi thêm đối số 'ignore_index' vào '. concat' đặt nó thành 'True'

Dòng tiếp theo của tập lệnh là 'shutil. phương thức di chuyển' và điều này sẽ di chuyển các tệp từ 'nguồn' sang đường dẫn 'đích'. Khi đã xong, tôi thêm một dòng 'print(df)' để xuất nội dung của khung dữ liệu mới vào thiết bị đầu cuối

Bước 8 - Gửi khung dữ liệu đến cơ sở dữ liệu MySQL Bây giờ tôi cuối cùng cũng có thể viết mã để chèn khung dữ liệu vào cơ sở dữ liệu của chúng ta. Tôi vẫn đang ở trong câu lệnh khác, đảm bảo rằng tôi duy trì cùng một khoảng thụt đầu dòng cho một vài dòng tiếp theo

Tôi sử dụng khung dữ liệu của Pandas. phương thức to_sql' tại đây. Có ba đối số trong phương pháp này để chúng tôi sử dụng. bảng cơ sở dữ liệu, công cụ/kết nối sqlalchemy và if_exists. Hai cái đầu tiên tương đối dễ hiểu. Đối số 'if_exists' cho phép chúng tôi chỉ định hành vi nếu bảng đã tồn tại. Giá trị 'thay thế' sẽ loại bỏ bảng hiện có và tạo lại nó. Giá trị 'chắp thêm' sẽ chỉ thêm các hàng vào bảng dữ liệu hiện có nếu nó tồn tại

Trong trường hợp sử dụng này, tôi đang tự động hóa một quy trình có thể lặp lại và muốn tiếp tục thêm dữ liệu khi các tệp văn bản mới được thêm vào thư mục 'nguồn'. Vì vậy, tôi đã chọn ‘append’ cho 'if_exists'

Tôi thêm một dòng in ở cuối để xuất thông báo thành công ra bàn điều khiển. Và đó là nó. Điều duy nhất còn lại phải làm bây giờ là chạy tập lệnh

Để bắt đầu, tôi có năm tệp csv trong thư mục 'nguồn' mà tôi cần nhập vào cơ sở dữ liệu của mình

Trở lại thư mục dự án của mình, tôi sẽ kích hoạt môi trường ảo bằng cách gõ lệnh ‘pipenv shell’

Bây giờ tôi đã sẵn sàng để chạy tập lệnh bằng cách gõ lệnh 'python3 import_files. py'

Vì 'print(df)' trên dòng 42, tôi nhận được bản xem trước của khung dữ liệu trong bảng điều khiển cũng như thông báo thành công của tôi ('hoàn tất nhập tệp. ) từ dòng 48.  

Để xác minh dữ liệu đã được nhập vào cơ sở dữ liệu MySQL của chúng tôi, hãy quay lại trình bao MySQL trong thiết bị đầu cuối của chúng tôi và chạy 'CHỌN * TỪ khách hàng tiềm năng'

Một điều cuối cùng cần kiểm tra là các tệp của chúng tôi đã được chuyển từ thư mục 'nguồn' sang thư mục 'đích' của chúng tôi

Lưu ý rằng lệnh 'ls' cho thấy rằng các tệp không còn trong thư mục 'CSV' và hiện nằm trong thư mục 'Archive' được lồng trong thư mục 'CSV'

Làm cách nào để nhập dữ liệu trong MySQL bằng Python?

Làm cách nào để chèn dữ liệu Excel vào bảng MySQL bằng Python?

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

Chúng tôi có thể nhập CSV trong MySQL không?