Hướng dẫn PostgreSQL cho người mới bắt đầu

Khả năng truy vấn các hệ quản trị cơ sở dữ liệu quan hệ là kỹ năng bắt buộc đối với một nhà khoa học dữ liệu. SQL (Structured Query Language) cho phép bạn làm điều này một cách rất hiệu quả. SQL không chỉ giúp bạn đặt ra những câu hỏi có ý nghĩa với dữ liệu mà còn cho phép bạn thao tác dữ liệu theo nhiều cách khác nhau. Thiếu cơ sở dữ liệu thì hầu như không có ứng dụng thực tế nào khả thi. Vì vậy, kiến thức về cơ sở dữ liệu và khả năng xử lý chúng là những phần quan trọng trong bộ công cụ của một nhà khoa học dữ liệu.

Thông tin nhanh: SQL còn được gọi là SE-QU-EL. Điều này có ý nghĩa lịch sử - tên ban đầu của SQL là Simple English Query Language.

Nhìn chung, cơ sở dữ liệu quan hệ trông như sau -

Các quan hệ còn được gọi là bảng (table). Có nhiều cách để biểu diễn cơ sở dữ liệu. Đây chỉ là một trong số đó và cũng là cách phổ biến nhất.

Hướng dẫn này giới thiệu bốn thao tác phổ biến nhất được thực hiện với SQL: Tạo (Create), Đọc (Read), Cập nhật (Update) và Xóa (Delete). Gộp lại, bốn thao tác này thường được gọi là CRUD. Trong bất kỳ ứng dụng nào có tương tác với người dùng, bốn thao tác này luôn xuất hiện.

Bạn sẽ sử dụng PostgreSQL làm hệ quản trị cơ sở dữ liệu quan hệ. PostgreSQL rất nhẹ và cũng miễn phí. Trong hướng dẫn này, bạn sẽ -

• Bắt đầu và chạy PostgreSQL
• Kết nối đến một cơ sở dữ liệu PostgreSQL
• Tạo, đọc, cập nhật và xóa các bảng trong cơ sở dữ liệu đó
• Chạy SQL trên Jupyter Notebook
• Chạy SQL trong Python

Hãy bắt đầu.

Bắt đầu với PostgreSQL

PostgreSQL là một RDBMS mã nguồn mở và nhẹ. Nó được ngành công nghiệp chấp nhận rất rộng rãi. Bạn có thể tìm hiểu thêm về PostgreSQL trên trang web chính thức.

Để có thể bắt đầu viết và thực thi truy vấn trong PostgreSQL, bạn cần cài đặt nó trên máy của mình. Việc cài đặt vô cùng dễ dàng. Hai video ngắn sau cho thấy cách tải về và cài đặt PostgreSQL trên máy Windows 7 32-bit:

• Tải PostgreSQL
• Cài đặt PostgreSQL

Lưu ý: Trong khi cài đặt PostgreSQL, hãy ghi lại mật khẩu và số cổng mà bạn nhập.

Sau khi cài đặt PostgreSQL thành công trên máy, hãy mở pgAdmin. pgAdmin là một tiện ích hữu ích đi kèm với bản cài đặt PostgreSQL, cho phép bạn thực hiện các tác vụ liên quan đến cơ sở dữ liệu thông qua giao diện đồ họa đẹp mắt. Giao diện pgAdmin trông như sau -

Khi mở pgAdmin, bạn sẽ thấy một máy chủ có tên "PostgreSQL 9.4 (localhost:5432)" được liệt kê trong giao diện -

Lưu ý: Phiên bản của bạn có thể khác so với trên và vì thế số cổng (5432) cũng có thể khác.

Kết nối đến máy chủ bằng cách nhập mật khẩu mà bạn đã đặt trong quá trình cài đặt. Tham khảo - https://www.loom.com/share/71708f6a99ee483a977991e9e8a25808.

Sau khi kết nối thành công đến máy chủ cơ sở dữ liệu cục bộ, bạn sẽ có giao diện tương tự như sau -

Nhiệm vụ đầu tiên của bạn là tạo một cơ sở dữ liệu. Bạn có thể làm điều này bằng cách nhấp chuột phải vào thẻ Databases rồi chọn New Database từ menu thả xuống. Hãy tạo một cơ sở dữ liệu tên là DataCamp_Courses. Khi cơ sở dữ liệu đã được tạo, bạn có thể chuyển sang các phần tiếp theo của hướng dẫn.

