automatically add a column if it doesn't exist when appending a table (python pandas)

Question

I am writing away lines in an SQL database,

Sometimes however there is a new feature that is not yet a column in the SQL database, and so I get an error.

Is there a way to write a line into SQL and add automatically a new column if it did not exist yet?

you need to alter the table to add the column. http://stackoverflow.com/a/2614737/4080476 — Brian Pendleton, Sep 05 '15 at 13:10
This can be done in SQL with if not exists ... else.. or a case statement. — , Sep 05 '15 at 13:14

Craig McQueen · Answer 1 · 2017-06-30T00:31:03.730

With Python, you could do something like:

def add_column_to_table(c, table_name, column_name, column_type):
    for row in c.execute('PRAGMA table_info({})'.format(table_name)):
        if row[1] == column_name:
            # print('column {} already exists in {}'.format(column_name, table_name))
            break
    else:
        # print('add column {} to {}'.format(column_name, table_name))
        c.execute('ALTER TABLE {} ADD COLUMN {} {}'.format(table_name, column_name, column_type))

c = db.cursor()
add_column_to_table(c, 'mytable', 'newcolumn', 'INTEGER')

score 0 · Answer 2 · answered Jul 30 '19 at 23:31

Let say the table name is MY_TABLE and you need to add BLOB column MY_COLUMN with default value = 0

hasColumn = 0
for row in self.conn.execute('PRAGMA table_info(MY_TABLE)'):
    if row[1] == 'MY_COLUMN':
        hasColumn = 1
        break


    if(hasColumn == 1):
        self.conn.execute('ALTER TABLE MY_TABLE ADD COLUMN MY_COLUMN BLOOB DEFAULT 0 ')

automatically add a column if it doesn't exist when appending a table (python pandas)

2 Answers2