--I want to version control the DB table definition --I want to review the fix
(I think that there are few new services at this time) I think that it is convenient if it is a service that has been operated so that each developer changes the definition (it is difficult for other developers to recognize) only because the master of the table definition is in the DB.
This is supposed to be executed only for the first time Subsequent table definition changes are in the form of changing the output file.
"""export_database_schema.py
Used to export the CREATE TABLE statement from the database
optional arguments:
-h, --help show this help message and exit
--user USER database user name
--password PASSWORD database password
--host HOST database host name
--port PORT database port number
--charset CHARSET Database character code
--target TARGET [TARGET ...]
DB list to be exported(Space separated)
"""
import os
import sys
import shutil
import pymysql
import argparse
ROOT_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
#Argument setting
parser = argparse.ArgumentParser()
parser.add_argument("--user", help="Database username", default="root")
parser.add_argument("--password", help="Database password", default="password")
parser.add_argument("--host", help="Database host name", default="127.0.0.1")
parser.add_argument("--port", help="Database port number", default=3306)
parser.add_argument("--charset", help="Database character code", default="utf8mb4")
parser.add_argument("--target", help="DB list to be exported(Space separated)",
default=["users", "shops"],
nargs="+")
args = parser.parse_args()
print("Connection: mysql://%s:%s@%s:%s?charset=%s" % (
args.user,
args.password,
args.host,
args.port,
args.charset))
con = pymysql.connect(
user=args.user,
password=args.password,
host=args.host,
port=args.port,
charset=args.charset)
with con.cursor() as cursor:
sql = "SHOW DATABASES"
cursor.execute(sql)
databases = cursor.fetchall()
#Export process is performed only when the specified DB exists.
for d in databases:
if d[0] not in args.target:
continue
#Create a directory for each DB(Delete once if it exists)
currentDirName = "%s/%s" % (ROOT_DIR, d[0])
if os.path.isdir(currentDirName):
shutil.rmtree(currentDirName)
os.mkdir(currentDirName)
dirname = "%s/schemas" % (currentDirName)
os.mkdir(dirname)
print("Export database: ", d[0])
sql = "USE %s" % d[0]
cursor.execute(sql)
sql = "SHOW TABLES"
cursor.execute(sql)
tables = cursor.fetchall()
for t in tables:
print("\tExporting ", t[0])
sql = "SHOW CREATE TABLE %s" % t
cursor.execute(sql)
schema = cursor.fetchone()
filename = "%s/%s.sql" % (dirname, t[0])
with open(filename, mode="w") as f:
f.write(schema[1] + ";\n\n")
con.close()
As an example, python export_database_schema.py --target users shops
By executing, the CREATE statement file will be installed with the following directory structure.
├── users
│ └── schemas #Stores the CREATE statement of the table in the users DB
└── shops
└── schemas #Stores the CREATE statement of the table in shops DB
#!/bin/bash
usage() {
cat << EOS
Usage: $0 [option]
-t TARGET target table definition directory
-d DATABASE Import destination database name
-H HOST database host name
-u USER database username
-p PASSWORD database password
-P PORT database port number
-y Prompt for execution confirmation omitted
EOS
exit 1
}
TARGET=""
DATABASE_NAME=""
HOST="127.0.0.1"
USER="root"
PASSWORD=""
PORT="3306"
AUTO_YES=false
while getopts d:t:H:u:p:P:yh OPT
do
case $OPT in
d) DATABASE_NAME=$OPTARG ;;
t) TARGET=$OPTARG ;;
H) HOST=$OPTARG ;;
u) USER=$OPTARG ;;
p) PASSWORD=$OPTARG ;;
P) PORT=$OPTARG ;;
y) AUTO_YES=true ;;
h) usage ;;
esac
done
ROOT_PATH=$(dirname "$(cd "$(dirname "${BASH_SOURCE:-$0}")" && pwd)")
if [ "${DATABASE_NAME}" == "" ]; then
echo "Please specify the database to be imported."
echo " ./$0 -d DATABASE_NAME"
exit 1
fi
if [ ! -d $ROOT_PATH/$TARGET/schemas ]; then
echo "The specified table definition directory does not exist"
echo "${ROOT_PATH}Check if the directory exists under"
echo " ./$0 -t TARGET"
exit 1
fi
echo "Parameters-------------------------"
echo "TARGET: $TARGET"
echo "DATABASE_NAME: $DATABASE_NAME"
echo "HOST: $HOST"
echo "USER: $USER"
echo "PASSWORD: $PASSWORD"
echo "PORT: $PORT"
echo "------------------------------------"
#Verification
if ! "${AUTO_YES}"; then
read -p "\"$DATABASE_NAME\"Can I initialize the database?(y/N)" yn
case "$yn" in
[yY]*) ;;
*) exit 1;;
esac
fi
echo "Database initialization in progress..."
CMD="mysql -h$HOST"
if [ "$USER" != "" ]; then
CMD="$CMD -u$USER"
fi
if [ "$PASSWORD" != "" ]; then
CMD="$CMD -p$PASSWORD"
fi
if [ "$PORT" != "" ]; then
CMD="$CMD -P$PORT"
fi
echo 'SET FOREIGN_KEY_CHECKS = 0;' > "${ROOT_PATH}/tmp.sql"
cat $ROOT_PATH/$TARGET/schemas/*.sql >> "${ROOT_PATH}/tmp.sql"
`$CMD -e "set FOREIGN_KEY_CHECKS=0; DROP DATABASE IF EXISTS $DATABASE_NAME;"`
`$CMD -e "CREATE DATABASE $DATABASE_NAME;"`
`$CMD -t $DATABASE_NAME < "${ROOT_PATH}/tmp.sql"`
rm "${ROOT_PATH}/tmp.sql"
echo "Done"
#If you want to put the initial data for the test, place the file in the following format and import it together
# echo "Creating initial data..."
# `$CMD -t $DATABASE_NAME < "${ROOT_PATH}/${TARGET}/testdata/dump.sql"`
# echo "Done"
exit 0
Import is
As an example, ./scripts/import_database_schema.sh -t users -d users -y
I will do it in the form of.
In actual operation, test data is set up as shown below, and the test data is also imported at the time of import, and the test is run with github actions.
├── users
│ ├── testdata #Stores user DB test data
│ └── schemas #Stores the CREATE statement of the table in the users DB
└── shops
├── testdata #Stores test data of shops DB
└── schemas #Stores the CREATE statement of the table in shops DB
Recommended Posts