Subscribed unsubscribe Subscribe Subscribe

oinume journal

Scratchpad of what I learned

MySQLでデータベースの全テーブルを強制的にInnoDBに変換するPythonスクリプト

Python

仕事でMyISAMなテーブルをInnoDBにしたかったのでこんなん書いた。

 

$ ~/to_innodb.py -h localhost -u root mydatabase

 

 

 

#!/usr/bin/env python

# -*- coding: utf-8 -*-

 

# INSTALL ====================

# $ sudo easy_install argparse

# $ sudo easy_install mysql-python

 

import argparse

import MySQLdb

import os

 

def main():

# setup command line options

parser = argparse.ArgumentParser(

prog = os.path.basename(__file__),

description = 'Convert all tables to InnoDB',

conflict_handler = 'resolve'

)

parser.add_argument(

'database', metavar='DATABASE', nargs=1,

help='database name.',

)

parser.add_argument(

'-h', '--host', metavar='HOST', default='localhost',

help='host name',

)

parser.add_argument(

'-u', '--user', metavar='USER', default='root',

help='user'

)

parser.add_argument(

'-p', '--password', metavar='PASSWORD', default='',

help='password'

)

parser.add_argument(

'-P', '--port', metavar='PORT', type=int, default=3306,

help='port'

)

 

options = parser.parse_args()

 

db = MySQLdb.connect(

host = options.host,

port = options.port,

user = options.user,

passwd = options.password,

db = options.database[0])

 

cursor = db.cursor()

# get all table names

cursor.execute("SHOW TABLES")

for row in cursor.fetchall():

table = row[0]

print("--- %s ---" % (table))

cursor.execute("ALTER TABLE %s ENGINE=InnoDB" % (table))

 

cursor.close()

db.close()

 

if __name__ == '__main__':

main()

 

 

[tmkm-amazon]4873113938[/tmkm-amazon]