Jak usunąć lub dodać kolumnę w SQLITE?

Chcę usunąć lub dodać kolumnę w bazie danych sqlite

Używam następującego zapytania, aby usunąć kolumnę.

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME

Ale daje błąd

System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error

ALTER TABLE SQLite

SQLite obsługuje ograniczony podzbiór ALTER TABLE. Polecenie ALTER TABLE w SQLite pozwala użytkownikowi zmienić nazwę tabeli lub dodać nową kolumnę do istniejącej tabeli. Nie można zmienić nazwy kolumny, usunąć kolumny ani dodać lub usunąć ograniczeń z tabeli.

Możesz:

1. utwórz nowy stół jako ten, który próbujesz zmienić,
2. skopiuj wszystkie dane,
3. upuść stary stół,
4. zmień nazwę nowego.

Napisałem implementację Java w oparciu o zalecany sposób Sqlite:

private void dropColumn(SQLiteDatabase db,
        ConnectionSource connectionSource,
        String createTableCmd,
        String tableName,
        String[] colsToRemove) throws java.sql.SQLException {

    List<String> updatedTableColumns = getTableColumns(tableName);
    // Remove the columns we don't want anymore from the table's list of columns
    updatedTableColumns.removeAll(Arrays.asList(colsToRemove));

    String columnsSeperated = TextUtils.join(",", updatedTableColumns);

    db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");

    // Creating the table on its new format (no redundant columns)
    db.execSQL(createTableCmd);

    // Populating the table with the data
    db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
            + columnsSeperated + " FROM " + tableName + "_old;");
    db.execSQL("DROP TABLE " + tableName + "_old;");
}

Aby uzyskać kolumnę tabeli, użyłem „PRAGMA table_info”:

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

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

    return columns;
}

Napisałem o tym na swoim blogu, możesz tam znaleźć więcej wyjaśnień:

http://udinic.wordpress.com/2012/05/09/sqlite-drop-column-support/

Jak zauważyli inni

Nie można zmienić nazwy kolumny, usunąć kolumny ani dodać lub usunąć ograniczeń z tabeli.

źródło: http://www.sqlite.org/lang_altertable.html

Chociaż zawsze możesz utworzyć nowy stół, a następnie upuścić starszy. Spróbuję wyjaśnić to obejście przykładem.

sqlite> .schema
CREATE TABLE person(
 id INTEGER PRIMARY KEY, 
 first_name TEXT,
 last_name TEXT, 
 age INTEGER, 
 height INTEGER
);
sqlite> select * from person ; 
id          first_name  last_name   age         height    
----------  ----------  ----------  ----------  ----------
0           john        doe         20          170       
1           foo         bar         25          171       

Teraz chcesz usunąć kolumnę heightz tej tabeli.

Utwórz kolejną tabelę o nazwie new_person

sqlite> CREATE TABLE new_person(
   ...>  id INTEGER PRIMARY KEY, 
   ...>  first_name TEXT, 
   ...>  last_name TEXT, 
   ...>  age INTEGER 
   ...> ) ; 
sqlite> 

Teraz skopiuj dane ze starej tabeli

sqlite> INSERT INTO new_person
   ...> SELECT id, first_name, last_name, age FROM person ;
sqlite> select * from new_person ;
id          first_name  last_name   age       
----------  ----------  ----------  ----------
0           john        doe         20        
1           foo         bar         25        
sqlite>

Teraz upuść persontabelę i zmień nazwę new_personnaperson

sqlite> DROP TABLE IF EXISTS person ; 
sqlite> ALTER TABLE new_person RENAME TO person ;
sqlite>

Więc teraz, jeśli to zrobisz .schema, zobaczysz

sqlite>.schema
CREATE TABLE "person"(
 id INTEGER PRIMARY KEY, 
 first_name TEXT, 
 last_name TEXT, 
 age INTEGER 
);

http://www.sqlite.org/lang_altertable.html

Jak widać na schemacie, obsługiwana jest tylko opcja DODAJ KOLUMNĘ. Istnieje jednak (dość ciężkie) obejście: http://www.sqlite.org/faq.html#q11

Nie możemy upuścić określonej kolumny w SQLite 3. Zobacz FAQ .

Jak zauważyli inni, ALTER TABLEoświadczenie sqlite nie obsługuje DROP COLUMN, a standardowa recepta na to nie zachowuje ograniczeń i wskaźników.

Oto kod Pythona, aby to zrobić ogólnie, zachowując wszystkie kluczowe ograniczenia i indeksy.

Przed użyciem wykonaj kopię zapasową bazy danych! Ta funkcja polega na sprawdzeniu oryginalnej instrukcji CREATE TABLE i jest potencjalnie trochę niebezpieczna - na przykład zrobi coś złego, jeśli identyfikator zawiera osadzony przecinek lub nawias.

Jeśli ktoś chciałby przyczynić się do lepszego sposobu parsowania SQL, byłoby świetnie!

