如何使用 Django 中的 manage.py CLI 从数据库中删除所有表？

小开

最佳答案

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

小开

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.

小开

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

小开

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

小开

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

小开

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

小开

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)

小开

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.

小开

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.

小开

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;/"'

小开

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

小开

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.

小开

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?

小开

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

小开

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

小开

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

小开

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

manage.py reset_schema

小开

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

小开

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

小开

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