Các thao tác CRUD trong PostgreSQL

Tạo một bảng theo một đặc tả cho trước -

Để có thể thao tác trên một cơ sở dữ liệu, bạn cần một bảng. Vậy hãy tạo một bảng đơn giản (còn gọi là quan hệ) tên là datacamp_courses với đặc tả (lược đồ) sau -

Đặc tả cung cấp cho chúng ta khá nhiều thông tin về các cột của bảng -

• Khóa chính của bảng phải là course_id (lưu ý chỉ mục này được in đậm) và kiểu dữ liệu là số nguyên. Khóa chính là một ràng buộc buộc các giá trị cột phải không rỗng và duy nhất. Nó cho phép bạn nhận diện duy nhất một bản ghi cụ thể hoặc một tập hợp bản ghi trong bảng.
• Phần thông tin còn lại trong đặc tả giờ đã dễ hiểu.

Để tạo một bảng, nhấp chuột phải vào cơ sở dữ liệu mới tạo DataCamp_Courses và chọn CREATE Script từ các tùy chọn. Bạn sẽ thấy tương tự như sau -

Hãy thực thi truy vấn sau -

CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);

Để thực thi truy vấn, chỉ cần chọn nó rồi nhấp nút thực thi trên thanh menu -

Kết quả sẽ là -

Cấu trúc chung của một truy vấn tạo bảng trong PostgreSQL như sau -

CREATE TABLE table_name (
 column_name TYPE column_constraint,
 table_constraint table_constraint
)

Chúng ta không chỉ định bất kỳ table_constraint nào khi tạo bảng. Hiện tại có thể bỏ qua điều đó. Mọi thứ còn lại khá dễ đọc ngoại trừ từ khóa SERIAL. Serial trong PostgreSQL cho phép bạn tạo một cột tự tăng. Mặc định, nó tạo các giá trị kiểu số nguyên. Serial giúp chúng ta không phải ghi nhớ khóa chính vừa chèn/cập nhật cuối cùng của một bảng, và việc dùng tự tăng cho khóa chính là một thực hành tốt. Bạn có thể tìm hiểu thêm về serial tại đây.

Chèn một vài bản ghi vào bảng vừa tạo -

Ở bước này, bạn sẽ chèn một số bản ghi vào bảng. Bản ghi của bạn nên bao gồm -

• Tên khóa học
• Tên giảng viên của khóa học
• Chủ đề khóa học

Các giá trị cho cột course_id sẽ do chính PostgreSQL xử lý. Cấu trúc chung của một truy vấn chèn trong PostgreSQL như sau -

INSERT INTO table(column1, column2, …)
VALUES
 (value1, value2, …);

Hãy chèn một vài bản ghi -

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');

Lưu ý rằng bạn không chỉ định khóa chính một cách tường minh. Bạn sẽ thấy tác dụng của điều này ngay.

Khi bạn thực thi hai truy vấn trên, bạn sẽ nhận được kết quả sau nếu chèn thành công -

Query returned successfully: one row affected, 11 ms execution time.

Đọc/xem dữ liệu từ bảng -

Đây có lẽ là việc bạn sẽ làm rất nhiều trong hành trình khoa học dữ liệu. Hiện tại, hãy xem bảng datacamp_courses như thế nào.

Việc này thường được gọi là truy vấn select, và cấu trúc chung của một truy vấn select như sau -

SELECT
 column_1,
 column_2,
 ...
FROM
 table_name;

Hãy chọn tất cả các cột từ bảng datacamp_courses

SELECT * FROM datacamp_courses;

Và bạn nhận được -

Lưu ý các khóa chính lúc này. Nếu bạn chỉ muốn xem tên các khóa học, bạn có thể làm như sau -

SELECT course_name from datacamp_courses;

Và bạn nhận được -

Bạn có thể chỉ định bao nhiêu tên cột tùy ý muốn xem trong kết quả, miễn là chúng tồn tại trong bảng. Nếu bạn chạy select course_name, number_particpants from datacamp_courses; bạn sẽ gặp lỗi vì cột number_particpants không tồn tại trong bảng. Bây giờ bạn sẽ thấy cách cập nhật một bản ghi cụ thể trong bảng.

