Nhập môn Cơ sở dữ liệu với Python
BasicSkill Level
4 giờ
102.1K learners
SQL là gì
Structured Query Language (SQL) là ngôn ngữ phổ biến nhất dùng để thực hiện nhiều tác vụ phân tích dữ liệu. Nó cũng được dùng để vận hành cơ sở dữ liệu quan hệ, ví dụ: thêm bảng, xóa giá trị và tối ưu hóa cơ sở dữ liệu. Một cơ sở dữ liệu quan hệ đơn giản gồm nhiều bảng liên kết với nhau, và mỗi bảng gồm các hàng và cột.
Trung bình, một công ty công nghệ tạo ra hàng triệu điểm dữ liệu mỗi ngày. Họ cần một giải pháp lưu trữ vững chắc và hiệu quả để có thể dùng dữ liệu cải thiện hệ thống hiện tại hoặc phát triển sản phẩm mới. Các cơ sở dữ liệu quan hệ như MySQL, PostgreSQL và SQLite giải quyết các vấn đề này bằng cách cung cấp quản trị cơ sở dữ liệu mạnh mẽ, bảo mật và hiệu năng cao.
Các chức năng cốt lõi của SQL
- Tạo bảng mới trong cơ sở dữ liệu
- Thực thi truy vấn trên cơ sở dữ liệu
- Truy xuất dữ liệu từ cơ sở dữ liệu
- Chèn bản ghi vào cơ sở dữ liệu
- Cập nhật bản ghi trong cơ sở dữ liệu
- Xóa bản ghi khỏi cơ sở dữ liệu
- Tối ưu hóa mọi cơ sở dữ liệu
SQL là kỹ năng có nhu cầu cao giúp bạn tìm được việc trong ngành công nghệ. Các công ty như Meta, Google và Netflix luôn tìm kiếm chuyên gia dữ liệu có thể trích xuất thông tin từ cơ sở dữ liệu SQL và đề xuất giải pháp sáng tạo. Bạn có thể học kiến thức cơ bản về SQL qua khóa Giới thiệu về SQL trên DataCamp.
Vì sao dùng SQL cùng Python và R?
SQL giúp chúng ta khám phá hiệu suất của công ty, hiểu hành vi khách hàng và theo dõi các chỉ số thành công của chiến dịch tiếp thị. Hầu hết nhà phân tích dữ liệu có thể thực hiện đa số tác vụ BI bằng cách chạy truy vấn SQL, vậy tại sao cần các công cụ như PoweBI, Python và R? Với truy vấn SQL, bạn có thể biết điều gì đã xảy ra trong quá khứ, nhưng không thể dự đoán tương lai. Những công cụ này giúp chúng ta hiểu sâu hơn về hiệu suất hiện tại và tiềm năng tăng trưởng.
Python và R là ngôn ngữ đa dụng cho phép chuyên gia chạy phân tích thống kê nâng cao, xây dựng mô hình học máy, tạo API dữ liệu và cuối cùng giúp công ty nhìn xa hơn các KPI. Trong hướng dẫn này, chúng ta sẽ học cách kết nối cơ sở dữ liệu SQL, nạp dữ liệu vào cơ sở dữ liệu và chạy truy vấn SQL bằng Python và R.
Lưu ý: Nếu bạn mới bắt đầu với SQL, hãy học lộ trình kỹ năng SQL để nắm các nguyên tắc viết truy vấn SQL.
Hướng dẫn Python
Phần Python sẽ bao quát những điều cơ bản về kết nối các cơ sở dữ liệu khác nhau (MySQL và SQLite), tạo bảng, thêm bản ghi, chạy truy vấn và tìm hiểu hàm Pandas read_sql.
Thiết lập
Chúng ta có thể kết nối cơ sở dữ liệu bằng SQLAlchemy, nhưng trong hướng dẫn này, chúng ta sẽ dùng gói tích hợp sẵn của Python là SQLite3 để chạy truy vấn trên cơ sở dữ liệu. SQLAlchemy hỗ trợ mọi loại cơ sở dữ liệu thông qua một API thống nhất. Nếu bạn muốn tìm hiểu thêm về SQLAlchemy và cách nó hoạt động với các cơ sở dữ liệu khác, hãy xem khóa Giới thiệu về Cơ sở dữ liệu trong Python.
MySQL là hệ quản trị cơ sở dữ liệu phổ biến nhất thế giới, được dùng rộng rãi bởi các công ty như Youtube, Paypal, LinkedIn và GitHub. Ở đây chúng ta sẽ học cách kết nối cơ sở dữ liệu. Các bước còn lại khi dùng MySQL tương tự gói SQLite3.
Trước tiên, cài đặt gói mysql bằng '!pip install mysql' rồi tạo một engine cơ sở dữ liệu cục bộ bằng cách cung cấp tên người dùng, mật khẩu và tên cơ sở dữ liệu.
import mysql.connector as sql
conn = sql.connect(
host="localhost",
user="abid",
password="12345",
database="datacamp_python"
)
Tương tự, chúng ta có thể tạo hoặc tải một cơ sở dữ liệu SQLite bằng hàm sqlite3.connect. SQLite là thư viện triển khai engine cơ sở dữ liệu tự chứa, không cần cấu hình và không cần máy chủ. Nó thân thiện với DataLab, nên chúng ta sẽ dùng trong dự án để tránh lỗi localhost.
import sqlite3
import pandas as pd
conn= sqlite3.connect("datacamp_python.db")
Bắt đầu học Python và SQL miễn phí
Tìm hiểu kiến thức cơ bản về cơ sở dữ liệu quan hệ và cách tương tác với chúng trong khóa học này.
Nhập môn SQL
BasicSkill Level
2 giờ
1.6M learners
Tạo cơ sở dữ liệu
Trong phần này, chúng ta sẽ học cách nạp bộ dữ liệu Tác động của COVID-19 đến lưu lượng sân bay, theo giấy phép CC BY-NC-SA 4.0, vào cơ sở dữ liệu SQLite của chúng ta. Chúng ta cũng sẽ học cách tạo bảng từ đầu.
Bộ dữ liệu lưu lượng sân bay gồm tỷ lệ phần trăm khối lượng lưu lượng trong giai đoạn cơ sở từ 1/2/2020 đến 15/3/2020. Chúng ta sẽ nạp tệp CSV bằng hàm Pandas read_csv và sau đó dùng hàm to_sql để chuyển dataframe vào bảng SQLite. Hàm to_sql yêu cầu tên bảng (chuỗi) và kết nối tới engine SQLite.
data = pd.read_csv("data/covid_impact_on_airport_traffic.csv")
data.to_sql(
'airport', # Name of the sql table
conn, # sqlite.Connection or sqlalchemy.engine.Engine
if_exists='replace'
)
Bây giờ, hãy kiểm tra xem chúng ta đã thành công chưa bằng cách chạy một truy vấn SQL nhanh. Trước khi chạy truy vấn, chúng ta cần tạo một con trỏ để giúp thực thi truy vấn, như trong khối mã dưới đây. Bạn có thể có nhiều con trỏ trên cùng một cơ sở dữ liệu trong một kết nối.
Trong ví dụ của chúng ta, truy vấn SQL trả về ba cột và năm hàng từ bảng airport. Để hiển thị hàng đầu tiên, chúng ta sẽ dùng cursor.fetchone().
cursor = conn.cursor()
cursor.execute("""SELECT Date, AirportName, PercentOfBaseline
FROM airport
LIMIT 5""")
cursor.fetchone()
>>> ('2020-04-03', 'Kingsford Smith', 64)
Để hiển thị các bản ghi còn lại, chúng ta sẽ dùng cursor.fetchall(). Bộ dữ liệu sân bay đã được nạp thành công vào cơ sở dữ liệu chỉ với vài dòng mã.
cursor.fetchall()
>>> [('2020-04-13', 'Kingsford Smith', 29),
('2020-07-10', 'Kingsford Smith', 54),
('2020-09-02', 'Kingsford Smith', 18),
('2020-10-31', 'Kingsford Smith', 22)]
Giờ hãy học cách tạo bảng từ đầu và nạp dữ liệu bằng cách thêm các giá trị mẫu. Chúng ta sẽ tạo bảng studentinfo với id (số nguyên, khóa chính, tự tăng), name (văn bản) và subject (văn bản).
Lưu ý: Cú pháp SQLite hơi khác. Khuyến nghị tham khảo cheat sheet SQLite để hiểu các truy vấn SQL được nêu trong hướng dẫn này.
cursor.execute("""
CREATE TABLE studentinfo
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
subject TEXT
)
""")
Hãy kiểm tra xem chúng ta đã thêm bao nhiêu bảng vào cơ sở dữ liệu bằng cách chạy một truy vấn SQLite đơn giản.
cursor.execute("""
SELECT name
FROM sqlite_master
WHERE type='table'
""")
cursor.fetchall()
>>> [('airport',), ('studentinfo',)]
Chạy truy vấn
Trong phần này, chúng ta sẽ thêm giá trị vào bảng studentinfo và chạy các truy vấn SQL đơn giản. Bằng cách dùng INSERT INTO, chúng ta có thể thêm một hàng vào bảng studentinfo.
Để chèn giá trị, chúng ta cần cung cấp tham số query và value cho hàm execute. Hàm sẽ điền các ô “?” bằng các giá trị đã cung cấp.
query = """
INSERT INTO studentinfo
(name, subject)
VALUES (?, ?)
"""
value = ("Marry", "Math")
cursor.execute(query,value)
Lặp lại truy vấn trên bằng cách thêm nhiều bản ghi.
query = """
INSERT INTO studentinfo
(name, subject)
VALUES (?, ?)
"""
values = [("Abid", "Stat"),
("Carry", "Math"),
("Ali","Data Science"),
("Nisha","Data Science"),
("Matthew","Math"),
("Henry","Data Science")]
cursor.executemany(query,values)
Đã đến lúc xác minh bản ghi. Để làm điều này, chúng ta sẽ chạy một truy vấn SQL đơn giản trả về các hàng có subject là Data Science.
cursor.execute("""
SELECT *
FROM studentinfo
WHERE subject LIKE 'Data Science'
""")
cursor.fetchall()
>>> [(4, 'Ali', 'Data Science'),
(5, 'Nisha', 'Data Science'),
(7, 'Henry', 'Data Science')]
Lệnh DISTINCT subject dùng để hiển thị các giá trị duy nhất có trong cột subject. Ở đây là Math, Stat và Data Science.
cursor.execute("SELECT DISTINCT subject from studentinfo")
cursor.fetchall()
>>> [('Math',), ('Stat',), ('Data Science',)]
Để lưu tất cả thay đổi, chúng ta sẽ dùng hàm commit(). Nếu không commit, dữ liệu sẽ mất sau khi máy khởi động lại.
conn.commit()
SQL với Pandas
Trong phần này, chúng ta sẽ học cách trích xuất dữ liệu từ cơ sở dữ liệu SQLite và chuyển thành dataframe Pandas chỉ với một dòng mã. read_sql làm được nhiều hơn là chỉ chạy truy vấn SQL. Ta có thể dùng để đặt cột chỉ mục, phân tích cú pháp ngày giờ, thêm giá trị và lọc tên cột. Tìm hiểu thêm về nhập dữ liệu trong Python qua khóa học ngắn trên DataCamp.
read_sql cần hai tham số: một truy vấn SQL và kết nối tới engine SQLite. Kết quả bao gồm năm hàng đầu từ bảng airport nơi PercentOfBaseline lớn hơn 20.
data_sql_1 = pd.read_sql("""
SELECT Date,City,PercentOfBaseline
FROM airport
WHERE PercentOfBaseline > 20
ORDER BY Date DESC
""",
conn)
print(data_sql_1.head())
Date City PercentOfBaseline
0 2020-12-02 Sydney 27
1 2020-12-02 Santiago 48
2 2020-12-02 Calgary 99
3 2020-12-02 Leduc County 100
4 2020-12-02 Richmond 86
Chạy phân tích dữ liệu trên cơ sở dữ liệu quan hệ trở nên dễ dàng hơn với tích hợp Pandas. Chúng ta cũng có thể dùng dữ liệu này để dự báo giá trị và chạy các phân tích thống kê phức tạp.
Hàm plot được dùng để trực quan hóa biểu đồ histogram của cột PercentOfBaseline.
data_sql_1.plot(y="PercentOfBaseline",kind="hist");
Tương tự, chúng ta có thể giới hạn giá trị ở top 20 và hiển thị biểu đồ đường chuỗi thời gian.
data_sql_2 = pd.read_sql("""
SELECT Date,City,PercentOfBaseline
FROM airport
WHERE PercentOfBaseline > 20
ORDER BY Date DESC
LIMIT 20
""",
conn)
data_sql_2.plot(x="Date",y="PercentOfBaseline",kind="line");
Cuối cùng, chúng ta sẽ đóng kết nối để giải phóng tài nguyên. Phần lớn gói sẽ làm việc này tự động, nhưng tốt nhất là đóng kết nối sau khi hoàn tất thay đổi.
conn.close()
Hướng dẫn R
Chúng ta sẽ tái hiện tất cả tác vụ từ phần Python bằng R. Hướng dẫn gồm tạo kết nối, ghi bảng, nối thêm hàng, chạy truy vấn và phân tích dữ liệu với dplyr.
Thiết lập
Gói DBI được dùng để kết nối với các cơ sở dữ liệu phổ biến như MariaDB, Postgres, Duckdb và SQLite. Ví dụ, cài gói RMySQL và tạo cơ sở dữ liệu bằng cách cung cấp tên người dùng, mật khẩu, tên cơ sở dữ liệu và địa chỉ host.
install.packages("RMySQL")
library(RMySQL)
conn = dbConnect(
MySQL(),
user = 'abid',
password = '1234',
dbname = 'datacamp_R',
host = 'localhost'
)
Trong hướng dẫn này, chúng ta sẽ tạo cơ sở dữ liệu SQLite bằng cách cung cấp tên và hàm SQLite.
library(RSQLite)
library(DBI)
library(tidyverse)
conn = dbConnect(SQLite(), dbname = 'datacamp_R.db')
Tạo cơ sở dữ liệu
Bằng cách nhập thư viện tidyverse, chúng ta sẽ có quyền truy cập vào dplyr, ggplot và các bộ dữ liệu mặc định.
Hàm dbWriteTable nhận data.frame và thêm vào bảng SQL. Nó nhận ba tham số: kết nối tới SQLite, tên bảng và data frame. Với dbReadTable, chúng ta có thể xem toàn bộ bảng. Để xem 6 hàng đầu, chúng ta dùng head.
dbWriteTable(conn, "cars", mtcars)
head(dbReadTable(conn, "cars"))
mpg cyl disp hp drat wt qsec vs am gear carb
1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
5 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
6 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1
dbExecute cho phép chúng ta thực thi bất kỳ truy vấn SQLite nào, vì vậy chúng ta sẽ dùng để tạo bảng tên idcard.
Để hiển thị tên các bảng trong cơ sở dữ liệu, chúng ta sẽ dùng dbListTables.
dbExecute(conn, 'CREATE TABLE idcard (id int, name text)')
dbListTables(conn)
>>> 'cars''idcard'
Hãy thêm một hàng vào bảng idcard và dùng dbGetQuery để hiển thị kết quả.
Lưu ý: dbGetQuery chạy truy vấn và trả về bản ghi, trong khi dbExecute chạy truy vấn SQL nhưng không trả về bản ghi.
dbExecute(conn, "INSERT INTO idcard (id,name)\
VALUES(1,'love')")
dbGetQuery(conn,"SELECT * FROM idcard")
id name
1 love
Giờ chúng ta sẽ thêm hai hàng nữa và hiển thị kết quả bằng dbReadTable.
dbExecute(conn,"INSERT INTO idcard (id,name)\
VALUES(2,'Kill'),(3,'Game')
")
dbReadTable(conn,'idcard')
id name
1 love
2 Kill
3 Game
dbCreateTable giúp chúng ta tạo bảng một cách thuận tiện. Nó cần ba tham số; kết nối, tên bảng và hoặc là một vector ký tự hoặc một data.frame. Vector ký tự gồm tên (tên cột) và giá trị (kiểu). Trong trường hợp này, chúng ta sẽ cung cấp một data.frame population mặc định để tạo cấu trúc ban đầu.
dbCreateTable(conn,'population',population)
dbReadTable(conn,'population')
country year population
Sau đó, chúng ta sẽ dùng dbAppendTable để thêm giá trị vào bảng population.
dbAppendTable(conn,'population',head(population))
dbReadTable(conn,'population')
country year population
Afghanistan 1995 17586073
Afghanistan 1996 18415307
Afghanistan 1997 19021226
Afghanistan 1998 19496836
Afghanistan 1999 19987071
Afghanistan 2000 20595360
Chạy truy vấn
Chúng ta sẽ dùng dbGetQuery để thực hiện tất cả tác vụ phân tích dữ liệu. Hãy thử chạy một truy vấn đơn giản rồi tìm hiểu thêm các hàm khác.
dbGetQuery(conn,"SELECT * FROM idcard")
id name
1 love
2 Kill
3 Game
Bạn cũng có thể chạy truy vấn SQL phức tạp để lọc công suất động cơ và hiển thị số lượng hàng và cột giới hạn.
dbGetQuery(conn, "SELECT mpg,hp,gear\
FROM cars\
WHERE hp > 50\
LIMIT 5")
mpg hp gear
21.0 110 4
21.0 110 4
22.8 93 4
21.4 110 3
18.7 175 3
Để xóa bảng, dùng dbRemoveTable. Như ta thấy, chúng ta đã xóa thành công bảng idcard.
dbRemoveTable(conn,'idcard')
dbListTables(conn)
>>> 'cars''population'
Để hiểu thêm về bảng, chúng ta sẽ dùng dbListFields để hiển thị tên cột trong một bảng cụ thể.
dbListFields(conn, "cars")
>>> 'mpg''cyl''disp''hp''drat''wt''qsec''vs''am''gear''carb'
SQL với dplyr
Trong phần này, chúng ta sẽ dùng dplyr để đọc bảng rồi chạy truy vấn bằng filter, select và collect. Nếu bạn không muốn học cú pháp SQL và muốn thực hiện mọi tác vụ chỉ bằng R thuần, thì phương pháp này phù hợp. Chúng ta đã lấy bảng cars, lọc theo gears và mpg, rồi chọn ba cột như bên dưới.
cars_results <-
tbl(conn, "cars") %>%
filter(gear %in% c(4, 3),
mpg >= 14,
mpg <= 21) %>%
select(mpg, hp, gear) %>%
collect()
cars_results
mpg hp gear
21.0 110 4
21.0 110 4
18.7 175 3
18.1 105 3
14.3 245 3
... ... ...
Chúng ta có thể dùng dataframe đã lọc để hiển thị biểu đồ hộp bằng ggplot.
ggplot(cars_results,aes(fill=as.character(gear), y=mpg)) +
geom_boxplot()
Hoặc hiển thị biểu đồ điểm theo từng ô (facet) chia theo số gears.
ggplot(cars_results,
aes(mpg, ..count.. ) ) +
geom_point(stat = "count", size = 4) +
coord_flip()+
facet_grid( as.character(gear) ~ . )
Kết luận
Trong hướng dẫn này, chúng ta đã học tầm quan trọng của việc chạy truy vấn SQL với Python và R, tạo cơ sở dữ liệu, thêm bảng và thực hiện phân tích dữ liệu bằng truy vấn SQL. Chúng ta cũng đã học cách Pandas và dplyr giúp chạy truy vấn chỉ với một dòng mã.
SQL là kỹ năng bắt buộc phải học cho mọi công việc liên quan đến công nghệ. Nếu bạn bắt đầu sự nghiệp là nhà phân tích dữ liệu, chúng tôi khuyến nghị hoàn thành lộ trình nghề nghiệp Data Analyst với SQL Server trong vòng hai tháng. Lộ trình này sẽ dạy bạn mọi thứ về truy vấn SQL, máy chủ và quản lý tài nguyên.
Bạn có thể chạy miễn phí tất cả các script dùng trong bài hướng dẫn này:
- Dự án SQL với Python có trong sổ tay DataLab này.
- Dự án SQL với R có trong sổ tay DataLab này.
