Skip to content

Instantly share code, notes, and snippets.

@msakai

msakai/test_psycopg2_isolation_level.py

Last active November 26, 2023 03:22
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save msakai/d7f27ad948c7cb3502e0bae5c00a2344 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save msakai/d7f27ad948c7cb3502e0bae5c00a2344 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. msakai revised this gist Nov 26, 2023. 1 changed file with 9 additions and 7 deletions.
    16 changes: 9 additions & 7 deletions test_psycopg2_isolation_level.py
    View file
    Original file line number Diff line number Diff line change
    @@ -2,20 +2,21 @@


    dsn = "postgresql://user:password@localhost:5432/"
    db_name = "test_db"

    conn = psycopg2.connect(dsn)
    try:
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    with conn.cursor() as cur1:
    with conn.cursor() as cur:
    try:
    cur1.execute("DROP database db_test;")
    cur.execute(f"DROP database {db_name};")
    except psycopg2.errors.InvalidCatalogName:
    pass
    cur1.execute("CREATE database db_test;")
    cur.execute(f"CREATE database {db_name};")
    finally:
    conn.close()

    dsn = dsn + "db_test"
    dsn = dsn + db_name

    with psycopg2.connect(dsn) as conn1:
    with conn1.cursor() as cur:
    @@ -30,8 +31,9 @@
    )
    cur.execute("INSERT INTO users (email, balance) VALUES (%s, %s)", ("[email protected]", 100))

    # level = psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED
    level = psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
    level = psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED
    # level = psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ
    # level = psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE

    with psycopg2.connect(dsn) as conn1:
    conn1.set_isolation_level(level)
    @@ -44,5 +46,5 @@
    with conn2.cursor() as cur2:
    cur2.execute("UPDATE users SET balance = %s WHERE email=%s;", (0, "[email protected]"))

    # psycopg2.errors.SerializationFailure is raised if the isolation level is ISOLATION_LEVEL_SERIALIZABLE
    # psycopg2.errors.SerializationFailure is raised if the isolation level is ISOLATION_LEVEL_REPEATABLE_READ or ISOLATION_LEVEL_SERIALIZABLE
    cur1.execute("UPDATE users SET balance = %s WHERE email=%s;", (user1_balance + 100, "[email protected]"))
  2. msakai created this gist Nov 22, 2023.
    48 changes: 48 additions & 0 deletions test_psycopg2_isolation_level.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,48 @@
    import psycopg2


    dsn = "postgresql://user:password@localhost:5432/"

    conn = psycopg2.connect(dsn)
    try:
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    with conn.cursor() as cur1:
    try:
    cur1.execute("DROP database db_test;")
    except psycopg2.errors.InvalidCatalogName:
    pass
    cur1.execute("CREATE database db_test;")
    finally:
    conn.close()

    dsn = dsn + "db_test"

    with psycopg2.connect(dsn) as conn1:
    with conn1.cursor() as cur:
    cur.execute(
    """
    CREATE TABLE users (
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    email varchar(100) NOT NULL,
    balance integer NOT NULL
    );
    """
    )
    cur.execute("INSERT INTO users (email, balance) VALUES (%s, %s)", ("[email protected]", 100))

    # level = psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED
    level = psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE

    with psycopg2.connect(dsn) as conn1:
    conn1.set_isolation_level(level)
    with conn1.cursor() as cur1:
    cur1.execute("SELECT balance FROM users WHERE email=%s;", ("[email protected]",))
    user1_balance = cur1.fetchone()[0]

    with psycopg2.connect(dsn) as conn2:
    conn2.set_isolation_level(level)
    with conn2.cursor() as cur2:
    cur2.execute("UPDATE users SET balance = %s WHERE email=%s;", (0, "[email protected]"))

    # psycopg2.errors.SerializationFailure is raised if the isolation level is ISOLATION_LEVEL_SERIALIZABLE
    cur1.execute("UPDATE users SET balance = %s WHERE email=%s;", (user1_balance + 100, "[email protected]"))