Đọc và ghi tệp SQL trong Pandas

Được xuất bản lại bởi Plato

Người theo dõi: 0

Khi tôi bắt đầu học Phân tích dữ liệu vài năm trước, điều đầu tiên tôi học là SQL và Pandas. Là một nhà phân tích dữ liệu, điều quan trọng là phải có nền tảng vững chắc khi làm việc với SQL và Pandas. Cả hai đều là những công cụ mạnh mẽ giúp các nhà phân tích dữ liệu phân tích và thao tác dữ liệu được lưu trữ trong cơ sở dữ liệu một cách hiệu quả.

Tổng quan về SQL và Pandas

SQL (Structured Query Language) là ngôn ngữ lập trình được sử dụng để quản lý và thao tác cơ sở dữ liệu quan hệ. Mặt khác, Pandas là một thư viện Python được sử dụng để thao tác và phân tích dữ liệu.

Phân tích dữ liệu liên quan đến việc làm việc với lượng lớn dữ liệu và cơ sở dữ liệu thường được sử dụng để lưu trữ dữ liệu này. SQL và Pandas cung cấp các công cụ mạnh mẽ để làm việc với cơ sở dữ liệu, cho phép các nhà phân tích dữ liệu trích xuất, thao tác và phân tích dữ liệu một cách hiệu quả. Bằng cách tận dụng các công cụ này, các nhà phân tích dữ liệu có thể thu được những hiểu biết có giá trị từ dữ liệu mà khó có được.

Trong bài viết này, chúng ta sẽ khám phá cách sử dụng SQL và Pandas để đọc và ghi vào cơ sở dữ liệu.

Kết nối với DB

Cài đặt các thư viện

Trước tiên chúng ta phải cài đặt các thư viện cần thiết trước khi có thể kết nối với cơ sở dữ liệu SQL bằng Pandas. Hai thư viện chính được yêu cầu là Pandas và SQLAlchemy. Pandas là một thư viện thao tác dữ liệu phổ biến cho phép lưu trữ các cấu trúc dữ liệu lớn, như đã đề cập trong phần giới thiệu. Ngược lại, SQLAlchemy cung cấp API để kết nối và tương tác với cơ sở dữ liệu SQL.

Chúng ta có thể cài đặt cả hai thư viện bằng trình quản lý gói Python, pip, bằng cách chạy các lệnh sau tại dấu nhắc lệnh.

$ pip install pandas
$ pip install sqlalchemy

Tạo kết nối

Với các thư viện đã được cài đặt, giờ đây chúng ta có thể sử dụng Pandas để kết nối với cơ sở dữ liệu SQL.

Để bắt đầu, chúng ta sẽ tạo một đối tượng công cụ SQLAlchemy với create_engine(). Các create_engine() chức năng kết nối mã Python với cơ sở dữ liệu. Nó nhận làm đối số là một chuỗi kết nối chỉ định loại cơ sở dữ liệu và chi tiết kết nối. Trong ví dụ này, chúng tôi sẽ sử dụng loại cơ sở dữ liệu SQLite và đường dẫn của tệp cơ sở dữ liệu.

Tạo một đối tượng động cơ cho cơ sở dữ liệu SQLite bằng ví dụ bên dưới:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db')

Nếu tệp cơ sở dữ liệu SQLite, student.db trong trường hợp của chúng tôi, nằm trong cùng thư mục với tập lệnh Python, chúng tôi có thể sử dụng tên tệp trực tiếp, như minh họa bên dưới.

engine = create_engine('sqlite:///student.db')

Đọc tệp SQL với Pandas

Hãy đọc dữ liệu ngay bây giờ khi chúng ta đã thiết lập kết nối. Trong phần này, chúng ta sẽ xem xét các read_sql, read_sql_tablevà read_sql_query chức năng và cách sử dụng chúng để làm việc với cơ sở dữ liệu.

Thực thi các truy vấn SQL bằng Panda's read_sql() Chức năng

Sản phẩm read_sql() là một chức năng thư viện Pandas cho phép chúng tôi thực hiện truy vấn SQL và truy xuất kết quả vào khung dữ liệu Pandas. Các read_sql() chức năng kết nối SQL và Python, cho phép chúng tôi tận dụng sức mạnh của cả hai ngôn ngữ. chức năng kết thúc tốt đẹp read_sql_table() và read_sql_query(). Các read_sql() chức năng được định tuyến nội bộ dựa trên đầu vào được cung cấp, có nghĩa là nếu đầu vào là để thực thi một truy vấn SQL, thì nó sẽ được định tuyến đến read_sql_query()và nếu đó là một bảng cơ sở dữ liệu, nó sẽ được định tuyến đến read_sql_table().

