"Too many SQL variables" error in django with sqlite3
I'm getting this error using sqlite3 in django:
Exception Value: too many SQL variables
And I think the answer to it is this, 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."
However, there's a remaining oddity that I don't understand, which is that thesame query runs fine from a django shell session (launched with python manage.py shell), but not when the call is made from my views.py.
These are the lines of code that are causing the error:
vals = Company.objects.filter(id__in=comp_ids).values('id', 'name').order_by('name')names_map = SortedDict(vals)
where comp_ids is a set containing 1038 integer elements.
The exact same query, with an even greater no. of comp_ids (3800+) runs fine inthe django shell (launched with python manage.py shell) - the dictionary getscreated, and I could iterate through it.
I've tried breaking up comp_ids into sets of e.g. [:996] (that seemed to be thelimit before it barfed) i.e. filter(id__in=comp_ids[:996]), then the rest inthe next iteration, which would be consistent with the "no. of host parameters"explanation.
But why would it work in the django shell but not from views.py?
EDIT: Some more information:Entering the query into the sqlite shell (manage.py dbshell) returns the fullset of results with no errors, same as in the django shell (manage.py shell).
Here are the exact list of parameters (1039 elements) and the query, if you'dlike to know the details:
params
(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)
(which was also in the helpful backtrace shown in the error page), whereDatabase is an alias for either pysqlite2.dbapi2, or sqlite3.dbapi2, dependingon your python version.
But I thought that was too far down the call stack to keep debugging (for now),so decided to stop, and started thinking about workarounds and googling foranswers instead. :)
https://codehunter.cc/a/django/too-many-sql-variables-error-in-django-with-sqlite3
0 notes