How to drop all tables from the database with manage.py CLI in Django?

Question

How can I drop all tables from a database using manage.py and command line? Is there any way to do that executing manage.py with appropriate parameters so I can execute it from a .NET application?

Answer 1

As far as I know there is no management command to drop all tables. If you don't mind hacking Python you can write your own custom command to do that. You may find the sqlclear option interesting. Documentation says that ./manage.py sqlclear Prints the DROP TABLE SQL statements for the given app name(s).

Update: Shamelessly appropriating @Mike DeSimone's comment below this answer to give a complete answer.

./manage.py sqlclear | ./manage.py dbshell

As of django 1.9 it's now ./manage.py sqlflush

Answer 2

There's no native Django management command to drop all tables. Both sqlclear and reset require an app name.

However, you can install Django Extensions which gives you manage.py reset_db, which does exactly what you want (and gives you access to many more useful management commands).

Answer 3

If you're using the South package to handle database migrations (highly recommended), then you could just use the ./manage.py migrate appname zero command.

Otherwise, I'd recommend the ./manage.py dbshell command, piping in SQL commands on standard input.

Answer 4

python manage.py migrate <app> zero

sqlclear was removed from 1.9.

Release notes mention that it is due to the introduction of migrations: https://docs.djangoproject.com/en/1.9/releases/1.9/

Unfortunately I could not find a method that works on all apps at once, nor a built-in way to list all installed apps from the admin: How to list all installed apps with manage.py in Django?

Related: How to reset migrations in Django 1.7?

Answer 5

It is better to use ./manage.py sqlflush | ./manage.py dbshell because sqlclear requires app to flush.

Answer 6

simple(?) way to do it from python (on mysql):

from django.db import connection

cursor = connection.cursor()
cursor.execute('show tables;')
parts = ('DROP TABLE IF EXISTS %s;' % table for (table,) in cursor.fetchall())
sql = 'SET FOREIGN_KEY_CHECKS = 0;\n' + '\n'.join(parts) + 'SET FOREIGN_KEY_CHECKS = 1;\n'
connection.cursor().execute(sql)

Answer 7

If you want to completely wipe the database and resync it in the same go you need something like the following. I also combine adding test data in this command:

#!/usr/bin/env python

import os
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "main.settings") # Replace with your app name.

from django.db import connection
from django.core.management import call_command
from django.conf import settings
# If you're using postgres you can't use django's sql stuff for some reason that I
# can't remember. It has to do with that autocommit thing I think.
# import psychodb2 as db

def recreateDb():
    print("Wiping database")
    dbinfo = settings.DATABASES['default']

    # Postgres version
    #conn = db.connect(host=dbinfo['HOST'], user=dbinfo['USER'],
    #                 password=dbinfo['PASSWORD'], port=int(dbinfo['PORT'] or 5432))
    #conn.autocommit = True
    #cursor = conn.cursor()
    #cursor.execute("DROP DATABASE " + dbinfo['NAME'])
    #cursor.execute("CREATE DATABASE " + dbinfo['NAME'] + " WITH ENCODING 'UTF8'") # Default is UTF8, but can be changed so lets be sure.

    # Mysql version:
    print("Dropping and creating database " + dbinfo['NAME'])
    cursor = connection.cursor()
    cursor.execute("DROP DATABASE " + dbinfo["NAME"] + "; CREATE DATABASE " + dbinfo["NAME"] + "; USE " + dbinfo["NAME"] + ";")
    print("Done")


if __name__ == "__main__":
    recreateDb();
    print("Syncing DB")
    call_command('syncdb', interactive=False)
    print("Adding test data")
    addTestData() # ...

It would be nice to be able to do cursor.execute(call_command('sqlclear', 'main')) but call_command prints the SQL to stdout rather than returning it as a string, and I can't work out the sql_delete code...

Answer 8

Here's a shell script I ended up piecing together to deal with this issue. Hope it saves someone some time.

#!/bin/sh

drop() {
    echo "Droping all tables prefixed with $1_."
    echo
    echo "show tables" | ./manage.py dbshell |
    egrep "^$1_" | xargs -I "@@" echo "DROP TABLE @@;" |
    ./manage.py dbshell
    echo "Tables dropped."
    echo
}

cancel() {
    echo "Cancelling Table Drop."
    echo
}

if [ -z "$1" ]; then
    echo "Please specify a table prefix to drop."
else
    echo "Drop all tables with $1_ prefix?"
    select choice in drop cancel;do
        $choice $1
        break
    done
fi

Answer 9

Here's an example Makefile to do some nice things with multiple settings files:

test:
    python manage.py test --settings=my_project.test

db_drop:
    echo 'DROP DATABASE my_project_development;' | ./manage.py dbshell
    echo 'DROP DATABASE my_project_test;' | ./manage.py dbshell

db_create:
    echo 'CREATE DATABASE my_project_development;' | ./manage.py dbshell
    echo 'CREATE DATABASE my_project_test;' | ./manage.py dbshell

db_migrate:
    python manage.py migrate --settings=my_project.base
    python manage.py migrate --settings=my_project.test

db_reset: db_drop db_create db_migrate

.PHONY: test db_drop db_create db_migrate db_reset

