Quản lý cơ sở dữ liệu PostgreSQL trong Python với psycopg2

Tạo bảng trong PostgreSQL

Đã đến lúc tạo bảng đầu tiên trong cơ sở dữ liệu datacamp_courses. Chúng ta muốn tạo một bảng chứa thông tin về một số khóa học trong danh mục khóa học của DataCamp. Bảng có lược đồ như sau: 

Bản đặc tả cung cấp khá nhiều thông tin về các cột của bảng. Khóa chính của bảng nên là course_id, và kiểu dữ liệu của nó là số nguyên. Khóa chính là một ràng buộc đảm bảo các giá trị trong cột không rỗng và duy nhất. Nó cho phép bạn định danh duy nhất một bản ghi cụ thể hoặc một tập bản ghi trong bảng. 

Các cột còn lại cung cấp thông tin về tên khóa học, tên giảng viên và chủ đề của khóa học.

Trước khi tạo bảng, điều quan trọng là giải thích cách hoạt động của đối tượng kết nối bạn vừa tạo. Về bản chất, kết nối bao bọc một phiên làm việc với cơ sở dữ liệu và cho phép bạn thực thi các lệnh và truy vấn SQL như SELECT, INSERT, CREATE, UPDATE hoặc DELETE bằng phương thức cursor(), và ghi nhận thay đổi vĩnh viễn bằng phương thức commit() .

Sau khi tạo đối tượng con trỏ, bạn có thể gửi lệnh tới cơ sở dữ liệu bằng phương thức execute() và truy xuất dữ liệu từ bảng bằng fetchone(), fetchmany() hoặc fetchall().

Cuối cùng, điều quan trọng là đóng con trỏ và kết nối tới cơ sở dữ liệu khi bạn đã hoàn tất thao tác. Nếu không, chúng sẽ tiếp tục giữ tài nguyên phía máy chủ. Để làm vậy, bạn có thể dùng phương thức close() .

Bên dưới là mã để tạo bảng datacamp_courses:

# Open a cursor to perform database operations
cur = conn.cursor()
# Execute a command: create datacamp_courses table
cur.execute("""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);
            """)
# Make the changes to the database persistent
conn.commit()
# Close cursor and communication with the database
cur.close()
conn.close()

Đây là ví dụ rất cơ bản về cách tạo bảng trên PostgreSQL, nhưng mọi thứ có thể phức tạp hơn nhiều. Nếu bạn muốn học thêm cách tạo cơ sở dữ liệu PostgreSQL và khám phá cấu trúc, kiểu dữ liệu, cũng như cách chuẩn hóa cơ sở dữ liệu, hãy xem khóa học Creating PostgreSQL Database của chúng tôi.

Thực thi các truy vấn PostgreSQL cơ bản trong Python

Bảng datacamp_courses đã sẵn sàng; bây giờ là lúc dùng SQL để thực hiện vài truy vấn cơ bản!

INSERT 

Bạn có thể nhận thấy rằng bảng hiện chưa có giá trị nào. Để tạo bản ghi trong bảng datacamp_courses, chúng ta cần lệnh INSERT.

cur = conn.cursor()

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to SQL','Izzy Weber','Julia')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Analyzing Survey Data in Python','EbunOluwa Andrew','Python')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to ChatGPT','James Chapman','Theory')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to Statistics in R','Maggie Matsui','R')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Hypothesis Testing in Python','James Chapman','Python')");

conn.commit()
cur.close()
conn.close()

Bảng thu được sẽ trông như sau:

SELECT 

Đọc dữ liệu trong cơ sở dữ liệu SQL 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. Việc này thường được gọi là truy vấn SELECT. Bây giờ, hãy xem bảng datacamp_courses trông như thế nào.

Chúng ta sẽ gọi câu lệnh kinh điển SELECT * FROM database_name để đọc tất cả dữ liệu có trong bảng. Sau đó, dùng phương thức fetchall() để lấy tất cả các hàng. Lưu ý PostgreSQL tự động tạo chỉ mục số cho cột course_id. 

cur = conn.cursor()
cur.execute('SELECT * FROM datacamp_courses;')
rows = cur.fetchall()
conn.commit()
conn.close()
for row in rows:
    print(row)

