【环境】
Python 版本:3.6.5 (v3.6.5:f59c0932b4, Mar 28 2018, 17:00:18) [MSC v.1900 64 bit (AMD64)]
PyMySQL 版本:0.8.1
【代码】
#encoding: utf-8
#author: walker
#date: 2018-07-26
#summary: 打印 MySQL/MariaDB 里面的所有库名、表名和字段名
import pymysql
import pprint
DBHost = r'127.0.0.1'
DBPort = 3306
DBUser = 'root'
DBPwd = 'password'
# 忽略掉系统库
IgnoreDB = {'information_schema', 'mysql', 'performance_schema', 'sys'}
# 处理一个数据库
def ProcOneDB(dbName):
print('************ use %s ************' % dbName)
connDB = pymysql.connect(host=DBHost,
port=DBPort,
user=DBUser,
passwd=DBPwd,
db=dbName,
charset='utf8mb4')
cur = connDB.cursor()
sql = 'show tables;'
cur.execute(sql)
rowList = cur.fetchall()
tableList = list()
for row in rowList:
tableList.append(row[0])
print('tableList(%d):\n%s\n' % (len(tableList), pprint.pformat(tableList, indent=4)))
# 处理每个表
for tabName in tableList:
print('table %s ...' % tabName)
sql = "select column_name from information_schema.columns where table_schema='%s' and table_name='%s';"
sql = sql % (dbName, tabName)
cur.execute(sql)
rowList = cur.fetchall()
fieldList = list()
for row in rowList:
fieldList.append(row[0])
print('fieldList(%d):\n%s\n' % (len(fieldList), pprint.pformat(fieldList, indent=4)))
cur.close()
connDB.close()
# 处理所有数据库
def ProcAllDB():
connDB = pymysql.connect(host=DBHost,
port=DBPort,
user=DBUser,
passwd=DBPwd,
charset='utf8mb4')
cur = connDB.cursor()
sql = "show databases;"
print('input sql:' + sql)
cur.execute(sql)
rowList = cur.fetchall()
cur.close()
connDB.close()
dbList = list()
for row in rowList:
dbList.append(row[0])
print('dbList(%d):\n%s\n' % (len(dbList), pprint.pformat(dbList, indent=4)))
for dbName in dbList:
if dbName in IgnoreDB:
continue
ProcOneDB(dbName)
if __name__ == '__main__':
ProcAllDB()
*** walker ***
转载于:https://blog.51cto.com/walkerqt/2150530