0

I have this df in R with various columns such as ID, Name, Prod, and other stuff which is not important. The only important thing is the IDs. In this df there are various rows with the same ID that has the same names as the schemas that I want to update in MySQL. Lets say this is the table:

ID Name Prod otherstuff
ttt637 jj Q2 ghafdj...
ttt637 bf Q4 jhsdf...
ttt637 cj Q1 jhfdl...
klm457 jj Q2 jshfd...
klm457 lk Q4 uiepc...
klm457 wt Q1 qapfn..
.. for all 100+ ID schemas

As I mentioned the schemas have the same names as IDs in the dataframe. However in the schemas there is a table called info; so we have: ttt637.info, klm457.info and 100+.

What I want to do is I want to update the .info table in the 100+ schemas in MySQL based on the single dataframe in R but assign the stuff related to each ID to all the 100+ schemas.info tables. Please note that there might be some information already available in some schemas.info tables, so in case there is value there that need to be updated as well.

I need to automate the whole procedure using a for loop or a function.

Could someone please help with how I could manage to do that?

I know for one schema I could use something like this How to write entire dataframe into mySql table in R but not sure about multiple schemas.

Thanks.

Abtin Ijadi Maghsoodi
  • 3
  • 4
  • do you know how to do this for 1 schema manually? Can you include the code for that? – Ronak Shah Jul 14 '21 at 10:40
  • I guess for just one schema we could use: https://stackoverflow.com/questions/41466031/how-to-write-entire-dataframe-into-mysql-table-in-r or https://stackoverflow.com/questions/12186839/writing-the-data-frame-to-mysql-db-table but I don't know what to do in my case – Abtin Ijadi Maghsoodi Jul 16 '21 at 05:15

1 Answers1

0

My PhD supervisor helped me figure out how to do this. Basically he wrote:

for ID_loop in info$ID {
print(ID_loop)
whattoinsert <- info %>% select(ID) %>% distinct()
query = paste("USE schemas.info",
               ID_loop,
               sep = " ")
print(query)
results <- DBI:dbGetQuery(con,query)
dbWriteTable(con, "info",whattoinsert,overwrite = TRUE)
 }

Kudus to him :)

Abtin Ijadi Maghsoodi
  • 3
  • 4