“Too many SQL variables” error in django witih sql

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 http://www.sqlite.org/limits.html:

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".

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

However, there's a remaining oddity that I don't understand, which is that the same 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 in the django shell (launched with python manage.py shell) - the dictionary gets created, and I could iterate through it.

I've tried breaking up comp_ids into sets of e.g. [:996] (that seemed to be the limit before it barfed) i.e. filter(id__in=comp_ids[:996]), then the rest in the 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 full set 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'd like 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 helpfully displays the backtrace in the browser (in render_to_response()?) when an error occurs, with a full list of the local variables at each step, so you can see the full query there.)

I had, however, previously tried stepping into the django code with pdb, and found the error was actually originating from python's pysqlite2.dbapi2 (or sqlite3.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), where Database is an alias for either pysqlite2.dbapi2, or sqlite3.dbapi2, depending on 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 for answers instead. :)

回答1:

Googling this error message brought me here so adding my solution.

In my case this error was caused by line:

Event.objects.all().delete()

Probably Django was trying to "DELETE WHERE id IN (?,?,?,..." and list of ids was to long.

Performance was not an issue for me so i solved this by:

while Event.objects.count():
    ids = Event.objects.values_list('pk', flat=True)[:100]
    Event.objects.filter(pk__in = ids).delete()

回答2:

actually those limits are given here: https://www.sqlite.org/c3ref/c_limit_attached.html#sqlitelimitvariablenumber

with an explanation:

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

回答3:

There is no way to bypass this, it's an SQLITE limitation. Your query will run fine on MySQL however. But if you have to do this kind of query, you are most probably doing it wrong. Your mysql schema should be redone. And if you can't, then you may want to break this query into 100 of small queries.

回答4:

Note: I am not Django expert

The problem is that in your query

vals = Company.objects.filter(id__in=comp_ids)...

you pass a bloated set of values and the underlaying engine cannot execute the query. Fortunately sqlalchemy, that you execute in that line, is lazy. You an execute a SELECT and then filter the entries "manually" without big performance penalties.

It would look more-less like that:

# here you should do a proper session.query(CompanyTable)
vals_all = Company.objects

vals_filtered = (item for item in vals_all if item.id in comp_ids)
vals_ordered = sorted(vals_filtered, key=attrgetter('name'))
vals_final = [(v.id, v.name) for v in vals_ordered]

回答5:

I hava found the same error when I run the line:

    Entry.objects.all().delete()

I solved the problem by

    while Entry.objects.count():
          Entry.objects.all()[0].delete()

I think it's a better idea than others.

回答6:

I had a similar problem, but when deleting just single row that had a large number of related rows in another table. Django was calling a cascading delete causing the problem, but since my code was only calling a single object delete, the solutions described in other answers could not be applied (unless I modified the Django library code, which I didn't want to do).

My solution was to first delete the related objects via raw sql query, then use the normal Django delete() on the parent object, all wrapped in a transaction.

Before:

object.delete() # Django tries to delete a large number of related object and fails

After:

from django.db import connection

Class MyObject:
    def delete_related(self):
        cur = connection.cursor()
        sql = 'delete from my_table where ...'
        cur.execute(sql, params)

...

with transaction.atomic():
    object.delete_related() # so that Django won't try (and fail) on its own
    object.delete()