環境
$ cat /etc/os-release
NAME="Amazon Linux"
VERSION="2"
ID="amzn"
ID_LIKE="centos rhel fedora"
VERSION_ID="2"
PRETTY_NAME="Amazon Linux 2"
ANSI_COLOR="0;33"
CPE_NAME="cpe:2.3:o:amazon:amazon_linux:2"
HOME_URL="https://amazonlinux.com/"
$ python3.9 -V
Python 3.9.13
必要なパッケージ
・pip install mysql-connector-python
get_all_table_count.py
#!/usr/local/bin/python3.9
import mysql.connector
def convertTuple(tup):
str = ''.join(tup)
return str
def tableCount (HOST:str,USER:str,PASSWORD:str,db:str,tablename:str) -> str:
conn = mysql.connector.connect(
host=HOST,
user=USER,
password=PASSWORD,
database=db
)
sql = f"SELECT count(*) FROM {tablename};"
cursor = conn.cursor()
cursor.execute(sql)
ROWS = cursor.fetchall()[0][0]
return ROWS
def showAllTables(HOST:str,USER:str,PASSWORD:str,db:str) -> None:
#データベースへの接続
conn = mysql.connector.connect(
host=HOST,
user=USER,
password=PASSWORD,
database=db
)
cursor = conn.cursor()
cursor.execute("SHOW TABLES")
#テーブルの一覧
for tt in cursor:
tablename = None
tablename = convertTuple(tt)
if tablename is None or db == "information_schema":
count = ""
else:
count = tableCount(HOST,USER,PASSWORD,databaseName,tablename)
print(f"{db} {tablename} {count}")
#ホスト名
HOST="abchost.ap-northeast-1.rds.amazonaws.com"
#ユーザー名
USER="user"
#パスワード
PASSWORD="password"
# データベースへの接続
conn = mysql.connector.connect(
#ホスト名
host=HOST,
#ユーザー名
user=USER,
#パスワード
password=PASSWORD
)
#カーソルの生成
cursor = conn.cursor()
#データベース取得
cursor.execute("SHOW DATABASES")
for cr in cursor:
databaseName = convertTuple(cr)
showAllTables(HOST,USER,PASSWORD,databaseName)