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?
This question is related to
mysql
django
django-admin
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': '',
}
I would recommend you to install django-extensions and use python manage.py reset_db command. It does exactly what you want.
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']])
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;/"'
It is better to use ./manage.py sqlflush | ./manage.py dbshell because sqlclear requires app to flush.
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.
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.
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?
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).
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...
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.
If you are using psql and have django-more 2.0.0 installed, you can do
manage.py reset_schema
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 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
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
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.
Source: Stackoverflow.com