Sản phẩm read_sql() cú pháp như sau:

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

Các tham số SQL và con là bắt buộc; phần còn lại là tùy chọn. Tuy nhiên, chúng ta có thể thao tác kết quả bằng cách sử dụng các tham số tùy chọn này. Chúng ta hãy xem xét kỹ hơn từng tham số.

sql: Truy vấn SQL hoặc tên bảng cơ sở dữ liệu
con: Đối tượng kết nối hoặc URL kết nối
index_col: Tham số này cho phép chúng tôi sử dụng một hoặc nhiều cột từ kết quả truy vấn SQL làm chỉ mục khung dữ liệu. Nó có thể lấy một cột hoặc một danh sách các cột.
coerce_float: Tham số này chỉ định xem các giá trị không phải là số nên được chuyển đổi thành số thực hay để nguyên dưới dạng chuỗi. Nó được đặt thành true theo mặc định. Nếu có thể, nó sẽ chuyển đổi các giá trị không phải là số thành kiểu float.
params: Các thông số cung cấp một phương pháp an toàn để chuyển các giá trị động tới truy vấn SQL. Chúng ta có thể sử dụng tham số params để truyền từ điển, bộ dữ liệu hoặc danh sách. Tùy thuộc vào cơ sở dữ liệu, cú pháp của params khác nhau.
parse_dates: Điều này cho phép chúng tôi chỉ định cột nào trong khung dữ liệu kết quả sẽ được hiểu là ngày. Nó chấp nhận một cột, một danh sách các cột hoặc một từ điển với khóa là tên cột và giá trị là định dạng cột.
columns: Điều này cho phép chúng tôi chỉ tìm nạp các cột đã chọn từ danh sách.
chunksize: Khi làm việc với một tập dữ liệu lớn, chunksize rất quan trọng. Nó truy xuất kết quả truy vấn theo các phần nhỏ hơn, nâng cao hiệu suất.

Đây là một ví dụ về cách sử dụng read_sql():

Mã Code:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql("SELECT * FROM Student", engine, index_col='Roll Number', parse_dates='dateOfBirth')
print(df)
print("The Data type of dateOfBirth: ", df.dateOfBirth.dtype) engine.dispose()

Đầu ra:

 firstName lastName email dateOfBirth
rollNumber
1 Mark Simson  2000-02-23
2 Peter Griffen  2001-04-15
3 Meg Aniston  2001-09-20
Date type of dateOfBirth: datetime64[ns]

Sau khi kết nối với cơ sở dữ liệu, chúng tôi thực hiện một truy vấn trả về tất cả các bản ghi từ Student bảng và lưu trữ chúng trong DataFrame df. Cột "Số cuộn" được chuyển đổi thành một chỉ mục bằng cách sử dụng index_col tham số và kiểu dữ liệu “dateOfBirth” là “datetime64[ns]” do parse_dates. Chúng ta có thể sử dụng read_sql() không chỉ để truy xuất dữ liệu mà còn để thực hiện các thao tác khác như chèn, xóa và cập nhật. read_sql() là một hàm chung.

Đang tải các bảng hoặc dạng xem cụ thể từ DB

Đang tải một bảng hoặc dạng xem cụ thể bằng Pandas read_sql_table() là một kỹ thuật khác để đọc dữ liệu từ cơ sở dữ liệu vào khung dữ liệu Pandas.

Là gì bảng đọc_sql_?

Thư viện Pandas cung cấp read_sql_table được thiết kế đặc biệt để đọc toàn bộ bảng SQL mà không thực hiện bất kỳ truy vấn nào và trả về kết quả dưới dạng khung dữ liệu Pandas.

Cú pháp của read_sql_table() như dưới đây:

pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

Ngoại trừ table_name và lược đồ, các tham số được giải thích giống như read_sql().

table_name: Thông số table_name là tên của bảng SQL trong cơ sở dữ liệu.
schema: Tham số tùy chọn này là tên của lược đồ chứa tên bảng.