Cập nhật một bản ghi trong bảng -

Cấu trúc chung của một truy vấn cập nhật trong SQL như sau:

UPDATE table
SET column1 = value1,
    column2 = value2 ,...
WHERE
 condition;

Bạn sẽ cập nhật bản ghi nơi course_instructor = "Chester Ismay" và đặt course_name thành "Joining Data in SQL". Sau đó bạn sẽ kiểm tra xem bản ghi đã được cập nhật chưa. Truy vấn để làm việc này như sau -

UPDATE datacamp_courses SET course_name = 'Joining Data in SQL'
WHERE course_instructor = 'Chester Ismay';

Hãy xem truy vấn cập nhật có hiệu lực như mong muốn không bằng cách chạy một truy vấn select -

Bạn có thể thấy truy vấn cập nhật đã thực hiện đúng như mong muốn. Tiếp theo, bạn sẽ xem cách xóa một bản ghi khỏi bảng.

Xóa một bản ghi trong bảng -

Cấu trúc chung của một truy vấn xóa trong SQL như sau:

DELETE FROM table
WHERE condition;

Bạn sẽ xóa bản ghi nơi course_name = "Deep Learning in Python" rồi kiểm tra xem bản ghi đã bị xóa hay chưa. Theo cấu trúc trên, truy vấn sau có thể làm việc này -

DELETE from datacamp_courses
WHERE course_name = 'Deep Learning in Python';

Hãy nhớ rằng các từ khóa trong SQL không phân biệt chữ hoa chữ thường, nhưng dữ liệu thì có phân biệt. Đó là lý do bạn thấy lẫn lộn chữ hoa và chữ thường trong các truy vấn.

Hãy xem bản ghi dự định có bị xóa khỏi bảng hay không -

Vâng, đúng là bản ghi dự định đã bị xóa.

Các cấu trúc chung của truy vấn được đề cập trong hướng dẫn tham khảo từ PostgreSQL Tutorial.

Giờ bạn đã biết cách thực hiện các truy vấn CRUD cơ bản trong SQL. Một số bạn có thể dùng Jupyter Notebook nhiều và nghĩ rằng sẽ thật tuyệt nếu có thể thực thi các truy vấn này trực tiếp từ Jupyter Notebook. Ở phần tiếp theo, bạn sẽ thấy cách thực hiện điều đó.

SQL + Jupyter Notebooks

Để có thể chạy truy vấn SQL từ Jupyter Notebook, bước đầu tiên là cài gói ipython-sql.

Nếu chưa cài, hãy cài bằng:

pip install ipython-sql

Sau khi xong, nạp tiện ích mở rộng sql trong Jupyter Notebook bằng cách thực thi -

%load_ext sql

Bước tiếp theo là kết nối đến một cơ sở dữ liệu PostgreSQL. Bạn sẽ kết nối đến cơ sở dữ liệu đã tạo - DataCamp_Courses.

Để kết nối đến một cơ sở dữ liệu đã có trong hệ thống, bạn sẽ cần hướng dẫn Python nhận biết kiểu ngôn ngữ truy vấn của nó. Nói đơn giản, bạn phải cho Python biết đây là cơ sở dữ liệu PostgreSQL. Vì vậy, bạn cần psycopg2, có thể cài bằng:

pip install psycopg2

Sau khi cài psycopg, kết nối đến cơ sở dữ liệu bằng -

%sql postgresql://postgres:postgres@localhost:5432/DataCamp_Courses

'Connected: postgres@DataCamp_Courses'

Lưu ý cách dùng %sql. Đây là một magic command. Nó cho phép bạn thực thi các câu lệnh SQL từ Jupyter Notebook. Phần theo sau %sql được gọi là URL kết nối cơ sở dữ liệu, nơi bạn chỉ định -

• Kiểu ngôn ngữ (postgres)
• Tên người dùng (postgres)
• Mật khẩu (postgres)
• Địa chỉ máy chủ (localhost)
• Cổng (5432)
• Tên cơ sở dữ liệu (DaaCamp_Courses)

Bây giờ bạn có thể thực hiện mọi thứ từ Jupyter Notebook như đã làm trong giao diện pgAdmin. Hãy bắt đầu bằng việc tạo bảng _datacampcourses với cùng lược đồ.

