CODEWITHSUNDEEP
pythonmysqldatabaseschemadatabase-administration
Continuation
Continuation

Reputation: 13040

Possible to programmatically ALTER multiple MySQL tables?

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

Upvotes: 2

Views: 482

Answers (3)

Ankit Jaiswal
Ankit Jaiswal

Reputation: 23427

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

Upvotes: 1

rain
rain

Reputation: 403

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,...

Upvotes: 0

joni
joni

Reputation: 5462

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

Upvotes: 4

Related Questions