Possible to programmatically ALTER multiple MySQL tables?

Question

I have multiple MySQL tables with names of the form "Shard_0", "Shard_1", "Shard_2" ... "Shard_n" All of them have identical table structure. They all live in the same database.

Say I want to add a column to all those tables. Is there a way to do that programmatically?

Something like:

# pseudo code    
for i in range(n):
    tablename = "shard_"+str(i)
    ALTER TABLE tablename ...

Is it possible to do something like that? If so what language and/or library do I need?

Thanks

Answer 1

No problem. Python has several third party libraries to connect to a db. But the simplest approach if you have to do this for just one time would be a python script that writes the SQL instructions just to stdout:

for i in range(n):
    tablename = "shard_"+str(i)
    print 'ALTER TABLE tablename ...'

Then just call it from CLI like this:

./sqlgenscript.py | mysql -u username -p

Answer 2

I think you can create a routine which takes one argument, and send "i" as argument to your routine. Then you can call your routine.

Call test.My_Alter(i);

where i=1,2,3,...

Answer 3

Yes its possible, you can use MySqlDb module for python and write the queries similar to sql queries and execute them to update the tables. Have a look at this: http://mysql-python.sourceforge.net/MySQLdb.html