python - 使用 sqlite3 在 django 中出现 "Too many SQL variables"错误

Question

我在 django 中使用 sqlite3 时遇到此错误:

Exception Value: too many SQL variables

我认为答案是这样的，from here :

Many SQL programmers are familiar with using a question mark ("?") as a hostparameter. SQLite also supports named host parameters prefaced by ":", "$", or"@" and numbered host parameters of the form "?123".

To prevent excessive memory allocations, the maximum value of a host parameternumber is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999."

但是，还有一个我不明白的奇怪之处，那就是相同的查询在 django shell session 中运行良好(使用 python manage.py shell 启动)，但在从我的 views.py 进行调用时却不行。

这些是导致错误的代码行:

vals = Company.objects.filter(id__in=comp_ids).values('id', 'name').order_by('name')
names_map = SortedDict(vals)

其中 comp_ids 是一个包含 1038 个整数元素的集合。

完全相同的查询，但编号更大。 comp_ids (3800+) 在django shell(使用 python manage.py shell 启动)——字典得到创建，我可以遍历它。

我尝试将 comp_ids 分解为一组，例如[:996](这似乎是限制之前)，即 filter(id__in=comp_ids[:996])，然后在下一次迭代，这将与“主机参数数量”一致解释。

但为什么它可以在 django shell 中工作，而不是在 views.py 中工作？

编辑:更多信息:将查询输入到 sqlite shell (manage.py dbshel​​l) 会返回完整的一组没有错误的结果，与 django shell (manage.py shell) 中的相同。

以下是参数(1039 个元素)和查询的确切列表，如果您愿意的话想了解详情:

参数

