17th March 2013

Alter sqlite3 tables

alter_table is a python utility to alter sqlite3 tables. It allows, with limitations, for several operations not directly supported in sqlite:

  • Drop columns
  • Rename columns: it supports also renaming the special rowid column
  • Reorder columns

All operations are performed from the command line. The supported arguments are:

  • First argument: the filename with the sqlite3 database.
  • Next arguments: operations to perform, which can be one of:
    • table:-column drops the specified column in the given table.
    • table:column@position modifies the position of the column in the table. The position is zero- based, and is handled in the order specified on the command line.
    • table:column=new_name renames the specified column in the given table with the new provided name
    An argument can provide several operations at once, like in: table:column=new_name:-column

Example:

python alter_table.py db.sqlite movies:rowid=id:name=title:title@2

In this case, the table movies is modified: the current primary key (rowid) is renamed as id, the column name is renamed as title, and positioned as 3rd column in the table.

The utility generates a new database, on a file on the same path with the same name as the original, but prepending altered-

Restrictions

When a column in a table is dropped renamed, the related indexes are automatically recreated (or removed), reflecting the new names given to the columns.

There could be additional side effects on altering tables: triggers and views require additional updates, but alter_table does not perform these. So the user must ensure proper recreation of triggers and views if required (indexes are properly updated)

To perform the modifications, this script effectively drops the affected tables, recreating them and transferring the data: if there are foreign key constraints against the tables being dropped, the script will fail with a sqlite exception: foreign keys are currently not properly handled.

Download

  • Current version is 0.20, released the 22nd March 2013: alter_table.py

History:

  • 22nd March 2013: version 0.20
    • Columns can be now reordered
    • Special column rowid can be renamed.
    • Better checks on command line arguments.
  • 19th March 2013: version 0.11, solves a bug where columns in tables would be defined in lower case
  • Initial version: 0.1, released the 17th March 2013

alter_table is open source, with the following generic python license:

Copyright (c) Luis M. Pena <lu@coderazzi.net>  All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are
met:

Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
Redistributions in bytecode form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in
the documentation and/or other materials provided with the
distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR
CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.