We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Create canonical ordering for tables and return it by default
## Problem
<!-- Please provide a clear and concise description of the problem that this feature request is designed to solve.-->
We should have a canonical ordering for each table whenever possible. This will make infinite scroll easier to deal with.

## Proposed solution
<!-- A clear and concise description of your proposed solution or feature. -->
We should order by primary key by default if the table has one. Otherwise, we should use the entire row as a sorting key (it won't be possible to tell the difference if two identical rows "switch order"). We should always return rows in these orders when they are accessed unless the user specifies a different `ORDER BY`.

## Additional context
<!-- Add any other context or screenshots about the feature request here.-->
- Please see #361

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

{"golden_diff": "diff --git a/db/records.py b/db/records.py\n--- a/db/records.py\n+++ b/db/records.py\n@@ -72,6 +72,17 @@\n                   field, in addition to an 'value' field if appropriate.\n                   See: https://github.com/centerofci/sqlalchemy-filters#filters-format\n     \"\"\"\n+    if not order_by:\n+        # Set default ordering if none was requested\n+        if len(table.primary_key.columns) > 0:\n+            # If there are primary keys, order by all primary keys\n+            order_by = [{'field': col, 'direction': 'asc'}\n+                        for col in table.primary_key.columns]\n+        else:\n+            # If there aren't primary keys, order by all columns\n+            order_by = [{'field': col, 'direction': 'asc'}\n+                        for col in table.columns]\n+\n     query = _get_query(table, limit, offset, order_by, filters)\n     return _execute_query(query, engine)\n", "issue": "Create canonical ordering for tables and return it by default\n## Problem\r\n<!-- Please provide a clear and concise description of the problem that this feature request is designed to solve.-->\r\nWe should have a canonical ordering for each table whenever possible. This will make infinite scroll easier to deal with.\r\n\r\n## Proposed solution\r\n<!-- A clear and concise description of your proposed solution or feature. -->\r\nWe should order by primary key by default if the table has one. Otherwise, we should use the entire row as a sorting key (it won't be possible to tell the difference if two identical rows \"switch order\"). We should always return rows in these orders when they are accessed unless the user specifies a different `ORDER BY`.\r\n\r\n## Additional context\r\n<!-- Add any other context or screenshots about the feature request here.-->\r\n- Please see #361\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\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 table.columns[col.name]\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    table_columns = _create_col_objects(table, group_by)\n    count_query = (\n        select(*table_columns, func.count(table_columns[0]))\n        .group_by(*table_columns)\n    )\n    if filters is not None:\n        count_query = apply_filters(count_query, filters)\n    filtered_count_query = _get_filtered_group_by_count_query(\n        table, engine, group_by, limit, offset, order_by, filters, count_query\n    )\n    if filtered_count_query is not None:\n        records = _execute_query(filtered_count_query, engine)\n        # Last field is the count, preceding fields are the group by fields\n        counts = {(*record[:-1],): record[-1] for record in records}\n    else:\n        counts = {}\n    return counts\n\n\ndef _get_filtered_group_by_count_query(\n        table, engine, group_by, limit, offset, order_by, filters, count_query\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_subtable_query = _get_query(table, limit, offset, order_by, filters)\n    relevant_subtable_cte = relevant_subtable_query.cte()\n    cte_columns = _create_col_objects(relevant_subtable_cte, group_by)\n    distinct_tuples = get_distinct_tuple_values(cte_columns, engine, output_table=table)\n    if distinct_tuples:\n        limited_filters = [\n            {\n                \"or\": [\n                    distinct_tuples_to_filter(distinct_tuple_spec)\n                    for distinct_tuple_spec in distinct_tuples\n                ]\n            }\n        ]\n        filtered_count_query = apply_filters(count_query, limited_filters)\n    else:\n        filtered_count_query = None\n    return filtered_count_query\n\n\ndef get_distinct_tuple_values(\n        column_list, engine, table=None, limit=None, offset=None, output_table=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    if output_table is not None:\n        column_objects = [output_table.columns[col.name] for col in column_objects]\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"}], "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 table.columns[col.name]\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    if not order_by:\n        # Set default ordering if none was requested\n        if len(table.primary_key.columns) > 0:\n            # If there are primary keys, order by all primary keys\n            order_by = [{'field': col, 'direction': 'asc'}\n                        for col in table.primary_key.columns]\n        else:\n            # If there aren't primary keys, order by all columns\n            order_by = [{'field': col, 'direction': 'asc'}\n                        for col in table.columns]\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    table_columns = _create_col_objects(table, group_by)\n    count_query = (\n        select(*table_columns, func.count(table_columns[0]))\n        .group_by(*table_columns)\n    )\n    if filters is not None:\n        count_query = apply_filters(count_query, filters)\n    filtered_count_query = _get_filtered_group_by_count_query(\n        table, engine, group_by, limit, offset, order_by, filters, count_query\n    )\n    if filtered_count_query is not None:\n        records = _execute_query(filtered_count_query, engine)\n        # Last field is the count, preceding fields are the group by fields\n        counts = {(*record[:-1],): record[-1] for record in records}\n    else:\n        counts = {}\n    return counts\n\n\ndef _get_filtered_group_by_count_query(\n        table, engine, group_by, limit, offset, order_by, filters, count_query\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_subtable_query = _get_query(table, limit, offset, order_by, filters)\n    relevant_subtable_cte = relevant_subtable_query.cte()\n    cte_columns = _create_col_objects(relevant_subtable_cte, group_by)\n    distinct_tuples = get_distinct_tuple_values(cte_columns, engine, output_table=table)\n    if distinct_tuples:\n        limited_filters = [\n            {\n                \"or\": [\n                    distinct_tuples_to_filter(distinct_tuple_spec)\n                    for distinct_tuple_spec in distinct_tuples\n                ]\n            }\n        ]\n        filtered_count_query = apply_filters(count_query, limited_filters)\n    else:\n        filtered_count_query = None\n    return filtered_count_query\n\n\ndef get_distinct_tuple_values(\n        column_list, engine, table=None, limit=None, offset=None, output_table=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    if output_table is not None:\n        column_objects = [output_table.columns[col.name] for col in column_objects]\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"}]}