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