Python-sqlite如何更改表add columb（如果不存在）

Question

我想要：如果列不存在，添加列

def tablo_olustur():
    cursor.execute("CREATE TABLE IF NOT EXISTS "+asin+"(Tarih TEXT)")
    con.commit()
tablo_olustur()

def veri_ekle(Satıcı_ismi,Stok_Miktarı):
    cursor.execute("INSERT INTO "+asin+' (Tarih, "{}") values (?,?)'.format(Satıcı_ismi),(currentDT,Stok_Miktarı))
    con.commit()

#def ver_ekle2(Stok_Miktarı):
#    cursor.execute("INSERT INTO "+asin+" (Satıcı_ismi, '{}') values (?,?)".format(currentDT),(Satıcı_ismi,Stok_Miktarı))
#    con.commit()

def sutun_ekle(sutun):
    cursor.execute("ALTER TABLE "+asin+' ADD COLUMN "{}" '.format(sutun))
    con.commit()

我从python得到sqlite3.OperationalError: duplicate column name: xxxx错误

Answer 1

您可以尝试这样做，如果列存在，它将添加，如果不存在，您可以检查表中是否已经存在同名的任何列

def sutun_ekle(sutun):
    try:
        cursor.execute("ALTER TABLE "+asin+' ADD COLUMN "{}" '.format(sutun))
        con.commit()
    except:
        cursor.execute("PRAGMA table_info(asin)")
        print cursor.fetchall() #prints the list of existing column header

Answer 2

您需要检查表以查看列是否存在：

# replace your_table with your table you want to inspect
existing_cols = [row.column_name for row in cursor.columns(table='your_table')

# check if the column you want to add is in the list
if col_add in existing_cols:
    pass  # do nothing
else:
    # alter table
    # replace your_table with the table you want to alter
    # replace varchar(32) with the data type for the column
    cursor.execute("ALTER TABLE your_table ADD COLUMN {} varchar(32)".format(col_add)
    con.commit()