1.1 --- a/imiptools/period.py Wed Mar 09 00:06:08 2016 +0100
1.2 +++ b/imiptools/period.py Wed Mar 09 00:08:23 2016 +0100
1.3 @@ -835,7 +835,7 @@
1.4
1.5 period_columns = ["start", "end", "object_uid", "transp", "object_recurrenceid", "summary", "organiser", "expires"]
1.6
1.7 - def __init__(self, cursor, table_name, column_names=None, filter_values=None, mutable=True):
1.8 + def __init__(self, cursor, table_name, column_names=None, filter_values=None, mutable=True, paramstyle=None):
1.9
1.10 """
1.11 Initialise the collection with the given 'cursor' and with the
1.12 @@ -844,7 +844,7 @@
1.13 """
1.14
1.15 FreeBusyCollectionBase.__init__(self, mutable)
1.16 - DatabaseOperations.__init__(self, column_names, filter_values)
1.17 + DatabaseOperations.__init__(self, column_names, filter_values, paramstyle)
1.18 self.cursor = cursor
1.19 self.table_name = table_name
1.20
1.21 @@ -856,7 +856,7 @@
1.22 def __iter__(self):
1.23 query, values = self.get_query(
1.24 "select %(columns)s from %(table)s :condition" % {
1.25 - "columns" : ", ".join(self.period_columns),
1.26 + "columns" : self.columnlist(self.period_columns),
1.27 "table" : self.table_name
1.28 })
1.29 self.cursor.execute(query, values)
2.1 --- a/imiptools/sql.py Wed Mar 09 00:06:08 2016 +0100
2.2 +++ b/imiptools/sql.py Wed Mar 09 00:08:23 2016 +0100
2.3 @@ -25,53 +25,110 @@
2.4
2.5 "Special database-related operations."
2.6
2.7 - def __init__(self, column_names=None, filter_values=None):
2.8 + def __init__(self, column_names=None, filter_values=None, paramstyle=None):
2.9 self.column_names = column_names
2.10 self.filter_values = filter_values
2.11 + self.paramstyle = paramstyle
2.12
2.13 - def get_query(self, query, columns=None, values=None):
2.14 + def get_query(self, query, columns=None, values=None, setcolumns=None,
2.15 + setvalues=None):
2.16
2.17 """
2.18 Return 'query' parameterised with condition clauses indicated by
2.19 ":condition" in 'query' that are themselves populated using the given
2.20 'columns' and 'values' together with any conditions provided when
2.21 initialising this class.
2.22 +
2.23 + If 'setcolumns' and 'setvalues' are given, such column details and
2.24 + values will be used to parameterise ":set" clauses in the query.
2.25 """
2.26
2.27 columns = self.merge_default_columns(columns)
2.28 values = self.merge_default_values(values)
2.29
2.30 condition = self.get_condition(columns)
2.31 -
2.32 - # Replace ":condition", replicating the values the appropriate number of
2.33 - # times.
2.34 -
2.35 - query, count = re.subn(":condition(?=[^a-zA-Z]|$)", condition, query)
2.36 - all_values = values * count
2.37 -
2.38 - # Replace ":columns" and ":values", replicating the values again.
2.39 -
2.40 columnlist = self.columnlist(columns)
2.41 placeholders = self.placeholders(values)
2.42 + setters = self.get_setters(setcolumns)
2.43
2.44 - query, _count = re.subn(":columns(?=[^a-zA-Z]|$)", columnlist, query)
2.45 - query, count = re.subn(":values(?=[^a-zA-Z]|$)", placeholders, query)
2.46 - all_values += values * count
2.47 + setvalues = setvalues or []
2.48 +
2.49 + # Obtain the placeholder markers in order.
2.50 +
2.51 + parts = re.split("(:(?:condition|set|columns|values)(?=[^a-zA-Z]|$))", query)
2.52 +
2.53 + l = [parts[0]]
2.54 + is_placeholder = True
2.55 + all_values = []
2.56 +
2.57 + for part in parts[1:]:
2.58 + if is_placeholder:
2.59 +
2.60 + # Replace ":condition", replicating the given values.
2.61 +
2.62 + if part == ":condition":
2.63 + all_values += values
2.64 + l.append(condition)
2.65 +
2.66 + # Replace ":set", replicating the given values.
2.67
2.68 + elif part == ":set":
2.69 + all_values += setvalues
2.70 + l.append(setters)
2.71 +
2.72 + # Replace ":columns", providing a column list.
2.73 +
2.74 + elif part == ":columns":
2.75 + l.append(columnlist)
2.76 +
2.77 + # Replace ":values", replicating the given values.
2.78 +
2.79 + elif part == ":values":
2.80 + all_values += values
2.81 + l.append(placeholders)
2.82 +
2.83 + else:
2.84 + l.append(part)
2.85 + else:
2.86 + l.append(part)
2.87 +
2.88 + is_placeholder = not is_placeholder
2.89 +
2.90 + query = "".join(l)
2.91 return query, all_values
2.92
2.93 def get_condition(self, columns=None):
2.94
2.95 "Return a condition clause featuring the given 'columns'."
2.96
2.97 + l = self._get_columns(columns)
2.98 + return "where %s" % " and ".join(l)
2.99 +
2.100 + def get_setters(self, columns=None):
2.101 +
2.102 + "Return set operations featuring the given 'columns'."
2.103 +
2.104 + l = self._get_columns(columns)
2.105 + return "set %s" % ", ".join(l)
2.106 +
2.107 + def _get_columns(self, columns=None):
2.108 +
2.109 + "Return a list of statements or tests involving 'columns'."
2.110 +
2.111 l = []
2.112 - for column in columns:
2.113 - if " " in column:
2.114 - l.append(column)
2.115 - else:
2.116 - l.append("%s = ?" % column)
2.117
2.118 - return "where %s" % " and ".join(l)
2.119 + if columns:
2.120 + for column in columns:
2.121 + if " " in column:
2.122 + column_name, remaining = column.split(" ", 1)
2.123 + l.append("%s %s" % (self._quote(column_name), remaining))
2.124 + else:
2.125 + l.append("%s = %s" % (self._quote(column), self._param()))
2.126 +
2.127 + return l
2.128 +
2.129 + def _quote(self, column):
2.130 + return '"%s"' % column
2.131
2.132 def merge_default_columns(self, columns=None):
2.133 return list(self.column_names or []) + list(columns or [])
2.134 @@ -80,9 +137,18 @@
2.135 return list(self.filter_values or []) + list(values or [])
2.136
2.137 def columnlist(self, columns=None):
2.138 - return ", ".join(columns)
2.139 + return ", ".join([self._quote(column) for column in columns])
2.140
2.141 def placeholders(self, values=None):
2.142 - return ", ".join(["?"] * len(values))
2.143 + return ", ".join([self._param()] * len(values))
2.144 +
2.145 + def _param(self):
2.146 +
2.147 + # NOTE: To be expanded.
2.148 +
2.149 + if self.paramstyle == "pyformat":
2.150 + return "%s"
2.151 + else:
2.152 + return "?"
2.153
2.154 # vim: tabstop=4 expandtab shiftwidth=4