pandas, pymysql을 활용해서 자동으로 데이터베이스 명세서 만들기

여러 데이터베이스 명세서를 만들어야 할 일이 생겼다.

한개도 아니고 여러개.

 

일일이 복사해서 붙여 넣을 수는 없어서, 자동으로 만들어주는 프로그램을 짰다.

아래 코드 또는 첨부한 주피터 노트북에 데이터베이스 연결 정보를 넣고 실행하면 자동으로 데이터베이스명으로 엑셀 파일이 만들어진다.

get-database-table-column-datatype.ipynb

0.00MB

 

	```
	# Test Environments
	Python v3.8.2
	# Create requirements.txt and install packages below
	pymysql==1.0.2
	pandas==1.4.1
	openpyxl==3.0.9
	```
	import pymysql.cursors
	import pandas as pd
	# Database connection
	username = 'username'
	password = 'verysecret'
	host = 'db_host'
	port = 3306
	database = 'db_name'
	connection = pymysql.connect(host=host,
	user=username,
	password=password,
	database=database,
	cursorclass=pymysql.cursors.DictCursor)
	with connection:
	with connection.cursor() as cursor:
	sql = "show tables;"
	cursor.execute(sql, ())
	tables = cursor.fetchall()
	df_tables = pd.DataFrame(data=tables)
	def get_tables(table_name):
	connection = pymysql.connect(host=host,
	user=username,
	password=password,
	database=database,
	cursorclass=pymysql.cursors.DictCursor)
	with connection:
	with connection.cursor() as cursor:
	sql = "show full columns from {};".format(table_name)
	cursor.execute(sql, ())
	columns = cursor.fetchall()
	df_columns = pd.DataFrame(data=columns)
	df_columns.insert(loc=0, column='table', value=table_name)
	df_list.append(df_columns)
	df_list = []
	tables_in = f'Tables_in_{database}'
	for table_name in df_tables[tables_in].tolist():
	get_tables(table_name)
	df_columns_all = pd.concat([df for df in df_list], axis=0)
	df_columns_all.reset_index(drop=True, inplace=True)
	df_columns_all.to_excel('./{}.xlsx'.format(database))

view raw export_tables_columns_info_to_excel.py hosted with ❤ by GitHub

위와 같이 table, field, type, collation, null, key, default, extra, privileges, comment 정보를 쉽게 얻을 수 있다.