58

I made a table using SQLAlchemy and forgot to add a column. I basically want to do this:

users.addColumn('user_id', ForeignKey('users.user_id'))

What's the syntax for this? I couldn't find it in the docs.

ballade4op52
  • 2,142
  • 5
  • 27
  • 42
Chris
  • 11,819
  • 19
  • 91
  • 145
  • 2
    Somewhat related: `sqlalchemy.Table` has [`.append_column()`](https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table.append_column) and `append_constraint()`. This is useful to build up sqlalchemy's metadata in separate calls, but not for altering the database objects (for which you need migrations, as discussed in the answers to this question) – Nickolay Sep 24 '19 at 13:32

12 Answers12

62

I have the same problem, and a thought of using migration library only for this trivial thing makes me
tremble. Anyway, this is my attempt so far:

def add_column(engine, table_name, column):
    column_name = column.compile(dialect=engine.dialect)
    column_type = column.type.compile(engine.dialect)
    engine.execute('ALTER TABLE %s ADD COLUMN %s %s' % (table_name, column_name, column_type))

column = Column('new_column_name', String(100), primary_key=True)
add_column(engine, table_name, column)

Still, I don't know how to insert primary_key=True into raw SQL request.

Smart Manoj
  • 5,230
  • 4
  • 34
  • 59
AlexP
  • 1,416
  • 1
  • 19
  • 26
  • 3
    `column.type.compile(engine.dialect)` is what I was looking for. Could not find it anywhere else. – user443854 Jun 21 '18 at 17:00
  • Since it's been over seven years, extended this answer a bit... https://stackoverflow.com/a/65874034/2558739 – Ben Jan 24 '21 at 17:53
28