Sau khi tạo kết nối đến cơ sở dữ liệu, chúng ta sẽ sử dụng read_sql_table chức năng tải Student bảng thành Khung dữ liệu Pandas.

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Student', engine)
print(df.head()) engine.dispose()

Đầu ra:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson  2000-02-23
1 2 Peter Griffen  2001-04-15
2 3 Meg Aniston  2001-09-20

Chúng tôi sẽ cho rằng đó là một bảng lớn có thể sử dụng nhiều bộ nhớ. Hãy khám phá cách chúng ta có thể sử dụng chunksize tham số để giải quyết vấn đề này.

Xem hướng dẫn thực hành, thực tế của chúng tôi để học Git, với các phương pháp hay nhất, các tiêu chuẩn được ngành công nghiệp chấp nhận và bảng lừa đảo đi kèm. Dừng lệnh Googling Git và thực sự học nó!

Mã Code:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df_iterator = pd.read_sql_table('Student', engine, chunksize = 1) for df in df_iterator: print(df.head()) engine.dispose()

Đầu ra:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson  2000-02-23
0 2 Peter Griffen  2001-04-15
0 3 Meg Aniston  2001-09-20

Hãy ghi nhớ rằng chunksize Tôi đang sử dụng ở đây là 1 vì tôi chỉ có 3 bản ghi trong bảng của mình.

Truy vấn DB trực tiếp bằng Cú pháp SQL của Pandas

Trích xuất thông tin chi tiết từ cơ sở dữ liệu là một phần quan trọng đối với các nhà phân tích dữ liệu và nhà khoa học. Để làm như vậy, chúng tôi sẽ tận dụng read_sql_query() chức năng.

Read_sql_query() là gì?

Sử dụng Pandas' read_sql_query() chức năng, chúng tôi có thể chạy các truy vấn SQL và nhận kết quả trực tiếp vào DataFrame. Các read_sql_query() chức năng được tạo riêng cho SELECT các câu lệnh. Nó không thể được sử dụng cho bất kỳ hoạt động nào khác, chẳng hạn như DELETE or UPDATE.

Cú pháp:

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None, dtype=None, dtype_backend=_NoDefault.no_default)

Tất cả các mô tả tham số đều giống như read_sql() chức năng. Đây là một ví dụ về read_sql_query():

Mã Code:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_query('Select firstName, lastName From Student Where rollNumber = 1', engine)
print(df) engine.dispose()

Đầu ra:

 firstName lastName
0 Mark Simson

Viết tệp SQL với Pandas

Trong khi phân tích dữ liệu, giả sử chúng tôi phát hiện ra rằng cần phải sửa đổi một vài mục nhập hoặc cần có một bảng hoặc dạng xem mới với dữ liệu. Để cập nhật hoặc chèn một bản ghi mới, một phương pháp là sử dụng read_sql() và viết một truy vấn. Tuy nhiên, phương pháp đó có thể kéo dài. Pandas cung cấp một phương pháp tuyệt vời gọi là to_sql() cho những tình huống như thế này.

Trong phần này, trước tiên chúng ta sẽ tạo một bảng mới trong cơ sở dữ liệu và sau đó chỉnh sửa bảng hiện có.

Tạo một bảng mới trong cơ sở dữ liệu SQL

Trước khi chúng ta tạo một bảng mới, trước tiên chúng ta hãy thảo luận to_sql() chi tiết.

Là gì to_sql()?

Sản phẩm to_sql() chức năng của thư viện Pandas cho phép chúng tôi viết hoặc cập nhật cơ sở dữ liệu. Các to_sql() có thể lưu dữ liệu DataFrame vào cơ sở dữ liệu SQL.

Cú pháp cho to_sql():

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

Chỉ có name và con tham số là bắt buộc để chạy to_sql(); tuy nhiên, các tham số khác cung cấp các tùy chọn tùy chỉnh và linh hoạt bổ sung. Hãy thảo luận chi tiết từng tham số:

name: Tên của bảng SQL sẽ được tạo hoặc thay đổi.
con: Đối tượng kết nối của cơ sở dữ liệu.
schema: Lược đồ của bảng (tùy chọn).
if_exists: Giá trị mặc định của tham số này là “fail”. Tham số này cho phép chúng tôi quyết định hành động sẽ được thực hiện nếu bảng đã tồn tại. Các tùy chọn bao gồm "thất bại", "thay thế" và "chắp thêm".
index: Tham số chỉ mục chấp nhận giá trị boolean. Theo mặc định, nó được đặt thành True, nghĩa là chỉ mục của DataFrame sẽ được ghi vào bảng SQL.
index_label: Tham số tùy chọn này cho phép chúng tôi chỉ định nhãn cột cho các cột chỉ mục. Theo mặc định, chỉ mục được ghi vào bảng, nhưng có thể đặt tên cụ thể bằng cách sử dụng tham số này.
chunksize: Số hàng được ghi tại một thời điểm trong cơ sở dữ liệu SQL.
dtype: Tham số này chấp nhận một từ điển với các khóa là tên cột và giá trị là kiểu dữ liệu của chúng.
method: Tham số phương thức cho phép chỉ định phương thức được sử dụng để chèn dữ liệu vào SQL. Theo mặc định, nó được đặt thành Không, có nghĩa là gấu trúc sẽ tìm ra cách hiệu quả nhất dựa trên cơ sở dữ liệu. Có hai tùy chọn chính cho các tham số phương pháp:
- multi: Nó cho phép chèn nhiều hàng trong một truy vấn SQL duy nhất. Tuy nhiên, không phải tất cả các cơ sở dữ liệu đều hỗ trợ chèn nhiều hàng.
- chức năng có thể gọi: Ở đây, chúng ta có thể viết một hàm tùy chỉnh để chèn và gọi nó bằng cách sử dụng các tham số của phương thức.

Đây là một ví dụ sử dụng to_sql():

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') data = {'Name': ['Paul', 'Tom', 'Jerry'], 'Age': [9, 8, 7]}
df = pd.DataFrame(data) df.to_sql('Customer', con=engine, if_exists='fail') engine.dispose()

Một bảng mới có tên là Khách hàng được tạo trong cơ sở dữ liệu, với hai trường có tên là “Tên” và “Tuổi”.

Ảnh chụp cơ sở dữ liệu:

Đầu ra của to_sql()

Cập nhật các bảng hiện có với Pandas Dataframes

Cập nhật dữ liệu trong cơ sở dữ liệu là một nhiệm vụ phức tạp, đặc biệt khi xử lý dữ liệu lớn. Tuy nhiên, sử dụng to_sql() chức năng trong Pandas có thể làm cho nhiệm vụ này dễ dàng hơn nhiều. Để cập nhật bảng hiện có trong cơ sở dữ liệu, to_sql() chức năng có thể được sử dụng với if_exists tham số được đặt thành "thay thế". Thao tác này sẽ ghi đè lên bảng hiện có bằng dữ liệu mới.

Đây là một ví dụ về to_sql() cập nhật cái đã tạo trước đó Customer bàn. Giả sử, trong Customer chúng tôi muốn cập nhật tuổi của một khách hàng có tên Paul từ 9 lên 10. Để làm như vậy, trước tiên, chúng tôi có thể sửa đổi hàng tương ứng trong DataFrame, sau đó sử dụng to_sql() chức năng cập nhật cơ sở dữ liệu.

Mã Code:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Customer', engine) df.loc[df['Name'] == 'Paul', 'Age'] = 10 df.to_sql('Customer', con=engine, if_exists='replace') engine.dispose()

Trong cơ sở dữ liệu, tuổi của Paul được cập nhật:

Đầu ra của to_sql()

Kết luận

Tóm lại, Pandas và SQL đều là những công cụ mạnh mẽ cho các tác vụ phân tích dữ liệu như đọc và ghi dữ liệu vào cơ sở dữ liệu SQL. Pandas cung cấp một cách dễ dàng để kết nối với cơ sở dữ liệu SQL, đọc dữ liệu từ cơ sở dữ liệu vào khung dữ liệu Pandas và ghi dữ liệu khung dữ liệu trở lại cơ sở dữ liệu.

Thư viện Pandas giúp dễ dàng thao tác dữ liệu trong khung dữ liệu, trong khi SQL cung cấp ngôn ngữ mạnh mẽ để truy vấn dữ liệu trong cơ sở dữ liệu. Sử dụng cả Pandas và SQL để đọc và ghi dữ liệu có thể tiết kiệm thời gian và công sức trong các tác vụ phân tích dữ liệu, đặc biệt khi dữ liệu rất lớn. Nhìn chung, việc tận dụng SQL và Pandas cùng nhau có thể giúp các nhà phân tích dữ liệu và nhà khoa học hợp lý hóa quy trình làm việc của họ.