|
1
|
|
|
from _mssql import ROW_FORMAT_DICT |
|
2
|
|
|
from tempfile import NamedTemporaryFile |
|
3
|
|
|
import csv |
|
4
|
|
|
import os |
|
5
|
|
|
|
|
6
|
|
|
__all__ = [ |
|
7
|
|
|
'ResultsProcessor' |
|
8
|
|
|
] |
|
9
|
|
|
|
|
10
|
|
|
|
|
11
|
|
|
# noinspection PyUnusedLocal |
|
12
|
|
|
# noinspection PyMethodMayBeStaticInspection |
|
13
|
|
|
class ResultsProcessor(object): |
|
14
|
|
|
""" |
|
15
|
|
|
Utility for processing action response, i.e. changing the returned results, passing data via disk, etc. |
|
|
|
|
|
|
16
|
|
|
""" |
|
17
|
|
|
|
|
18
|
|
|
def __init__(self, config): |
|
19
|
|
|
self.config = config |
|
20
|
|
|
|
|
21
|
|
|
def execute_scalar(self, response, cursor): |
|
22
|
|
|
""" |
|
23
|
|
|
Returns the scalar response. |
|
24
|
|
|
""" |
|
25
|
|
|
return response |
|
26
|
|
|
|
|
27
|
|
|
def execute_row(self, response, cursor): |
|
28
|
|
|
""" |
|
29
|
|
|
Returns a mapping of column name to value for a row. |
|
30
|
|
|
""" |
|
31
|
|
|
return self._filter_numbered_columns(response) |
|
32
|
|
|
|
|
33
|
|
|
def execute_insert(self, response, cursor): |
|
34
|
|
|
""" |
|
35
|
|
|
Returns identity value of the inserted row. |
|
36
|
|
|
""" |
|
37
|
|
|
return cursor.identity |
|
38
|
|
|
|
|
39
|
|
|
def execute_non_query(self, response, cursor): |
|
40
|
|
|
""" |
|
41
|
|
|
Returns the number of rows affected by the non-query. |
|
42
|
|
|
""" |
|
43
|
|
|
return cursor.rows_affected |
|
44
|
|
|
|
|
45
|
|
|
def execute_query(self, response, cursor): |
|
46
|
|
|
""" |
|
47
|
|
|
Writes results to CSV for downstream processing. Each result set is written to its own file. |
|
48
|
|
|
Returns a list of all file names in order of result set for use by downstream actions. |
|
49
|
|
|
|
|
50
|
|
|
Checks `output_csv` section in `config.yaml` to determine where to write CSV files. You can specify |
|
|
|
|
|
|
51
|
|
|
the output `directory` as well as the file `prefix` and `suffix`. |
|
52
|
|
|
|
|
53
|
|
|
Tries writing to `$TMPDIR`, `$TEMP`, and `$TMP` in order before falling back to platform-specific locations. |
|
|
|
|
|
|
54
|
|
|
See https://docs.python.org/2/library/tempfile.html#tempfile.tempdir for details. |
|
55
|
|
|
""" |
|
56
|
|
|
output_files = [] |
|
57
|
|
|
while True: |
|
58
|
|
|
with self._get_output_file() as csv_file: |
|
59
|
|
|
# Since pythonrunner runs as root, only root can read and write CSV file by default |
|
60
|
|
|
# Let's chmod it so downstream processes which run as stanley can also read and write |
|
|
|
|
|
|
61
|
|
|
os.chmod(csv_file.name, 0o666) # race condition with open() is OK since we're making less restrictive |
|
|
|
|
|
|
62
|
|
|
try: |
|
63
|
|
|
# Grab the first row so we can read the headers and write them to the CSV |
|
64
|
|
|
first_row = self._filter_numbered_columns(next(cursor.get_iterator(ROW_FORMAT_DICT))) |
|
|
|
|
|
|
65
|
|
|
except StopIteration: |
|
66
|
|
|
# the last result set will always be empty, so remove the file created for it |
|
67
|
|
|
os.unlink(csv_file.name) |
|
68
|
|
|
# we're done, no more result sets |
|
69
|
|
|
break |
|
70
|
|
|
output_files.append(csv_file.name) |
|
71
|
|
|
writer = csv.DictWriter(csv_file, fieldnames=first_row.keys()) |
|
72
|
|
|
writer.writeheader() |
|
73
|
|
|
writer.writerow(first_row) |
|
74
|
|
|
for row in cursor: |
|
75
|
|
|
writer.writerow(self._filter_numbered_columns(row)) |
|
76
|
|
|
return output_files |
|
77
|
|
|
|
|
78
|
|
|
def _filter_numbered_columns(self, row): |
|
79
|
|
|
"""only return columns by name, not column number""" |
|
80
|
|
|
return {k: v for k, v in row.iteritems() if not isinstance(k, (int, long))} |
|
|
|
|
|
|
81
|
|
|
|
|
82
|
|
|
def _get_output_file(self, prefix='mssql-query.', suffix='.csv'): |
|
83
|
|
|
output_config = self.config.get('output_csv', {}) |
|
84
|
|
|
return NamedTemporaryFile( |
|
85
|
|
|
dir=output_config.get('directory', None), |
|
86
|
|
|
prefix=output_config.get('output_prefix', prefix), |
|
87
|
|
|
suffix=output_config.get('output_suffix', suffix), |
|
88
|
|
|
delete=False) |
|
89
|
|
|
|
StackStorm /
st2contrib
Loading history...
This check looks for lines that are too long. You can specify the maximum line length.