3

I have a large .sql file, created as a backup from a MySQL database (containing several tables), and I would like to search elements within it from R.

Ideally, there would be a read.sql function that would turn the tables into some R list with data.frames in it. Is there something that comes close? If not, can RSQLite or RMySQL help? (going through the reference manuals, I don't see a simple function for what I described)

Tal Galili
  • 24,605
  • 44
  • 129
  • 187

3 Answers3

2

No can do, boss. For R to interpret your MySQL database file, it would have to do a large part of what the DBMS itself does. That's a tall order, infeasible in the general case.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
  • Thanks James, I had a feeling that would be the case. Is there a command line tool for Windows that does this job reasonably well? (so I would run something like "turnToDB filename.sql DBname.db"? (sorry if this is obvious for DB people, as I am not one - I have to ask) – Tal Galili Apr 17 '13 at 10:43
1

Would this return what you seek (which I think upon review you will admit is not yet particularly well described):

 require(RMySQL)
 drv <- dbDriver("MySQL")
 con <- dbConnect(drv)
 dbListTables(con)
 # Or
 names(dbGetInfo(drv))

If these are just source code than all you would need is readLines. If you are looking for an R-engine that can take SQL code and produce useful results then the sqldf package may provide some help. It parses SQL code embedded in quoted strings and applies it either to dataframe objects in memory or to disk-resident tables (or both). Its default driver for disk files is SQLite but other drivers can be used.

IRTFM
  • 258,963
  • 21
  • 364
  • 487
  • Hi DWin, sorry for not being clearer. I have a specific .sql file that I wish to read into R. I understand I can import it into a database in MySQL, and then read it into R - but wondered if there is a way to do so without going through the stages I've listed. Thanks. – Tal Galili Apr 16 '13 at 20:47
  • Loading a driver that is matched to the creator is an R operation. From your text I thought you wanted the names of the tables, but if you want something else, well then edit to clarify. – IRTFM Apr 17 '13 at 06:20
  • Hi DWin, I'm not sure how to write it better. But basically - if I have a .sql file with a script - I could go into a phpMyAdmin and paste the script there - run it - and I would get tables into my database. I wonder if I could have done something similar inside R (so I'd get an object I can manipulate). Does this read better? – Tal Galili Apr 17 '13 at 07:04
  • All of the .sql files I have are program source code, i.e. simple ASCII files. You question implied that you had something different than that which needed a driver or a full IDE, but I'm starting to wonder if you also just have text? – IRTFM Apr 18 '13 at 01:48
  • Hi DWin, what I have is a text .sql file with commands on how to build the tables of a database (its just a data dump from some server), and I simply wish to re-create these tables into R objects. (thanks for being so patient with me :) ) – Tal Galili Apr 18 '13 at 15:56
0

My workaround so far (I am also a newbie with db) is to export the database as .csv file in the phpMyAdmin (need to tick "Export tables as separate files" in the "custom" method). And then use read_csv() on tables I want to work with. snapshot of the custom export, tick the last box

It is not ideal because I would like to export the database and work on it on my computer with R (creating functions that will work when accessing the database that is online) and access the real database later, when I have done all my testing. But from the answers here, it seems the .sql export would not help for that anyway (?) and that I would need to recreate the db locally...

Julien Colomb
  • 508
  • 4
  • 20