5

Is it possible to get the maximum number of columns supported from sqlite3 at runtime? This database limitation is established with a compile-time variable SQLITE_MAX_COLUMN (see limits). The default is normally 2000 columns.

I'm looking for something accessible from either the Python or SQL interface.

Mike T
  • 41,085
  • 18
  • 152
  • 203

2 Answers2

8

This appears to be impossible in practical terms (i.e. without a very expensive brute-force approach along the lines of dan04's rather brilliant answer).

The source (1, 2) for the sqlite3 module contains no reference either to SQLITE_MAX_COLUMN or to compile-time limits in general; neither does there appear to be any way to access them from within the SQL interface.

UPDATE:

A simple modification of dan04's solution to use a binary search speeds things up considerably:

import sqlite3

def max_columns():
    db = sqlite3.connect(':memory:')
    low = 1
    high = 32767  # hard limit <http://www.sqlite.org/limits.html>
    while low < high - 1:
        guess = (low + high) // 2
        try:
            db.execute('CREATE TABLE T%d (%s)' % (
                guess, ','.join('C%d' % i for i in range(guess))
            ))
        except sqlite3.DatabaseError as ex:
            if 'too many columns' in str(ex):
                high = guess
            else:
                raise
        else:
            low = guess
    return low

Running the code above through timeit.repeat():

>>> max_columns()
2000
>>> import timeit
>>> timeit.repeat(
...     "max_columns()", 
...     setup="from __main__ import max_columns",
...     number=50
... )
[10.347190856933594, 10.0917809009552, 10.320987939834595]

... which comes to an average run-time of 30.76 / 150 = 0.205 seconds (on a 2.6 GHz quad-core machine) - not exactly fast, but likely more usable than the 15-20 seconds of the "kick it 'til it breaks" counting-from-one method.

Community
  • 1
  • 1
Zero Piraeus
  • 56,143
  • 27
  • 150
  • 160
3

A simple but inefficient way to do this from Python:

import itertools
import sqlite3

db = sqlite3.connect(':memory:')
try:
    for num_columns in itertools.count(1):
        db.execute('CREATE TABLE T%d (%s)' % (num_columns, ','.join('C%d' % i for i in range(num_columns))))
except sqlite3.DatabaseError as ex:
    if 'too many columns' in str(ex):
        print('Max columns = %d' % (num_columns - 1))
dan04
  • 87,747
  • 23
  • 163
  • 198
  • 1
    Ha! That's quite ingenious. +1 for sheer brutality, even if it does take nearly 20 seconds to run on my machine :-) – Zero Piraeus Jul 26 '13 at 06:41
  • 1
    @ZeroPiraeus: There are optimizations you could perform. For example, using exponential growth to find the upper bound and bisection to find the lower bound would reduce the number of tables created from N to O(log N). You could also start by checking 2000 and 2001 on the assumption that most SQLite builds will just use the default `SQLITE_MAX_COLUMN` option. – dan04 Jul 26 '13 at 08:06
  • And it looks like you realized that at the same time I did :-) – dan04 Jul 26 '13 at 08:07
  • 1
    Yeah ... I did consider checking for 2000 first (which obviously would speed things up even more), but didn't want to make the code any more complicated than it already was. – Zero Piraeus Jul 26 '13 at 08:09