[PYTHON] SQL statement specifications that SQLite does not support and how to handle ALTER TABLE operations

Introduction

SQLite3 is easy to build, lightweight and easy to use, but some SQL statements do not support it, and there are some difficulties. For example, ALTER TABLE functions only support RENAME COLUMN and ADD COLUMN.

It took a long time to reach this specification and it is quite troublesome to deal with it, so I will leave it as a reminder. (I wanted to change the table definition, so how to do it)

How to do

The general procedure is as follows

1. Get the CREATE TABLE statement for the original table
2. Create a temporary table with the schema definition changed based on the SQL acquired in 1. Insert the data of the original table into the table created in 1.2
3. DROP TABLE the original table
4. RENAME TABLE a temporary table

It's simple to do, but the second operation is quite awkward to mechanize. The lower part is what I tried to support with Python. (Still, you need to set each time you change the schema definition ...)

SQLite 3 operation

import sqlite3

con = sqlite3.connect('test.db')
cur = con.cursor()

create_table_sql = cur.execute("select sql from sqlite_master where name = 'target_table'").fetchone()[0]
create_temp_table_sql = (  #Example of change to temporary table creation SQL
    create_table_sql
    .lower()
    .replace('target_table', 'temp_table')
    .replace(')', ', foreign key (test_fk) references test_table(id) )')  #Insert an additional statement for the foreign key
)
cur.execute(create_temp_table_sql)
cur.execute('insert into temp_table select * from target_table')
cur.execute('drop table target_table')
cur.execute('alter table temp_table rename to target_table')

cur.close()
con.close()

This makes it difficult to update a table with complicated join relationships, so is there any other good way? .. ..

reference

• SQL Features That SQLite Does Not Implement -Shortest route provisional version to change column definition (column name change, attribute change, etc.) with SQlite --Utsurogu