Python Sqlite3 Add column to table starting with digit

Question

I am trying to use the ALTER TABLE command to add a column to an existing table. I have not found a solution to get around an unrecognized token error because of the leading digit for the column name.

Column name example: column = 1abc

So far I have tried the following with no luck.

sql = '''ALTER TABLE {table} ADD COLUMN {column} {data_type};'''.format(table=table, column=column, data_type=data_type)
self.cursor.execute(sql)

sql = '''ALTER TABLE ? ADD COLUMN ? ?;'''
self.cursor.execute(sql, (table, column, data_type))

sql = '''ALTER TABLE %s ADD COLUMN %s %s;''' % (table, column, data_type)
self.cursor.execute(sql)

I understand I need to parametize the query, but I am unsure how to get it to work with the ALTER TABLE command.

The error output I am getting:

unrecognized token: "1abc"

Answer 1

The column name must be quoted with double quotes^*:

>>> conn = sqlite3.connect(':memory:')
>>> DDL1 = """CREATE TABLE test ("col1" TEXT);"""
>>> cur.execute(DDL1)
<sqlite3.Cursor object at 0x7f98a67ead50>
>>> conn.commit()
>>> DDL2 = """ALTER TABLE test ADD COLUMN "{}" TEXT"""
>>> cur.execute(DDL2.format('1abc'))
<sqlite3.Cursor object at 0x7f98a67ead50>
>>> conn.commit()
>>> cur.execute("""SELECT * FROM test;""")
<sqlite3.Cursor object at 0x7f98a67ead50>
>>> cur.description
(('col1', None, None, None, None, None, None), ('1abc', None, None, None, None, None, None))

^* Backticks (``) and square brackets [] may also be used for quoting, but the docs describe these as non-standard methods.