69 lines
3.1 KiB
Python
69 lines
3.1 KiB
Python
|
import mysql.connector
|
||
|
|
from mysql.connector import Error
|
||
|
|
|
||
|
|
# Define the database connection details
|
||
|
|
db_configs = [
|
||
|
|
{
|
||
|
|
'user': 'snehalatha',
|
||
|
|
'password': 'paSsWord@#654',
|
||
|
|
'host': 'konectar-readreplica-rds.konectar.io',
|
||
|
|
'database': 'kolm_lite_cardio',
|
||
|
|
'instance':'cardio'
|
||
|
|
},
|
||
|
|
{
|
||
|
|
'user': 'snehalatha',
|
||
|
|
'password': 'paSsWord@#654',
|
||
|
|
'host': 'konectar-readreplica-rds.konectar.io',
|
||
|
|
'database': 'kolm_lite_veterinary',
|
||
|
|
'instance':'veterinary'
|
||
|
|
},
|
||
|
|
{
|
||
|
|
'user': 'snehalatha',
|
||
|
|
'password': 'paSsWord@#654',
|
||
|
|
'host': 'konectar-readreplica-rds.konectar.io',
|
||
|
|
'database': 'kolm_lite_oralhealth',
|
||
|
|
'instance':'oralhealth'
|
||
|
|
}
|
||
|
|
]
|
||
|
|
|
||
|
|
def fetch_records(config):
|
||
|
|
try:
|
||
|
|
# Connect to the database
|
||
|
|
connection = mysql.connector.connect(
|
||
|
|
host=config['host'],
|
||
|
|
user=config['user'],
|
||
|
|
password=config['password'],
|
||
|
|
database=config['database']
|
||
|
|
)
|
||
|
|
if connection.is_connected():
|
||
|
|
|
||
|
|
cursor = connection.cursor(dictionary=True)
|
||
|
|
cursor.execute(f"SELECT '{ config['instance'] }' as instance, kols.npi_num,kols.id,CONCAT_WS(' ',kols.first_name,kols.middle_name,kols.last_name) as kol_name, projects.name as project_name, clients.name as client_name,CONCAT(client_users.first_name,' ',client_users.last_name) as user_name, GROUP_CONCAT(DISTINCT log_activities.transaction_name,':',date(log_activities.created_on)) as project_details,case when user_kols.opt_in_out_status = 3 then 'Opted Out' when user_kols.opt_in_out_status = 4 then 'Opted In' else '' end as status , date(kols.data_updated) as data_processed_date,countries.Country as user_country,CONCAT(poc.first_name,' ',poc.last_name) as client_poc FROM kols left join user_kols on user_kols.kol_id = kols.id left join countries on countries.CountryId = kols.country_id inner join opt_inout_statuses on opt_inout_statuses.id = user_kols.opt_in_out_status left join log_activities on log_activities.miscellaneous1 = user_kols.kol_id left join client_users on client_users.id = user_kols.user_id left join clients on client_users.client_id = clients.id left join client_users as poc on poc.id = clients.created_by left join project_clients on project_clients.client_id = clients.id left join projects on project_clients.project_id = projects.id where log_activities.module ='opt_in_out' and log_activities.transaction_name in ('New','Opt-in Requested','Opt-out','Opt-in Approved','Opt-in Expired','Opt-in Received','Opt-in') group by kols.id;")
|
||
|
|
records = cursor.fetchall()
|
||
|
|
return records
|
||
|
|
|
||
|
|
except Error as e:
|
||
|
|
print(f"Error while connecting to MySQL: {e}")
|
||
|
|
return None
|
||
|
|
|
||
|
|
finally:
|
||
|
|
if connection.is_connected():
|
||
|
|
cursor.close()
|
||
|
|
connection.close()
|
||
|
|
|
||
|
|
# Loop through the databases and fetch records
|
||
|
|
|
||
|
|
def get_records():
|
||
|
|
all_records = []
|
||
|
|
for config in db_configs:
|
||
|
|
records = fetch_records(config)
|
||
|
|
if records:
|
||
|
|
all_records.extend(records)
|
||
|
|
|
||
|
|
return all_records
|
||
|
|
|
||
|
|
# Print or process the fetched records
|
||
|
|
# for record in all_records:
|
||
|
|
# print(record)
|
||
|
|
|