1
|
import mysql.connector
|
2
|
import sys
|
3
|
import argparse
|
4
|
import getpass
|
5
|
import os
|
6
|
import os.path
|
7
|
from sshtunnel import SSHTunnelForwarder
|
8
|
|
9
|
if len(sys.argv) < 2 or not sys.argv[1].strip():
|
10
|
print("Please provide a non-empty reference value.")
|
11
|
sys.exit(1)
|
12
|
|
13
|
parser = argparse.ArgumentParser(description='Synchronize databases.')
|
14
|
parser.add_argument('reference_value', type=str, help='Reference value for the database operation.')
|
15
|
parser.add_argument('-b', '--backsync', action='store_true', help='Enable backsync: change first database with second database.')
|
16
|
args = parser.parse_args()
|
17
|
|
18
|
reference_value = args.reference_value
|
19
|
table_name = 'GroupMember'
|
20
|
search_column = 'reference'
|
21
|
|
22
|
# SSH configuration
|
23
|
ssh_config_1 = {
|
24
|
'ssh_address_or_host': ('webwork20a.math.uzh.ch', 22), # e.g., ('webwork20a.math.uzh.ch', 22)
|
25
|
'ssh_username': 'root',
|
26
|
'ssh_pkey': os.path.expanduser('~/.ssh/id_rsa'), # e.g., "/home/user/.ssh/id_rsa"
|
27
|
}
|
28
|
|
29
|
ssh_config_2 = {
|
30
|
'ssh_address_or_host': ('medtool.uzh.ch', 22), # e.g., ('medtool.uzh.ch', 22)
|
31
|
'ssh_username': 'root',
|
32
|
'ssh_pkey': os.path.expanduser('~/.ssh/id_rsa'), # e.g., "/home/user/.ssh/id_rsa"
|
33
|
}
|
34
|
db_name_1 = 'medtool_dev_db'
|
35
|
db_name_2 = 'medtool_preview_db'
|
36
|
db_user_1 = 'export_user'
|
37
|
db_user_2 = 'import_user'
|
38
|
|
39
|
db_config_1_pwd = getpass.getpass(f'Enter database password for localhost:{db_name_1}, user - {db_user_1}:')
|
40
|
db_config_2_pwd = getpass.getpass(f'Enter database password for localhost:{db_name_1}, user - {db_user_2}:')
|
41
|
|
42
|
# Define database configurations
|
43
|
db_config_1 = {
|
44
|
'host': 'localhost',
|
45
|
'port': '3307',
|
46
|
'user': db_user_1,
|
47
|
'password': db_config_1_pwd,
|
48
|
'database': db_name_1
|
49
|
}
|
50
|
|
51
|
db_config_2 = {
|
52
|
'host': 'localhost',
|
53
|
'port': 3308,
|
54
|
'user': db_user_2,
|
55
|
'password': db_config_2_pwd,
|
56
|
'database': db_name_2
|
57
|
}
|
58
|
|
59
|
# Change source and target database
|
60
|
if args.backsync:
|
61
|
ssh_dummy = ssh_config_1
|
62
|
ssh_config_1 = ssh_config_2
|
63
|
ssh_config_2 = ssh_dummy
|
64
|
db_dummy = db_config_1
|
65
|
db_config_1 = db_config_2
|
66
|
db_config_2 = db_dummy
|
67
|
|
68
|
# Prompt with database names
|
69
|
print(f"-----------------------------------------------------\nStarting {table_name} table sync with searching in column: {search_column}")
|
70
|
print(f"Source - Host: {ssh_config_1['ssh_address_or_host']} / DB: {db_config_1['database']}")
|
71
|
print(f"Target - Host: {ssh_config_2['ssh_address_or_host']} / DB: {db_config_2['database']}")
|
72
|
|
73
|
# Prompt for confirmation
|
74
|
confirmation = input(f"Do you really want to proceed with the reference value '{reference_value}'? (y/N): ").strip().lower()
|
75
|
|
76
|
# Check the response
|
77
|
if confirmation != 'y':
|
78
|
print("Operation aborted by the user.")
|
79
|
sys.exit(1)
|
80
|
|
81
|
# Establish the SSH tunnel for first host
|
82
|
with SSHTunnelForwarder(
|
83
|
**ssh_config_1,
|
84
|
remote_bind_address=('localhost', 3306),
|
85
|
local_bind_address=('0.0.0.0', 3307)
|
86
|
) as tunnel1:
|
87
|
|
88
|
# Establish the SSH tunnel for second host
|
89
|
with SSHTunnelForwarder(
|
90
|
**ssh_config_2,
|
91
|
remote_bind_address=('localhost', 3306),
|
92
|
local_bind_address=('0.0.0.0', 3308)
|
93
|
) as tunnel2:
|
94
|
|
95
|
# Establish the connections
|
96
|
connection1 = mysql.connector.connect(**db_config_1)
|
97
|
connection2 = mysql.connector.connect(**db_config_2)
|
98
|
|
99
|
try:
|
100
|
cursor1 = connection1.cursor(dictionary=True)
|
101
|
cursor2 = connection2.cursor()
|
102
|
|
103
|
# First check if no record exists in source database
|
104
|
sql_count = f"SELECT COUNT(*) AS count_column FROM {table_name} WHERE {search_column} LIKE %s"
|
105
|
cursor1.execute(sql_count, (reference_value,))
|
106
|
records = cursor1.fetchone()
|
107
|
records = records['count_column']
|
108
|
if records == 0:
|
109
|
print("No records found!")
|
110
|
sys.exit()
|
111
|
|
112
|
# Select the specific records from the source database
|
113
|
sql_select = f"SELECT * FROM {table_name} WHERE {search_column} LIKE %s"
|
114
|
cursor1.execute(sql_select, (reference_value,))
|
115
|
|
116
|
for row in cursor1:
|
117
|
row.pop('id', None)
|
118
|
ref_val = row[search_column]
|
119
|
|
120
|
# Check if the record already exists in the target database
|
121
|
sql_check = f"SELECT COUNT(*) FROM {table_name} WHERE {search_column} = %s"
|
122
|
cursor2.execute(sql_check, (ref_val,))
|
123
|
count = cursor2.fetchone()[0]
|
124
|
|
125
|
if count == 0:
|
126
|
# Insert the record
|
127
|
columns = ", ".join(row.keys())
|
128
|
placeholders = ", ".join(["%s"] * len(row))
|
129
|
values = tuple(row.values())
|
130
|
sql_insert = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
|
131
|
cursor2.execute(sql_insert, values)
|
132
|
if cursor2.rowcount > 0:
|
133
|
print("INSERT: " + ref_val)
|
134
|
else:
|
135
|
print("No INSERT (Something wrong): " + ref_val)
|
136
|
else:
|
137
|
# Update the record
|
138
|
update_clauses = ", ".join(f"{col} = %s" for col in row.keys())
|
139
|
values = tuple(row.values()) + (ref_val,)
|
140
|
sql_update = f"UPDATE {table_name} SET {update_clauses} WHERE {search_column} = %s"
|
141
|
cursor2.execute(sql_update, values)
|
142
|
if cursor2.rowcount > 0:
|
143
|
print("UPDATE: " + ref_val)
|
144
|
else:
|
145
|
print("No UPDATE (Records are identical or something went wrong): " + ref_val)
|
146
|
|
147
|
connection2.commit()
|
148
|
|
149
|
finally:
|
150
|
cursor1.close()
|
151
|
cursor2.close()
|
152
|
connection1.close()
|
153
|
connection2.close()
|
154
|
print(f"{table_name} sync is finished!")
|