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): 29 self.column_names = column_names 30 self.filter_values = filter_values 31 32 def get_query(self, query, columns=None, values=None): 33 34 """ 35 Return 'query' parameterised with condition clauses indicated by 36 ":condition" in 'query' that are themselves populated using the given 37 'columns' and 'values' together with any conditions provided when 38 initialising this class. 39 """ 40 41 columns = self.merge_default_columns(columns) 42 values = self.merge_default_values(values) 43 44 condition = self.get_condition(columns) 45 46 # Replace ":condition", replicating the values the appropriate number of 47 # times. 48 49 query, count = re.subn(":condition(?=[^a-zA-Z]|$)", condition, query) 50 all_values = values * count 51 52 # Replace ":columns" and ":values", replicating the values again. 53 54 columnlist = self.columnlist(columns) 55 placeholders = self.placeholders(values) 56 57 query, _count = re.subn(":columns(?=[^a-zA-Z]|$)", columnlist, query) 58 query, count = re.subn(":values(?=[^a-zA-Z]|$)", placeholders, query) 59 all_values += values * count 60 61 return query, all_values 62 63 def get_condition(self, columns=None): 64 65 "Return a condition clause featuring the given 'columns'." 66 67 l = [] 68 for column in columns: 69 if " " in column: 70 l.append(column) 71 else: 72 l.append("%s = ?" % column) 73 74 return "where %s" % " and ".join(l) 75 76 def merge_default_columns(self, columns=None): 77 return list(self.column_names or []) + list(columns or []) 78 79 def merge_default_values(self, values=None): 80 return list(self.filter_values or []) + list(values or []) 81 82 def columnlist(self, columns=None): 83 return ", ".join(columns) 84 85 def placeholders(self, values=None): 86 return ", ".join(["?"] * len(values)) 87 88 # vim: tabstop=4 expandtab shiftwidth=4