Renaming sqlite3 table column

Question

I am struggling to rename my sqlite3 column, with python.

if n[:1] == 'B': 
     c.execute('''ALTER TABLE '''+n+''' RENAME COLUMN DRcode TO CRcodetemp''')

Where n = ‘B1’ and type(n) is <class 'str'> and c = conn.cursor()

Error:

c.execute('''ALTER TABLE '''+n+''' RENAME COLUMN DRcode TO CRcodetemp''')                                                              
sqlite3.OperationalError: near "COLUMN": syntax error

Thanks

You could temporarily replace the "c.execute" by a "print" to see what exactly is sent to sqlite (and to show it in the question). — Michael Butscher, May 06 '20 at 19:09
Renaming a column is possible since version 3.25.0: https://sqlite.org/releaselog/3_25_1.html — forpas, May 06 '20 at 19:19
Thanks. How do I check my sqlite3 version. printing sqlite3.version gives 2.6.0 which I believe refers to the python version sqlite is using? — tedioustortoise, May 06 '20 at 19:22

score 0 · Answer 1 · answered May 06 '20 at 19:16

0

Based on other questions which I missed, it seems that the only plausible way of doing this is creating a temp table and then creating a new table with the correct column names.

if n[:1] == 'B':  # For Bank Accounts
            # rename table, create new table, and put value in correct place
            c.execute('''ALTER TABLE '''+n+''' RENAME TO temp''')
            c.execute('''CREATE TABLE '''+n+''' (id, CRcodetemp)''')
            c.execute('''INSERT INTO '''+n+'''(id, CRcodetemp)
                         SELECT id, DRcode
                         FROM temp''')
            c.execute('''DROP TABLE temp''')

answered May 06 '20 at 19:16

tedioustortoise

259
3
20

1

That's incorrect @Ollie. sqlite allows you to rename a column. Your suggestion is required for removing a column. – Balaji Ambresh May 06 '20 at 20:12
If you read [the documentation](https://www.sqlite.org/lang_altertable.html) you'll see that order of operations is explicitly called out as incorrect. – Shawn May 06 '20 at 20:49

score 0 · Answer 2 · answered May 06 '20 at 19:26

Works fine here:

In [1]: import sqlite3

In [2]: db = sqlite3.connect(':memory:')
Out[2]: <sqlite3.Connection at 0x803bfbab0>

In [3]: db.execute('CREATE TABLE B1(foo TEXT, DRcode INT)')
Out[3]: <sqlite3.Cursor at 0x803b30960>

In [4]: db.execute('ALTER TABLE B1 RENAME COLUMN DRcode TO CRcodetemp')
Out[4]: <sqlite3.Cursor at 0x803b30c00>

Version info:

In [5]: import sys

In [6]: sys.version
Out[6]: '3.7.7 (default, Mar 19 2020, 19:56:47)'

In [7]: sqlite3.version
Out[7]: '2.6.0'

Note that the sqlite3 module version is not the same as the sqlite version. In my case the latter is: SQLite version 3.31.1.

Renaming sqlite3 table column

2 Answers2