I have a list of tuples that will store my db credentials later used by DBClient
class to perform a SELECT
using get_data()
. dd_client
's post_sql_metrics
method builds entire data set, adds envs
tag and injectes into the dash.board
Issue I'm having is how to run the loop for ech environment with its dedicated credentials.
envs_creds = [
('dev', 'xyz', 'db', 'usr', 'pass'),
('test', 'xyz', 'db', 'usr', 'pass'),
]
for i,e in enumerate(envs_creds):
client = DBClient(envs_creds[0][1], envs_creds[0][2], envs_creds[0][3], envs_creds[0][4])
sql_query, header = client .get_data()
dd_client.post_sql_metrics(sql_query, header, envs_creds[0][0])
DBClient class:
class DBClient:
def __init__(self, server, database, username, password):
self.server = server
self.database = database
self.username = username
self.password = password
def get_data(self):
query = 'SELECT col1, colN FROM tbl'
conn = pymssql.connect(server=self.server, user=self.username, password=self.password, database=self.database)
cursor = conn.cursor()
cursor.execute(query)
res_list = cursor.fetchall()
conn.close()
header = re.search('SELECT(.*)FROM', query) #q[7:q.index('FROM')]
header = [x.strip() for x in header.group(1).split(sep=',')] #[x.strip() for x in q.split(sep=',')]
return res_list, header
Metrics post method:
def post_sql_metrics(self, tasks, header, env, metric_name="my_metric"):
tags = [[f'{a}:{b}' for a, b in zip(header, i)] for i in tasks]
tags = [sub_lst [f'env:{env}'] for sub_lst in tags]
col_to_remove = 2
tags = [(x[0:col_to_remove] x[col_to_remove 1:]) for x in tags]
series = [
DDMetric(
self.HOST,
metric_name,
record[2],
tag,
).to_series() for record, tag in zip(tasks, tags)
]
print(series)
CodePudding user response:
Your problem is that you are constantly referring to the 0th element rather than i
. You are also starting from element 1 when instantiating your DBClient
which will give an IndexError
# Using _ instead of e as e is not being used here
for i, _ in enumerate(envs_creds):
client = DBClient(envs_creds[i][0], envs_creds[i][1], envs_creds[i][1], envs_creds[i][3])
sql_query, header = client .get_data()
dd_client.post_sql_metrics(sql_query, header, envs_creds[i][0])
Typically, in Python you can avoid indexing (especially in a for
loop).
# e will be a single tuple with the DB credentials
for e in envs_creds:
client = DBClient(e[0], e[1], e[2], e[3]) # or more simply: DBClient(*e)
sql_query, header = client .get_data()
dd_client.post_sql_metrics(sql_query, header, e[0])
When you are looping over a list
with consistent elements you can also use tuple unpacking as demonstrated below.
# Assigning each element to a variable
for server, db, usr, pwd in envs_creds:
client = DBClient(server, db, usr, pwd)
sql_query, header = client.get_data()
dd_client.post_sql_metrics(sql_query, header, server)
As an aside, you are going to have a serious security issue if you are storing your DB credentials in your code. You should read up on proper methods to store sensitive information.
Note: as mentioned in the comments, the credentials are just there to represent structure and are en/decrypted.