0

Using Sqlalchemy, I try to add a column to a table if it doesn't exist:

mytable = ticker +"data"
connection.execute("alter table "   + mytable    + " add column IF NOT EXISTS tokeep boolean;")

But I get this error:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS tokeep boolean' at line 1")
[SQL: alter table ABTdata add column IF NOT EXISTS tokeep boolean;]
(Background on this error at: https://sqlalche.me/e/14/f405)
Ivan
  • 7,448
  • 14
  • 69
  • 134
  • Consider using a tool like alembic for schema changes; this doesn't look like the sort of change you should need to change on the fly. – snakecharmerb Aug 15 '21 at 07:47

2 Answers2

1

ALTER TABLE ADD COLUMN has no IF NOT EXTSTS

See manual

  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)

What you can do is check if such a column exists see for example MySQL, Check if a column exists in a table with SQL

or use a tryin sqlalchemy

nbk
  • 45,398
  • 8
  • 30
  • 47
1

Read https://dev.mysql.com/doc/refman/8.0/en/alter-table.html and notice that IF NOT EXISTS is not supported syntax for ALTER TABLE ... ADD COLUMN.

Your options are:

  • Check if the table contains that column first, before you attempt to add it.

  • Try to add the column. It will return an error if the column already exists. Handle the error.

  • Try using a metadata management tool like Skeema, which analyzes your current table structure and the desired table structure, and generates the ALTER TABLE needed to ensure the columns you want are present.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828