Nhưng trước đó, bạn sẽ phải drop bảng vì SQL không cho phép lưu hai bảng cùng tên. Bạn có thể drop một bảng bằng -

%sql DROP table datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] 

Bảng _datacampcourses hiện đã bị xóa khỏi PostgreSQL, do đó bạn có thể tạo một bảng mới với tên này.

%%sql
CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] 

Lưu ý cách dùng %sql và %%sql ở đây. Để thực thi một dòng truy vấn, bạn dùng %sql, còn nếu muốn thực thi nhiều truy vấn một lúc, bạn phải dùng %%sql.

Hãy chèn một vài bản ghi -

%%sql
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
1 rows affected.





[] 

Xem bảng để đảm bảo việc chèn đã diễn ra như mong đợi -

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. 

Hãy tiếp tục mạch nội dung. Bước tiếp theo, bạn sẽ cập nhật một bản ghi trong bảng -

%sql update datacamp_courses set course_name = 'Joining Data in SQL' where course_instructor = 'Chester Ismay';

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] 

Hãy chú ý khi làm việc với chuỗi trong SQL. Khác với các ngôn ngữ lập trình truyền thống, giá trị chuỗi cần được đặt trong dấu nháy đơn.

Bây giờ hãy kiểm tra xem truy vấn cập nhật đã có hiệu lực như mong muốn chưa -

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. 

Bây giờ hãy xóa một bản ghi và kiểm tra -

%%sql
delete from datacamp_courses where course_name = 'Deep Learning in Python';

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] 

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. 

Đến đây bạn đã có cái nhìn rõ ràng về cách thực thi các thao tác CRUD trong PostgreSQL và cách thực hiện chúng qua Jupyter Notebook. Nếu bạn quen với Python và muốn truy cập cơ sở dữ liệu thông qua mã Python, bạn cũng có thể làm được. Phần tiếp theo sẽ nói về điều đó.

Bắt đầu với SQLAlchemy và kết hợp với các lệnh SQL Magic

Ở phần này, bạn sẽ cần gói SQLAlchemy. Thông thường nó có sẵn trong bản phân phối Anaconda. Bạn cũng có thể cài bằng pip. Khi đã cài đặt, bạn có thể import như sau -

import sqlalchemy

Để có thể tương tác với cơ sở dữ liệu bằng SQLAlchemy, bạn cần tạo một engine cho RDBMS nơi cơ sở dữ liệu được lưu trữ. Trong trường hợp của bạn là PostgreSQL. SQLAlchemy cho phép tạo một engine của RDBMS chỉ với một lần gọi create_engine(), và phương thức này nhận một URL kết nối cơ sở dữ liệu mà bạn đã thấy trước đó.

from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/DataCamp_Courses')
print(engine.table_names()) # Cho phép bạn xem tên các bảng có trong cơ sở dữ liệu

['datacamp_courses']

Bạn có thể thấy bảng tên _datacampcourses, điều này càng xác nhận rằng bạn đã tạo engine thành công. Hãy thực thi một truy vấn select đơn giản để xem các bản ghi của bảng _datacampcourses và lưu vào một đối tượng DataFrame của pandas.

Bạn sẽ dùng phương thức read_sql() (do pandas cung cấp) nhận một chuỗi truy vấn SQL và một engine.

import pandas as pd

df = pd.read_sql('select * from datacamp_courses', engine)
df.head()

Bạn cũng có thể kết hợp lệnh magic %sql trong mã Python của mình.

df_new = %sql select * from datacamp_courses
df_new.DataFrame().head()

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. 

Bạn đã đi được một chặng dài!

Chúc mừng và hãy tự thưởng cho mình! Trong hướng dẫn này, bạn không chỉ bắt đầu với PostgreSQL mà còn thấy cách thực thi các truy vấn SQL theo nhiều cách khác nhau. Bước tiếp theo, bạn nên xem webinar này của David Robinson (Chief Data Scientist tại DataCamp), nơi anh ấy chia sẻ cách sử dụng SQL trong công việc hằng ngày. Xem thêm hướng dẫn Cách cài đặt PostgreSQL trên Windows và Mac OS X. Nếu bạn muốn nâng cao kỹ năng SQL, các tài nguyên sau là điểm khởi đầu rất tốt -

• Nhập môn SQL cho Khoa học dữ liệu
• SQL của Kaggle Learn
• Joining Data in SQL