I ended up creating my subclass of Select, copy/pasting the __init__ and
__str__ methods and filled in my own functionality, and then monkey-patched
it as described above.
It is by no means full featured, but it works alright for my use case. I've
posted my implementation below for your viewing pleasure ;)
class SelectWithDistinct(sql.Select):
"""Select definition that allows for DISTINCT queries.
DISTINCT is - unfortunately - not yet available in the python-sql module,
so we implement this ourselves in this class, which can be monkey-patched
into the python-sql library. Not ideal, but it is robust enough.
The entry point for the monkey-patching would be in sql.From.select(..)
This method needs to be replaced with one that uses this Select
implementation instead of the default one.
"""
__slots__ = ('_columns', 'distincts', '_where', '_group_by', '_having',
'_for_', 'from_')
def __init__(self, columns, distincts=None, from_=None, where=None,
group_by=None,
having=None, for_=None, **kwargs):
self._columns = None
self._where = None
self._group_by = None
self._having = None
self._for_ = None
super(SelectWithDistinct, self).__init__(columns, **kwargs)
self.distincts = distincts
self.columns = columns
self.from_ = from_
self.where = where
self.group_by = group_by
self.having = having
self.for_ = for_
def __str__(self):
if (
Flavor.get().limitstyle == 'rownum'
and (self.limit is not None or self.offset is not None)
):
return self._rownum(str)
with AliasManager():
from_ = str(self.from_)
if self.columns:
columns = ', '.join(map(self._format_column, self.columns))
else:
columns = '*'
if self.distincts is not None:
if len(self.distincts) == 0:
distinct = 'DISTINCT '
else:
distinct = 'DISTINCT ON ({}) '.format(
', '.join(map(self._format_column, self.distincts))
)
columns = distinct + columns
where = ''
if self.where:
where = ' WHERE ' + str(self.where)
group_by = ''
if self.group_by:
group_by = ' GROUP BY ' + ', '.join(map(str, self.group_by))
having = ''
if self.having:
having = ' HAVING ' + str(self.having)
window = ''
windows = [f.window for f in self._window_functions()]
if windows:
window = ' WINDOW ' + ', '.join(
'"%s" AS (%s)' % (w.alias, w) for w in windows)
for_ = ''
if self.for_ is not None:
for_ = ' ' + ' '.join(map(str, self.for_))
return (self._with_str()
+ 'SELECT %s FROM %s' % (columns, from_)
+ where + group_by + having + window + self._order_by_str
+ self._limit_offset_str + for_)
# Monkey-patch the python-sql library with an implementation of Select
# that supports DISTINCT
def monkey_select(self, *args, **kwargs):
return SelectWithDistinct(args, from_=self, **kwargs)
sql.From.select = monkey_select
On Wednesday, September 14, 2016 at 11:35:03 AM UTC+2, Cédric Krier wrote:
>
> On 2016-09-14 10:35, Cédric Krier wrote:
> > On 2016-09-14 00:10, Ruben Nielsen wrote:
> > > Any plans on adding SELECT DISTINCT? I see in the code, that there is
> a
> > > TODO comment in place.
> >
> > Usually DISTINCT can be avoided by using a GROUP BY.
> > But it should not be too difficult to implement.
>
> Here is: https://python-sql.tryton.org/bug53
>
> --
> Cédric Krier - B2CK SPRL
> Email/Jabber: cedric...@b2ck.com <javascript:>
> Tel: +32 472 54 46 59
> Website: http://www.b2ck.com/
>
--
You received this message because you are subscribed to the Google Groups
"python-sql" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to python-sql+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
