Django与sqlite3中“变量过多”的错误
我在使用Django中的sqlite3时遇到了这个错误：
异常值：SQL变量太多了
我觉得这个问题的答案可以在这里找到，来自这里：
很多SQL程序员都知道用问号（"?"）作为占位符。SQLite也支持用":"、"$"或"@"开头的命名占位符，以及形如"?123"的编号占位符。
为了防止过多的内存分配，单个占位符的最大值是SQLITE_MAX_VARIABLE_NUMBER，默认值是999。
不过，我还有一个不明白的地方，就是同样的查询在Django的shell会话中（用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 dbshell）返回完整的结果集，没有错误，和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)
查询
'SELECT "screen_company"."id", "screen_company"."name" FROM "screen_company" WHERE "screen_company"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY "screen_company"."name" ASC'
（顺便说一下，你不需要使用pdb来调试——Django在浏览器中非常友好地显示了错误发生时的回溯信息（在render_to_response()中？），并列出了每一步的局部变量，这样你就可以看到完整的查询了。）
不过，我之前确实尝试过用pdb逐步调试Django的代码，发现错误实际上是来自Python的pysqlite2.dbapi2（或sqlite3.dbapi2）模块，在django/db/backends/sqlite3/base.py的第200行：
return Database.Cursor.execute(self, query, params)
（这也在错误页面显示的有用回溯中），其中Database是pysqlite2.dbapi2或sqlite3.dbapi2的别名，具体取决于你的Python版本。
但我觉得调试到调用栈的这个深度有点太远了（暂时），所以决定停止，开始考虑解决方法并在网上搜索答案。 :)
6 个回答
4
这个问题是因为SQLite的限制，没办法绕过。不过在MySQL上，你的查询可以正常运行。如果你需要做这种查询，可能说明你的做法有问题。你应该重新设计一下你的MySQL数据库结构。如果实在无法改动，那就考虑把这个查询拆分成100个小查询来执行。
20
我在网上搜索这个错误信息时找到了这里，所以我想分享一下我的解决办法。
在我的情况下，这个错误是由以下代码引起的：
Event.objects.all().delete()
可能是Django在尝试执行“删除操作，条件是id在(?,?,?,...)中”，而这个id的列表太长了。
对我来说，性能不是问题，所以我通过以下方式解决了这个问题：
while Event.objects.count():
ids = Event.objects.values_list('pk', flat=True)[:100]
Event.objects.filter(pk__in = ids).delete()
27
其实这些限制可以在这里找到：
https://www.sqlite.org/c3ref/c_limit_attached.html#sqlitelimitvariablenumber
下面是一些解释：
单个SQL语句中主机参数的最大数量
主机参数是SQL语句中的一个占位符，它会通过sqlite3_bind_XXXX()接口来填充。很多SQL程序员都知道用问号（"?"）作为主机参数。SQLite还支持以“:”、“$”或“@”开头的命名主机参数，以及形如"?123"的编号主机参数。
在SQLite语句中，每个主机参数都有一个编号。这个编号通常从1开始，每增加一个参数就加1。不过，当使用"?123"这种形式时，主机参数的编号就是问号后面的数字。
SQLite会为所有主机参数分配空间，这个空间的范围是从1到使用的最大主机参数编号。因此，如果SQL语句中有像?1000000000这样的主机参数，就需要占用好几个GB的存储空间。这可能会轻易超出主机的资源限制。为了防止过多的内存分配，主机参数编号的最大值是SQLITE_MAX_VARIABLE_NUMBER，默认值是999。
在运行时，可以通过sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size)接口来降低最大主机参数编号。