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

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

提问by kmalmur

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?

如何使用 manage.py 和命令行从数据库中删除所有表？有没有办法用适当的参数执行 manage.py 以便我可以从 .NET 应用程序执行它？

回答by Manoj Govindan

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 sqlclearoption interesting. Documentation says that ./manage.py sqlclearPrints the DROP TABLE SQL statements for the given app name(s).

据我所知，没有管理命令可以删除所有表。如果您不介意 hack Python，您可以编写自己的自定义命令来执行此操作。您可能会发现该sqlclear选项很有趣。文档说./manage.py sqlclear打印给定应用程序名称的 DROP TABLE SQL 语句。

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

更新：无耻地挪用@Mike DeSimone在此答案下方的评论以给出完整答案。

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

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

从 Django 1.9 开始，现在是 ./manage.py sqlflush

回答by Mike DeSimone

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

如果您使用 South 包来处理数据库迁移（强烈推荐），那么您可以只使用该./manage.py migrate appname zero命令。

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

否则，我会推荐./manage.py dbshell命令，在标准输入的 SQL 命令中管道。

回答by Anuj Gupta

There's no native Django management command to drop all tables. Both sqlclearand resetrequire an app name.

没有本地 Django 管理命令来删除所有表。无论sqlclear和reset需要的应用程序名称。

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

但是，您可以安装Django 扩展，它为您提供manage.py reset_db，这正是您想要的（并让您可以访问更多有用的管理命令）。

回答by FeroxTL

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

最好使用，./manage.py sqlflush | ./manage.py dbshell因为 sqlclear 需要应用程序刷新。

回答by lemanyk

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

简单（？）从python（在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)

回答by Peter G

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

这是我最终拼凑起来处理这个问题的一个 shell 脚本。希望它可以节省一些时间。

#!/bin/sh

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

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

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

回答by Timmmm

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_commandprints the SQL to stdout rather than returning it as a string, and I can't work out the sql_deletecode...

能够这样做会很好，cursor.execute(call_command('sqlclear', 'main'))但是call_command将 SQL 打印到标准输出而不是将其作为字符串返回，而且我无法计算出sql_delete代码......

回答by iyogeshjoshi

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

命令./manage.py sqlclearor./manage.py sqlflush似乎清除表而不是删除它们，但是如果您想删除完整的数据库，请尝试以下操作：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.

警告：这将完全删除您的数据库，您将丢失所有数据，所以如果这不重要，请继续尝试。

回答by daino3

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

下面是一个示例 Makefile，它可以使用多个设置文件做一些不错的事情：

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

然后你可以做这样的事情： $ make db_reset

回答by Kostyantyn

This answer is for postgresql DB:

此答案适用于 postgresql 数据库：

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

运行：echo 'drop由some_user拥有' | ./manage.py dbshel​​l

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

注意：some_user是您用来访问数据库的用户名，请参阅 settings.py 文件：

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