我猜您想做的是数据库迁移。在SQLite中不存在“删除”列。但是，您可以使用ALTER表查询添加一个额外的列。

基于http://www.sqlite.org/faq.html#q11上的信息在Python中实现。

import sqlite3 as db
import random
import string

QUERY_TEMPLATE_GET_COLUMNS = "PRAGMA table_info(@table_name)"
QUERY_TEMPLATE_DROP_COLUMN = """
  BEGIN TRANSACTION;
  CREATE TEMPORARY TABLE @tmp_table(@columns_to_keep);
  INSERT INTO @tmp_table SELECT @columns_to_keep FROM @table_name;
  DROP TABLE @table_name;
  CREATE TABLE @table_name(@columns_to_keep);
  INSERT INTO @table_name SELECT @columns_to_keep FROM @tmp_table;
  DROP TABLE @tmp_table;
  COMMIT;
"""

def drop_column(db_file, table_name, column_name):
    con = db.connect(db_file)
    QUERY_GET_COLUMNS = QUERY_TEMPLATE_GET_COLUMNS.replace("@table_name", table_name)
    query_res = con.execute(QUERY_GET_COLUMNS).fetchall()
    columns_list_to_keep = [i[1] for i in query_res if i[1] != column_name]
    columns_to_keep = ",".join(columns_list_to_keep)
    tmp_table = "tmp_%s" % "".join(random.sample(string.ascii_lowercase, 10))
    QUERY_DROP_COLUMN = QUERY_TEMPLATE_DROP_COLUMN.replace("@table_name", table_name)\
        .replace("@tmp_table", tmp_table).replace("@columns_to_keep", columns_to_keep)
    con.executescript(QUERY_DROP_COLUMN)
    con.close()

drop_column(DB_FILE, TABLE_NAME, COLUMN_NAME)

这个脚本首先创建随机的临时表，并只插入必要列的数据，除了将要删除的列。然后根据临时表恢复原表，删除临时表。

我的解，只需要调用这个方法。

public static void dropColumn(SQLiteDatabase db, String tableName, String[] columnsToRemove) throws java.sql.SQLException {
    List<String> updatedTableColumns = getTableColumns(db, tableName);
    updatedTableColumns.removeAll(Arrays.asList(columnsToRemove));
    String columnsSeperated = TextUtils.join(",", updatedTableColumns);

    db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
    db.execSQL("CREATE TABLE " + tableName + " (" + columnsSeperated + ");");
    db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
            + columnsSeperated + " FROM " + tableName + "_old;");
    db.execSQL("DROP TABLE " + tableName + "_old;");
}

辅助方法获取列:

public static List<String> getTableColumns(SQLiteDatabase db, String tableName) {
    ArrayList<String> columns = new ArrayList<>();
    String cmd = "pragma table_info(" + tableName + ");";
    Cursor cur = db.rawQuery(cmd, null);

    while (cur.moveToNext()) {
        columns.add(cur.getString(cur.getColumnIndex("name")));
    }
    cur.close();

    return columns;
}

正如其他人指出的那样，sqlite的ALTER TABLE语句不支持DROP COLUMN，并且标准的做法是不保留约束和索引。

下面是一些python代码，在维护所有关键约束和索引的同时，可以通用地执行此操作。

请在使用之前备份您的数据库!这个函数依赖于修改原始的CREATE TABLE语句，可能有点不安全——例如，如果标识符包含嵌入的逗号或圆括号，它就会出错。

如果有人愿意提供一种更好的解析SQL的方法，那就太好了!

我发现了一个更好的方法来解析使用开源sqlparse包。如果有任何兴趣，我会张贴在这里，只要留下评论要求它…

import re
import random

def DROP_COLUMN(db, table, column):
    columns = [ c[1] for c in db.execute("PRAGMA table_info(%s)" % table) ]
    columns = [ c for c in columns if c != column ]
    sql = db.execute("SELECT sql from sqlite_master where name = '%s'" 
        % table).fetchone()[0]
    sql = format(sql)
    lines = sql.splitlines()
    findcol = r'\b%s\b' % column
    keeplines = [ line for line in lines if not re.search(findcol, line) ]
    create = '\n'.join(keeplines)
    create = re.sub(r',(\s*\))', r'\1', create)
    temp = 'tmp%d' % random.randint(1e8, 1e9)
    db.execute("ALTER TABLE %(old)s RENAME TO %(new)s" % { 
        'old': table, 'new': temp })
    db.execute(create)
    db.execute("""
        INSERT INTO %(new)s ( %(columns)s ) 
        SELECT %(columns)s FROM %(old)s
    """ % { 
        'old': temp,
        'new': table,
        'columns': ', '.join(columns)
    })  
    db.execute("DROP TABLE %s" % temp)

