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
Hide
Easily highlight source code for your blog with our Syntax Highlighter. Join Siafoo Now or Learn More

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/