We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Undesirable record grouping behaviours
## Description
Record grouping has a set of behaviours, that are not desirable.
* It considers order_by, which leads to formation of incorrect query on the backend, if we don't group by the sorted column.
![Screenshot 2021-07-13 at 12 05 01 AM](https://user-images.githubusercontent.com/11850603/125338760-3630af00-e36e-11eb-9240-68cd524c4eec.png)

* It considers limit and offset. These apply on the grouped result itself, and is unrelated to the record limit & offset.
![Screenshot 2021-07-13 at 12 02 20 AM](https://user-images.githubusercontent.com/11850603/125338797-45aff800-e36e-11eb-8845-91c128a21606.png)
![Screenshot 2021-07-13 at 12 02 42 AM](https://user-images.githubusercontent.com/11850603/125338811-49dc1580-e36e-11eb-8f5e-ca0560b41239.png)


## Expected behavior
* It should not consider order_by.
* It should not consider limit and offset.

We could also probably have a dedicated API for this. It could also obtain the values for columns, to filter the grouped results. Having it as part of records API makes less sense, since the group count is not a reflection of the record results.

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `db/records.py`
Content:
```
1 import logging
2 from sqlalchemy import delete, select, Column, func
3 from sqlalchemy.inspection import inspect
4 from sqlalchemy_filters import apply_filters, apply_sort
5 from sqlalchemy_filters.exceptions import FieldNotFound
6 
7 from db.constants import ID
8 
9 logger = logging.getLogger(__name__)
10 
11 
12 # Grouping exceptions follow the sqlalchemy_filters exceptions patterns
13 class BadGroupFormat(Exception):
14     pass
15 
16 
17 class GroupFieldNotFound(FieldNotFound):
18     pass
19 
20 
21 def _get_primary_key_column(table):
22     primary_key_list = list(inspect(table).primary_key)
23     # We do not support getting by composite primary keys
24     assert len(primary_key_list) == 1
25     return primary_key_list[0]
26 
27 
28 def _create_col_objects(table, column_list):
29     return [
30         table.columns[col] if type(col) == str else col
31         for col in column_list
32     ]
33 
34 
35 def get_record(table, engine, id_value):
36     primary_key_column = _get_primary_key_column(table)
37     query = select(table).where(primary_key_column == id_value)
38     with engine.begin() as conn:
39         result = conn.execute(query).fetchall()
40         assert len(result) <= 1
41         return result[0] if result else None
42 
43 
44 def get_records(
45         table, engine, limit=None, offset=None, order_by=[], filters=[],
46 ):
47     """
48     Returns records from a table.
49 
50     Args:
51         table:    SQLAlchemy table object
52         engine:   SQLAlchemy engine object
53         limit:    int, gives number of rows to return
54         offset:   int, gives number of rows to skip
55         order_by: list of dictionaries, where each dictionary has a 'field' and
56                   'direction' field.
57                   See: https://github.com/centerofci/sqlalchemy-filters#sort-format
58         filters:  list of dictionaries, where each dictionary has a 'field' and 'op'
59                   field, in addition to an 'value' field if appropriate.
60                   See: https://github.com/centerofci/sqlalchemy-filters#filters-format
61     """
62     query = select(table).limit(limit).offset(offset)
63     if order_by is not None:
64         query = apply_sort(query, order_by)
65     if filters is not None:
66         query = apply_filters(query, filters)
67     with engine.begin() as conn:
68         return conn.execute(query).fetchall()
69 
70 
71 def get_group_counts(
72         table, engine, group_by, limit=None, offset=None, order_by=[], filters=[],
73 ):
74     """
75     Returns counts by specified groupings
76 
77     Args:
78         table:    SQLAlchemy table object
79         engine:   SQLAlchemy engine object
80         limit:    int, gives number of rows to return
81         offset:   int, gives number of rows to skip
82         group_by: list or tuple of column names or column objects to group by
83         order_by: list of dictionaries, where each dictionary has a 'field' and
84                   'direction' field.
85                   See: https://github.com/centerofci/sqlalchemy-filters#sort-format
86         filters:  list of dictionaries, where each dictionary has a 'field' and 'op'
87                   field, in addition to an 'value' field if appropriate.
88                   See: https://github.com/centerofci/sqlalchemy-filters#filters-format
89     """
90     if type(group_by) not in (tuple, list):
91         raise BadGroupFormat(f"Group spec {group_by} must be list or tuple.")
92     for field in group_by:
93         if type(field) not in (str, Column):
94             raise BadGroupFormat(f"Group field {field} must be a string or Column.")
95         field_name = field if type(field) == str else field.name
96         if field_name not in table.c:
97             raise GroupFieldNotFound(f"Group field {field} not found in {table}.")
98 
99     query = (
100         select(table)
101         .limit(limit)
102         .offset(offset)
103     )
104     if order_by is not None:
105         query = apply_sort(query, order_by)
106     if filters is not None:
107         query = apply_filters(query, filters)
108     subquery = query.subquery()
109 
110     group_by = [
111         subquery.columns[col] if type(col) == str else subquery.columns[col.name]
112         for col in group_by
113     ]
114     query = select(*group_by, func.count(subquery.c[ID])).group_by(*group_by)
115     with engine.begin() as conn:
116         records = conn.execute(query).fetchall()
117 
118     # Last field is the count, preceding fields are the group by fields
119     counts = {
120         (*record[:-1],): record[-1]
121         for record in records
122     }
123     return counts
124 
125 
126 def get_distinct_tuple_values(
127         column_list, engine, table=None, limit=None, offset=None,
128 ):
129     """
130     Returns distinct tuples from a given list of columns.
131 
132     Args:
133         column_list: list of column names or SQLAlchemy column objects
134         engine:   SQLAlchemy engine object
135         table:    SQLAlchemy table object
136         limit:    int, gives number of rows to return
137         offset:   int, gives number of rows to skip
138 
139     If no table is given, the column_list must consist entirely of
140     SQLAlchemy column objects associated with a table.
141     """
142     if table is not None:
143         column_objects = _create_col_objects(table, column_list)
144     else:
145         column_objects = column_list
146     try:
147         assert all([type(col) == Column for col in column_objects])
148     except AssertionError as e:
149         logger.error("All columns must be str or sqlalchemy.Column type")
150         raise e
151 
152     query = (
153         select(*column_objects)
154         .distinct()
155         .limit(limit)
156         .offset(offset)
157     )
158     with engine.begin() as conn:
159         res = conn.execute(query).fetchall()
160     return [tuple(zip(column_objects, row)) for row in res]
161 
162 
163 def distinct_tuples_to_filter(distinct_tuples):
164     filters = []
165     for col, value in distinct_tuples:
166         filters.append({
167             "field": col,
168             "op": "==",
169             "value": value,
170         })
171     return filters
172 
173 
174 def create_record_or_records(table, engine, record_data):
175     """
176     record_data can be a dictionary, tuple, or list of dictionaries or tuples.
177     if record_data is a list, it creates multiple records.
178     """
179     id_value = None
180     with engine.begin() as connection:
181         result = connection.execute(table.insert(), record_data)
182         # If there was only a single record created, return the record.
183         if result.rowcount == 1:
184             # We need to manually commit insertion so that we can retrieve the record.
185             connection.commit()
186             id_value = result.inserted_primary_key[0]
187             if id_value is not None:
188                 return get_record(table, engine, id_value)
189     # Do not return any records if multiple rows were added.
190     return None
191 
192 
193 def create_records_from_csv(
194         table,
195         engine,
196         csv_filename,
197         column_names,
198         header,
199         delimiter=None,
200         escape=None,
201         quote=None,
202 ):
203     with open(csv_filename, 'rb') as csv_file:
204         with engine.begin() as conn:
205             cursor = conn.connection.cursor()
206             relation = '.'.join('"{}"'.format(part) for part in (table.schema, table.name))
207             formatted_columns = '({})'.format(','.join([f'"{column_name}"' for column_name in column_names]))
208 
209             copy_sql = f'COPY {relation} {formatted_columns} FROM STDIN CSV'
210             if header:
211                 copy_sql += " HEADER"
212             if delimiter:
213                 copy_sql += f" DELIMITER E'{delimiter}'"
214             if escape:
215                 copy_sql += f" ESCAPE '{escape}'"
216             if quote:
217                 if quote == "'":
218                     quote = "''"
219                 copy_sql += f" QUOTE '{quote}'"
220 
221             cursor.copy_expert(copy_sql, csv_file)
222 
223 
224 def update_record(table, engine, id_value, record_data):
225     primary_key_column = _get_primary_key_column(table)
226     with engine.begin() as connection:
227         connection.execute(
228             table.update().where(primary_key_column == id_value).values(record_data)
229         )
230     return get_record(table, engine, id_value)
231 
232 
233 def delete_record(table, engine, id_value):
234     primary_key_column = _get_primary_key_column(table)
235     query = delete(table).where(primary_key_column == id_value)
236     with engine.begin() as conn:
237         return conn.execute(query)
238
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/db/records.py b/db/records.py
--- a/db/records.py
+++ b/db/records.py
@@ -4,7 +4,6 @@
 from sqlalchemy_filters import apply_filters, apply_sort
 from sqlalchemy_filters.exceptions import FieldNotFound
 
-from db.constants import ID
 
 logger = logging.getLogger(__name__)
 
@@ -32,13 +31,27 @@
     ]
 
 
+def _get_query(table, limit, offset, order_by, filters):
+    query = select(table).limit(limit).offset(offset)
+    if order_by is not None:
+        query = apply_sort(query, order_by)
+    if filters is not None:
+        query = apply_filters(query, filters)
+    return query
+
+
+def _execute_query(query, engine):
+    with engine.begin() as conn:
+        records = conn.execute(query).fetchall()
+        return records
+
+
 def get_record(table, engine, id_value):
     primary_key_column = _get_primary_key_column(table)
     query = select(table).where(primary_key_column == id_value)
-    with engine.begin() as conn:
-        result = conn.execute(query).fetchall()
-        assert len(result) <= 1
-        return result[0] if result else None
+    result = _execute_query(query, engine)
+    assert len(result) <= 1
+    return result[0] if result else None
 
 
 def get_records(
@@ -59,13 +72,8 @@
                   field, in addition to an 'value' field if appropriate.
                   See: https://github.com/centerofci/sqlalchemy-filters#filters-format
     """
-    query = select(table).limit(limit).offset(offset)
-    if order_by is not None:
-        query = apply_sort(query, order_by)
-    if filters is not None:
-        query = apply_filters(query, filters)
-    with engine.begin() as conn:
-        return conn.execute(query).fetchall()
+    query = _get_query(table, limit, offset, order_by, filters)
+    return _execute_query(query, engine)
 
 
 def get_group_counts(
@@ -96,24 +104,17 @@
         if field_name not in table.c:
             raise GroupFieldNotFound(f"Group field {field} not found in {table}.")
 
-    query = (
-        select(table)
-        .limit(limit)
-        .offset(offset)
-    )
-    if order_by is not None:
-        query = apply_sort(query, order_by)
-    if filters is not None:
-        query = apply_filters(query, filters)
-    subquery = query.subquery()
+    # Get the list of groups that we should count.
+    # We're considering limit and offset here so that we only count relevant groups
+    relevant_groups_query = _get_query(table, limit, offset, order_by, filters)
+    subquery = relevant_groups_query.subquery()
 
-    group_by = [
+    columns = [
         subquery.columns[col] if type(col) == str else subquery.columns[col.name]
         for col in group_by
     ]
-    query = select(*group_by, func.count(subquery.c[ID])).group_by(*group_by)
-    with engine.begin() as conn:
-        records = conn.execute(query).fetchall()
+    count_query = select(*columns, func.count(columns[0])).group_by(*columns)
+    records = _execute_query(count_query, engine)
 
     # Last field is the count, preceding fields are the group by fields
     counts = {
@@ -155,9 +156,8 @@
         .limit(limit)
         .offset(offset)
     )
-    with engine.begin() as conn:
-        res = conn.execute(query).fetchall()
-    return [tuple(zip(column_objects, row)) for row in res]
+    result = _execute_query(query, engine)
+    return [tuple(zip(column_objects, row)) for row in result]
 
 
 def distinct_tuples_to_filter(distinct_tuples):

{"golden_diff": "diff --git a/db/records.py b/db/records.py\n--- a/db/records.py\n+++ b/db/records.py\n@@ -4,7 +4,6 @@\n from sqlalchemy_filters import apply_filters, apply_sort\n from sqlalchemy_filters.exceptions import FieldNotFound\n \n-from db.constants import ID\n \n logger = logging.getLogger(__name__)\n \n@@ -32,13 +31,27 @@\n     ]\n \n \n+def _get_query(table, limit, offset, order_by, filters):\n+    query = select(table).limit(limit).offset(offset)\n+    if order_by is not None:\n+        query = apply_sort(query, order_by)\n+    if filters is not None:\n+        query = apply_filters(query, filters)\n+    return query\n+\n+\n+def _execute_query(query, engine):\n+    with engine.begin() as conn:\n+        records = conn.execute(query).fetchall()\n+        return records\n+\n+\n def get_record(table, engine, id_value):\n     primary_key_column = _get_primary_key_column(table)\n     query = select(table).where(primary_key_column == id_value)\n-    with engine.begin() as conn:\n-        result = conn.execute(query).fetchall()\n-        assert len(result) <= 1\n-        return result[0] if result else None\n+    result = _execute_query(query, engine)\n+    assert len(result) <= 1\n+    return result[0] if result else None\n \n \n def get_records(\n@@ -59,13 +72,8 @@\n                   field, in addition to an 'value' field if appropriate.\n                   See: https://github.com/centerofci/sqlalchemy-filters#filters-format\n     \"\"\"\n-    query = select(table).limit(limit).offset(offset)\n-    if order_by is not None:\n-        query = apply_sort(query, order_by)\n-    if filters is not None:\n-        query = apply_filters(query, filters)\n-    with engine.begin() as conn:\n-        return conn.execute(query).fetchall()\n+    query = _get_query(table, limit, offset, order_by, filters)\n+    return _execute_query(query, engine)\n \n \n def get_group_counts(\n@@ -96,24 +104,17 @@\n         if field_name not in table.c:\n             raise GroupFieldNotFound(f\"Group field {field} not found in {table}.\")\n \n-    query = (\n-        select(table)\n-        .limit(limit)\n-        .offset(offset)\n-    )\n-    if order_by is not None:\n-        query = apply_sort(query, order_by)\n-    if filters is not None:\n-        query = apply_filters(query, filters)\n-    subquery = query.subquery()\n+    # Get the list of groups that we should count.\n+    # We're considering limit and offset here so that we only count relevant groups\n+    relevant_groups_query = _get_query(table, limit, offset, order_by, filters)\n+    subquery = relevant_groups_query.subquery()\n \n-    group_by = [\n+    columns = [\n         subquery.columns[col] if type(col) == str else subquery.columns[col.name]\n         for col in group_by\n     ]\n-    query = select(*group_by, func.count(subquery.c[ID])).group_by(*group_by)\n-    with engine.begin() as conn:\n-        records = conn.execute(query).fetchall()\n+    count_query = select(*columns, func.count(columns[0])).group_by(*columns)\n+    records = _execute_query(count_query, engine)\n \n     # Last field is the count, preceding fields are the group by fields\n     counts = {\n@@ -155,9 +156,8 @@\n         .limit(limit)\n         .offset(offset)\n     )\n-    with engine.begin() as conn:\n-        res = conn.execute(query).fetchall()\n-    return [tuple(zip(column_objects, row)) for row in res]\n+    result = _execute_query(query, engine)\n+    return [tuple(zip(column_objects, row)) for row in result]\n \n \n def distinct_tuples_to_filter(distinct_tuples):\n", "issue": "Undesirable record grouping behaviours\n## Description\r\nRecord grouping has a set of behaviours, that are not desirable.\r\n* It considers order_by, which leads to formation of incorrect query on the backend, if we don't group by the sorted column.\r\n![Screenshot 2021-07-13 at 12 05 01 AM](https://user-images.githubusercontent.com/11850603/125338760-3630af00-e36e-11eb-9240-68cd524c4eec.png)\r\n\r\n* It considers limit and offset. These apply on the grouped result itself, and is unrelated to the record limit & offset.\r\n![Screenshot 2021-07-13 at 12 02 20 AM](https://user-images.githubusercontent.com/11850603/125338797-45aff800-e36e-11eb-8845-91c128a21606.png)\r\n![Screenshot 2021-07-13 at 12 02 42 AM](https://user-images.githubusercontent.com/11850603/125338811-49dc1580-e36e-11eb-8f5e-ca0560b41239.png)\r\n\r\n\r\n## Expected behavior\r\n* It should not consider order_by.\r\n* It should not consider limit and offset.\r\n\r\nWe could also probably have a dedicated API for this. It could also obtain the values for columns, to filter the grouped results. Having it as part of records API makes less sense, since the group count is not a reflection of the record results.\n", "before_files": [{"content": "import logging\nfrom sqlalchemy import delete, select, Column, func\nfrom sqlalchemy.inspection import inspect\nfrom sqlalchemy_filters import apply_filters, apply_sort\nfrom sqlalchemy_filters.exceptions import FieldNotFound\n\nfrom db.constants import ID\n\nlogger = logging.getLogger(__name__)\n\n\n# Grouping exceptions follow the sqlalchemy_filters exceptions patterns\nclass BadGroupFormat(Exception):\n    pass\n\n\nclass GroupFieldNotFound(FieldNotFound):\n    pass\n\n\ndef _get_primary_key_column(table):\n    primary_key_list = list(inspect(table).primary_key)\n    # We do not support getting by composite primary keys\n    assert len(primary_key_list) == 1\n    return primary_key_list[0]\n\n\ndef _create_col_objects(table, column_list):\n    return [\n        table.columns[col] if type(col) == str else col\n        for col in column_list\n    ]\n\n\ndef get_record(table, engine, id_value):\n    primary_key_column = _get_primary_key_column(table)\n    query = select(table).where(primary_key_column == id_value)\n    with engine.begin() as conn:\n        result = conn.execute(query).fetchall()\n        assert len(result) <= 1\n        return result[0] if result else None\n\n\ndef get_records(\n        table, engine, limit=None, offset=None, order_by=[], filters=[],\n):\n    \"\"\"\n    Returns records from a table.\n\n    Args:\n        table:    SQLAlchemy table object\n        engine:   SQLAlchemy engine object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n        order_by: list of dictionaries, where each dictionary has a 'field' and\n                  'direction' field.\n                  See: https://github.com/centerofci/sqlalchemy-filters#sort-format\n        filters:  list of dictionaries, where each dictionary has a 'field' and 'op'\n                  field, in addition to an 'value' field if appropriate.\n                  See: https://github.com/centerofci/sqlalchemy-filters#filters-format\n    \"\"\"\n    query = select(table).limit(limit).offset(offset)\n    if order_by is not None:\n        query = apply_sort(query, order_by)\n    if filters is not None:\n        query = apply_filters(query, filters)\n    with engine.begin() as conn:\n        return conn.execute(query).fetchall()\n\n\ndef get_group_counts(\n        table, engine, group_by, limit=None, offset=None, order_by=[], filters=[],\n):\n    \"\"\"\n    Returns counts by specified groupings\n\n    Args:\n        table:    SQLAlchemy table object\n        engine:   SQLAlchemy engine object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n        group_by: list or tuple of column names or column objects to group by\n        order_by: list of dictionaries, where each dictionary has a 'field' and\n                  'direction' field.\n                  See: https://github.com/centerofci/sqlalchemy-filters#sort-format\n        filters:  list of dictionaries, where each dictionary has a 'field' and 'op'\n                  field, in addition to an 'value' field if appropriate.\n                  See: https://github.com/centerofci/sqlalchemy-filters#filters-format\n    \"\"\"\n    if type(group_by) not in (tuple, list):\n        raise BadGroupFormat(f\"Group spec {group_by} must be list or tuple.\")\n    for field in group_by:\n        if type(field) not in (str, Column):\n            raise BadGroupFormat(f\"Group field {field} must be a string or Column.\")\n        field_name = field if type(field) == str else field.name\n        if field_name not in table.c:\n            raise GroupFieldNotFound(f\"Group field {field} not found in {table}.\")\n\n    query = (\n        select(table)\n        .limit(limit)\n        .offset(offset)\n    )\n    if order_by is not None:\n        query = apply_sort(query, order_by)\n    if filters is not None:\n        query = apply_filters(query, filters)\n    subquery = query.subquery()\n\n    group_by = [\n        subquery.columns[col] if type(col) == str else subquery.columns[col.name]\n        for col in group_by\n    ]\n    query = select(*group_by, func.count(subquery.c[ID])).group_by(*group_by)\n    with engine.begin() as conn:\n        records = conn.execute(query).fetchall()\n\n    # Last field is the count, preceding fields are the group by fields\n    counts = {\n        (*record[:-1],): record[-1]\n        for record in records\n    }\n    return counts\n\n\ndef get_distinct_tuple_values(\n        column_list, engine, table=None, limit=None, offset=None,\n):\n    \"\"\"\n    Returns distinct tuples from a given list of columns.\n\n    Args:\n        column_list: list of column names or SQLAlchemy column objects\n        engine:   SQLAlchemy engine object\n        table:    SQLAlchemy table object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n\n    If no table is given, the column_list must consist entirely of\n    SQLAlchemy column objects associated with a table.\n    \"\"\"\n    if table is not None:\n        column_objects = _create_col_objects(table, column_list)\n    else:\n        column_objects = column_list\n    try:\n        assert all([type(col) == Column for col in column_objects])\n    except AssertionError as e:\n        logger.error(\"All columns must be str or sqlalchemy.Column type\")\n        raise e\n\n    query = (\n        select(*column_objects)\n        .distinct()\n        .limit(limit)\n        .offset(offset)\n    )\n    with engine.begin() as conn:\n        res = conn.execute(query).fetchall()\n    return [tuple(zip(column_objects, row)) for row in res]\n\n\ndef distinct_tuples_to_filter(distinct_tuples):\n    filters = []\n    for col, value in distinct_tuples:\n        filters.append({\n            \"field\": col,\n            \"op\": \"==\",\n            \"value\": value,\n        })\n    return filters\n\n\ndef create_record_or_records(table, engine, record_data):\n    \"\"\"\n    record_data can be a dictionary, tuple, or list of dictionaries or tuples.\n    if record_data is a list, it creates multiple records.\n    \"\"\"\n    id_value = None\n    with engine.begin() as connection:\n        result = connection.execute(table.insert(), record_data)\n        # If there was only a single record created, return the record.\n        if result.rowcount == 1:\n            # We need to manually commit insertion so that we can retrieve the record.\n            connection.commit()\n            id_value = result.inserted_primary_key[0]\n            if id_value is not None:\n                return get_record(table, engine, id_value)\n    # Do not return any records if multiple rows were added.\n    return None\n\n\ndef create_records_from_csv(\n        table,\n        engine,\n        csv_filename,\n        column_names,\n        header,\n        delimiter=None,\n        escape=None,\n        quote=None,\n):\n    with open(csv_filename, 'rb') as csv_file:\n        with engine.begin() as conn:\n            cursor = conn.connection.cursor()\n            relation = '.'.join('\"{}\"'.format(part) for part in (table.schema, table.name))\n            formatted_columns = '({})'.format(','.join([f'\"{column_name}\"' for column_name in column_names]))\n\n            copy_sql = f'COPY {relation} {formatted_columns} FROM STDIN CSV'\n            if header:\n                copy_sql += \" HEADER\"\n            if delimiter:\n                copy_sql += f\" DELIMITER E'{delimiter}'\"\n            if escape:\n                copy_sql += f\" ESCAPE '{escape}'\"\n            if quote:\n                if quote == \"'\":\n                    quote = \"''\"\n                copy_sql += f\" QUOTE '{quote}'\"\n\n            cursor.copy_expert(copy_sql, csv_file)\n\n\ndef update_record(table, engine, id_value, record_data):\n    primary_key_column = _get_primary_key_column(table)\n    with engine.begin() as connection:\n        connection.execute(\n            table.update().where(primary_key_column == id_value).values(record_data)\n        )\n    return get_record(table, engine, id_value)\n\n\ndef delete_record(table, engine, id_value):\n    primary_key_column = _get_primary_key_column(table)\n    query = delete(table).where(primary_key_column == id_value)\n    with engine.begin() as conn:\n        return conn.execute(query)\n", "path": "db/records.py"}], "after_files": [{"content": "import logging\nfrom sqlalchemy import delete, select, Column, func\nfrom sqlalchemy.inspection import inspect\nfrom sqlalchemy_filters import apply_filters, apply_sort\nfrom sqlalchemy_filters.exceptions import FieldNotFound\n\n\nlogger = logging.getLogger(__name__)\n\n\n# Grouping exceptions follow the sqlalchemy_filters exceptions patterns\nclass BadGroupFormat(Exception):\n    pass\n\n\nclass GroupFieldNotFound(FieldNotFound):\n    pass\n\n\ndef _get_primary_key_column(table):\n    primary_key_list = list(inspect(table).primary_key)\n    # We do not support getting by composite primary keys\n    assert len(primary_key_list) == 1\n    return primary_key_list[0]\n\n\ndef _create_col_objects(table, column_list):\n    return [\n        table.columns[col] if type(col) == str else col\n        for col in column_list\n    ]\n\n\ndef _get_query(table, limit, offset, order_by, filters):\n    query = select(table).limit(limit).offset(offset)\n    if order_by is not None:\n        query = apply_sort(query, order_by)\n    if filters is not None:\n        query = apply_filters(query, filters)\n    return query\n\n\ndef _execute_query(query, engine):\n    with engine.begin() as conn:\n        records = conn.execute(query).fetchall()\n        return records\n\n\ndef get_record(table, engine, id_value):\n    primary_key_column = _get_primary_key_column(table)\n    query = select(table).where(primary_key_column == id_value)\n    result = _execute_query(query, engine)\n    assert len(result) <= 1\n    return result[0] if result else None\n\n\ndef get_records(\n        table, engine, limit=None, offset=None, order_by=[], filters=[],\n):\n    \"\"\"\n    Returns records from a table.\n\n    Args:\n        table:    SQLAlchemy table object\n        engine:   SQLAlchemy engine object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n        order_by: list of dictionaries, where each dictionary has a 'field' and\n                  'direction' field.\n                  See: https://github.com/centerofci/sqlalchemy-filters#sort-format\n        filters:  list of dictionaries, where each dictionary has a 'field' and 'op'\n                  field, in addition to an 'value' field if appropriate.\n                  See: https://github.com/centerofci/sqlalchemy-filters#filters-format\n    \"\"\"\n    query = _get_query(table, limit, offset, order_by, filters)\n    return _execute_query(query, engine)\n\n\ndef get_group_counts(\n        table, engine, group_by, limit=None, offset=None, order_by=[], filters=[],\n):\n    \"\"\"\n    Returns counts by specified groupings\n\n    Args:\n        table:    SQLAlchemy table object\n        engine:   SQLAlchemy engine object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n        group_by: list or tuple of column names or column objects to group by\n        order_by: list of dictionaries, where each dictionary has a 'field' and\n                  'direction' field.\n                  See: https://github.com/centerofci/sqlalchemy-filters#sort-format\n        filters:  list of dictionaries, where each dictionary has a 'field' and 'op'\n                  field, in addition to an 'value' field if appropriate.\n                  See: https://github.com/centerofci/sqlalchemy-filters#filters-format\n    \"\"\"\n    if type(group_by) not in (tuple, list):\n        raise BadGroupFormat(f\"Group spec {group_by} must be list or tuple.\")\n    for field in group_by:\n        if type(field) not in (str, Column):\n            raise BadGroupFormat(f\"Group field {field} must be a string or Column.\")\n        field_name = field if type(field) == str else field.name\n        if field_name not in table.c:\n            raise GroupFieldNotFound(f\"Group field {field} not found in {table}.\")\n\n    # Get the list of groups that we should count.\n    # We're considering limit and offset here so that we only count relevant groups\n    relevant_groups_query = _get_query(table, limit, offset, order_by, filters)\n    subquery = relevant_groups_query.subquery()\n\n    columns = [\n        subquery.columns[col] if type(col) == str else subquery.columns[col.name]\n        for col in group_by\n    ]\n    count_query = select(*columns, func.count(columns[0])).group_by(*columns)\n    records = _execute_query(count_query, engine)\n\n    # Last field is the count, preceding fields are the group by fields\n    counts = {\n        (*record[:-1],): record[-1]\n        for record in records\n    }\n    return counts\n\n\ndef get_distinct_tuple_values(\n        column_list, engine, table=None, limit=None, offset=None,\n):\n    \"\"\"\n    Returns distinct tuples from a given list of columns.\n\n    Args:\n        column_list: list of column names or SQLAlchemy column objects\n        engine:   SQLAlchemy engine object\n        table:    SQLAlchemy table object\n        limit:    int, gives number of rows to return\n        offset:   int, gives number of rows to skip\n\n    If no table is given, the column_list must consist entirely of\n    SQLAlchemy column objects associated with a table.\n    \"\"\"\n    if table is not None:\n        column_objects = _create_col_objects(table, column_list)\n    else:\n        column_objects = column_list\n    try:\n        assert all([type(col) == Column for col in column_objects])\n    except AssertionError as e:\n        logger.error(\"All columns must be str or sqlalchemy.Column type\")\n        raise e\n\n    query = (\n        select(*column_objects)\n        .distinct()\n        .limit(limit)\n        .offset(offset)\n    )\n    result = _execute_query(query, engine)\n    return [tuple(zip(column_objects, row)) for row in result]\n\n\ndef distinct_tuples_to_filter(distinct_tuples):\n    filters = []\n    for col, value in distinct_tuples:\n        filters.append({\n            \"field\": col,\n            \"op\": \"==\",\n            \"value\": value,\n        })\n    return filters\n\n\ndef create_record_or_records(table, engine, record_data):\n    \"\"\"\n    record_data can be a dictionary, tuple, or list of dictionaries or tuples.\n    if record_data is a list, it creates multiple records.\n    \"\"\"\n    id_value = None\n    with engine.begin() as connection:\n        result = connection.execute(table.insert(), record_data)\n        # If there was only a single record created, return the record.\n        if result.rowcount == 1:\n            # We need to manually commit insertion so that we can retrieve the record.\n            connection.commit()\n            id_value = result.inserted_primary_key[0]\n            if id_value is not None:\n                return get_record(table, engine, id_value)\n    # Do not return any records if multiple rows were added.\n    return None\n\n\ndef create_records_from_csv(\n        table,\n        engine,\n        csv_filename,\n        column_names,\n        header,\n        delimiter=None,\n        escape=None,\n        quote=None,\n):\n    with open(csv_filename, 'rb') as csv_file:\n        with engine.begin() as conn:\n            cursor = conn.connection.cursor()\n            relation = '.'.join('\"{}\"'.format(part) for part in (table.schema, table.name))\n            formatted_columns = '({})'.format(','.join([f'\"{column_name}\"' for column_name in column_names]))\n\n            copy_sql = f'COPY {relation} {formatted_columns} FROM STDIN CSV'\n            if header:\n                copy_sql += \" HEADER\"\n            if delimiter:\n                copy_sql += f\" DELIMITER E'{delimiter}'\"\n            if escape:\n                copy_sql += f\" ESCAPE '{escape}'\"\n            if quote:\n                if quote == \"'\":\n                    quote = \"''\"\n                copy_sql += f\" QUOTE '{quote}'\"\n\n            cursor.copy_expert(copy_sql, csv_file)\n\n\ndef update_record(table, engine, id_value, record_data):\n    primary_key_column = _get_primary_key_column(table)\n    with engine.begin() as connection:\n        connection.execute(\n            table.update().where(primary_key_column == id_value).values(record_data)\n        )\n    return get_record(table, engine, id_value)\n\n\ndef delete_record(table, engine, id_value):\n    primary_key_column = _get_primary_key_column(table)\n    query = delete(table).where(primary_key_column == id_value)\n    with engine.begin() as conn:\n        return conn.execute(query)\n", "path": "db/records.py"}]}