Python PostgreSQL - Hướng dẫn nhanh

PostgreSQL là một hệ thống cơ sở dữ liệu quan hệ đối tượng mã nguồn mở, mạnh mẽ. Nó có hơn 15 năm trong giai đoạn phát triển tích cực và một kiến ​​trúc đã được chứng minh đã mang lại cho nó một danh tiếng mạnh mẽ về độ tin cậy, tính toàn vẹn của dữ liệu và tính đúng đắn.

Để giao tiếp với PostgreSQL bằng Python, bạn cần cài đặt psycopg, một bộ điều hợp được cung cấp cho lập trình python, phiên bản hiện tại của cái này là psycog2.

psycopg2 được viết với mục đích rất nhỏ và nhanh, ổn định như một tảng đá. Nó có sẵn trong PIP (trình quản lý gói của python)

Cài đặt Psycog2 bằng PIP

Trước hết, hãy đảm bảo rằng python và PIP được cài đặt đúng cách trong hệ thống của bạn và PIP được cập nhật.

Để nâng cấp PIP, hãy mở dấu nhắc lệnh và thực hiện lệnh sau:

C:\Users\Tutorialspoint>python -m pip install --upgrade pip
Collecting pip
   Using cached 
https://files.pythonhosted.org/packages/8d/07/f7d7ced2f97ca3098c16565efbe6b15fafcba53e8d9bdb431e09140514b0/pip-19.2.2-py2.py3-none-any.whl
Installing collected packages: pip
   Found existing installation: pip 19.0.3
      Uninstalling pip-19.0.3:
         Successfully uninstalled pip-19.0.3
Successfully installed pip-19.2.2

Sau đó, mở dấu nhắc lệnh ở chế độ quản trị và thực hiện pip install psycopg2-binary lệnh như hình dưới đây -

C:\WINDOWS\system32>pip install psycopg2-binary
Collecting psycopg2-binary
   Using cached 
https://files.pythonhosted.org/packages/80/79/d0d13ce4c2f1addf4786f4a2ded802c2df66ddf3c1b1a982ed8d4cb9fc6d/psycopg2_binary-2.8.3-cp37-cp37m-win32.whl
Installing collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.8.3

xác minh

Để xác minh cài đặt, hãy tạo một tập lệnh python mẫu với dòng sau trong đó.

import mysql.connector

Nếu cài đặt thành công, khi bạn thực thi nó, bạn sẽ không gặp bất kỳ lỗi nào -

D:\Python_PostgreSQL>import psycopg2
D:\Python_PostgreSQL>

PostgreSQL cung cấp trình bao của riêng nó để thực thi các truy vấn. Để thiết lập kết nối với cơ sở dữ liệu PostgreSQL, hãy đảm bảo rằng bạn đã cài đặt nó đúng cách trong hệ thống của mình. Mở lời nhắc trình bao PostgreSQL và chuyển các chi tiết như Máy chủ, Cơ sở dữ liệu, tên người dùng và mật khẩu. Nếu tất cả các chi tiết bạn đã đưa ra là phù hợp, kết nối sẽ được thiết lập với cơ sở dữ liệu PostgreSQL.

Trong khi chuyển các chi tiết, bạn có thể đi với máy chủ, cơ sở dữ liệu, cổng và tên người dùng mặc định do shell đề xuất.

Thiết lập kết nối bằng Python

Lớp kết nối của psycopg2đại diện / xử lý một thể hiện của kết nối. Bạn có thể tạo kết nối mới bằng cách sử dụngconnect()chức năng. Điều này chấp nhận các tham số kết nối cơ bản như dbname, user, password, host, port và trả về một đối tượng kết nối. Sử dụng chức năng này, bạn có thể thiết lập kết nối với PostgreSQL.

Thí dụ

Đoạn mã Python sau đây cho thấy cách kết nối với cơ sở dữ liệu hiện có. Nếu cơ sở dữ liệu không tồn tại, thì nó sẽ được tạo và cuối cùng một đối tượng cơ sở dữ liệu sẽ được trả về. Tên của cơ sở dữ liệu mặc định của PostgreSQL là postrgre. Do đó, chúng tôi đang cung cấp nó làm tên cơ sở dữ liệu.

import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="postgres", user='postgres', password='password', 
   host='127.0.0.1', port= '5432'
)

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Executing an MYSQL function using the execute() method
cursor.execute("select version()")

#Fetch a single row using fetchone() method.
data = cursor.fetchone()
print("Connection established to: ",data)

#Closing the connection
conn.close()
Connection established to: (
   'PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit',
)

Đầu ra

Connection established to: (
   'PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit',
)

Bạn có thể tạo cơ sở dữ liệu trong PostgreSQL bằng cách sử dụng câu lệnh CREATE DATABASE. Bạn có thể thực hiện câu lệnh này trong dấu nhắc trình bao PostgreSQL bằng cách chỉ định tên của cơ sở dữ liệu sẽ được tạo sau lệnh.

Cú pháp

Sau đây là cú pháp của câu lệnh CREATE DATABASE.

CREATE DATABASE dbname;

Thí dụ

Câu lệnh sau tạo một cơ sở dữ liệu có tên testdb trong PostgreSQL.

postgres=# CREATE DATABASE testdb;
CREATE DATABASE

Bạn có thể liệt kê cơ sở dữ liệu trong PostgreSQL bằng lệnh \ l. Nếu bạn xác minh danh sách cơ sở dữ liệu, bạn có thể tìm thấy cơ sở dữ liệu mới được tạo như sau:

postgres=# \l
                                                List of databases
   Name    | Owner    | Encoding |        Collate             |     Ctype   |
-----------+----------+----------+----------------------------+-------------+
mydb       | postgres | UTF8     | English_United States.1252 | ........... |
postgres   | postgres | UTF8     | English_United States.1252 | ........... |
template0  | postgres | UTF8     | English_United States.1252 | ........... |
template1  | postgres | UTF8     | English_United States.1252 | ........... |
testdb     | postgres | UTF8     | English_United States.1252 | ........... |
(5 rows)

Bạn cũng có thể tạo cơ sở dữ liệu trong PostgreSQL từ dấu nhắc lệnh bằng cách sử dụng lệnh createb , một trình bao bọc xung quanh câu lệnh SQL CREATE DATABASE.

C:\Program Files\PostgreSQL\11\bin> createdb -h localhost -p 5432 -U postgres sampledb
Password:

Tạo cơ sở dữ liệu bằng Python

Lớp con trỏ của psycopg2 cung cấp nhiều phương thức thực thi các lệnh PostgreSQL khác nhau, tìm nạp các bản ghi và sao chép dữ liệu. Bạn có thể tạo một đối tượng con trỏ bằng cách sử dụng phương thức con trỏ () của lớp Kết nối.

Phương thức execute () của lớp này chấp nhận một truy vấn PostgreSQL làm tham số và thực thi nó.

Do đó, để tạo cơ sở dữ liệu trong PostgreSQL, hãy thực hiện truy vấn TẠO CƠ SỞ DỮ LIỆU bằng phương pháp này.

Thí dụ

Ví dụ về python sau tạo một cơ sở dữ liệu có tên mydb trong cơ sở dữ liệu PostgreSQL.

import psycopg2

#establishing the connection

conn = psycopg2.connect(
   database="postgres", user='postgres', password='password', 
   host='127.0.0.1', port= '5432'
)
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Preparing query to create a database
sql = '''CREATE database mydb''';

#Creating a database
cursor.execute(sql)
print("Database created successfully........")

#Closing the connection
conn.close()

Đầu ra

Database created successfully........

Bạn có thể tạo một bảng mới trong cơ sở dữ liệu trong PostgreSQL bằng cách sử dụng câu lệnh CREATE TABLE. Trong khi thực hiện điều này, bạn cần chỉ định tên bảng, tên cột và kiểu dữ liệu của chúng.

Cú pháp

Sau đây là cú pháp của câu lệnh CREATE TABLE trong PostgreSQL.

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

Thí dụ

Ví dụ sau tạo một bảng với tên CRICKETERS trong PostgreSQL.

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age INT,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255));
CREATE TABLE
postgres=#

Bạn có thể lấy danh sách các bảng trong cơ sở dữ liệu trong PostgreSQL bằng lệnh \ dt. Sau khi tạo bảng, nếu bạn có thể xác minh danh sách các bảng, bạn có thể quan sát bảng mới tạo trong đó như sau:

postgres=# \dt
         List of relations
Schema  | Name       | Type  | Owner
--------+------------+-------+----------
public  | cricketers | table | postgres
(1 row)
postgres=#

Theo cách tương tự, bạn có thể lấy mô tả của bảng đã tạo bằng cách sử dụng \ d như hình dưới đây -

postgres=# \d cricketers
                        Table "public.cricketers"
Column          | Type                   | Collation | Nullable | Default
----------------+------------------------+-----------+----------+---------
first_name      | character varying(255) |           |          |
last_name       | character varying(255) |           |          |
age             | integer                |           |          |
place_of_birth  | character varying(255) |           |          |
country         | character varying(255) |           |          |

postgres=#

Tạo bảng bằng Python

Để tạo bảng bằng python, bạn cần thực hiện câu lệnh CREATE TABLE bằng cách sử dụng phương thức execute () của Con trỏ của pyscopg2 .

Thí dụ

Ví dụ Python sau đây tạo một bảng với tên nhân viên.

import psycopg2

#Establishing the connection

conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#Creating table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT)'''
cursor.execute(sql)
print("Table created successfully........")

#Closing the connection
conn.close()

Đầu ra

Table created successfully........

Bạn có thể chèn bản ghi vào một bảng hiện có trong PostgreSQL bằng cách sử dụng INSERT INTOtuyên bố. Trong khi thực hiện điều này, bạn cần chỉ định tên của bảng và giá trị cho các cột trong đó.

Cú pháp

Sau đây là cú pháp được khuyến nghị của câu lệnh INSERT:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

Trong đó, column1, column2, column3, .. là tên của các cột trong bảng và value1, value2, value3, ... là các giá trị bạn cần chèn vào bảng.

Thí dụ

Giả sử chúng ta đã tạo một bảng với tên CRICKETERS bằng cách sử dụng câu lệnh CREATE TABLE như hình dưới đây -

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age INT,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
CREATE TABLE
postgres=#

Sau câu lệnh PostgreSQL chèn một hàng trong bảng đã tạo ở trên:

postgres=# insert into CRICKETERS 
   (First_Name, Last_Name, Age, Place_Of_Birth, Country) values
   ('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=#

Trong khi chèn bản ghi bằng câu lệnh INSERT INTO, nếu bạn bỏ qua bất kỳ cột nào, tên cột Bản ghi sẽ được chèn để lại khoảng trống tại các cột mà bạn đã bỏ qua.

postgres=# insert into CRICKETERS 
   (First_Name, Last_Name, Country) values('Jonathan', 'Trott', 'SouthAfrica');
INSERT 0 1

Bạn cũng có thể chèn bản ghi vào bảng mà không cần chỉ định tên cột, nếu thứ tự của các giá trị bạn chuyển giống với tên cột tương ứng của chúng trong bảng.

postgres=# insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1
postgres=#

Sau khi chèn các bản ghi vào một bảng, bạn có thể xác minh nội dung của nó bằng cách sử dụng câu lệnh SELECT như hình dưới đây:

postgres=# SELECT * from CRICKETERS;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      |     |                | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
Virat       | Kohli      | 30  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
(5 rows)

Chèn dữ liệu bằng Python

Lớp con trỏ của psycopg2 cung cấp một phương thức có tên là phương thức execute (). Phương thức này chấp nhận truy vấn như một tham số và thực thi nó.

Do đó, để chèn dữ liệu vào bảng trong PostgreSQL bằng python -

• Nhập khẩu psycopg2 gói hàng.

• Tạo một đối tượng kết nối bằng cách sử dụng connect() bằng cách chuyển tên người dùng, mật khẩu, máy chủ (mặc định tùy chọn: localhost) và, cơ sở dữ liệu (tùy chọn) dưới dạng tham số cho nó.

• Tắt chế độ cam kết tự động bằng cách đặt giá trị false thành thuộc tính autocommit.

• Các cursor() phương pháp của Connectionlớp của thư viện psycopg2 trả về một đối tượng con trỏ. Tạo một đối tượng con trỏ bằng phương pháp này ..

• Sau đó, thực thi (các) câu lệnh INSERT bằng cách chuyển nó / chúng làm tham số cho phương thức execute ().

Thí dụ

Chương trình Python sau tạo một bảng với tên EMPLOYEE trong cơ sở dữ liệu PostgreSQL và chèn các bản ghi vào đó bằng phương thức execute () -

import psycopg2

#Establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

# Preparing SQL queries to INSERT a record into the database.
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Ramya', 'Rama priya', 27, 'F', 9000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Vinay', 'Battacharya', 20, 'M', 6000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Sharukh', 'Sheik', 25, 'M', 8300)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Sarmista', 'Sharma', 26, 'F', 10000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES ('Tripthi', 'Mishra', 24, 'F', 6000)''')

# Commit your changes in the database
conn.commit()

print("Records inserted........")

# Closing the connection
conn.close()

Đầu ra

Records inserted........

Bạn có thể truy xuất nội dung của một bảng hiện có trong PostgreSQL bằng cách sử dụng câu lệnh SELECT. Tại câu lệnh này, bạn cần chỉ định tên của bảng và nó trả về nội dung của nó ở định dạng bảng được gọi là tập kết quả.

Cú pháp

Sau đây là cú pháp của câu lệnh SELECT trong PostgreSQL:

SELECT column1, column2, columnN FROM table_name;

Thí dụ

Giả sử chúng ta đã tạo một bảng có tên CRICKETERS bằng truy vấn sau:

postgres=# CREATE TABLE CRICKETERS ( 
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, 
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);

CREATE TABLE

postgres=#

Và nếu chúng ta đã chèn 5 bản ghi vào nó bằng cách sử dụng câu lệnh INSERT như -

postgres=# insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
INSERT 0 1
postgres=# insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1

Sau khi truy vấn SELECT truy xuất giá trị của các cột FIRST_NAME, LAST_NAME và COUNTRY từ bảng CRICKETERS.

postgres=# SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
 first_name | last_name  | country
------------+------------+-------------
Shikhar     | Dhawan     | India
Jonathan    | Trott      | SouthAfrica
Kumara      | Sangakkara | Srilanka
Virat       | Kohli      | India
Rohit       | Sharma     | India
(5 rows)

Nếu bạn muốn truy xuất tất cả các cột của mỗi bản ghi, bạn cần thay thế tên của các cột bằng "⚹" như hình dưới đây -

postgres=# SELECT * FROM CRICKETERS;
first_name  | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
Virat       | Kohli      | 30  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
(5 rows)

postgres=#

Truy xuất dữ liệu bằng Python

ĐỌC Hoạt động trên bất kỳ cơ sở dữ liệu nào có nghĩa là lấy một số thông tin hữu ích từ cơ sở dữ liệu. Bạn có thể tìm nạp dữ liệu từ PostgreSQL bằng phương thức fetch () được cung cấp bởi psycopg2.

Lớp Cursor cung cấp ba phương thức cụ thể là fetchall (), fetchmany () và, fetchone () trong đó,

• Phương thức fetchall () truy xuất tất cả các hàng trong tập kết quả của một truy vấn và trả về chúng dưới dạng danh sách các bộ giá trị. (Nếu chúng ta thực hiện điều này sau khi lấy một vài hàng, nó sẽ trả về những hàng còn lại).

• Phương thức fetchone () tìm nạp hàng tiếp theo trong kết quả của một truy vấn và trả về nó dưới dạng một bộ giá trị.

Note - Tập kết quả là một đối tượng được trả về khi một đối tượng con trỏ được sử dụng để truy vấn một bảng.

Thí dụ

Chương trình Python sau kết nối với cơ sở dữ liệu có tên mydb của PostgreSQL và truy xuất tất cả các bản ghi từ bảng có tên EMPLOYEE.

import psycopg2

#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving data
cursor.execute('''SELECT * from EMPLOYEE''')

#Fetching 1st row from the table
result = cursor.fetchone();
print(result)

#Fetching 1st row from the table
result = cursor.fetchall();
print(result)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Đầu ra

('Ramya', 'Rama priya', 27, 'F', 9000.0)
[
   ('Vinay', 'Battacharya', 20, 'M', 6000.0),
   ('Sharukh', 'Sheik', 25, 'M', 8300.0),
   ('Sarmista', 'Sharma', 26, 'F', 10000.0),
   ('Tripthi', 'Mishra', 24, 'F', 6000.0)
]

Trong khi thực hiện các thao tác SELECT, UPDATE hoặc, DELETE, bạn có thể chỉ định điều kiện để lọc các bản ghi bằng mệnh đề WHERE. Thao tác sẽ được thực hiện trên các bản ghi thỏa mãn điều kiện đã cho.

Cú pháp

Sau đây là cú pháp của mệnh đề WHERE trong PostgreSQL:

SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

Bạn có thể chỉ định điều kiện tìm kiếm bằng cách sử dụng các toán tử so sánh hoặc lôgic. like>, <, =, LIKE, NOT, vv Các ví dụ sau đây sẽ làm rõ khái niệm này.

Thí dụ

Giả sử chúng ta đã tạo một bảng có tên CRICKETERS bằng truy vấn sau:

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, 
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
CREATE TABLE
postgres=#

Và nếu chúng ta đã chèn 5 bản ghi vào nó bằng cách sử dụng câu lệnh INSERT như -

postgres=# insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
INSERT 0 1
postgres=# insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1

Câu lệnh SELECT sau truy xuất các bản ghi có tuổi lớn hơn 35 -

postgres=# SELECT * FROM CRICKETERS WHERE AGE > 35;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
(2 rows)

postgres=#

Mệnh đề Where sử dụng Python

Để tìm nạp các bản ghi cụ thể từ một bảng bằng cách sử dụng chương trình python, hãy thực hiện câu lệnh SELECT với mệnh đề WHERE , bằng cách chuyển nó dưới dạng tham số choexecute() phương pháp.

Thí dụ

Ví dụ về python sau minh họa cách sử dụng lệnh WHERE bằng python.

import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql = '''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT)'''
cursor.execute(sql)

#Populating the table
insert_stmt = "INSERT INTO EMPLOYEE 
   (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (%s, %s, %s, %s, %s)"
data = [('Krishna', 'Sharma', 19, 'M', 2000), ('Raj', 'Kandukuri', 20, 'M', 7000),
   ('Ramya', 'Ramapriya', 25, 'M', 5000),('Mac', 'Mohan', 26, 'M', 2000)]
cursor.executemany(insert_stmt, data)

#Retrieving specific records using the where clause
cursor.execute("SELECT * from EMPLOYEE WHERE AGE <23")
print(cursor.fetchall())

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Đầu ra

[('Krishna', 'Sharma', 19, 'M', 2000.0), ('Raj', 'Kandukuri', 20, 'M', 7000.0)]

Thông thường, nếu bạn cố gắng truy xuất dữ liệu từ một bảng, bạn sẽ nhận được các bản ghi theo cùng thứ tự mà bạn đã chèn chúng.

Sử dụng ORDER BY , trong khi truy xuất các bản ghi của một bảng, bạn có thể sắp xếp các bản ghi kết quả theo thứ tự tăng dần hoặc giảm dần dựa trên cột mong muốn.

Cú pháp

Sau đây là cú pháp của mệnh đề ORDER BY trong PostgreSQL.

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Thí dụ

Giả sử chúng ta đã tạo một bảng có tên CRICKETERS bằng truy vấn sau:

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, 
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
CREATE TABLE
postgres=#

Và nếu chúng ta đã chèn 5 bản ghi vào nó bằng cách sử dụng câu lệnh INSERT như -

postgres=# insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
INSERT 0 1
postgres=# insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1

Sau câu lệnh SELECT truy xuất các hàng của bảng CRICKETERS theo thứ tự tăng dần của độ tuổi của chúng -

postgres=# SELECT * FROM CRICKETERS ORDER BY AGE;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Virat       | Kohli      | 30  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
(5 rows)es:

Bạn có thể sử dụng nhiều cột để sắp xếp các bản ghi của một bảng. Các câu lệnh SELECT sau đây sắp xếp các bản ghi của bảng CRICKETERS dựa trên độ tuổi của cột và FIRST_NAME.

postgres=# SELECT * FROM CRICKETERS ORDER BY AGE, FIRST_NAME;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Virat       | Kohli      | 30  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
(5 rows)

Theo mặc định, ORDER BYmệnh đề sắp xếp các bản ghi của một bảng theo thứ tự tăng dần. Bạn có thể sắp xếp các kết quả theo thứ tự giảm dần bằng cách sử dụng DESC như sau:

postgres=# SELECT * FROM CRICKETERS ORDER BY AGE DESC;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Kumara      | Sangakkara | 41  | Matale         | Srilanka
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Shikhar     | Dhawan     | 33  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
Virat       | Kohli      | 30  | Delhi          | India
(5 rows)

Mệnh đề ORDER BY sử dụng Python

Để truy xuất nội dung của một bảng theo thứ tự cụ thể, hãy gọi phương thức execute () trên đối tượng con trỏ và chuyển câu lệnh SELECT cùng với mệnh đề ORDER BY, làm tham số cho nó.

Thí dụ

Trong ví dụ sau, chúng ta đang tạo một bảng có tên và Nhân viên, điền nó vào và lấy lại các bản ghi của nó theo thứ tự (tăng dần) tuổi của chúng, sử dụng mệnh đề ORDER BY.

import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#Creating a table
sql = '''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT, SEX CHAR(1),
   INCOME INT,
   CONTACT INT)'''
cursor.execute(sql)

#Populating the table
insert_stmt = "INSERT INTO EMPLOYEE 
   (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME, CONTACT) VALUES (%s, %s, %s, %s, %s, %s)"

data = [('Krishna', 'Sharma', 26, 'M', 2000, 101), 
   ('Raj', 'Kandukuri', 20, 'M', 7000, 102),
   ('Ramya', 'Ramapriya', 29, 'F', 5000, 103),
   ('Mac', 'Mohan', 26, 'M', 2000, 104)]
cursor.executemany(insert_stmt, data)
conn.commit()

#Retrieving specific records using the ORDER BY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY AGE")
print(cursor.fetchall())

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Đầu ra

[('Sharukh', 'Sheik', 25, 'M', 8300.0), ('Sarmista', 'Sharma', 26, 'F', 10000.0)]

Bạn có thể sửa đổi nội dung của các bản ghi hiện có của một bảng trong PostgreSQL bằng cách sử dụng câu lệnh UPDATE. Để cập nhật các hàng cụ thể, bạn cần sử dụng mệnh đề WHERE cùng với nó.

Cú pháp

Sau đây là cú pháp của câu lệnh UPDATE trong PostgreSQL:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

Thí dụ

Giả sử chúng ta đã tạo một bảng có tên CRICKETERS bằng truy vấn sau:

postgres=# CREATE TABLE CRICKETERS ( 
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, 
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
CREATE TABLE
postgres=#

Và nếu chúng ta đã chèn 5 bản ghi vào nó bằng cách sử dụng câu lệnh INSERT như -

postgres=# insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
INSERT 0 1
postgres=# insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1

Câu lệnh sau sửa đổi tuổi của người chơi cricket, có tên đầu tiên là Shikhar -

postgres=# UPDATE CRICKETERS SET AGE = 45 WHERE FIRST_NAME = 'Shikhar' ;
UPDATE 1
postgres=#

Nếu bạn truy xuất bản ghi có FIRST_NAME là Shikhar, bạn thấy rằng giá trị tuổi đã được thay đổi thành 45 -

postgres=# SELECT * FROM CRICKETERS WHERE FIRST_NAME = 'Shikhar';
 first_name | last_name | age | place_of_birth | country
------------+-----------+-----+----------------+---------
Shikhar     | Dhawan    | 45  | Delhi          | India
(1 row)

postgres=#

Nếu bạn chưa sử dụng mệnh đề WHERE, giá trị của tất cả các bản ghi sẽ được cập nhật. Sau câu lệnh UPDATE tăng tuổi của tất cả các bản ghi trong bảng CRICKETERS lên 1 -

postgres=# UPDATE CRICKETERS SET AGE = AGE+1;
UPDATE 5

Nếu bạn truy xuất nội dung của bảng bằng lệnh SELECT, bạn có thể thấy các giá trị được cập nhật dưới dạng:

postgres=# SELECT * FROM CRICKETERS;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Jonathan    | Trott      | 39  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 42  | Matale         | Srilanka
Virat       | Kohli      | 31  | Delhi          | India
Rohit       | Sharma     | 33  | Nagpur         | India
Shikhar     | Dhawan     | 46  | Delhi          | India
(5 rows)

Cập nhật bản ghi bằng Python

Lớp con trỏ của psycopg2 cung cấp một phương thức có tên là phương thức execute (). Phương thức này chấp nhận truy vấn như một tham số và thực thi nó.

Do đó, để chèn dữ liệu vào bảng trong PostgreSQL bằng python -

• Nhập khẩu psycopg2 gói hàng.

• Tạo một đối tượng kết nối bằng cách sử dụng connect() bằng cách chuyển tên người dùng, mật khẩu, máy chủ (mặc định tùy chọn: localhost) và, cơ sở dữ liệu (tùy chọn) dưới dạng tham số cho nó.

• Tắt chế độ cam kết tự động bằng cách đặt giá trị false thành thuộc tính autocommit.

• Các cursor() phương pháp của Connectionlớp của thư viện psycopg2 trả về một đối tượng con trỏ. Tạo một đối tượng con trỏ bằng phương pháp này.

• Sau đó, thực thi câu lệnh UPDATE bằng cách chuyển nó làm tham số cho phương thức execute ().

Thí dụ

Sau mã Python cập nhật nội dung của bảng Nhân viên và truy xuất kết quả:

import psycopg2
#establishing the connection
conn = psycopg2.connect (
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Fetching all the rows before the update
print("Contents of the Employee table: ")
sql = '''SELECT * from EMPLOYEE'''
cursor.execute(sql)
print(cursor.fetchall())

#Updating the records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M'"
cursor.execute(sql)
print("Table updated...... ")

#Fetching all the rows after the update
print("Contents of the Employee table after the update operation: ")
sql = '''SELECT * from EMPLOYEE'''
cursor.execute(sql)
print(cursor.fetchall())

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Đầu ra

Contents of the Employee table:
[
   ('Ramya', 'Rama priya', 27, 'F', 9000.0), 
   ('Vinay', 'Battacharya', 20, 'M', 6000.0), 
   ('Sharukh', 'Sheik', 25, 'M', 8300.0), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0), 
   ('Tripthi', 'Mishra', 24, 'F', 6000.0)
]
Table updated......
Contents of the Employee table after the update operation:
[
   ('Ramya', 'Rama priya', 27, 'F', 9000.0), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0), 
   ('Tripthi', 'Mishra', 24, 'F', 6000.0), 
   ('Vinay', 'Battacharya', 21, 'M', 6000.0), 
   ('Sharukh', 'Sheik', 26, 'M', 8300.0)
]

Bạn có thể xóa các bản ghi trong bảng hiện có bằng cách sử dụng DELETE FROMtuyên bố của cơ sở dữ liệu PostgreSQL. Để xóa các bản ghi cụ thể, bạn cần sử dụng mệnh đề WHERE cùng với nó.

Cú pháp

Sau đây là cú pháp của truy vấn DELETE trong PostgreSQL:

DELETE FROM table_name [WHERE Clause]

Thí dụ

Giả sử chúng ta đã tạo một bảng có tên CRICKETERS bằng truy vấn sau:

postgres=# CREATE TABLE CRICKETERS ( 
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, 
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
CREATE TABLE
postgres=#

Và nếu chúng ta đã chèn 5 bản ghi vào nó bằng cách sử dụng câu lệnh INSERT như -

postgres=# insert into CRICKETERS values ('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1

Tuyên bố sau đây xóa kỷ lục của vận động viên cricket có họ là 'Sangakkara'.

postgres=# DELETE FROM CRICKETERS WHERE LAST_NAME = 'Sangakkara';
DELETE 1

Nếu bạn truy xuất nội dung của bảng bằng câu lệnh SELECT, bạn chỉ có thể thấy 4 bản ghi vì chúng tôi đã xóa một bản ghi.

postgres=# SELECT * FROM CRICKETERS;
 first_name | last_name | age | place_of_birth | country
------------+-----------+-----+----------------+-------------
Jonathan    |     Trott |  39 | CapeTown       | SouthAfrica
Virat       |     Kohli |  31 | Delhi          | India
Rohit       |    Sharma |  33 | Nagpur         | India
Shikhar     |    Dhawan |  46 | Delhi          | India

(4 rows)

Nếu bạn thực hiện câu lệnh DELETE FROM mà không có mệnh đề WHERE, tất cả các bản ghi từ bảng được chỉ định sẽ bị xóa.

postgres=# DELETE FROM CRICKETERS;
DELETE 4

Vì bạn đã xóa tất cả các bản ghi, nếu bạn cố gắng truy xuất nội dung của bảng CRICKETERS, bằng cách sử dụng câu lệnh SELECT, bạn sẽ nhận được một tập hợp kết quả trống như hình dưới đây:

postgres=# SELECT * FROM CRICKETERS;
 first_name | last_name | age | place_of_birth | country
------------+-----------+-----+----------------+---------
(0 rows)

Xóa dữ liệu bằng Python

Lớp con trỏ của psycopg2 cung cấp một phương thức có tên là phương thức execute (). Phương thức này chấp nhận truy vấn như một tham số và thực thi nó.

Do đó, để chèn dữ liệu vào bảng trong PostgreSQL bằng python -

• Nhập khẩu psycopg2 gói hàng.

• Tạo một đối tượng kết nối bằng cách sử dụng connect() bằng cách chuyển tên người dùng, mật khẩu, máy chủ (mặc định tùy chọn: localhost) và, cơ sở dữ liệu (tùy chọn) dưới dạng tham số cho nó.

• Tắt chế độ cam kết tự động bằng cách đặt giá trị false thành thuộc tính autocommit.

• Các cursor() phương pháp của Connectionlớp của thư viện psycopg2 trả về một đối tượng con trỏ. Tạo một đối tượng con trỏ bằng phương pháp này.

• Sau đó, thực thi câu lệnh DELETE bằng cách chuyển nó dưới dạng tham số cho phương thức execute ().

Thí dụ

Sau mã Python sẽ xóa các bản ghi của bảng EMPLOYEE có giá trị tuổi lớn hơn 25 -

import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving contents of the table
print("Contents of the table: ")
cursor.execute('''SELECT * from EMPLOYEE''')
print(cursor.fetchall())

#Deleting records
cursor.execute('''DELETE FROM EMPLOYEE WHERE AGE > 25''')

#Retrieving data after delete
print("Contents of the table after delete operation ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Đầu ra

Contents of the table:
[
   ('Ramya', 'Rama priya', 27, 'F', 9000.0), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0), 
   ('Tripthi', 'Mishra', 24, 'F', 6000.0), 
   ('Vinay', 'Battacharya', 21, 'M', 6000.0), 
   ('Sharukh', 'Sheik', 26, 'M', 8300.0)
]
Contents of the table after delete operation:
[  
   ('Tripthi', 'Mishra', 24, 'F', 6000.0), 
   ('Vinay', 'Battacharya', 21, 'M', 6000.0)
]

Bạn có thể xóa một bảng từ cơ sở dữ liệu PostgreSQL bằng cách sử dụng câu lệnh DROP TABLE.

Cú pháp

Sau đây là cú pháp của câu lệnh DROP TABLE trong PostgreSQL:

DROP TABLE table_name;

Thí dụ

Giả sử chúng ta đã tạo hai bảng có tên CRICKETERS và EMPLOYEES bằng cách sử dụng các truy vấn sau:

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, 
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
CREATE TABLE
postgres=#
postgres=# CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), 
   AGE INT, SEX CHAR(1), INCOME FLOAT
);
CREATE TABLE
postgres=#

Bây giờ nếu bạn xác minh danh sách các bảng bằng lệnh “\ dt”, bạn có thể thấy các bảng đã tạo ở trên là:

postgres=# \dt;
            List of relations
 Schema | Name       | Type  | Owner
--------+------------+-------+----------
 public | cricketers | table | postgres
 public | employee   | table | postgres
(2 rows)
postgres=#

Câu lệnh sau xóa bảng có tên Nhân viên khỏi cơ sở dữ liệu -

postgres=# DROP table employee;
DROP TABLE

Vì bạn đã xóa bảng Nhân viên, nếu bạn truy xuất lại danh sách các bảng, bạn chỉ có thể quan sát một bảng trong đó.

postgres=# \dt;
            List of relations
Schema  | Name       | Type  | Owner
--------+------------+-------+----------
public  | cricketers | table | postgres
(1 row)


postgres=#

Nếu bạn cố gắng xóa lại bảng Nhân viên, vì bạn đã xóa nó rồi, bạn sẽ gặp lỗi “bảng không tồn tại” như hình dưới đây -

postgres=# DROP table employee;
ERROR: table "employee" does not exist
postgres=#

Để giải quyết vấn đề này, bạn có thể sử dụng mệnh đề IF EXISTS cùng với câu lệnh DELTE. Thao tác này sẽ xóa bảng nếu nó tồn tại, nếu không thì sẽ bỏ qua thao tác DLETE.

postgres=# DROP table IF EXISTS employee;
NOTICE: table "employee" does not exist, skipping
DROP TABLE
postgres=#

Xóa toàn bộ bảng bằng Python

Bạn có thể bỏ một bảng bất cứ khi nào bạn cần, bằng cách sử dụng câu lệnh DROP. Nhưng bạn cần phải rất cẩn thận trong khi xóa bất kỳ bảng nào hiện có vì dữ liệu bị mất sẽ không được phục hồi sau khi xóa bảng.

import psycopg2
#establishing the connection
conn = psycopg2.connect(database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432')

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists
cursor.execute("DROP TABLE emp")
print("Table dropped... ")

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Đầu ra

#Table dropped...

Trong khi thực hiện một câu lệnh PostgreSQL SELECT, bạn có thể giới hạn số lượng bản ghi trong kết quả của nó bằng cách sử dụng mệnh đề LIMIT.

Cú pháp

Sau đây là cú pháp của mệnh đề LMIT trong PostgreSQL:

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

Thí dụ

Giả sử chúng ta đã tạo một bảng có tên CRICKETERS bằng truy vấn sau:

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255), Last_Name VARCHAR(255), 
   Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
CREATE TABLE
postgres=#

Và nếu chúng ta đã chèn 5 bản ghi vào nó bằng cách sử dụng câu lệnh INSERT như -

postgres=# insert into CRICKETERS values ('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1

Câu lệnh sau truy xuất 3 bản ghi đầu tiên của bảng Cricketers bằng cách sử dụng mệnh đề LIMIT -

postgres=# SELECT * FROM CRICKETERS LIMIT 3;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
 Shikhar    | Dhawan     | 33  | Delhi          | India
 Jonathan   | Trott      | 38  | CapeTown       | SouthAfrica
 Kumara     | Sangakkara | 41  | Matale         | Srilanka

   (3 rows)

Nếu bạn muốn lấy các bản ghi bắt đầu từ một bản ghi cụ thể (bù đắp), bạn có thể làm như vậy bằng cách sử dụng mệnh đề OFFSET cùng với LIMIT.

postgres=# SELECT * FROM CRICKETERS LIMIT 3 OFFSET 2;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+----------
 Kumara     | Sangakkara | 41  | Matale         | Srilanka
 Virat      | Kohli      | 30  | Delhi          | India
 Rohit      | Sharma     | 32  | Nagpur         | India

   (3 rows)
postgres=#

Mệnh đề giới hạn sử dụng Python

Ví dụ python sau truy xuất nội dung của bảng có tên EMPLOYEE, giới hạn số bản ghi trong kết quả là 2 -

import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving single row
sql = '''SELECT * from EMPLOYEE LIMIT 2 OFFSET 2'''

#Executing the query
cursor.execute(sql)

#Fetching the data
result = cursor.fetchall();
print(result)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Đầu ra

[('Sharukh', 'Sheik', 25, 'M', 8300.0), ('Sarmista', 'Sharma', 26, 'F', 10000.0)]

Khi bạn đã chia dữ liệu trong hai bảng, bạn có thể tìm nạp các bản ghi kết hợp từ hai bảng này bằng cách sử dụng Kết hợp.

Thí dụ

Giả sử chúng ta đã tạo một bảng với tên CRICKETERS và chèn 5 bản ghi vào đó như hình dưới đây -

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, 
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
postgres=# insert into CRICKETERS values ('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
postgres=# insert into CRICKETERS values ('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
postgres=# insert into CRICKETERS values ('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
postgres=# insert into CRICKETERS values ('Virat', 'Kohli', 30, 'Delhi', 'India');
postgres=# insert into CRICKETERS values ('Rohit', 'Sharma', 32, 'Nagpur', 'India');

Và, nếu chúng ta đã tạo một bảng khác với tên OdiStats và chèn 5 bản ghi vào đó dưới dạng:

postgres=# CREATE TABLE ODIStats (
   First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, 
   Centuries INT, HalfCenturies INT
);
postgres=# insert into OdiStats values ('Shikhar', 133, 5518, 44.5, 17, 27);
postgres=# insert into OdiStats values ('Jonathan', 68, 2819, 51.25, 4, 22);
postgres=# insert into OdiStats values ('Kumara', 404, 14234, 41.99, 25, 93);
postgres=# insert into OdiStats values ('Virat', 239, 11520, 60.31, 43, 54);
postgres=# insert into OdiStats values ('Rohit', 218, 8686, 48.53, 24, 42);

Câu lệnh sau truy xuất dữ liệu kết hợp các giá trị trong hai bảng này:

postgres=# SELECT
   Cricketers.First_Name, Cricketers.Last_Name, Cricketers.Country,
   OdiStats.matches, OdiStats.runs, OdiStats.centuries, OdiStats.halfcenturies
   from Cricketers INNER JOIN OdiStats ON Cricketers.First_Name = OdiStats.First_Name;
 first_name | last_name  | country     | matches | runs  | centuries | halfcenturies
------------+------------+-------------+---------+-------+-----------+---------------
 Shikhar    | Dhawan     | India       | 133     | 5518  | 17        | 27
 Jonathan   | Trott      | SouthAfrica | 68      | 2819  | 4         | 22
 Kumara     | Sangakkara | Srilanka    | 404     | 14234 | 25        | 93
 Virat      | Kohli      | India       | 239     | 11520 | 43        | 54
 Rohit      | Sharma     | India       | 218     | 8686  | 24        | 42
(5 rows)
   
postgres=#

Tham gia bằng Python

Khi bạn đã chia dữ liệu trong hai bảng, bạn có thể tìm nạp các bản ghi kết hợp từ hai bảng này bằng cách sử dụng Kết hợp.

Thí dụ

Chương trình python sau minh họa cách sử dụng mệnh đề JOIN:

import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving single row
sql = '''SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID'''

#Executing the query
cursor.execute(sql)

#Fetching 1st row from the table
result = cursor.fetchall();
print(result)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Đầu ra

[
   ('Ramya', 'Rama priya', 27, 'F', 9000.0, 101, 101, '[email protected]', 'Hyderabad'), 
   ('Vinay', 'Battacharya', 20, 'M', 6000.0, 102, 102, '[email protected]', 'Vishakhapatnam'), 
   ('Sharukh', 'Sheik', 25, 'M', 8300.0, 103, 103, '[email protected] ', 'Pune'), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0, 104, 104, '[email protected]', 'Mumbai')
]

Lớp Cursor của thư viện psycopg cung cấp các phương thức để thực thi các lệnh PostgreSQL trong cơ sở dữ liệu bằng cách sử dụng mã python.

Sử dụng các phương thức của nó, bạn có thể thực thi các câu lệnh SQL, tìm nạp dữ liệu từ tập kết quả, gọi các thủ tục.

Bạn có thể tạo Cursor đối tượng sử dụng phương thức con trỏ () của đối tượng / lớp Connection.

Thí dụ

import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

Phương pháp

Sau đây là các phương thức khác nhau được cung cấp bởi lớp / đối tượng Cursor.

Sr.No.	Phương pháp & Mô tả
1	callproc() Phương thức này được sử dụng để gọi các thủ tục hiện có trong cơ sở dữ liệu PostgreSQL.
2	close() Phương thức này được sử dụng để đóng đối tượng con trỏ hiện tại.
3	executemany() Phương thức này chấp nhận một loạt danh sách danh sách tham số. Chuẩn bị một truy vấn MySQL và thực thi nó với tất cả các tham số.
4	execute() Phương thức này chấp nhận một truy vấn MySQL làm tham số và thực hiện truy vấn đã cho.
5	fetchall() Phương thức này truy xuất tất cả các hàng trong tập kết quả của một truy vấn và trả về chúng dưới dạng danh sách các bộ giá trị. (Nếu chúng ta thực hiện điều này sau khi truy xuất một vài hàng, nó sẽ trả về những hàng còn lại)
6	fetchone() Phương thức này tìm nạp hàng tiếp theo trong kết quả của một truy vấn và trả về nó dưới dạng một bộ giá trị.
7	fetchmany() Phương thức này tương tự như fetchone () nhưng, nó truy xuất tập hợp các hàng tiếp theo trong tập kết quả của một truy vấn, thay vì một hàng duy nhất.

Tính chất

Sau đây là các thuộc tính của lớp Cursor:

Sr.No.	Kê khai tài sản
1	description Đây là thuộc tính chỉ đọc trả về danh sách chứa mô tả các cột trong tập kết quả.
2	lastrowid Đây là thuộc tính chỉ đọc, nếu có bất kỳ cột nào được tăng tự động trong bảng, thì nó sẽ trả về giá trị được tạo cho cột đó trong thao tác INSERT hoặc UPDATE cuối cùng.
3	rowcount Điều này trả về số hàng được trả về / cập nhật trong trường hợp hoạt động CHỌN và CẬP NHẬT.
4	closed Thuộc tính này chỉ định xem một con trỏ có được đóng hay không, nếu vậy nó trả về true, ngược lại là false.
5	connection Điều này trả về một tham chiếu đến đối tượng kết nối bằng cách sử dụng con trỏ này được tạo.
6	name Thuộc tính này trả về tên của con trỏ.
7	scrollable Thuộc tính này chỉ định liệu một con trỏ cụ thể có thể cuộn được hay không.