(2, 3, 4, 2053, 6, 7, 2732, 10, 3737, 13, 2063, 2064, 17, 18, 21, 2393, 2052, 29, 30, 32, 2456, 35, 36, 38, 40, 2396, 42, 2731, 45, 46, 2095, 3343, 49, 2098, 2252, 53, 54, 58, 59, 2058, 62, 63, 64, 66, 67, 72, 2123, 3426, 3344, 79, 80, 81, 85, 2134, 87, 91, 92, 95, 98, 2747, 2149, 102, 104, 106, 2155, 109, 110, 3773, 2455, 2165, 118, 122, 2171, 2172, 127, 134, 135, 137, 138, 2187, 2413, 145, 148, 2414, 2198, 2199, 152, 153, 154, 2204, 157, 2210, 163, 2417, 169, 170, 2219, 172, 173, 3279, 2077, 179, 180, 181, 2230, 2231, 184, 186, 187, 2237, 190, 191, 2240, 2241, 3787, 2245, 2246, 2247, 2248, 2249, 2251, 204, 205, 207, 208, 209, 210, 213, 214, 2263, 2265, 3450, 222, 2273, 2274, 2275, 228, 2277, 2811, 231, 234, 2283, 2284, 238, 240, 243, 248, 250, 2299, 2300, 254, 2303, 2305, 258, 2307, 2308, 2309, 2310, 2311, 2312, 2313, 267, 269, 2318, 271, 2320, 2321, 2322, 2323, 2325, 278, 2327, 280, 282, 283, 285, 286, 287, 290, 2342, 295, 298, 2347, 2348, 2349, 2350, 2351, 304, 2353, 307, 309, 312, 318, 320, 321, 323, 326, 327, 328, 332, 333, 334, 2385, 338, 2387, 2388, 2389, 2390, 2391, 2392, 345, 2394, 347, 348, 350, 352, 354, 355, 356, 357, 359, 360, 2410, 364, 365, 366, 2415, 2416, 369, 2451, 373, 2422, 375, 377, 2426, 2428, 2429, 2430, 2432, 2433, 387, 2454, 391, 2441, 2443, 398, 399, 400, 401, 2553, 403, 404, 405, 406, 407, 408, 409, 410, 411, 413, 2462, 3628, 418, 2459, 420, 2469, 2470, 2472, 425, 427, 2476, 2461, 2483, 2485, 440, 441, 2490, 443, 444, 445, 446, 2496, 2497, 2499, 2501, 2502, 455, 2504, 2505, 459, 460, 2466, 2510, 463, 465, 466, 467, 469, 470, 2519, 2520, 2468, 474, 2529, 2531, 484, 486, 487, 488, 490, 491, 493, 2542, 2543, 2544, 497, 498, 499, 500, 2549, 502, 2551, 504, 505, 2554, 2555, 2556, 509, 2558, 511, 2560, 2562, 2475, 2564, 517, 2566, 2567, 2568, 2569, 523, 2572, 526, 527, 529, 536, 538, 539, 2588, 2589, 2590, 2596, 550, 552, 556, 2605, 2607, 2608, 2612, 565, 566, 567, 568, 580, 2634, 587, 2636, 2638, 2639, 2270, 595, 598, 2489, 605, 606, 609, 610, 2407, 615, 618, 619, 620, 2669, 2670, 2493, 2672, 2152, 628, 629, 630, 2680, 2682, 2684, 640, 641, 642, 2691, 645, 648, 650, 2699, 2700, 2701, 2702, 2703, 2705, 659, 2708, 661, 2712, 667, 2718, 2719, 2720, 2721, 2723, 2724, 2725, 2728, 681, 2730, 2614, 684, 2733, 2735, 2736, 689, 2739, 2741, 2746, 699, 701, 706, 707, 2757, 2759, 2760, 2508, 2763, 2766, 721, 2771, 725, 730, 2511, 732, 2170, 734, 2343, 743, 749, 2798, 2799, 752, 753, 2515, 756, 2078, 2807, 2808, 2412, 763, 2813, 766, 769, 2818, 2821, 2822, 779, 2861, 2833, 2834, 2835, 2836, 2837, 790, 802, 2852, 2854, 2856, 2550, 810, 2860, 813, 2862, 2863, 817, 2869, 2870, 2871, 824, 826, 2876, 829, 830, 2882, 836, 2885, 2886, 3574, 2890, 2892, 2893, 847, 2896, 2872, 2899, 2902, 2546, 2909, 2256, 2912, 2913, 870, 2920, 2921, 2922, 876, 2930, 887, 2937, 3903, 892, 893, 894, 895, 896, 897, 898, 899, 900, 901, 903, 904, 905, 906, 907, 908, 909, 910, 911, 2884, 2970, 927, 2977, 2978, 2979, 2980, 2981, 2545, 941, 942, 2205, 948, 949, 950, 951, 952, 953, 954, 955, 956, 957, 958, 959, 960, 961, 962, 963, 964, 965, 3014, 967, 968, 969, 970, 971, 3234, 2552, 980, 981, 982, 983, 985, 2895, 3038, 995, 996, 998, 2897, 2538, 2761, 3051, 1012, 1013, 3062, 3063, 3066, 2559, 3073, 1027, 1028, 1029, 1030, 2421, 3080, 1036, 1038, 1039, 1040, 1041, 3092, 3246, 3853, 1050, 3099, 1057, 3109, 3112, 1067, 1068, 1073, 1074, 1075, 1076, 1078, 1081, 2491, 1086, 2229, 1088, 1089, 1090, 1091, 1092, 1093, 1095, 1096, 1097, 1098, 1099, 1100, 1101, 1102, 1103, 1104, 1105, 1106, 1107, 1108, 1109, 1110, 1111, 1112, 3161, 1119, 3170, 2235, 3174, 3176, 3177, 1132, 1133, 1134, 1135, 1136, 3186, 1141, 3192, 3193, 3194, 3195, 3197, 3198, 2425, 1152, 1155, 1156, 3205, 2924, 3214, 1168, 1169, 1171, 1172, 1173, 1174, 1175, 1176, 3225, 1178, 3230, 1186, 1189, 1193, 1194, 1195, 1196, 3245, 1198, 1199, 1200, 1202, 2541, 3273, 1210, 3259, 1213, 1225, 1226, 1228, 1230, 2253, 3280, 3281, 1234, 1235, 1238, 1243, 1244, 1248, 1249, 3298, 3299, 1253, 3302, 2257, 3304, 3305, 3306, 3308, 1262, 2838, 3315, 3317, 3381, 1277, 1278, 3327, 3331, 3332, 1285, 2945, 1288, 1289, 1292, 1295, 1296, 1298, 3347, 3348, 3349, 1302, 3351, 3352, 3353, 1306, 1307, 3356, 1312, 3364, 1320, 3370, 3371, 3372, 3374, 3375, 1328, 3377, 3378, 3380, 1333, 1334, 3388, 3390, 3296, 3394, 3395, 2227, 1350, 2615, 1357, 1361, 1362, 3411, 3412, 3413, 1368, 2276, 1371, 1372, 3105, 1378, 2228, 3428, 3430, 3433, 1400, 1402, 1404, 3453, 3455, 3808, 1409, 3458, 3459, 1412, 1414, 1416, 1417, 1418, 1419, 1420, 2093, 3470, 1437, 3486, 1439, 2571, 3491, 3492, 3493, 1446, 2435, 1454, 1463, 3663, 3523, 1477, 1478, 1479, 1480, 1481, 1482, 3531, 1484, 1488, 1489, 1490, 3540, 1494, 1495, 1496, 3546, 3547, 2298, 1510, 1512, 3666, 1520, 1523, 2302, 1526, 1527, 3584, 1539, 1540, 1542, 1543, 1548, 1549, 3393, 1554, 1555, 1560, 1561, 3079, 1565, 2507, 3616, 3617, 1570, 3619, 3620, 3621, 3622, 1575, 3624, 2817, 3629, 3630, 1583, 1590, 1591, 1592, 2996, 3643, 3645, 2997, 3649, 2781, 3653, 3655, 1608, 3657, 2317, 1618, 3667, 1620, 3669, 1625, 2319, 3002, 2782, 1633, 1634, 3199, 3691, 3346, 3688, 1650, 1656, 3007, 3709, 3008, 1666, 2616, 3009, 2668, 2306, 3723, 3010, 2238, 1681, 1682, 1683, 1687, 3012, 3738, 2671, 1693, 3013, 1697, 1698, 2548, 3749, 3755, 3757, 3758, 2333, 1714, 1718, 3770, 3018, 1729, 3778, 3783, 1739, 1744, 3793, 1746, 1752, 1753, 1755, 3741, 3707, 1771, 1774, 2514, 1779, 2494, 3831, 1788, 2346, 3029, 3840, 3182, 2689, 1802, 1803, 2690, 1811, 1815, 1817, 3011, 2352, 1827, 2467, 1832, 3881, 1839, 1840, 2107, 1848, 1849, 2698, 2653, 1855, 1857, 1864, 1867, 1872, 1873, 3923, 1878, 1882, 1889, 3354, 1903, 1904, 2366, 1915, 1920, 1921, 1927, 1928, 1930, 1931, 3133, 1943, 1947, 1957, 1958, 1959, 1964, 1967, 1968, 1976, 2809, 3612, 1987, 1988, 1989, 3745, 1994, 2000, 2003, 2382, 2008, 3613, 2021, 2022, 3410, 3464, 2033, 2034, 2037, 2038, 2729, 2044)

query

'SELECT "screen_company"."id", "screen_company"."name" FROM "screen_company" WHERE "screen_company"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY "screen_company"."name" ASC'

(Incidentally, you don't need to use pdb for that - django very helpfullydisplays the backtrace in the browser (in render_to_response()?) when an error occurs, with afull list of the local variables at each step, so you can see the full querythere.)

I had, however, previously tried stepping into the django code with pdb, andfound the error was actually originating from python's pysqlite2.dbapi2 (orsqlite3.dbapi2) module, at line 200 of django/db/backends/sqlite3/base.py:

return Database.Cursor.execute(self, query, params)

(这也在错误页面中显示的有用回溯中)，其中数据库是 pysqlite2.dbapi2 或 sqlite3.dbapi2 的别名，具体取决于在你的 python 版本上。

但我认为调用堆栈太远了，无法继续调试(目前)，所以决定停下来，开始考虑解决方法并在谷歌上搜索而是回答。 :)

Answer 1

实际上这些限制在这里给出: https://www.sqlite.org/c3ref/c_limit_attached.html#sqlitelimitvariablenumber

附上解释:

Maximum Number Of Host Parameters In A Single SQL Statement

A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces. Many SQL programmers are familiar with using a question mark ("?") as a host parameter. SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123".

Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter. However, when the "?123" form is used, the host parameter number is the number that follows the question mark.

SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.

The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) interface.

https://www.sqlite.org/limits.html