AKTUALIZACJA Znalazłem lepszy sposób na analizę za pomocąsqlparsepakietuopen source. Jeśli jest jakieś zainteresowanie, opublikuję go tutaj, po prostu zostaw komentarz z prośbą o to ...

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

I przepisał odpowiedź @Udinic tak, że kod generuje zapytanie tworzenia tabeli automatycznie . To też nie potrzebuje ConnectionSource. Musi to również zrobić w ramach transakcji .

public static String getOneTableDbSchema(SQLiteDatabase db, String tableName) {
    Cursor c = db.rawQuery(
            "SELECT * FROM `sqlite_master` WHERE `type` = 'table' AND `name` = '" + tableName + "'", null);
    String result = null;
    if (c.moveToFirst()) {
        result = c.getString(c.getColumnIndex("sql"));
    }
    c.close();
    return result;
}

public 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;
}

private void dropColumn(SQLiteDatabase db, String tableName, String[] columnsToRemove) {
    db.beginTransaction();
    try {
        List<String> columnNamesWithoutRemovedOnes = getTableColumns(db, tableName);
        // Remove the columns we don't want anymore from the table's list of columns
        columnNamesWithoutRemovedOnes.removeAll(Arrays.asList(columnsToRemove));

        String newColumnNamesSeparated = TextUtils.join(" , ", columnNamesWithoutRemovedOnes);
        String sql = getOneTableDbSchema(db, tableName);
        // Extract the SQL query that contains only columns
        String oldColumnsSql = sql.substring(sql.indexOf("(")+1, sql.lastIndexOf(")"));

        db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
        db.execSQL("CREATE TABLE `" + tableName + "` (" + getSqlWithoutRemovedColumns(oldColumnsSql, columnsToRemove)+ ");");
        db.execSQL("INSERT INTO " + tableName + "(" + newColumnNamesSeparated + ") SELECT " + newColumnNamesSeparated + " FROM " + tableName + "_old;");
        db.execSQL("DROP TABLE " + tableName + "_old;");
        db.setTransactionSuccessful();
    } catch {
        //Error in between database transaction 
    } finally {
        db.endTransaction();
    }


}

Przeglądarka DB dla SQLite pozwala dodawać lub upuszczać kolumny.

W głównym widoku karty Database Structurekliknij nazwę tabeli. Włączony Modify Tablezostaje przycisk , który otwiera nowe okno, w którym możesz wybrać kolumnę / pole i je usunąć.

możesz użyć Sqlitebrowser. W trybie przeglądarki dla odpowiedniej bazy danych i tabeli, w strukturze zakładki -bazy danych, po opcji Modyfikuj tabelę, odpowiednią kolumnę można usunąć.

Poprawiłem odpowiedź user2638929 i teraz zachowuje typ kolumny, klucz podstawowy, wartość domyślną itp.

private static void dropColumn(SupportSQLiteDatabase database, String tableName, List<String> columnsToRemove){
    List<String> columnNames = new ArrayList<>();
    List<String> columnNamesWithType = new ArrayList<>();
    List<String> primaryKeys = new ArrayList<>();
    String query = "pragma table_info(" + tableName + ");";
    Cursor cursor = database.query(query);
    while (cursor.moveToNext()){
        String columnName = cursor.getString(cursor.getColumnIndex("name"));

        if (columnsToRemove.contains(columnName)){
            continue;
        }

        String columnType = cursor.getString(cursor.getColumnIndex("type"));
        boolean isNotNull = cursor.getInt(cursor.getColumnIndex("notnull")) == 1;
        boolean isPk = cursor.getInt(cursor.getColumnIndex("pk")) == 1;

        columnNames.add(columnName);
        String tmp = "`" + columnName + "` " + columnType + " ";
        if (isNotNull){
            tmp += " NOT NULL ";
        }

        int defaultValueType = cursor.getType(cursor.getColumnIndex("dflt_value"));
        if (defaultValueType == Cursor.FIELD_TYPE_STRING){
            tmp += " DEFAULT " + "\"" + cursor.getString(cursor.getColumnIndex("dflt_value")) + "\" ";
        }else if(defaultValueType == Cursor.FIELD_TYPE_INTEGER){
            tmp += " DEFAULT " + cursor.getInt(cursor.getColumnIndex("dflt_value")) + " ";
        }else if (defaultValueType == Cursor.FIELD_TYPE_FLOAT){
            tmp += " DEFAULT " + cursor.getFloat(cursor.getColumnIndex("dflt_value")) + " ";
        }
        columnNamesWithType.add(tmp);
        if (isPk){
            primaryKeys.add("`" + columnName + "`");
        }
    }
    cursor.close();

    String columnNamesSeparated = TextUtils.join(", ", columnNames);
    if (primaryKeys.size() > 0){
        columnNamesWithType.add("PRIMARY KEY("+ TextUtils.join(", ", primaryKeys) +")");
    }
    String columnNamesWithTypeSeparated = TextUtils.join(", ", columnNamesWithType);

    database.beginTransaction();
    try {
        database.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
        database.execSQL("CREATE TABLE " + tableName + " (" + columnNamesWithTypeSeparated + ");");
        database.execSQL("INSERT INTO " + tableName + " (" + columnNamesSeparated + ") SELECT "
                + columnNamesSeparated + " FROM " + tableName + "_old;");
        database.execSQL("DROP TABLE " + tableName + "_old;");
        database.setTransactionSuccessful();
    }finally {
        database.endTransaction();
    }
}

