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!

• 👉 Database Introspection Tool - source code (MIT License)
• 🎁 Free Support via email and Discord

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

✨ 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

Step #2 - Install the dependencies

$ virtualenv env
$ source env/bin/activate
$ pip install -r requirements.txt

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

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.

• SQLITE.sql - SQL definitions for all tables of api-django.sqlite3 sample
• SQLITE_api_user_user.sql - table data

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

• ✨ AppSeed for more tools and free starters
• ✨ More sample projects published on Github