CODEWITHSUNDEEP
mysqldjangodjango-admin
kmalmur
kmalmur

Reputation: 2839

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

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?

Upvotes: 114

Views: 164142

Answers (20)

Kostyantyn
Kostyantyn

Reputation: 5191

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': '',
}

Upvotes: 3

Manoj Govindan
Manoj Govindan

Reputation: 74675

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 | ./manage.py dbshell

Upvotes: 147

foobarbecue
foobarbecue

Reputation: 7060

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

Upvotes: 1

Weilory
Weilory

Reputation: 3101

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

Upvotes: 0

Diego Aragão
Diego Aragão

Reputation: 101

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

Upvotes: 3

alicederyn
alicederyn

Reputation: 13237

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

manage.py reset_schema

Upvotes: 3

tannu yadav
tannu yadav

Reputation: 29

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

Upvotes: -1

daino3
daino3

Reputation: 4566

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

Upvotes: 3

Ciro Santilli OurBigBook.com
Ciro Santilli OurBigBook.com

Reputation: 382512

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?

Upvotes: 35

iyogeshjoshi
iyogeshjoshi

Reputation: 539

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.

Upvotes: 2

FeroxTL
FeroxTL

Reputation: 5098

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

Upvotes: 14

Timmmm
Timmmm

Reputation: 96547

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...

Upvotes: 5

Anuj Gupta
Anuj Gupta

Reputation: 10526

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).

Upvotes: 55

Stephen C
Stephen C

Reputation: 791

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:

Credits:

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

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

Upvotes: 0

Rock Lee
Rock Lee

Reputation: 9566

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.

Upvotes: 0

hobs
hobs

Reputation: 19259

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.

Upvotes: 0

lemanyk
lemanyk

Reputation: 101

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)

Upvotes: 8

Natim
Natim

Reputation: 18092

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']])

Upvotes: 1

Peter G
Peter G

Reputation: 413

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

Upvotes: 5

Mike DeSimone
Mike DeSimone

Reputation: 42795

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.

Upvotes: 37

Related Questions