Press "Enter" to skip to content

MySQL Data Type Mapping in R

There was a recent question in the /r/Rlanguage subreddit which piqued my interest. They asked how to find the right mapping, and with the large number of data types I wondered if there was a good way to dynamically discover how fields are cast.

First step is to decide how to communicate with the database. I used the package RMySQL for this.

Now to connect to the DB. I am running this locally. Make sure to use a password, even locally.

I included two different queries. The one below grabs all of the rows from the table.

We get a couple warnings with the current table.

We can use this loop to get the column name and data type for the imported dataframe.

We get this nice output.

The column names in MySQL were the data type followed by _test. As you can see, they were not all imported with a matching data type.

We can get a 1:1 mapping of the column type in MySQL and imported data type in R with the following piece of code.

This gives us a nice printout of a new dataframe created on the fly.

And of course we need to close the connection if no more queries need to be made.

Sharing is caring!

One Comment

  1. Alexey Seleznev Alexey Seleznev May 8, 2019

    Hi, thank for this point.
    I use RMariaDB for connect to MySQL, is similar package but it is more modern over RMySQL.
    In RMySQL anytime i get error in inconding when i work with cyrillic characters.

Leave a Reply

Your email address will not be published. Required fields are marked *