Add column to table

Date: March 11th 2016
Last updated: March 11th 2016

Adding a column to an existing table can be problematic if the table already contains records. This entry shows how to add default values for existing rows and for new records when adding a new column. The example follows on from the make table notebook entry.

import sqlite3

# connect
con = sqlite3.connect('gitbook.db')

# cursor
c = con.cursor()

# execute
c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct} DEFAULT '{df}'"\
        .format(tn='scoreboard', cn='name', ct='TEXT', df='player'))
# commit
con.commit()

# close
con.close()

This code has created a column in the table scoreboard called name. When scores are added and no name is given the default entry will read player.

sqlite> PRAGMA table_info(scoreboard);
# 0|score|DECIMAL|0||1
# 1|name|TEXT|0|'player'|0 #<==== new column

Note the position of player in the table info.

# connect
con = sqlite3.connect('gitbook.db')

# cursor
c = con.cursor()

# execute
c.execute("INSERT INTO scoreboard (score) VALUES (12.3)")

# commit
con.commit()

# close
con.close()

Two sets of values received the same default name, player. The code above only shows the last entry. Another thing to note is the method of writing the SQL statement. I have added the table name (scoreboard) and column name (score) directly to the SQL statement. This is in contrast to using the format function in the first code block above.