Loading Data Frame to Relational Database with R

Once you create a data frame with R, you may need to load it to a relational database for data persistence. You might have a data transformation batch job written in R and want to load database in a certain frequency.

Here, I created a function to load data into a relational database. I opted to use RODBC because it is probably the easiest way to interact with databases with R. As long as you install and configure ODBC for whatever database you are using, this should work.

RODBC has many functions that does database operations for you. The insertion is taken care of by the sqlSave method. RODBC has fantastic documentation so that you can check what options are available.

Function Parameters

The function below takes 6 parameters.

dsn: data source name that you configured for ODBC.

user: database user name.

pw: database password.

tableName: Name of the table including the schema

df: R data frame

columnTypes: List of SQL column types.

Usage

Call the function like this

Function: dbLoader

I could probably make drop table SQL statement as a parameter so that it can be used for any database. In this function, it is hard coded. The statement should work for most databases. Most of databases have the same drop statement syntax.

The db load pattern is the classic truncate and load. You can customise it to make it to upsert.

Easy!

Hi, thank you for publishing this. I have a sort of a broader method question I would like to ask. Is there an email address I could write to?

No worries. You can just write back the comment. I’m happy to reply. Thanks for reading!

HI
I wanted to use your codes in title “How To Get Facebook Data With Python”.But I have some problems and questions.How can I ask them and send you the print screen of them.I will really appreciate if you guide me by that.

thanks a lot

Hey, I haven’t really setup any correspondence mechanism for this site. You can host the screenshot and send me the link? or if it is simple, you can simply reply back here?

Yeah, these are mine. You can use it for whatever purpose. If you spot any bug or have improvement suggestion, please let me know. It would be great! Thanks.

Data Engineering
Sending XML Payload and Converting XML Response to JSON with Python

If you need to interact with a REST endpoint that takes a XML string as a payload and returns another XML string as a response, this is the quick guide if you want to use Python. If you want to do it with Node.js, you can check out the post …

Data Engineering
Sending XML Payload and Converting XML Response to JSON with Node.js

Here is the quick Node.js example of interacting with a rest API endpoint that takes XML string as a payload and return with XML string as response. Once we get the response, we will convert it to a JSON object. For this example, we will use the old-school QAS (Quick …

Data Engineering
Downloading All Public GitHub Gist Files

I used to use plug-ins to render code blocks for this blog. Yesterday, I decided to move all the code into GitHub Gist and inject them from there. Using a WordPress plugin to render code blocks can be problematic when update happens. Plugins might not be up to date. It …