Let's walk through creating a transaction ourselves based on the `BankAccounts` table described in the previous chapter.

We're going to perform a straightforward financial transaction: transferring funds from one account to another. Ensuring that both steps occur as a single logical unit exemplifies a real-life transaction.

We will work with account numbers `101` and `789` and the initial balance will be the following:

import unittest
import psycopg2
import pandas as pd

class TestPostgresQuery(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_balances_after_transaction(self):
        """Check if balances for accounts 101 and 789 are updated correctly after transferring 500 from 101 to 789."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute("UPDATE BankAccounts SET balance = 4500 WHERE account_number = 101;")
        self.cursor.execute("UPDATE BankAccounts SET balance = 3500 WHERE account_number = 789;")
        self.connection.commit()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        result_df = result_df.set_index('account_number')
        expected_balances = {101: 4000.00, 789: 4000.00}
        correct = True
        for acc, expected in expected_balances.items():
            if acc not in result_df.index or float(result_df.loc[acc, 'balance']) != expected:
                correct = False
        _dynamic_test(
            self,
            correct,
            "Balances are updated correctly after transaction.",
            f"Balances are not updated correctly.\\nExpected: {expected_balances}\\nGot: {result_df['balance'].to_dict()}"
        )

    def test_transaction_committed(self):
        """Check that the transaction is committed and balances persist after reconnect."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Reset balances
        self.cursor.execute("UPDATE BankAccounts SET balance = 4500 WHERE account_number = 101;")
        self.cursor.execute("UPDATE BankAccounts SET balance = 3500 WHERE account_number = 789;")
        self.connection.commit()
        self.cursor.execute(query)
        self.connection.commit()
        # Reconnect to check persistence
        self.cursor.close()
        self.connection.close()
        connection2 = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cursor2 = connection2.cursor()
        cursor2.execute("SELECT account_number, balance FROM BankAccounts WHERE account_number IN (101, 789);")
        rows = cursor2.fetchall()
        result_df = pd.DataFrame(rows, columns=['account_number', 'balance']).set_index('account_number')
        expected_balances = {101: 4000.00, 789: 4000.00}
        correct = True
        for acc, expected in expected_balances.items():
            if acc not in result_df.index or float(result_df.loc[acc, 'balance']) != expected:
                correct = False
        _dynamic_test(
            self,
            correct,
            "Transaction was committed and balances persisted.",
            f"Transaction was not committed or balances did not persist.\\nExpected: {expected_balances}\\nGot: {result_df['balance'].to_dict()}"
        )
        cursor2.close()
        connection2.close()
def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)

def normalize_text(text):
    text = text.lower()
    text = re.sub(r"\\s{2,}", " ", text)
    text = re.sub(r"\\s*([,:?])\\s*", r"\\1 ", text)
    return text.strip()

if __name__ == "__main__":
    unittest.main()


test_solution.py

Begin an exciting journey through SQL with Advanced Techniques in SQL. Learn about the important ACID properties that help keep data accurate in database transactions. Discover advanced ways to optimize your queries to improve database performance. Explore special SQL features like window functions, triggers, Data Control Language (DCL), and NoSQL databases. This will help you handle modern database management confidently and effectively.

Now we explore the basic principles of ACID (Atomicity, Consistency, Isolation, Durability) in database management. Learn how these principles ensure reliable transactions by guaranteeing complete operations, keeping data consistent, isolating concurrent transactions, and ensuring permanent data storage.

Explore how to enhance the efficiency of your SQL queries through index optimization. Delve into the significance of indexes in database performance, learning how to design, implement, and utilize them effectively. Discover strategies for choosing the appropriate indexes, understanding their impact on query execution plans, and optimizing query performance through index selection and utilization.

This section explores pivotal topics such as Triggers, Window Functions, and DCL (Data Control Language), offering comprehensive insights into their implementation and optimization strategies. Furthermore, it navigates the landscape of NoSQL DBMS, providing a thorough understanding of its architecture, advantages, and practical applications.

Challenge: Transaction Creation

Solution

Awesome!

Challenge: Transaction Creation

Solution