1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/imiptools/sql.py Mon Mar 07 23:31:35 2016 +0100
1.3 @@ -0,0 +1,88 @@
1.4 +#!/usr/bin/env python
1.5 +
1.6 +"""
1.7 +Database utilities.
1.8 +
1.9 +Copyright (C) 2016 Paul Boddie <paul@boddie.org.uk>
1.10 +
1.11 +This program is free software; you can redistribute it and/or modify it under
1.12 +the terms of the GNU General Public License as published by the Free Software
1.13 +Foundation; either version 3 of the License, or (at your option) any later
1.14 +version.
1.15 +
1.16 +This program is distributed in the hope that it will be useful, but WITHOUT
1.17 +ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
1.18 +FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
1.19 +details.
1.20 +
1.21 +You should have received a copy of the GNU General Public License along with
1.22 +this program. If not, see <http://www.gnu.org/licenses/>.
1.23 +"""
1.24 +
1.25 +import re
1.26 +
1.27 +class DatabaseOperations:
1.28 +
1.29 + "Special database-related operations."
1.30 +
1.31 + def __init__(self, column_names=None, filter_values=None):
1.32 + self.column_names = column_names
1.33 + self.filter_values = filter_values
1.34 +
1.35 + def get_query(self, query, columns=None, values=None):
1.36 +
1.37 + """
1.38 + Return 'query' parameterised with condition clauses indicated by
1.39 + ":condition" in 'query' that are themselves populated using the given
1.40 + 'columns' and 'values' together with any conditions provided when
1.41 + initialising this class.
1.42 + """
1.43 +
1.44 + columns = self.merge_default_columns(columns)
1.45 + values = self.merge_default_values(values)
1.46 +
1.47 + condition = self.get_condition(columns)
1.48 +
1.49 + # Replace ":condition", replicating the values the appropriate number of
1.50 + # times.
1.51 +
1.52 + query, count = re.subn(":condition(?=[^a-zA-Z]|$)", condition, query)
1.53 + all_values = values * count
1.54 +
1.55 + # Replace ":columns" and ":values", replicating the values again.
1.56 +
1.57 + columnlist = self.columnlist(columns)
1.58 + placeholders = self.placeholders(values)
1.59 +
1.60 + query, _count = re.subn(":columns(?=[^a-zA-Z]|$)", columnlist, query)
1.61 + query, count = re.subn(":values(?=[^a-zA-Z]|$)", placeholders, query)
1.62 + all_values += values * count
1.63 +
1.64 + return query, all_values
1.65 +
1.66 + def get_condition(self, columns=None):
1.67 +
1.68 + "Return a condition clause featuring the given 'columns'."
1.69 +
1.70 + l = []
1.71 + for column in columns:
1.72 + if " " in column:
1.73 + l.append(column)
1.74 + else:
1.75 + l.append("%s = ?" % column)
1.76 +
1.77 + return "where %s" % " and ".join(l)
1.78 +
1.79 + def merge_default_columns(self, columns=None):
1.80 + return list(self.column_names or []) + list(columns or [])
1.81 +
1.82 + def merge_default_values(self, values=None):
1.83 + return list(self.filter_values or []) + list(values or [])
1.84 +
1.85 + def columnlist(self, columns=None):
1.86 + return ", ".join(columns)
1.87 +
1.88 + def placeholders(self, values=None):
1.89 + return ", ".join(["?"] * len(values))
1.90 +
1.91 +# vim: tabstop=4 expandtab shiftwidth=4