Introspecting Legacy Databases - Open-Source Tool

Hello! This article presents a simple open-source tool for legacy databases introspection (tables definitions and data). The project can be used to extract complete tables information (fields, constraints, foreign keys, .. etc) and also data. The code is published on Github under the MIT License. Thanks for reading!


✨ Project Features

  • 👉 Minimal set up, Python-based tool
  • 👉 Supported DB: SQLite, MySql, PostgreSQL
  • 👉 Helpers: Dump Tables Definition (SQL and Model)
  • 👉 Dump Tables Data: CSV format

Available helpers can be invoked and used inside Python CLI. Curious minds can have a quick look over the generated files here (output directory): Tables SQL Definitions, Table Dump Sample.

Database Introspection Tool - Execution Commands

✨ How to use the tool

This tool can be used in any environment that has Python3 and GIT installed.

Step #1 - Clone the sources
$ git clone https://github.com/app-generator/devtool-db.git
$ cd devtool-db
Database Introspection Tool - Clone Sources
Step #2 - Install the dependencies
$ virtualenv env
$ source env/bin/activate
$ pip install -r requirements.txt
Database Introspection Tool - Install Modules
Step #3 - Launch the Python console
$ python
>>> 
>>> from util import *                                # import helpers     
>>>                    
>>> db_sqlite = DbWrapper()                           # invoke the Base Class  
>>> db_sqlite.driver = COMMON.DB_SQLITE               # set driver
>>> db_sqlite.db_name = 'samples/api-django.sqlite3'  # set db name
>>> db_sqlite.connect()                               # connect 
True 
>>> db_sqlite.load_models()                           # load DB SChema 
True
>>> db_sqlite.dump_tables()                           # Dump tables definitions 
True
>>> db_sqlite.dump_tables_data()                      # Dump data
 > Dump data for [api_user_user]
 > Dump data for [api_authentication_activesession]
...
(truncated data)
...
 > Dump data for [django_migrations]
 > Dump data for [django_session]
True
>>> db_sqlite.reset()                                  # reset data  
>>>
Database Introspection Tool - Execution Process

The above code chunk does the following tasks:

  • Invoke the database wrapper class DbWrapper()
  • Set up the database credentials: Driver, Name .. etc
  • Connect to the Database via connect() helper
  • load_models() inspect tables metadata

From this point, we are able to access all information related to tables and data. The output files are saved in the output directory.


Thanks for reading! For more resources, feel free to access: