-
Notifications
You must be signed in to change notification settings - Fork 141
Expand file tree
/
Copy pathtransactions.py
More file actions
47 lines (38 loc) · 1.76 KB
/
transactions.py
File metadata and controls
47 lines (38 loc) · 1.76 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
from databricks import sql
import os
with sql.connect(
server_hostname=os.getenv("DATABRICKS_SERVER_HOSTNAME"),
http_path=os.getenv("DATABRICKS_HTTP_PATH"),
access_token=os.getenv("DATABRICKS_TOKEN"),
) as connection:
# Disable autocommit to use explicit transactions
connection.autocommit = False
with connection.cursor() as cursor:
try:
# Create tables for demonstration
cursor.execute("CREATE TABLE IF NOT EXISTS accounts (id int, balance int)")
cursor.execute(
"CREATE TABLE IF NOT EXISTS transfers (from_id int, to_id int, amount int)"
)
connection.commit()
# Start a new transaction - transfer money between accounts
cursor.execute("INSERT INTO accounts VALUES (1, 1000), (2, 500)")
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
cursor.execute("INSERT INTO transfers VALUES (1, 2, 100)")
# Commit the transaction - all changes succeed together
connection.commit()
print("Transaction committed successfully")
# Verify the results
cursor.execute("SELECT * FROM accounts ORDER BY id")
print("Accounts:", cursor.fetchall())
cursor.execute("SELECT * FROM transfers")
print("Transfers:", cursor.fetchall())
except Exception as e:
# Roll back on error - all changes are discarded
connection.rollback()
print(f"Transaction rolled back due to error: {e}")
raise
finally:
# Restore autocommit to default state
connection.autocommit = True