Free SQL Help for Devs, DBAs, and the Curious

In the world of database management, there are two things that are certain: databases will grow, and documentation will lag behind. As a database administrator or developer, you know the importance of keeping your database documentation up-to-date. However, with the rapid evolution of database structures and the sheer size of databases, manual documentation can often be a tedious and time-consuming task. This is where Python scripting comes in. With Python, we can automate the process of documentation, ensuring that our database documentation is always current, accurate, and easily accessible.

Python is a powerful, flexible scripting language that interacts seamlessly with a wide range of database systems, including SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5, and Azure SQL. By writing a Python script that connects to your database and extracts schema information, you can generate a comprehensive documentation of your database structure automatically. This script can be scheduled to run at regular intervals, ensuring that your documentation is always up-to-date.

Let's start by looking at how we can use Python to connect to a SQL Server database and retrieve schema information. The Pyodbc module is a Python library that provides connectivity to ODBC databases, including SQL Server. To connect to a SQL Server database, you might use a script like this:

import pyodbc

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=sa;PWD=password')

cursor = conn.cursor()

cursor.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES")

rows = cursor.fetchall()

for row in rows:

    print(row)

This script connects to a SQL Server database, executes a query to retrieve a list of all table names, and then prints each table name. This is a very basic example, but you can see how you might extend this script to extract more detailed schema information, such as column names and data types, primary and foreign keys, indexes, and more.

Python can also be used to connect to MySQL databases using the MySQL Connector/Python module. The following script connects to a MySQL database and retrieves a list of all table names:

import mysql.connector

cnx = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='testdb')

cursor = cnx.cursor()

cursor.execute("SHOW TABLES")

rows = cursor.fetchall()

for row in rows:

    print(row)

For DB2 databases, you can use the ibm_db module to connect and extract schema information. Here's a simple script that connects to a DB2 database and retrieves a list of all table names:

import ibm_db

conn = ibm_db.connect("DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=password;", "", "")

stmt = ibm_db.exec_immediate(conn, "SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'DB2INST1'")

while ibm_db.fetch_row(stmt) != False:

    print(ibm_db.result(stmt, 0))

In conclusion, Python scripting offers a powerful solution to the challenge of maintaining up-to-date, comprehensive database documentation. By automating the process of extracting schema information and generating documentation, Python allows you to focus on more strategic tasks, while ensuring that your database documentation is always accurate and up-to-date.