/
APS_api_lite.py
131 lines (104 loc) · 5.84 KB
/
APS_api_lite.py
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
import pymssql
import os.path
import datetime
module_dir = os.path.dirname(__file__) # Stored value for module directory, used for calling saved queries
def txt_to_str(file):
"""Simple function takes txt and converts it to str"""
with open(file, 'r') as open_file:
return open_file.read()
def edit_txt_query_with_start_and_end_date(query_string, start=None, end=None):
"""edits saved queries with start and end dates if they're supplied in the function,
otherwise pass"""
if start and end:
start_str = start.strftime('%b %d %Y %I:%M%p')
end_str = end.strftime('%b %d %Y %I:%M%p')
trans_str = query_string.replace('--REPLACE--', '').\
replace('%START_STR%', start_str).\
replace('%END_STR%', end_str)
return trans_str
else:
return query_string
def filter_query_results_according_to_filters_argument(query_results, filters=None):
"""Takes a nested list and searches for any list that contains an element in the 'filters' list
If the 'filters' list is empty, return 'query_results'"""
if filters: # If filters is not empty, search the query for matches to the filter
filtered_query = []
for i in query_results:
if any(j in i for j in filters):
filtered_query.append(i)
return filtered_query
else:
return query_results
class QueryResult(object):
"""Class for storing outputs of SQL queries
Keyword Arguments:
results -- output in form of nested lists of sql server query
column_names -- column names according to query_results, derived from python dp_api
"""
def __init__(self, results=None, column_names=None):
self.results = results
self.column_names = column_names
class APSConnection(object):
"""The primary class for conecting to the APS database
Keyword Arguments:
server -- server IP or host
instance -- instance name
user -- user id login to MSSQL
password -- self-explanatory
database -- database id at server/instance
"""
def __init__(self, server=None, instance=None, user=None, password=None, database=None, desc=None):
self.server = server
self.instance = instance
self.user = user
self.password = password
self.database = database
self.desc = desc
def connect(self):
"""Attempts to establish connection to SQL Server database."""
conn = pymssql.connect(host=self.server+'\\'+self.instance,
user=self.user,
password=self.password,
database=self.database)
return conn
def general_query(self, query):
"""Directly queries database with given string; used in conjucton with specific methods."""
conn = self.connect()
cursor = conn.cursor()
cursor.execute(query)
self.desc = cursor.description
query_results = cursor.fetchall()
return QueryResult(results=query_results, column_names=[i[0] for i in self.desc])
def txt_query(self, file):
"""Runs query read from txt file at 'file' location."""
return self.general_query(txt_to_str(file))
def crane_transactions(self, start_date=None, end_date=None, filters=None):
"""Returns list of crane transactions between given times using premade query file."""
trans_file = txt_to_str(module_dir + '\\' + 'queries\\crane_transactions')
# First, edit the query with the start and end date- if it exists
trans_query = edit_txt_query_with_start_and_end_date(trans_file, start=start_date, end=end_date)
# Next, filter by the 'filters kwarg'- if it exists. Return the filtered query
query_results = filter_query_results_according_to_filters_argument(self.general_query(trans_query).results,
filters=filters)
return QueryResult(results=query_results,
column_names=self.general_query(trans_query).column_names) # Returns object type QueryResult
def ocr_read_rates(self, start_date=None, end_date=None, filters=None):
"""Provides successful percentage of OCR read rates by crane between 2 given dates."""
trans_file = txt_to_str(module_dir + '\\' + 'queries\\ocr_read_rate')
# First, edit the query with the start and end date- if it exists
trans_query = edit_txt_query_with_start_and_end_date(trans_file, start=start_date, end=end_date)
# Next, filter by the 'filters kwarg'- if it exists. Return the filtered query
query_results = filter_query_results_according_to_filters_argument(self.general_query(trans_query).results,
filters=filters)
return QueryResult(results=query_results,
column_names=self.general_query(trans_query).column_names) # Returns object type QueryResult
def mm_read_rates(self, start_date=None, end_date=None, filters=None):
"""Provides successful percentage of MM read rates by crane between 2 given dates."""
trans_file = txt_to_str(module_dir + '\\' + 'queries\\mm_read_rate')
# First, edit the query with the start and end date- if it exists
trans_query = edit_txt_query_with_start_and_end_date(trans_file, start=start_date, end=end_date)
# Next, filter by the 'filters kwarg'- if it exists. Return the filtered query
query_results = filter_query_results_according_to_filters_argument(self.general_query(trans_query).results,
filters=filters)
return QueryResult(results=query_results,
column_names=self.general_query(trans_query).column_names) # Returns object type QueryResult