背景
懒人必备导设计文档的数据库表结构
制作时间:2022年9月5日12:39:36
作者:huishao用途:一键输出数据库设计文档中的数据表
准备工作
导库
备注:docx是python3版本的docx操作库,直接install下载为Python2版本的,不兼容Python3
pip install psycopg2
pip install python-docx
脚本代码
#!/usr/bin/python3
# 引入依赖
from docx import Document
from docx.shared import Inches
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.oxml.ns import nsdecls
from docx.oxml import parse_xml
import psycopg2
# # 打开数据库连接
db = psycopg2.connect(database="oa", user="root", password="xxxxx", host="192.168.2.249", port="54321")
# 处理 None
def xstr(s):
return '' if s is None or s=='None' else str(s)
schema_name = input('数据库名:')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 cursor() 方法创建一个游标对象 cursor1
cursor1 = db.cursor()
# 获取文档对象
document = Document()
# 获取所有要遍历的表名
cursor.execute("""SELECT "TABLE_NAME" FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'active4j-test'""")
row = cursor.fetchall()
tables = [n[0] for n in row]
# 循环数据库中的表
for t in tables:
# 查询表的列名、数据类型、字符长度、是否允许空、默认值、备注信息
cursor.execute(f"""SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT,col_description ( A.attrelid, A.attnum ) AS COMMENT FROM INFORMATION_SCHEMA.COLUMNS,pg_class AS C,pg_attribute AS A WHERE table_schema ='{schema_name}' AND table_name='{t}' AND C.relname = '{t}' AND A.attrelid = C.oid AND A.attnum > 0 AND A.attname = "COLUMN_NAME" """)
# 文档添加换行符
document.add_paragraph()
# 文档添加表名
document.add_paragraph('表名:%s' % t)
# 文档添加表注释
cursor1.execute(f"""select obj_description(c.oid) from pg_class c where obj_description(c.oid) is not null AND relname = '{t}'""")
document.add_paragraph(i[0] for i in cursor1.fetchall())
# 文档添加表格
table = document.add_table(rows=1, cols=6)
# 表格为灰色线条表格
table.style = 'Table Grid'
# 表格列宽度
table.columns[0].width = Inches(0.49)
# 表格第一行的属性赋值
hdr_cells = table.rows[0].cells
hdr_cells[0].add_paragraph('字段名').alignment = WD_ALIGN_PARAGRAPH.CENTER
hdr_cells[1].add_paragraph('数据类型').alignment = WD_ALIGN_PARAGRAPH.CENTER
hdr_cells[2].add_paragraph('长度').alignment = WD_ALIGN_PARAGRAPH.CENTER
hdr_cells[3].add_paragraph('允许为空').alignment = WD_ALIGN_PARAGRAPH.CENTER
hdr_cells[4].add_paragraph('默认值').alignment = WD_ALIGN_PARAGRAPH.CENTER
hdr_cells[5].add_paragraph('描述').alignment = WD_ALIGN_PARAGRAPH.CENTER
# 数据库游标获取各个数据库每个字段的各个属性,包括字符名称、数据类型、是否允许空、默认值、备注等信息
table_fileds = cursor.fetchall()
#对字段进行遍历,row为每个字段,下面的[0],[1]这些为获取到的属性,比如row_cell[0]获取的是字段名称,row_cell[1]获取的是数据类型
for row in table_fileds:
row_cells = table.add_row().cells
row_cells[0].text = xstr(row[0])
row_cells[1].text = xstr(row[1])
row_cells[2].text = xstr(str(row[2]))
row_cells[3].text = xstr(row[3])
row_cells[4].text = xstr(str(row[4]))
row_cells[5].text = xstr(str(row[5]))
# 输出处理的表以及其字段的各个属性
print('正在处理数据库表: ' + t + '...')
_fields = [list(map(str, f)) for f in table_fileds]
print(_fields)
# 首行加底纹
shading_list = locals()
for i in range(6):
shading_list['shading_elm_' + str(i)] = parse_xml(
r'<w:shd {} w:fill="{bgColor}"/>'.format(nsdecls('w'), bgColor='cccccc'))
table.rows[0].cells[i]._tc.get_or_add_tcPr().append(shading_list['shading_elm_' + str(i)])
document.add_page_break()
# 此处可自定义路径,默认为脚本所在的路径下
document.save('demo.docx')
# 关闭数据库连接
db.close()
# 测试数据库连接
"""
用途:可测试数据库是否连通,连不通直接无法继续
"""
import psycopg2
# # 打开数据库连接
db = psycopg2.connect(database="oa", user="root", password="xxxxxx", host="192.168.2.249", port="54321")
cursor = db.cursor()
cursor.execute(""" SELECT "TABLE_NAME" FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'active4j-test' """)
for i in cursor.fetchall():
print(i[0])
# 此处输出有数据即可为成功