PS. Użyłem tutaj android.arch.persistence.db.SupportSQLiteDatabase, ale możesz łatwo zmodyfikować go do użyciaandroid.database.sqlite.SQLiteDatabase

Myślę, że chcesz zrobić migrację bazy danych. Upuszczanie kolumny nie istnieje w SQLite. Możesz jednak dodać dodatkową kolumnę, używając zapytania ALTER w tabeli.

Możesz użyć SQlite Administrator do zmiany nazw kolumn. Kliknij prawym przyciskiem myszy nazwę tabeli i wybierz Edytuj tabelę. Tutaj znajdziesz strukturę tabeli i możesz łatwo zmienić jej nazwę.

Ponieważ SQLite ma ograniczoną obsługę ALTER TABLE, możesz więc DODAJ kolumnę tylko na końcu tabeli LUB ZMIEŃ TABELĘ w SQLite.

Oto najlepsza odpowiedź na pytanie JAK USUNĄĆ KOLUMNĘ Z SQLITE?

odwiedź Usuń kolumnę z tabeli SQLite

Jako alternatywa:

Jeśli masz tabelę ze schematem

CREATE TABLE person(
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  age INTEGER,
  height INTEGER
);

możesz użyć CREATE TABLE...ASinstrukcji typu CREATE TABLE person2 AS SELECT id, first_name, last_name, age FROM person;, tj. pominąć kolumny, których nie chcesz. Następnie upuść oryginalny personstół i zmień nazwę nowego.

Zauważ, że ta metoda tworzy tabelę bez PODSTAWOWEGO KLUCZA i żadnych ograniczeń. Aby je zachować, wykorzystaj metody opisane w innych, aby utworzyć nową tabelę lub użyj tabeli tymczasowej jako pośredniej.

Ta odpowiedź na inne pytanie ma na celu modyfikację kolumny, ale uważam, że część odpowiedzi może również przynieść użyteczne podejście, jeśli masz wiele kolumn i nie chcesz ręcznie wpisywać większości z nich dla instrukcji INSERT:

https://stackoverflow.com/a/10385666

Możesz zrzucić bazę danych zgodnie z opisem w powyższym linku, a następnie pobrać instrukcję „create table” i szablon „insert” z tego zrzutu, a następnie postępować zgodnie z instrukcjami zawartymi we wpisie FAQ SQLite „Jak dodać lub usunąć kolumny z istniejącego tabela w SQLite. ” (FAQ znajduje się w innym miejscu na tej stronie).

Wdrożenie Pythonoparte na informacjach pod adresem http://www.sqlite.org/faq.html#q11 .

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)

Ten skrypt najpierw tworzy losową tabelę tymczasową i wstawia dane tylko niezbędnych kolumn z wyjątkiem tej, która zostanie usunięta. Następnie przywraca oryginalny stół na podstawie tabeli tymczasowej i upuszcza tabelę tymczasową.

Moje rozwiązanie, wystarczy wywołać tę metodę.

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;");
}

I pomocnicza metoda, aby uzyskać kolumny:

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;
}

public void DeleteColFromTable(String DbName, String TableName, String ColName){
    SQLiteDatabase db = openOrCreateDatabase(""+DbName+"", Context.MODE_PRIVATE, null);
    db.execSQL("CREATE TABLE IF NOT EXISTS "+TableName+"(1x00dff);");
    Cursor c = db.rawQuery("PRAGMA table_info("+TableName+")", null);
    if (c.getCount() == 0) {

    } else {
        String columns1 = "";
        String columns2 = "";
        while (c.moveToNext()) {
            if (c.getString(1).equals(ColName)) {
            } else {
                columns1 = columns1 + ", " + c.getString(1) + " " + c.getString(2);
                columns2 = columns2 + ", " + c.getString(1);
            }
            if (c.isLast()) {
                db.execSQL("CREATE TABLE IF NOT EXISTS DataBackup (" + columns1 + ");");
                db.execSQL("INSERT INTO DataBackup SELECT " + columns2 + " FROM "+TableName+";");
                db.execSQL("DROP TABLE "+TableName+"");
                db.execSQL("ALTER TABLE DataBackup RENAME TO "+TableName+";");
            }
        }
    }
}

i po prostu wywołaj metodę

DeleteColFromTable("Database name","Table name","Col name which want to delete");

Teraz możesz także używać przeglądarki DB dla SQLite do manipulowania kolumnami

przykład, aby dodać kolumnę: -

alter table student add column TOB time;

tutaj uczeń to nazwa_tabeli, a TOB to nazwa_kolumny, którą należy dodać.

Działa i jest testowany.