How to use PSYCOPG2 to execute SQL query and transaction in Python
Performing SQL query and transactions in Python is a common task, and PSYCOPG2 is a popular Python library that can be used to connect and interact with PostgreSQL database.This article will introduce how to use PSYCOPG2 to perform SQL query and transactions in Python.
# Install psycopg2
First, make sure that PsyCopG2 has been installed.You can use the following command to install psycopg2 in python:
python
pip install psycopg2
# 连 to the database
Before performing SQL inquiries and transactions, you first need to be connected to the PostgreSQL database.To connect to the database, you need to provide the host address, port, database name, user name and password of the database.The following is an example code connected to the database:
python
import psycopg2
# Database connection parameter
host = "localhost"
port = "5432"
database = "mydatabase"
user = "myuser"
password = "mypassword"
# 连 to the database
connection = psycopg2.connect(
host=host,
port=port,
database=database,
user=user,
password=password
)
Please modify the above connection parameters based on your own settings to match your postgreSQL configuration.
# 查 SQL query
Once you are successfully connected to the database, you can execute the SQL query.You can use the `cursor ()" method to create a cursor object, and use the cursor object to perform SQL query.The following is an example code that demonstrates how to perform SQL queries and get results:
python
# Create a cursor object
cursor = connection.cursor()
# SQL query
query = "SELECT * FROM users"
cursor.execute(query)
# Get the query results
result = cursor.fetchall()
# Print results
for row in result:
print(row)
# Close the campaign
cursor.close()
The above code executes a simple Select query, which retrieves all the lines from a table called `Users`.`fetchall ()` method is used to obtain query results and stores it in the `Result` variable.Then, you can circulate the `Result` variable to print the value of each row.Finally, use the `Close ()" method to close the cursor.
# 执 执
In addition to performing inquiries, you can also use PsycopG2 to perform transactions.The transaction consists of a set of SQL operations and can be submitted or rolled as a separate execution unit.The following is an example code that demonstrates how to perform transactions:
python
# Create a cursor object
cursor = connection.cursor()
try:
# 开始 开始
connection.autocommit = False
cursor.execute("BEGIN")
# Execute SQL statement
cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ("John Doe", "john@example.com"))
cursor.execute("UPDATE users SET email = %s WHERE name = %s", ("johndoe@example.com", "John Doe"))
# Submit transaction
connection.commit()
Print ("Successful transaction submission")
except Exception as e:
# Error, roll back the transaction
connection.rollback()
Print ("Rolling transaction:", E)
finally:
# The default automatic submission mode
connection.autocommit = True
# Close the campaign
cursor.close()
The above code shows a transaction that contains two SQL statements.First, start the transaction by setting the connected `AutoCommit` property to` false`.Then, execute two SQL statements, insert a line of data into the `USers` table and update a line of data.If the transaction is successfully executed, use the `Commit ()` method to submit the transaction.If errors occur during transaction, you can use the `rollback () method to roll back the transaction.Finally, restore the connected `AutoCommit` to the default value and close the cursor.
This is the basic process of using PSYCOPG2 to perform SQL inquiries and transactions in Python.Through these code examples, more complex queries and transactions can be written according to actual needs.Remember to turn off the connection after using the database connection.
I hope this article will understand how to use PSYCOPG2 in Python to perform SQL queries and transactions in Python!