License New BSD license
Lines 22
Keywords
Postgresql (1) SQLAlchemy (3)
Permissions
Owner: Stou S.
Group Owner: iCapsid
Viewable by Everyone
Editable by All Siafoo Users

Drop all tables and sequences from a PostgreSQL database Atom Feed 0

In Brief Given an SQLAlchemy database engine or metadata, bound to a PostgreSQL database, this snippet will drop all sequences and tables from the database. In general the SQLAlchemy / Elixir drop_all() method does the trick. However if your 'model' changes before the database was dropped, SQLAlchemy won't be aware of various foreign key relationships and because it is not doing a CASCADEd drop, it will fail to drop certain objects. This was an annoying problem in the early days of Siafoo... more
# 's
 1def drop_all_tables_and_sequences(engine):
2 '''
3 Drops all tables and sequences (but not VIEWS) from a postgres database
4 '''
5
6 sequence_sql='''SELECT sequence_name FROM information_schema.sequences
7 WHERE sequence_schema='public'
8 '''
9
10 table_sql='''SELECT table_name FROM information_schema.tables
11 WHERE table_schema='public' AND table_type != 'VIEW' AND table_name NOT LIKE 'pg_ts_%%'
12 '''
13
14 for table in [name for (name, ) in engine.execute(text(table_sql))]:
15 try:
16 engine.execute(text('DROP TABLE %s CASCADE' % table))
17 except SQLError, e:
18 print e
19
20 for seq in [name for (name, ) in engine.execute(text(sequence_sql))]:
21 try:
22 engine.execute(text('DROP SEQUENCE %s CASCADE' % seq))
23 except SQLError, e:
24 print e

Given an SQLAlchemy database engine or metadata, bound to a PostgreSQL database, this snippet will drop all sequences and tables from the database. In general the SQLAlchemy / Elixir drop_all() method does the trick. However if your 'model' changes before the database was dropped, SQLAlchemy won't be aware of various foreign key relationships and because it is not doing a CASCADEd drop, it will fail to drop certain objects. This was an annoying problem in the early days of Siafoo development, when the database was fluctuating wildly.

This is a more compact, less flexible, version of the code from http://www.luckydonkey.com/2007/11/23/postgresql-sqlalchemy-dropping-all-tables-and-sequences/