Debian Codegraph @

src/python/ping-sql-query-example

raw
1 #!/usr/bin/env python3
2 # ==========================================================================
3 # _ _ _ ____ ____ _____
4 # | | | (_) _ \ ___ _ __ / ___|___ _ _|_ _| __ __ _ ___ ___ _ __
5 # | |_| | | |_) / _ \ '__| | / _ \| '_ \| || '__/ _` |/ __/ _ \ '__|
6 # | _ | | __/ __/ | | |__| (_) | | | | || | | (_| | (_| __/ |
7 # |_| |_|_|_| \___|_| \____\___/|_| |_|_||_| \__,_|\___\___|_|
8 #
9 # --- High-Performance Connectivity Tracer (HiPerConTracer) ---
10 # https://www.nntb.no/~dreibh/hipercontracer/
11 # ==========================================================================
12 #
13 # High-Performance Connectivity Tracer (HiPerConTracer)
14 # Copyright (C) 2015-2026 by Thomas Dreibholz
15 #
16 # This program is free software: you can redistribute it and/or modify
17 # it under the terms of the GNU General Public License as published by
18 # the Free Software Foundation, either version 3 of the License, or
19 # (at your option) any later version.
20 #
21 # This program is distributed in the hope that it will be useful,
22 # but WITHOUT ANY WARRANTY; without even the implied warranty of
23 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
24 # GNU General Public License for more details.
25 #
26 # You should have received a copy of the GNU General Public License
27 # along with this program. If not, see <http://www.gnu.org/licenses/>.
28 #
29 # Contact: [email protected]
30
31 import csv
32 import datetime
33 import gzip
34 import os
35 import sys
36 import time
37
38 import HiPerConTracer
39 import QueryHelper
40
41 from typing import Any, Final, TextIO
42
43
44 # ###### Print log message ##################################################
45 def log(logstring : str) -> None:
46 sys.stdout.write('\x1b[34m' +
47 datetime.datetime.now().strftime('%Y-%m-%dT%H:%M:%S') + ': ' +
48 logstring +
49 '\x1b[0m\n')
50
51
52
53 # ###### Main program #######################################################
54
55 # ====== Handle arguments ===================================================
56 if len(sys.argv) != 5:
57 sys.stderr.write('Usage: ' + sys.argv[0] + ' database_configuration start_timestamp end_timestamp output_file\n')
58 sys.stderr.write('Example: ' + sys.argv[0] + ' ~/test4hpct-researcher-postgresql.conf "2020-01-01 00:00:00" "2025-01-01 00:00:00" output.csv.gz\n')
59 sys.exit(1)
60 databaseConfigurationFile : Final[str] = sys.argv[1]
61 startTimeStampString : Final[str] = sys.argv[2]
62 endTimeStampString : Final[str] = sys.argv[3]
63 outputFileName : Final[str] = sys.argv[4]
64 outputFileNameTmp : Final[str] = outputFileName + '.tmp'
65
66 startTimeStamp : Final[int] = HiPerConTracer.string_to_unix_time(startTimeStampString)
67 endTimeStamp : Final[int] = HiPerConTracer.string_to_unix_time(endTimeStampString)
68 if not startTimeStamp < endTimeStamp:
69 sys.stderr.write('ERROR: start_timestamp must be less than end_timestamp!\n')
70 sys.exit(1)
71
72 # Try to open the output file here, to avoid querying but being unable to
73 # create the file (e.g. permissions or invalid path):
74 outputFile : TextIO = gzip.open(outputFileNameTmp, 'wt', encoding='utf-8')
75
76
77 # ====== Connect to database ================================================
78 configuration = QueryHelper.DatabaseConfiguration(sys.argv[1])
79 client = configuration.createClient()
80
81
82 # ====== Prepare query ======================================================
83 query : Final[str] = """
84 SELECT
85 *
86 FROM
87 Ping p
88 WHERE
89 p.SendTimeStamp >= {startTimeStamp} AND
90 p.SendTimeStamp < {endTimeStamp}
91 """.format(startTimeStamp = startTimeStamp,
92 endTimeStamp = endTimeStamp)
93
94
95 try:
96 # ====== Run query =======================================================
97 log(f'Querying {startTimeStamp} - {endTimeStamp} ...')
98
99 queryStartTime : Final[float] = time.perf_counter()
100 ( rows, description ) = configuration.query(query)
101 headers : Final[list[str]] = [ column[0] for column in description ]
102 queryFinishTime : Final[float] = time.perf_counter()
103 queryRuntime : Final[float] = queryFinishTime - queryStartTime
104
105 log(f'Got {len(rows)} rows in {queryRuntime:.1f}s')
106
107
108 # ====== Store result ====================================================
109 log(f'Storing results to {outputFileName} ...')
110
111 csvWriter = csv.writer(outputFile)
112 csvWriter.writerow(headers)
113 csvWriter.writerows(rows)
114
115 os.rename(outputFileNameTmp, outputFileName)
116 log(f'Done!')
117
118 except KeyboardInterrupt:
119 sys.stdout.write('\nSIGINT\n')
120 sys.exit(1)