Connecting to MySQL is made very easy with the RMySQL package. To connect to a MySQL database simply install the package and load the library.
Connecting to MySQL:
Once the RMySQL library is installed create a database connection object.
mydb = dbConnect(MySQL(), user='user', password='password', dbname='database_name', host='host')
Listing Tables and Fields:
Now that a connection has been made we list the tables and fields in the database we connected to.
This will return a list of the tables in our connection.
This will return a list of the fields in
Queries can be run using the
dbSendQuery(mydb, 'drop table if exists some_table, some_other_table')
In my experience with this package any SQL query that will run on MySQL will run using this method.
We can create tables in the database using R dataframes.
dbWriteTable(mydb, name='table_name', value=data.frame.name)
Retrieving data from MySQL:
To retrieve data from the database we need to save a results set object.
rs = dbSendQuery(mydb, "select * from some_table")
I believe that the results of this query remain on the MySQL server, to access the results in R we need to use the
data = fetch(rs, n=-1)
This saves the results of the query as a data frame object. The
nin the function specifies the number of records to retrieve, using
n=-1retrieves all pending records.