This is referred to as database migration (SQLAlchemy doesn't support migration out of the box). You can look at using sqlalchemy-migrate to help in these kinds of situations, or you can just ALTER TABLE through your chosen database's command line utility,

James Mishra
  • 4,249
  • 4
  • 30
  • 35
Demian Brecht
  • 21,135
  • 5
  • 42
  • 46
  • 14
    So I have to migrate the entire database just to add a column? That doesn't sound like fun. Thanks! – Chris Sep 04 '11 at 17:38
  • So for ALTER TABLE, can I do that through SQLAlchemy (i.e. does it support writing straight SQLite)? But I'll get on migration – Chris Sep 04 '11 at 17:44
  • 15
    No, SQLAlchemy doesn't support database table modification, just creation. You'll have to do the `ALTER` through sqlite3. – Demian Brecht Sep 04 '11 at 17:57
  • It's a lot less fun to add a column in a version controlled world without 'migrating the entire database'... – TheGrimmScientist Oct 22 '16 at 20:40
  • 11
    @TheGrimmScientist well I see your point, but does it sounds like we have to buy a new kitchen to make coffee if I already made tea in the same kitchen? – Clain Dsilva Feb 27 '19 at 08:19
  • 1
    can I access just access the database and execute SQL commands to alter the table? – Abhishta Gatya Mar 24 '19 at 16:45
  • Nowadays alembic is the preferred tool, as described in [Mike's answer](https://stackoverflow.com/a/10329471/5320906) – snakecharmerb May 01 '21 at 17:09
26

See this section of the SQLAlchemy documentation: http://docs.sqlalchemy.org/en/latest/core/metadata.html#altering-schemas-through-migrations

Alembic is the latest software to offer this type of functionality and is made by the same author as SQLAlchemy.

Mark Amery
  • 143,130
  • 81
  • 406
  • 459
Mike
  • 1,060
  • 2
  • 11
  • 14
12

I have a database called "ncaaf.db" built with sqlite3 and a table called "games". So I would CD into the same directory on my linux command prompt and do

sqlite3 ncaaf.db
alter table games add column q4 type float

and that is all it takes! Just make sure you update your definitions in your sqlalchemy code.

appleLover
  • 14,835
  • 9
  • 33
  • 50
  • This answer is simple. It needs more votes. Just make sure you have the sqlite command line shell. https://www.sqlite.org/download.html. – ChaimG Jun 15 '21 at 17:25
  • The following worked for me as the second line: `ALTER TABLE games ADD COLUMN q4 FLOAT`; – ChaimG Jun 15 '21 at 17:44
6
from sqlalchemy import create_engine
engine = create_engine('sqlite:///db.sqlite3')

engine.execute('alter table table_name add column column_name String')
Roger Hayashi
  • 71
  • 1
  • 6
5

I had the same problem, I ended up just writing my own function in raw sql. If you are using SQLITE3 this might be useful.

Then if you add the column to your class definition at the same time it seems to do the trick.

import sqlite3 

def add_column(database_name, table_name, column_name, data_type):

  connection = sqlite3.connect(database_name)
  cursor = connection.cursor()

  if data_type == "Integer":
    data_type_formatted = "INTEGER"
  elif data_type == "String":
    data_type_formatted = "VARCHAR(100)"

  base_command = ("ALTER TABLE '{table_name}' ADD column '{column_name}' '{data_type}'")
  sql_command = base_command.format(table_name=table_name, column_name=column_name, data_type=data_type_formatted)

  cursor.execute(sql_command)
  connection.commit()
  connection.close()
chasmani
  • 2,362
  • 2
  • 23
  • 35
1

I've recently had this same issue so I took a point from AlexP in an earlier answer. The problem was in getting the new column into my program's metadata. Using sqlAlchemy's append_column functionality had some unexpected downstream effects ('str' object has no attribute 'dialect impl'). I corrected this by adding the column with DDL (MySQL database in this case) and then reflecting the table back from the DB into my metadata.

Code is as roughly as follows (modified slightly from what I have in order to reduce it to its minimal essence. I apologize for any mistakes - if there, they should be minor)...

try:
    # Use back quotes as a protection against SQL Injection Attacks. Can we do more?
    common.qry_engine.execute('ALTER TABLE %s ADD COLUMN %s %s' %
                              ('`' + self.tbl.schema + '`.`' + self.tbl.name + '`',
                               '`' + self.outputs[new_col] + '`', 'VARCHAR(50)'))
except exc.SQLAlchemyError as msg:
    raise GRError(desc='Unable to physically add derived column to table. Contact support.',
                  data=str(self.outputs), other_info=str(msg))

try:    # Refresh the metadata to show the new column
    self.tbl = sqlalchemy.Table(self.tbl.name, self.tbl.metadata, extend_existing=True, autoload=True)
except exc.SQLAlchemyError as msg:
    raise GRError(desc='Unable to establish metadata for new column. Contact support.',
                  data=str(self.outputs), other_info=str(msg))
Ben
  • 4,798
  • 3
  • 21
  • 35
1

Yes you can Install sqlalchemy-migrate (pip install sqlalchemy-migrate) and use it in your script to call Table and Column create() method:

from sqlalchemy import String, MetaData, create_engine
from migrate.versioning.schema import Table, Column

db_engine =  create_engine(app.config.get('SQLALCHEMY_DATABASE_URI'))
db_meta = MetaData(bind=db_engine)

table = Table('tabel_name' , db_meta)
col = Column('new_column_name', String(20), default='foo')
    col.create(table)
1

If using docker:

  • go to the terminal of the container holding your DB
  • get into the db: psql -U usr [YOUR_DB_NAME]
  • now you can alter tables using raw SQL: alter table [TABLE_NAME] add column [COLUMN_NAME] [TYPE]

Note you will need to have mounted your DB for the changes to persist between builds.

Scurrae
  • 31
  • 4
0

Just continuing the simple way proposed by chasmani, little improvement

'''
# simple migration
# columns to add: 
# last_status_change = Column(BigInteger, default=None) 
# last_complete_phase = Column(String, default=None)  
# complete_percentage = Column(DECIMAL, default=0.0)
'''

import sqlite3

from config import APP_STATUS_DB
from sqlalchemy import types


def add_column(database_name: str, table_name: str, column_name: str, data_type: types, default=None):
    ret = False

    if default is not None:
        try:
            float(default)
            ddl = ("ALTER TABLE '{table_name}' ADD column '{column_name}' '{data_type}' DEFAULT {default}")
        except:
            ddl = ("ALTER TABLE '{table_name}' ADD column '{column_name}' '{data_type}' DEFAULT '{default}'")
    else:
        ddl = ("ALTER TABLE '{table_name}' ADD column '{column_name}' '{data_type}'")

    sql_command = ddl.format(table_name=table_name, column_name=column_name, data_type=data_type.__name__,
                             default=default)
    try:
        connection = sqlite3.connect(database_name)
        cursor = connection.cursor()
        cursor.execute(sql_command)
        connection.commit()
        connection.close()
        ret = True
    except Exception as e:
        print(e)
        ret = False
    return ret


add_column(APP_STATUS_DB, 'procedures', 'last_status_change', types.BigInteger)
add_column(APP_STATUS_DB, 'procedures', 'last_complete_phase', types.String)
add_column(APP_STATUS_DB, 'procedures', 'complete_percentage', types.DECIMAL, 0.0)
LittleEaster
  • 527
  • 7
  • 10
-2

Adding the column "manually" (not using python or SQLAlchemy) is perhaps the easiest?

Martin Alexandersson
  • 1,269
  • 10
  • 12
-4

Same problem over here. What I will do is iterating over the db and add each entry to a new database with the extra column, then delete the old db and rename the new to this one.

sharpshadow
  • 1,256
  • 1
  • 9
  • 9