[파이썬] PostgreSQL 데이터베이스 트랜잭션

01 Sep 2023

python

PostgreSQL is a powerful relational database management system that provides support for transactions. Transactions are used to group multiple database operations into a single logical unit of work. In this blog post, we will explore how to perform database transactions using PostgreSQL in Python.

Prerequisites

Before we begin, make sure you have the following:

Python installed on your machine.
psycopg2 package installed. You can install it using pip: pip install psycopg2.

Establishing a Connection

To start using PostgreSQL in Python, we need to establish a connection to the database. The psycopg2 module provides a connect() function that we can use to create a connection object. Here’s an example:

import psycopg2

# Establish a connection
conn = psycopg2.connect(
    database="your_database",
    user="your_username",
    password="your_password",
    host="your_host",
    port="your_port"
)

Replace the placeholders (your_database, your_username, your_password, your_host, your_port) with the appropriate values for your PostgreSQL database.

Beginning a Transaction

Once we have a connection, we can begin a transaction using the begin() method. This method is provided by the connection object. Here’s an example:

# Begin a transaction
conn.begin()

Performing Database Operations

After starting a transaction, we can perform database operations such as inserting, updating, or deleting data. Let’s take an example of inserting a record into a table:

# Create a cursor object
cur = conn.cursor()

# Execute SQL statement
cur.execute("INSERT INTO employees (name, age) VALUES (%s, %s)", ("John Doe", 30))

# Commit the transaction
conn.commit()

# Close the cursor
cur.close()

Replace employees with the name of your table and adapt the SQL statement and values accordingly.

Committing or Rolling Back the Transaction

Once we have performed all the necessary database operations, we can choose to either commit or roll back the transaction.

To commit the transaction, we call the commit() method on the connection object. This will make all the changes permanent in the database.

# Commit the transaction
conn.commit()

On the other hand, if something goes wrong and we want to discard all the changes made within the transaction, we can roll back the transaction using the rollback() method.

# Roll back the transaction
conn.rollback()

Closing the Connection

Finally, when we are done working with the database, it’s important to close the connection to free up resources. We can do this by calling the close() method on the connection object.

# Close the connection
conn.close()

Conclusion

In this blog post, we have covered the basics of performing PostgreSQL database transactions in Python. Transactions are essential when dealing with critical database operations that should be treated as a single unit of work. By using the psycopg2 package, we can easily connect to a PostgreSQL database, start a transaction, perform database operations, and commit or roll back the transaction as needed.

Remember to always close the connection after you are finished to avoid resource leaks.