Python中的Psycopg2模組簡介

We know that Python is a programming language used for accomplishing various tasks in fields such as Data Analysis, AI, Machine Learning and so on. And obviously, there are different. which help us to do the job.

同樣地，Python程式碼是透過一個稱為「Psycopg2模組」的模組與PostgreSQL資料庫互動的。它是Python的一個流行的PostgreSQL資料庫適配器。這個模組為我們提供了一組函數和類，幫助我們進行資料庫連接、結果處理以及查詢執行。

Python中Psycopg2模組的主要特點

• 資料庫連線：Python中的Psycopg2模組帶有一個「connect（）」函數。此函數幫助建立與PostgreSQL資料庫的連線。我們可以將資料庫名稱、使用者名稱、密碼和主機等參數傳遞給該函數，從而幫助我們連接到我們選擇的資料庫

• 查詢執行：Psycopg2模組使我們能夠針對連接的PsycopgSQL資料庫輸入SQL查詢。 "execute()"方法幫助我們執行SQL語句，例如SELECT以存取數據，INSERT、UPDATE和DELETE用於資料操作。

• 預編譯語句：最佳化SQL查詢是Psycopg2模組非常有用的功能。只需預先準備一次SQL查詢，然後使用不同的參數多次執行，可以在效能方面帶來很大的改進。

• Transaction management: Psycopg2 provides us with a function which helps to manage transactions. Initiating a transaction, committing changes within a transaction and to rollback everything, is easier with this is within a transaction and to rollback everything, is easier with this is easier withactions is . integrity and consistency of data by grouping several database operations into a single unit.

• Error Handling: Psycopg2 handles errors and exceptions related to databases, and provides us with detailed error messages and information which helps us to debug issues with the database connection or the quebasen execution.

• Result Handling: After executing a query, the Psycopg2 module provides us with methods to fetch the result set, iterate over the rows and access the returned data. We can get indior cox rows as dictionaries for easier data manipulation.

• 資料類型轉換：Psycopg2會自動將Python物件轉換為PostgreSQL支援的對應資料類型。反之亦然。它支援各種內建的PostgreSQL資料類型，如整數、字串、日期、JSON等

Installation of the Postgre2 Module in Python

Here, we will use the pip command to install the Psycopg2 module. We have to make sure that the latest version of pip is being used. In the terminal, we have to type in the following:

pip install -U pip
pip install psycopg2-binary

These commands will install the binary version of Pycopg2 which doesn't require any built or runtime prerequisites.

模組的使用方法

The Psycopg2 module has a lot of applications, such as establishing a connection between Python code and a PostgreSQL database. Here is the code that does just that:

Example

import psycopg2

DB_NAME = "tkgafrwp"
DB_USER = "tkgafrwp"
DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"
DB_HOST = "tyke.db.elephantsql.com"
DB_PORT = "5692"

try:
   conn = psycopg2.connect(database=DB_NAME,
                user=DB_USER,
                password=DB_PASS,
                host=DB_HOST,
                port=DB_PORT)
   print("Database connected successfully")
except:
   print("Database not connected successfully")

在這裡，我們可以觀察到資料庫名稱、資料庫使用者、密碼、主機和連接埠已經儲存在不同的變數中。然後，為了讓程式碼盡可能健壯，我們使用了try和accept區塊。在try區塊內部，我們使用「connect()」函數將Python程式碼連接到PostgreSQL資料庫。該函數使用了我們儲存在不同變數中的所有信息

連接到資料庫後，我們肯定希望能夠對資料庫進行一些有用的操作。我們可以使用Python程式碼來產生SQL查詢！下面的程式碼段將會示範這一點：

Example

import psycopg2

DB_NAME = "tkgafrwp"
DB_USER = "tkgafrwp"
DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"
DB_HOST = "tyke.db.elephantsql.com"
DB_PORT = "5692"

conn = psycopg2.connect(database=DB_NAME,
                user=DB_USER,
                password=DB_PASS,
                host=DB_HOST,
                port=DB_PORT)
print("Database connected successfully")

cur = conn.cursor()
cur.execute("""
CREATE TABLE Employee
(
   ID INT  PRIMARY KEY NOT NULL,
   NAME TEXT NOT NULL,
   EMAI TEXT NOT NULL
)
""")
conn.commit()
print("Table Created successfully")

Here, we create a cursor using the "cursor()" function and then store it in the cur variable. Then we the format of a multi-line string and we type the SQL query which will go into the database. Then we use the commit() function to apply these changes to the database.

將資料插入現有表格中也是可以的！之前我們建立了表，然後我們將資料輸入到表中。下面的程式碼片段將會展示給我們看：

Example

import psycopg2

DB_NAME = "tkgafrwp"
DB_USER = "tkgafrwp"
DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"
DB_HOST = "tyke.db.elephantsql.com"
DB_PORT = "5692"

conn = psycopg2.connect(database=DB_NAME,
                user=DB_USER,
                password=DB_PASS,
                host=DB_HOST,
                port=DB_PORT)
print("Database connected successfully")

cur = conn.cursor()
cur.execute("""
   INSERT INTO Employee (ID, NAME, EMAIL) VALUES
   (1, 'Virat Kohli','viratk@gmail.com'),
   (2,' Lionel Messi','leomessi87@gmail.com')
 """)
conn.commit()
conn.close()

Here, we use the execute() function to execute the SQL statements to insert data into the existing table.

除了將資料插入實際資料庫並在伺服器上顯示，我們還可以在Python終端中顯示資料。但首先，我們需要安裝一個名為「mysqlx」的模組。這個模組在使用SQL資料庫時也非常有幫助。以下是程式碼：

Example

from mysqlx import Rows
import psycopg2

DB_NAME = "tkgafrwp"
DB_USER = "tkgafrwp"
DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"
DB_HOST = "tyke.db.elephantsql.com"
DB_PORT = "5692"

conn = psycopg2.connect(database=DB_NAME,
                user=DB_USER,
                password=DB_PASS,
                host=DB_HOST,
                port=DB_PORT)
print("Database connected successfully")

cur = conn.cursor()
cur.execute("SELECT * FROM Employee")
rows = cur.fetchall()
for data in rows:
   print("ID :" + str(data[0]))
   print("NAME :" + data[1])
   print("EMAIL :" + data[2])

print('Data fetched successfully and shown on the terminal!')
conn.close()

在這裡，我們有從「mysqlx」模組取得的行。然後，透過使用for循環，我們遍歷表的每一行。透過這種方式，我們取得每一行的所有資料。

以上是Python中的Psycopg2模組簡介的詳細內容。更多資訊請關注PHP中文網其他相關文章！