import pymysql
class DataDict(object):
def __init__(self, db):
self.host = '127.0.0.1'
self.port = 3306
self.user = 'root'
self.password = 'root'
self.database = db
def run(self, tables):
# 建立连接
try:
conn = pymysql.connect(host=self.host,
port=self.port,
user=self.user,
password=self.password,
database=self.database)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
print('数据库[%s:%d]连接成功' % (self.host, self.port))
except Exception:
print('数据库[%s:%d]连接失败' % (self.host, self.port))
exit(1)
# 创建文件
file_name = self.database + '.md'
dict_file = open(file_name, mode='a', encoding='UTF-8')
# 需要生成的表
tables_arr = []
if tables == 'all':
sql = "SELECT `TABLE_NAME`, `TABLE_COMMENT` FROM information_schema.TABLES WHERE TABLE_SCHEMA= '%s'" % self.database
cursor.execute(sql)
for table in cursor.fetchall():
table_name = table['TABLE_NAME']
tables_arr.append(table_name)
else:
tables_arr = table_str.split(',')
# 遍历获取表信息
for table_name in tables_arr:
table_name = table_name.strip()
# 1、获取表信息
sql = "SELECT `TABLE_COMMENT` FROM information_schema.TABLES WHERE TABLE_SCHEMA = '%s' AND `TABLE_NAME` = '%s'" % (self.database, table_name)
cursor.execute(sql)
if cursor.rowcount == 0:
print('[%s]表不存在' % table_name)
continue
table_info = cursor.fetchone()
table_comment = table_info['TABLE_COMMENT']
dict_file.write('#### %s %s' % (table_name, table_comment))
dict_file.write('\n| 字段名称 | 字段类型 | 允许NULL | 默认值 | 索引 | 字段注释 |')
dict_file.write('\n| --- | --- | --- | --- | --- | --- |')
# 2、获取表结构
field_str = "COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY, COLUMN_COMMENT"
sql = "SELECT %s FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='%s' AND `TABLE_NAME` = '%s'" % (field_str, self.database, table_name)
cursor.execute(sql)
for field in cursor.fetchall():
column_name = field['COLUMN_NAME']
column_type = field['COLUMN_TYPE']
column_nullable = field['IS_NULLABLE']
column_key = field['COLUMN_KEY']
column_default = str(field['COLUMN_DEFAULT'])
column_comment = field['COLUMN_COMMENT']
info = "| %s | %s | %s | %s | %s | %s |" % (column_name, column_type, column_nullable, column_default, column_key, column_comment)
dict_file.write('\n' + info)
dict_file.write('\n')
print('[%s]表生成完毕' % table_name)
# 关闭连接
dict_file.close()
cursor.close()
conn.close()
print('[%s]字典已生成' % file_name)
# 程序执行入口
if __name__ == '__main__':
db = input('请输入数据库名:')
dataDict = DataDict(db)
while True:
table_str = input('输入表名<t1,t2,t3>或输入<all>获取全部,输入<q>退出:\n')
if table_str == 'q':
print('Bye...')
break
dataDict.run(table_str)
原文地址:http://www.cnblogs.com/huanruke/p/16899219.html
1. 本站所有资源来源于用户上传和网络,如有侵权请邮件联系站长!
2. 分享目的仅供大家学习和交流,请务用于商业用途!
3. 如果你也有好源码或者教程,可以到用户中心发布,分享有积分奖励和额外收入!
4. 本站提供的源码、模板、插件等等其他资源,都不包含技术服务请大家谅解!
5. 如有链接无法下载、失效或广告,请联系管理员处理!
6. 本站资源售价只是赞助,收取费用仅维持本站的日常运营所需!
7. 如遇到加密压缩包,默认解压密码为"gltf",如遇到无法解压的请联系管理员!
8. 因为资源和程序源码均为可复制品,所以不支持任何理由的退款兑现,请斟酌后支付下载
声明:如果标题没有注明"已测试"或者"测试可用"等字样的资源源码均未经过站长测试.特别注意没有标注的源码不保证任何可用性