1 #!/usr/bin/env python 2 3 """ 4 Database utilities. 5 6 Copyright (C) 2016 Paul Boddie <paul@boddie.org.uk> 7 8 This program is free software; you can redistribute it and/or modify it under 9 the terms of the GNU General Public License as published by the Free Software 10 Foundation; either version 3 of the License, or (at your option) any later 11 version. 12 13 This program is distributed in the hope that it will be useful, but WITHOUT 14 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS 15 FOR A PARTICULAR PURPOSE. See the GNU General Public License for more 16 details. 17 18 You should have received a copy of the GNU General Public License along with 19 this program. If not, see <http://www.gnu.org/licenses/>. 20 """ 21 22 import re 23 24 class DatabaseOperations: 25 26 "Special database-related operations." 27 28 def __init__(self, column_names=None, filter_values=None, paramstyle=None): 29 self.column_names = column_names 30 self.filter_values = filter_values 31 self.paramstyle = paramstyle 32 33 def get_query(self, query, columns=None, values=None, setcolumns=None, 34 setvalues=None): 35 36 """ 37 Return 'query' parameterised with condition clauses indicated by 38 ":condition" in 'query' that are themselves populated using the given 39 'columns' and 'values' together with any conditions provided when 40 initialising this class. 41 42 If 'setcolumns' and 'setvalues' are given, such column details and 43 values will be used to parameterise ":set" clauses in the query. 44 """ 45 46 columns = self.merge_default_columns(columns) 47 values = self.merge_default_values(values) 48 49 condition = self.get_condition(columns) 50 columnlist = self.columnlist(columns) 51 placeholders = self.placeholders(values) 52 setters = self.get_setters(setcolumns) 53 54 setvalues = setvalues or [] 55 56 # Obtain the placeholder markers in order. 57 58 parts = re.split("(:(?:condition|set|columns|values)(?=[^a-zA-Z]|$))", query) 59 60 l = [parts[0]] 61 is_placeholder = True 62 all_values = [] 63 64 for part in parts[1:]: 65 if is_placeholder: 66 67 # Replace ":condition", replicating the given values. 68 69 if part == ":condition": 70 all_values += values 71 l.append(condition) 72 73 # Replace ":set", replicating the given values. 74 75 elif part == ":set": 76 all_values += setvalues 77 l.append(setters) 78 79 # Replace ":columns", providing a column list. 80 81 elif part == ":columns": 82 l.append(columnlist) 83 84 # Replace ":values", replicating the given values. 85 86 elif part == ":values": 87 all_values += values 88 l.append(placeholders) 89 90 else: 91 l.append(part) 92 else: 93 l.append(part) 94 95 is_placeholder = not is_placeholder 96 97 query = "".join(l) 98 return query, all_values 99 100 def get_condition(self, columns=None): 101 102 "Return a condition clause featuring the given 'columns'." 103 104 l = self._get_columns(columns) 105 return "where %s" % " and ".join(l) 106 107 def get_setters(self, columns=None): 108 109 "Return set operations featuring the given 'columns'." 110 111 l = self._get_columns(columns) 112 return "set %s" % ", ".join(l) 113 114 def _get_columns(self, columns=None): 115 116 "Return a list of statements or tests involving 'columns'." 117 118 l = [] 119 120 if columns: 121 for column in columns: 122 if " " in column: 123 column_name, remaining = column.split(" ", 1) 124 l.append("%s %s" % (self._quote(column_name), remaining.replace("?", self._param()))) 125 else: 126 l.append("%s = %s" % (self._quote(column), self._param())) 127 128 return l 129 130 def _quote(self, column): 131 return '"%s"' % column 132 133 def merge_default_columns(self, columns=None): 134 return list(self.column_names or []) + list(columns or []) 135 136 def merge_default_values(self, values=None): 137 return list(self.filter_values or []) + list(values or []) 138 139 def columnlist(self, columns=None): 140 return ", ".join([self._quote(column) for column in columns]) 141 142 def placeholders(self, values=None): 143 return ", ".join([self._param()] * len(values)) 144 145 def _param(self): 146 147 # NOTE: To be expanded. 148 149 if self.paramstyle == "pyformat": 150 return "%s" 151 else: 152 return "?" 153 154 # vim: tabstop=4 expandtab shiftwidth=4