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 modules with special functions 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 module. Transactions ensure the 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 query 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 individual columns or access 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中文网其他相关文章！