dbclear - Delete Data


Return to main dbmstools documentation page.


dbclear is a tool to delete data from tables in a database, either directly or by producing an SQL script to be executed later.

By default, all tables in the database are cleared. However, this can be restricted by schema and/or by table.


Because this script needs to get at metadata for the database, it uses the dbmsjy wrapper and a JDBC driver. See the dbmsjy page for details.


You use the tool like this:

dbmsjy.py dbclear.py [dbOptions] [generalOptions]

or, specifying JDBC details directly:

dbmsjy.py dbclear.py [jdbcOptions] [generalOptions]

Database Options:

-d dbms Name of the DBMS. Required. Values recognised are: postgres7, postgres8, mssql, oracle, mysql, hsql
-D dbName Name of the database to connect to. Required
-H dbHost Name or IP address of the database host to connect to. Optional, defaults to localhost
-P dbPort Port of the database host to connect to. Optional, defaults to the default port for the DBMS

JDBC Options:

-c driverClass Fully-qualified name of the JDBC driver class, e.g. org.postgresql.Driver. If omitted, will be defaulted based on URL. Must be present in classpath, usually by placing in the jdbc-drivers directory
-j JDBC_URL JDBC URL for connecting to the database, in the format expected by the JDBC driver, e.g. jdbc:postgresql://localhost:5432/PS30 Required

General Options:

-e tableName Specifically exclude this table (add a -e option for each table you want to exclude)
-f filepath Output file path. If omitted, output to stdout
-h Shows this screen
-i tableName Specifically include this table (add a -i option for each table you want to include)
-p password Password to use to connect to DB (if required)
-s schema Schema name to export (defaults to all schemas)
-u username Username to use to connect to DB (if required)
-v Enable verbose logging (to stderr)
-x Execute - rather than generating a script to clear the database, actually clear it using JDBC.

Long Options

--force-delete Always delete, rather than truncating
--version Print version number and exit

See the dbmsjy page for details of default JDBC drivers and URLs etc.


Clear (directly) all tables from a local Postgres database called mydb:

$ dbmsjy.py dbclear.py -u fred -p itsasecret -d postgres8 -D mydb -x

Generate an SQL script (to stdout) to clear 2 named tables from a local MySQL database:

$ dbmsjy.py dbclear.py -i tableone -i table2 -u fred -p itsasecret \
    -d mysql -D mydb

Generate an SQL script (to a specified path) to clear all tables in the specified schema from an Oracle database on host bigserver:

$ dbmsjy.py dbclear.py -s theschema -f /tmp/clearItAll.sql \
    -u fred -p itsasecret -d oracle -H bigserver -D mydb