Then you can do things like: $ make db_reset

Answer 10

If you are using psql and have django-more 2.0.0 installed, you can do

manage.py reset_schema

Answer 11

The command ./manage.py sqlclear or ./manage.py sqlflush seems to clear the table and not delete them, however if you want to delete the complete database try this : manage.py flush.

Warning: this will delete your database completely and you will lose all your data, so if that not important go ahead and try it.

Answer 12

This answer is for postgresql DB:

Run: echo 'drop owned by some_user' | ./manage.py dbshell

NOTE: some_user is the name of the user you use to access the database, see settings.py file:

default_database = {
    'ENGINE': 'django.db.backends.postgresql_psycopg2',
    'NAME': 'somedbname',
    'USER': 'some_user',
    'PASSWORD': 'somepass',
    'HOST': 'postgresql',
    'PORT': '',
}

Answer 13

I would recommend you to install django-extensions and use python manage.py reset_db command. It does exactly what you want.

Answer 14

Using Python to make a flushproject command, you use :

from django.db import connection
cursor = connection.cursor()
cursor.execute(“DROP DATABASE %s;”, [connection.settings_dict['NAME']])
cursor.execute(“CREATE DATABASE %s;”, [connection.settings_dict['NAME']])

Answer 15

Here's a south migration version of @peter-g's answer. I often fiddle with raw sql, so this comes in handy as 0001_initial.py for any befuddled apps. It will only work on DBs that support SHOW TABLES (like mysql). Substitute something like SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; if you use PostgreSQL. Also, I often do this exact same thing for both the forwards and backwards migrations.

from south.db import db
from south.v2 import SchemaMigration
from django.db.utils import DatabaseError
from os import path
from logging import getLogger
logger = getLogger(__name__)


class Migration(SchemaMigration):

    def forwards(self, orm):

        app_name = path.basename(path.split(path.split(path.abspath(__file__))[0])[0])
        table_tuples = db.execute(r"SHOW TABLES;")

        for tt in table_tuples:
            table = tt[0]
            if not table.startswith(app_name + '_'):
                continue
            try:
                logger.warn('Deleting db table %s ...' % table)
                db.delete_table(table)
            except DatabaseError:
                from traceback import format_exc
                logger.error("Error running %s: \n %s" % (repr(self.forwards), format_exc()))

Coworker/cocoders would kill me if they knew I did this, though.

Answer 16

There's an even simpler answer if you want to delete ALL your tables. You just go to your folder containing the database (which may be called mydatabase.db) and right-click the .db file and push "delete." Old fashioned way, sure-fire to work.

Answer 17

Drops all tables and recreates them:

python manage.py sqlclear app1 app2 appN | sed -n "2,$p" | sed -n "$ !p" | sed "s/";/" CASCADE;/" | sed -e "1s/^/BEGIN;/" -e "$s/$/COMMIT;/" | python manage.py dbshell
python manage.py syncdb

Explanation:

manage.py sqlclear - "prints the DROP TABLE SQL statements for the given app name(s)"

sed -n "2,$p" - grabs all lines except first line

sed -n "$ !p" - grabs all lines except last line

sed "s/";/" CASCADE;/" - replaces all semicolons (;) with (CASCADE;)

sed -e "1s/^/BEGIN;/" -e "$s/$/COMMIT;/" - inserts (BEGIN;) as first text, inserts (COMMIT;) as last text

manage.py dbshell - "Runs the command-line client for the database engine specified in your ENGINE setting, with the connection parameters specified in your USER, PASSWORD, etc., settings"

manage.py syncdb - "Creates the database tables for all apps in INSTALLED_APPS whose tables have not already been created"

Dependencies:

• sed, to use on Windows, I installed UnxUtils: (Download) (Installation Instructions)

---

Credits:

@Manoj Govindan and @Mike DeSimone for sqlclear piped to dbshell

@jpic for 'sed "s/";/" CASCADE;/"'

Answer 18

A solution to remove database and migrations manually.

at the same level of manage.py, create clean.py

import os


def recursor(dirpath):
    # print(dirpath)
    delfiles = []
    deldirs = []
    with os.scandir(dirpath) as l1:
        for e1 in l1:
            if not e1.is_file():
                with os.scandir(e1.path) as l2:
                    for e2 in l2:
                        if e2.name == 'migrations':
                            with os.scandir(e2.path) as l3:
                                for e3 in l3:
                                    if not e3.name == '__init__.py':
                                        print(e3.path)
                                        if e3.is_file():
                                            delfiles.append(e3.path)
                                        else:
                                            deldirs.append(e3.path)
                                            with os.scandir(e3.path) as l4:
                                                for e4 in l4:
                                                    delfiles.append(e4)
    yn = input('are you sure to delete all the files above?(y/n)')
    if yn == 'y':
        for dp in delfiles:
            os.remove(dp)
        for dp in deldirs:
            os.rmdir(dp)



recursor(os.path.dirname(os.path.realpath(__file__)))

delete db.sqlite3 file and run clean.py

Answer 19

In django 4.1, the answer is to use manage.py flush. See the docs.

Answer 20

use "python manage.py sqlflush" command in windows 10 for others type manage.py