def test_rollback(shared_instance, dbapi_database):
"""Test rollbacking a transaction with several statements."""
want_row = (2, "first-name", "last-name", "*****@*****.**")
# connect to the test database
conn = Connection(shared_instance, dbapi_database)
cursor = conn.cursor()
cursor.execute("""
INSERT INTO contacts (contact_id, first_name, last_name, email)
VALUES (2, 'first-name', 'last-name', '*****@*****.**')
""")
conn.commit()
# execute several DMLs with one transaction
cursor.execute("""
UPDATE contacts
SET first_name = 'updated-first-name'
WHERE first_name = 'first-name'
""")
cursor.execute("""
UPDATE contacts
SET email = '*****@*****.**'
WHERE email = '*****@*****.**'
""")
conn.rollback()
# read the resulting data from the database
cursor.execute("SELECT * FROM contacts")
got_rows = cursor.fetchall()
conn.commit()
assert got_rows == [want_row]
cursor.close()
conn.close()
def test_autocommit_mode_change(shared_instance, dbapi_database):
"""Test auto committing a transaction on `autocommit` mode change."""
want_row = (
2,
"updated-first-name",
"last-name",
"*****@*****.**",
)
# connect to the test database
conn = Connection(shared_instance, dbapi_database)
cursor = conn.cursor()
cursor.execute("""
INSERT INTO contacts (contact_id, first_name, last_name, email)
VALUES (2, 'first-name', 'last-name', '*****@*****.**')
""")
cursor.execute("""
UPDATE contacts
SET first_name = 'updated-first-name'
WHERE first_name = 'first-name'
""")
conn.autocommit = True
# read the resulting data from the database
cursor.execute("SELECT * FROM contacts")
got_rows = cursor.fetchall()
assert got_rows == [want_row]
cursor.close()
conn.close()
def test_staleness(shared_instance, dbapi_database):
"""Check the DB API `staleness` option."""
conn = Connection(shared_instance, dbapi_database)
cursor = conn.cursor()
before_insert = datetime.datetime.utcnow().replace(tzinfo=UTC)
cursor.execute("""
INSERT INTO contacts (contact_id, first_name, last_name, email)
VALUES (1, 'first-name', 'last-name', '*****@*****.**')
""")
conn.commit()
conn.read_only = True
conn.staleness = {"read_timestamp": before_insert}
cursor.execute("SELECT * FROM contacts")
conn.commit()
assert len(cursor.fetchall()) == 0
conn.staleness = None
cursor.execute("SELECT * FROM contacts")
conn.commit()
assert len(cursor.fetchall()) == 1
conn.close()
def test_execute_many(shared_instance, dbapi_database):
# connect to the test database
conn = Connection(shared_instance, dbapi_database)
cursor = conn.cursor()
row_data = [
(1, "first-name", "last-name", "*****@*****.**"),
(2, "first-name2", "last-name2", "*****@*****.**"),
]
cursor.executemany(
"""
INSERT INTO contacts (contact_id, first_name, last_name, email)
VALUES (%s, %s, %s, %s)
""",
row_data,
)
conn.commit()
cursor.executemany(
"""SELECT * FROM contacts WHERE contact_id = @a1""",
({
"a1": 1
}, {
"a1": 2
}),
)
res = cursor.fetchall()
conn.commit()
assert len(res) == len(row_data)
for found, expected in zip(res, row_data):
assert found[0] == expected[0]
# checking that execute() and executemany()
# results are not mixed together
cursor.execute("""
SELECT * FROM contacts WHERE contact_id = 1
""", )
res = cursor.fetchone()
conn.commit()
assert res[0] == 1
conn.close()
def test_DDL_commit(shared_instance, dbapi_database):
"""Check that DDLs in commit mode are executed on calling `commit()`."""
conn = Connection(shared_instance, dbapi_database)
cur = conn.cursor()
cur.execute("""
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
Name STRING(1024),
) PRIMARY KEY (SingerId)
""")
conn.commit()
conn.close()
# if previous DDL wasn't committed, the next DROP TABLE
# statement will fail with a ProgrammingError
conn = Connection(shared_instance, dbapi_database)
cur = conn.cursor()
cur.execute("DROP TABLE Singers")
conn.commit()
def test_DDL_autocommit(self):
"""Check that DDLs in autocommit mode are immediately executed."""
conn = Connection(Config.INSTANCE, self._db)
conn.autocommit = True
cur = conn.cursor()
cur.execute("""
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
Name STRING(1024),
) PRIMARY KEY (SingerId)
""")
conn.close()
# if previous DDL wasn't committed, the next DROP TABLE
# statement will fail with a ProgrammingError
conn = Connection(Config.INSTANCE, self._db)
cur = conn.cursor()
cur.execute("DROP TABLE Singers")
conn.commit()
def test_execute_many(self):
# connect to the test database
conn = Connection(Config.INSTANCE, self._db)
cursor = conn.cursor()
cursor.execute("""
INSERT INTO contacts (contact_id, first_name, last_name, email)
VALUES (1, 'first-name', 'last-name', '*****@*****.**'),
(2, 'first-name2', 'last-name2', '*****@*****.**')
""")
conn.commit()
cursor.executemany(
"""
SELECT * FROM contacts WHERE contact_id = @a1
""",
({
"a1": 1
}, {
"a1": 2
}),
)
res = cursor.fetchall()
conn.commit()
self.assertEqual(len(res), 2)
self.assertEqual(res[0][0], 1)
self.assertEqual(res[1][0], 2)
# checking that execute() and executemany()
# results are not mixed together
cursor.execute("""
SELECT * FROM contacts WHERE contact_id = 1
""", )
res = cursor.fetchone()
conn.commit()
self.assertEqual(res[0], 1)
conn.close()
def test_commit(self):
"""Test committing a transaction with several statements."""
want_row = (
1,
"updated-first-name",
"last-name",
"*****@*****.**",
)
# connect to the test database
conn = Connection(Config.INSTANCE, self._db)
cursor = conn.cursor()
# execute several DML statements within one transaction
cursor.execute("""
INSERT INTO contacts (contact_id, first_name, last_name, email)
VALUES (1, 'first-name', 'last-name', '*****@*****.**')
""")
cursor.execute("""
UPDATE contacts
SET first_name = 'updated-first-name'
WHERE first_name = 'first-name'
""")
cursor.execute("""
UPDATE contacts
SET email = '*****@*****.**'
WHERE email = '*****@*****.**'
""")
conn.commit()
# read the resulting data from the database
cursor.execute("SELECT * FROM contacts")
got_rows = cursor.fetchall()
conn.commit()
self.assertEqual(got_rows, [want_row])
cursor.close()
conn.close()
def test_autocommit_with_json_data(shared_instance, dbapi_database):
"""Check that DDLs in autocommit mode are immediately executed for
json fields."""
# Create table
conn = Connection(shared_instance, dbapi_database)
conn.autocommit = True
cur = conn.cursor()
cur.execute("""
CREATE TABLE JsonDetails (
DataId INT64 NOT NULL,
Details JSON,
) PRIMARY KEY (DataId)
""")
# Insert data to table
cur.execute(
sql="INSERT INTO JsonDetails (DataId, Details) VALUES (%s, %s)",
args=(123, JsonObject({
"name": "Jakob",
"age": "26"
})),
)
# Read back the data.
cur.execute("""select * from JsonDetails;""")
got_rows = cur.fetchall()
# Assert the response
assert len(got_rows) == 1
assert got_rows[0][0] == 123
assert got_rows[0][1] == {"age": "26", "name": "Jakob"}
# Drop the table
cur.execute("DROP TABLE JsonDetails")
conn.commit()
conn.close()
def test_rollback_on_connection_closing(shared_instance, dbapi_database):
"""
When closing a connection all the pending transactions
must be rollbacked. Testing if it's working this way.
"""
want_row = (1, "first-name", "last-name", "*****@*****.**")
# connect to the test database
conn = Connection(shared_instance, dbapi_database)
cursor = conn.cursor()
cursor.execute("""
INSERT INTO contacts (contact_id, first_name, last_name, email)
VALUES (1, 'first-name', 'last-name', '*****@*****.**')
""")
conn.commit()
cursor.execute("""
UPDATE contacts
SET first_name = 'updated-first-name'
WHERE first_name = 'first-name'
""")
conn.close()
# connect again, as the previous connection is no-op after closing
conn = Connection(shared_instance, dbapi_database)
cursor = conn.cursor()
# read the resulting data from the database
cursor.execute("SELECT * FROM contacts")
got_rows = cursor.fetchall()
conn.commit()
assert got_rows == [want_row]
cursor.close()
conn.close()
def test_ping(shared_instance, dbapi_database):
"""Check connection validation method."""
conn = Connection(shared_instance, dbapi_database)
conn.validate()
conn.close()