1.1 --- a/imiptools/sql.py Wed Mar 09 00:06:08 2016 +0100
1.2 +++ b/imiptools/sql.py Wed Mar 09 00:08:23 2016 +0100
1.3 @@ -25,53 +25,110 @@
1.4
1.5 "Special database-related operations."
1.6
1.7 - def __init__(self, column_names=None, filter_values=None):
1.8 + def __init__(self, column_names=None, filter_values=None, paramstyle=None):
1.9 self.column_names = column_names
1.10 self.filter_values = filter_values
1.11 + self.paramstyle = paramstyle
1.12
1.13 - def get_query(self, query, columns=None, values=None):
1.14 + def get_query(self, query, columns=None, values=None, setcolumns=None,
1.15 + setvalues=None):
1.16
1.17 """
1.18 Return 'query' parameterised with condition clauses indicated by
1.19 ":condition" in 'query' that are themselves populated using the given
1.20 'columns' and 'values' together with any conditions provided when
1.21 initialising this class.
1.22 +
1.23 + If 'setcolumns' and 'setvalues' are given, such column details and
1.24 + values will be used to parameterise ":set" clauses in the query.
1.25 """
1.26
1.27 columns = self.merge_default_columns(columns)
1.28 values = self.merge_default_values(values)
1.29
1.30 condition = self.get_condition(columns)
1.31 -
1.32 - # Replace ":condition", replicating the values the appropriate number of
1.33 - # times.
1.34 -
1.35 - query, count = re.subn(":condition(?=[^a-zA-Z]|$)", condition, query)
1.36 - all_values = values * count
1.37 -
1.38 - # Replace ":columns" and ":values", replicating the values again.
1.39 -
1.40 columnlist = self.columnlist(columns)
1.41 placeholders = self.placeholders(values)
1.42 + setters = self.get_setters(setcolumns)
1.43
1.44 - query, _count = re.subn(":columns(?=[^a-zA-Z]|$)", columnlist, query)
1.45 - query, count = re.subn(":values(?=[^a-zA-Z]|$)", placeholders, query)
1.46 - all_values += values * count
1.47 + setvalues = setvalues or []
1.48 +
1.49 + # Obtain the placeholder markers in order.
1.50 +
1.51 + parts = re.split("(:(?:condition|set|columns|values)(?=[^a-zA-Z]|$))", query)
1.52 +
1.53 + l = [parts[0]]
1.54 + is_placeholder = True
1.55 + all_values = []
1.56 +
1.57 + for part in parts[1:]:
1.58 + if is_placeholder:
1.59 +
1.60 + # Replace ":condition", replicating the given values.
1.61 +
1.62 + if part == ":condition":
1.63 + all_values += values
1.64 + l.append(condition)
1.65 +
1.66 + # Replace ":set", replicating the given values.
1.67
1.68 + elif part == ":set":
1.69 + all_values += setvalues
1.70 + l.append(setters)
1.71 +
1.72 + # Replace ":columns", providing a column list.
1.73 +
1.74 + elif part == ":columns":
1.75 + l.append(columnlist)
1.76 +
1.77 + # Replace ":values", replicating the given values.
1.78 +
1.79 + elif part == ":values":
1.80 + all_values += values
1.81 + l.append(placeholders)
1.82 +
1.83 + else:
1.84 + l.append(part)
1.85 + else:
1.86 + l.append(part)
1.87 +
1.88 + is_placeholder = not is_placeholder
1.89 +
1.90 + query = "".join(l)
1.91 return query, all_values
1.92
1.93 def get_condition(self, columns=None):
1.94
1.95 "Return a condition clause featuring the given 'columns'."
1.96
1.97 + l = self._get_columns(columns)
1.98 + return "where %s" % " and ".join(l)
1.99 +
1.100 + def get_setters(self, columns=None):
1.101 +
1.102 + "Return set operations featuring the given 'columns'."
1.103 +
1.104 + l = self._get_columns(columns)
1.105 + return "set %s" % ", ".join(l)
1.106 +
1.107 + def _get_columns(self, columns=None):
1.108 +
1.109 + "Return a list of statements or tests involving 'columns'."
1.110 +
1.111 l = []
1.112 - for column in columns:
1.113 - if " " in column:
1.114 - l.append(column)
1.115 - else:
1.116 - l.append("%s = ?" % column)
1.117
1.118 - return "where %s" % " and ".join(l)
1.119 + if columns:
1.120 + for column in columns:
1.121 + if " " in column:
1.122 + column_name, remaining = column.split(" ", 1)
1.123 + l.append("%s %s" % (self._quote(column_name), remaining))
1.124 + else:
1.125 + l.append("%s = %s" % (self._quote(column), self._param()))
1.126 +
1.127 + return l
1.128 +
1.129 + def _quote(self, column):
1.130 + return '"%s"' % column
1.131
1.132 def merge_default_columns(self, columns=None):
1.133 return list(self.column_names or []) + list(columns or [])
1.134 @@ -80,9 +137,18 @@
1.135 return list(self.filter_values or []) + list(values or [])
1.136
1.137 def columnlist(self, columns=None):
1.138 - return ", ".join(columns)
1.139 + return ", ".join([self._quote(column) for column in columns])
1.140
1.141 def placeholders(self, values=None):
1.142 - return ", ".join(["?"] * len(values))
1.143 + return ", ".join([self._param()] * len(values))
1.144 +
1.145 + def _param(self):
1.146 +
1.147 + # NOTE: To be expanded.
1.148 +
1.149 + if self.paramstyle == "pyformat":
1.150 + return "%s"
1.151 + else:
1.152 + return "?"
1.153
1.154 # vim: tabstop=4 expandtab shiftwidth=4