(1, 'Introduction to SQL', 'Izzy Weber', 'Julia')
(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Introduction to Statistics in R', 'Maggie Matsui', 'R')
(5, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

UPDATE 

Dữ liệu thường có lỗi. Bạn có thể đã nhận thấy ở phần trước rằng chủ đề gắn với khóa học “Introduction to SQL” là Julia. Sau khi kiểm tra thông tin khóa học, chúng ta phát hiện sai sót. Cần thay đổi thành “SQL”. Có thể thực hiện bằng lệnh UPDATE như sau:

cur = conn.cursor()
cur.execute("UPDATE datacamp_courses SET topic = 'SQL' WHERE course_name = 'Introduction to SQL';")
conn.commit()
conn.close()

DELETE 

Cuối cùng, bạn có thể muốn xóa một bản ghi khỏi bảng. Ví dụ, hãy xóa khóa học Introduction to Statistics in R:

cur = conn.cursor()
cur.execute("""DELETE from datacamp_courses WHERE course_name = 'Introduction to Statistics in R'""");
conn.commit()
cur.close()

Các truy vấn PostgreSQL nâng cao trong Python

Ở phần trước, chúng ta đã xem các truy vấn SQL cơ bản nhất. Nhưng còn nhiều điều để học. Hãy xem một số truy vấn nâng cao hơn.

ORDER BY

Giả sử bạn muốn sắp xếp cơ sở dữ liệu theo tên giảng viên. Bạn có thể dùng câu lệnh ORDER BY:

cur = conn.cursor()

cur.execute('SELECT * FROM datacamp_courses ORDER BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(1, 'Introduction to SQL', 'Izzy Weber', 'SQL')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

GROUP BY

Bạn có thể muốn thực hiện một số hàm tổng hợp trong các nhóm dữ liệu khác nhau. Ví dụ, bạn có thể quan tâm đến việc tính số lượng khóa học theo giảng viên. Bạn có thể làm điều này với GROUP BY.

cur = conn.cursor()
cur.execute('SELECT course_instructor, COUNT(*) FROM datacamp_courses GROUP BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

('James Chapman', 2)
('Izzy Weber', 1)
('EbunOluwa Andrew', 1)

JOIN

Đến thời điểm này, chúng ta mới chỉ làm việc với bảng datacamp_course. Tuy nhiên, bạn chỉ thực sự khai thác hết tiềm năng của cơ sở dữ liệu quan hệ như PostgreSQL khi làm việc với nhiều bảng cùng lúc. 

Công cụ “ma thuật” để kết hợp nhiều bảng là phép JOIN. Hãy tưởng tượng chúng ta có một bảng thứ hai trong cơ sở dữ liệu tên là programming_languages chứa thông tin cơ bản về các ngôn ngữ lập trình hàng đầu cho khoa học dữ liệu, gồm tên, vị trí trong Chỉ số TIOBE và số lượng khóa học về ngôn ngữ đó trên Datacamp. Bảng trông như sau:

Giả sử bạn muốn gộp hai bảng để lấy thông tin khóa học cùng với vị trí của ngôn ngữ trên chỉ số TIOBE. Chúng ta sẽ dùng INNER JOIN để chỉ lấy thông tin các ngôn ngữ lập trình xuất hiện trong bảng datacamp_course. 

cur = conn.cursor()
cur.execute("""SELECT course_name, course_instructor, topic, tiobe_ranking
FROM datacamp_courses
INNER JOIN programming_languages
ON datacamp_courses.topic = programming_languages.language_name""")
rows = cur.fetchall()
for row in rows:
    print(row)

('Introduction to SQL', 'Izzy Weber', 'SQL', 8)
('Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python', 1)
('Hypothesis Testing in Python', 'James Chapman', 'Python', 1)

Đây chỉ là một ví dụ, nhưng tiềm năng của các phép nối (join) trong SQL là vô tận, khiến đây trở thành chủ đề bắt buộc phải biết. Khóa học, hướng dẫn và cheatsheet về join của chúng tôi sẽ giúp bạn bắt đầu.

Bắt đầu với SQLAlchemy

Psycopg2 là công cụ tuyệt vời để kết nối với cơ sở dữ liệu PostgreSQL. Nhưng nếu bạn phải làm việc với các cơ sở dữ liệu được lưu trên những hệ quản trị khác, như Oracle và MySQL thì sao? Trong những tình huống này, có thể tốt hơn khi dùng công cụ có thể kết nối với nhiều loại cơ sở dữ liệu SQL khác nhau. SQLAlchemy là một lựa chọn tuyệt vời. 

Nói đơn giản, SQLAlchemy cho phép người dùng kết nối cơ sở dữ liệu bằng ngôn ngữ Python, chạy truy vấn SQL theo kiểu lập trình hướng đối tượng và tối ưu quy trình làm việc. 

Mặc dù cú pháp khác so với psycopg2, bạn hầu như có thể làm được những việc tương tự. Dưới đây là ví dụ tạo engine để kết nối tới tập dữ liệu datacamp_courses của chúng ta và đọc toàn bộ dữ liệu có trong bảng datacap_course

import sqlalchemy as db


engine = db.create_engine("postgresql://datacamp@localhost:5432/datacamp_courses")
conn = engine.connect() 
output = conn.execute("SELECT * FROM datacamp_courses")
print(output.fetchall())
conn.close()

[(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory'),
 (5, 'Hypothesis Testing in Python', 'James Chapman', 'Python'),
 (2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python'),
 (1, 'Introduction to SQL', 'Izzy Weber', 'SQL')]

SQLAlchemy cũng là một công cụ đáng để học. Để có cái nhìn tổng quan về cách công cụ hoạt động, hãy xem SQLALchemy Tutorial của chúng tôi.  

Sử dụng PostgreSQL trong DataCamp DataLab

Tại DataCamp, chúng tôi nỗ lực để khiến khoa học dữ liệu trở nên dễ dàng và tiếp cận được với mọi người. Đó là lý do ra đời DataCamp DataLab, một sổ tay trên nền đám mây được thiết kế riêng cho khoa học dữ liệu. Bạn có thể xem DataCamp DataLab như sự kết hợp giữa Jupyter Notebook và Google Docs, cho phép người dùng viết mã, phân tích dữ liệu cá nhân hoặc theo nhóm, và chia sẻ insight dữ liệu.

Kết nối DataLab với cơ sở dữ liệu PostgreSQL, cũng như các cơ sở dữ liệu SQL khác, cực kỳ dễ dàng. DataLab cho phép bạn kết nối tới nguồn dữ liệu một cách an toàn. Đồng thời, bạn có thể tiếp tục phân tích dữ liệu liền mạch bằng Python hoặc R. Bạn chỉ cần tạo một integration thông qua quy trình không cần viết mã, trong đó bạn sẽ nhập thông tin xác thực kết nối.

Cuối cùng, nếu bạn muốn thử tích hợp nhưng không có cơ sở dữ liệu PostgreSQL để kết nối, bạn có thể dùng một trong các cơ sở dữ liệu PostgreSQL mẫu của chúng tôi!

Quan tâm đến DataCamp DataLab? Xem bài viết này để tìm hiểu thêm về sổ tay thế hệ mới dành cho khoa học dữ liệu.

Kết luận

Tuyệt vời! Trong hướng dẫn này, bạn đã làm quen với PostgreSQL và học cách quản lý cơ sở dữ liệu ngay trong Python bằng gói psycopg2. Nhưng đây chỉ là bước khởi đầu. SQL là một trong những ngôn ngữ lập trình được dùng rộng rãi nhất trong khoa học dữ liệu. Nó cũng khá dễ học, nên không có lý do để trì hoãn.  

May mắn là Datacamp có đủ tài nguyên cho bạn. Dưới đây là một số nguồn hữu ích giúp bạn thành thạo PostgreSQL nói riêng và SQL nói chung:

• Beginner Introductions to PostgreSQL Tutorial
• Creating PostgreSQL Databases Course
• Improving Query Performance in PostgreSQL Course
• Managing Databases in SQL Course
• Functions for Manipulating Data in PostgreSQL Course