Inserts multiple rows into `tablename`. The `values` must be a list of dictionaries, one for each row to be inserted, each with the same set of keys. Returns the list of ids of the inserted rows. Set `seqname` to the ID if it's not the default, or to `False`
(self, tablename, values, seqname=None, _test=False)
| 1004 | return "INSERT INTO %s DEFAULT VALUES" % table |
| 1005 | |
| 1006 | def multiple_insert(self, tablename, values, seqname=None, _test=False): |
| 1007 | """ |
| 1008 | Inserts multiple rows into `tablename`. The `values` must be a list of |
| 1009 | dictionaries, one for each row to be inserted, each with the same set |
| 1010 | of keys. Returns the list of ids of the inserted rows. |
| 1011 | Set `seqname` to the ID if it's not the default, or to `False` |
| 1012 | if there isn't one. |
| 1013 | |
| 1014 | >>> db = DB(None, {}) |
| 1015 | >>> db.supports_multiple_insert = True |
| 1016 | >>> values = [{"name": "foo", "email": "foo@example.com"}, {"name": "bar", "email": "bar@example.com"}] |
| 1017 | >>> db.multiple_insert('person', values=values, _test=True) |
| 1018 | <sql: "INSERT INTO person (email, name) VALUES ('foo@example.com', 'foo'), ('bar@example.com', 'bar')"> |
| 1019 | """ |
| 1020 | if not values: |
| 1021 | return [] |
| 1022 | |
| 1023 | if not self.supports_multiple_insert: |
| 1024 | out = [ |
| 1025 | self.insert(tablename, seqname=seqname, _test=_test, **v) |
| 1026 | for v in values |
| 1027 | ] |
| 1028 | if seqname is False: |
| 1029 | return None |
| 1030 | else: |
| 1031 | return out |
| 1032 | |
| 1033 | keys = values[0].keys() |
| 1034 | # @@ make sure all keys are valid |
| 1035 | |
| 1036 | for v in values: |
| 1037 | if v.keys() != keys: |
| 1038 | raise ValueError("Not all rows have the same keys") |
| 1039 | |
| 1040 | # enforce query order for the above doctest compatibility with Py3 |
| 1041 | keys = sorted(keys) |
| 1042 | |
| 1043 | sql_query = SQLQuery( |
| 1044 | "INSERT INTO {} ({}) VALUES ".format(tablename, ", ".join(keys)) |
| 1045 | ) |
| 1046 | |
| 1047 | for i, row in enumerate(values): |
| 1048 | if i != 0: |
| 1049 | sql_query.append(", ") |
| 1050 | SQLQuery.join( |
| 1051 | [SQLParam(row[k]) for k in keys], |
| 1052 | sep=", ", |
| 1053 | target=sql_query, |
| 1054 | prefix="(", |
| 1055 | suffix=")", |
| 1056 | ) |
| 1057 | |
| 1058 | if _test: |
| 1059 | return sql_query |
| 1060 | |
| 1061 | db_cursor = self._db_cursor() |
| 1062 | if seqname is not False: |
| 1063 | sql_query = self._process_insert_query(sql_query, tablename, seqname) |