def get_df_from_query(self, query, params=None, pprint=False):
'''
query <string> - sql statement
params optional <tuple> - user input variables to prevent sql injection
sql statement should be formated with question
marks where variables should be placed
e.g. WHERE username = ?
pprint optional <boolean> - prints formated sql query and time to execute in minutes
returns a panadas dataframe
'''
clock = timer()
conn = self.connect_to_db()
if pprint == True:
print(self.format_sql(query))
with conn.cursor() as cur:
if params:
cur.execute(query, params)
else:
cur.execute(query)
data = cur.fetchall()
columns = [desc[0] for desc in cur.description]
conn.commit()
if pprint == True:
clock.print_lap('m')
df = pd.DataFrame.from_records(data, columns=columns)
return df
def update_db(self, query, params=None, pprint=False):
'''
query <string> - sql statement
params optional <tuple> - user input variables to prevent sql injection
sql statement should be formated with question
marks where variables should be placed
e.g. WHERE username = ?
pprint optional <boolean> - prints formated sql query and time to execute in minutes
returns effected row count
'''
clock = timer()
conn = self.connect_to_db()
if pprint == True:
print(self.format_sql(query))
with conn.cursor() as cur:
if params:
cur.execute(query, params)
else:
cur.execute(query)
row_count = cur.rowcount
conn.commit()
if pprint == True:
clock.print_lap('m')
return row_count
def transaction(self, queries, pprint=False):
'''
method for creating transcations
important to use when a rollback must be called if the
entire series of queries do not successfully complete
queries - list - sql statements
returns list of row counts for each query
'''
row_counts = []
conn = self.connect_to_db()
queries.insert(0, 'BEGIN;')
cur = conn.cursor()
try:
for query in queries:
clock = timer()
if pprint == True:
print(self.format_sql(query))
cur.execute(query)
if pprint == True:
clock.print_lap('m')
row_counts.append(cur.rowcount)
conn.commit()
except Exception as e:
print('Rolling back transacation')
conn.rollback()
raise mysql.connector.errors.ProgrammingError(str(e))
finally:
self.close_conn()
return row_counts
def get_dicts_from_query(self, query, params=None, pprint=False):
'''
query <string> - sql statement
params optional <tuple> - user input variables to prevent sql injection
sql statement should be formated with question
marks where variables should be placed
e.g. WHERE username = %s
pprint optional <boolean> - prints formated sql query and time to execute in minutes
returns a list of dictionaries
'''
conn = self.connect_to_db()
if pprint == True:
clock = timer()
print(self.format_sql(query))
with conn.cursor(DictCursor) as cur:
try:
cur.execute(query, params)
conn.commit()
data = cur.fetchall()
finally:
self.close_conn()
return data
def get_df_from_query(self, query, params=None, pprint=False, server_cur=False):
'''
query <string> - sql state ment
params optional <tuple> - user input variables to prevent sql injection
sql statement should be formated with question
marks where variables should be placed
e.g. WHERE username = %s
pprint optional <boolean> - prints formated sql query and time to execute in minutes
server_cur optional <boolean> - will return a server side cursor which you can iterate by using .fetchmany(<iterationsize>)
returns a panadas dataframe
'''
clock = timer()
conn = self.connect_to_db()
if pprint==True:
print(self.format_sql(query))
if server_cur==True:
cur = conn.cursor()
cur.execute(query, params)
return cur
with conn.cursor() as cur:
try:
cur.execute(query, params)
data = cur.fetchall()
columns = [desc[0] for desc in cur.description]
conn.commit()
finally:
cur.close()
self.close_conn()
if pprint==True:
clock.print_lap('m')
df = pd.DataFrame(data, columns=columns)
return df
def get_arr_from_query(self, query, params=None, pprint=False):
'''
query <string> - sql statement
params optional <tuple> - user input variables to prevent sql injection
sql statement should be formated with question
marks where variables should be placed
e.g. WHERE username = %s
pprint optional <boolean> - prints formated sql query and time to execute in minutes
returns a list of lists
'''
results_arr = []
clock = timer()
conn = self.connect_to_db()
if pprint == True:
print(self.format_sql(query))
with conn.cursor() as cur:
try:
if params is not None:
cur.execute(query, params)
else:
cur.execute(query)
data = cur.fetchall()
columns = [desc[0] for desc in cur.description]
results_arr.append(columns)
conn.commit()
finally:
cur.close()
self.close_conn()
if pprint==True:
clock.print_lap('m')
for row in data:
results_arr.append(list(row))
return results_arr
def copy_into(self, query, pprint=False):
'''
use copy into method to send and load data to and from S3:
https://docs.snowflake.net/manuals/sql-reference/sql/copy-into-table.html
1) unload to s3
2) copy from s3
database.conf file must have s3 credentials i.e.
aws_access_key_id=
aws_secret_access_key=
query <string> - sql statement must include AWS credentials variables
ex)
COPY INTO test_schema.test_table FROM 's3://<bucket>/test_key'
FILE_FORMAT = (
FIELD_DELIMITER = '|'
COMPRESSION = gzip
)
CREDENTIALS = (aws_key_id='{aws_access}' aws_secret_key='{aws_secret}')
pprint optional <boolean> - prints formated sql query and time to execute in minutes
returns dictionary of metadata
'''
conn = self.connect_to_db()
cp = configparser.ConfigParser()
cp.read(self.config_file)
aws_creds = {
'aws_access': cp.get(self.db_name, 'aws_access_key_id'),
'aws_secret': cp.get(self.db_name, 'aws_secret_access_key')
}
creds = "CREDENTIALS = (aws_key_id='{aws_access}' aws_secret_key='{aws_secret}')"
if pprint == True:
clock = timer()
print(self.format_sql(query))
with conn.cursor(DictCursor) as cur:
try:
cur.execute(query.format(**aws_creds))
data = cur.fetchall()
conn.commit()
if pprint == True:
clock.print_lap('m')
pretty_print(data)
status = data[0].get('status')
if status=='LOAD_FAILED':
raise snowflake.connector.errors.ProgrammingError('{}'.format(data[0]))
elif status=='PARTIALLY_LOADED':
warnings.warn('partially load - {0}'.format(data[0].get('first_error')))
finally:
self.close_conn()
return data
