Sqlite3 Python: can’t use “limit” as column name

There are 6 columns and for some reason when my program gets to this bit of code during install, it simply creates a blank file with no table.

Through trial and error, I found the only thing that did not create a blank file was removing the limit row.

I have other code that runs and looks the same just for different databases and it works fine.

        try:
            # Connect to Database
            conn = sqlite3.connect('databases/Categories.db')
            cur = conn.cursor()

            # Create Table
            cur.execute("""CREATE TABLE categories (
                priority text,
                name text,
                type text,
                increment text,
                total real,
                limit real)""")

            # Commit and Close
            conn.commit()
            conn.close()
        except sqlite3.OperationalError:
            pass

Answer

“limit” is an SQL keyword, for example, as in

SELECT foo 
  FROM bar
  LIMIT 10;

If you want to use “limit” as a column name in sqlite, it needs to be quoted, in one of these ways:

• ‘limit’
• “limit”
• [limit]
• `limit`

So for example, your statement could be

        cur.execute("""CREATE TABLE categories (
            priority text,
            name text,
            type text,
            increment text,
            total real,
            "limit" real)""")

Note that it must be quoted in other statements too, for example

"""INSERT INTO categories ("limit") VALUES (?);"""