def format(sql):
    sql = sql.replace(",", ",\n")
    sql = sql.replace("(", "(\n")
    sql = sql.replace(")", "\n)")
    return sql

在SQLite 3中不能删除特定的列。参见FAQ。

Kotlin解决方案，基于这里，还要:

确保临时表不存在 修复了检查默认值的类型，因为当它是Integer时返回String类型(此处报告了此问题)。 避免在希望删除的列不存在时执行任何操作。

object DbUtil {
    /** https://stackoverflow.com/a/51587449/878126 */
    @JvmStatic
    fun dropColumns(database: SQLiteDatabase, tableName: String,
        columnsToRemove: Collection<String>) {
        val columnNames: MutableList<String> = ArrayList()
        val columnNamesWithType: MutableList<String> = ArrayList()
        val primaryKeys: MutableList<String> = ArrayList()
        val query = "pragma table_info($tableName);"
        val cursor = database.rawQuery(query, null)
        val columnDefaultIndex = cursor.getColumnIndex("dflt_value")
        val columnNameIndex = cursor.getColumnIndex("name")
        val columnTypeIndex = cursor.getColumnIndex("type")
        val columnNotNullIndex = cursor.getColumnIndex("notnull")
        val columnPrimaryKeyIndex = cursor.getColumnIndex("pk")
        val sb = StringBuilder()
        var foundColumnsToRemove = false
        while (cursor.moveToNext()) {
            val columnName = cursor.getString(columnNameIndex)
            if (columnsToRemove.contains(columnName)) {
                foundColumnsToRemove = true
                continue
            }
            val columnType = cursor.getString(columnTypeIndex)
            val isNotNull = cursor.getInt(columnNotNullIndex) == 1
            val isPrimaryKey = cursor.getInt(columnPrimaryKeyIndex) == 1
            columnNames.add(columnName)
            sb.clear()
            sb.append("`$columnName` $columnType ")
            if (isNotNull)
                sb.append(" NOT NULL ")
            if (cursor.getType(columnDefaultIndex) != Cursor.FIELD_TYPE_NULL) {
                //has default value
                when (columnType.uppercase()) {
                    "INTEGER" -> sb.append(" DEFAULT ${cursor.getInt(columnDefaultIndex)} ")
                    "TEXT" -> sb.append(" DEFAULT \"${cursor.getString(columnDefaultIndex)}\" ")
                    "REAL" -> sb.append(" DEFAULT ${cursor.getFloat(columnDefaultIndex)} ")
                }
            }
            columnNamesWithType.add(sb.toString())
            if (isPrimaryKey)
                primaryKeys.add("`$columnName`")
        }
        cursor.close()
        if (!foundColumnsToRemove)
            return
        val columnNamesSeparated = TextUtils.join(", ", columnNames)
        if (primaryKeys.size > 0)
            columnNamesWithType.add("PRIMARY KEY(${TextUtils.join(", ", primaryKeys)})")
        val columnNamesWithTypeSeparated = TextUtils.join(", ", columnNamesWithType)
        database.beginTransaction()
        try {
            var newTempTableName: String
            var counter = 0
            while (true) {
                newTempTableName = "${tableName}_old_$counter"
                if (!isTableExists(database, newTempTableName))
                    break
                ++counter
            }
            database.execSQL("ALTER TABLE $tableName RENAME TO $newTempTableName;")
            database.execSQL("CREATE TABLE $tableName ($columnNamesWithTypeSeparated);")
            database.execSQL(
                "INSERT INTO $tableName ($columnNamesSeparated) SELECT $columnNamesSeparated FROM $newTempTableName;")
            database.execSQL("DROP TABLE ${newTempTableName};")
            database.setTransactionSuccessful()
        } finally {
            database.endTransaction()
        }
    }

    @JvmStatic
    fun isTableExists(database: SQLiteDatabase, tableName: String): Boolean {
        database.rawQuery(
            "select DISTINCT tbl_name from sqlite_master where tbl_name = '$tableName'", null)
            ?.use {
                return it.count > 0
            } ?: return false
    }
}