534 search results for "sql"

How not to reveal your MySQL DB login/password when sharing code on GitHub or BitBucket?

March 19, 2013
By

Solution: use your ~/.my/cnfInside your ~/.my.cnf file define the connection parameters to your databases. For example, here I define two groups called local and toto:user = rootpassword = ultra_secrethost = localhostuser = capitaine_flamp...

Read more »

R vs. Perl/mySQL – an applied genomics showdown

March 8, 2013
By

R vs. Perl/mySQL - an applied genomics showdown Recently I was given an assignment for a class I'm taking that got me thinking about speed in R. This isn't something I'm usually concerned with, but the first time I tried to run my solution (ussing plyr's ddply() it was going to take all night to compute. I consulted the professor that taught...

Read more »

UPDATE Multiple postgreSQL Table Records in Parellel

February 27, 2013
By
UPDATE Multiple postgreSQL Table Records in Parellel

Unfortunately the RpostgreSQL package (I’m pretty sure other SQL DBs as well) doesn’t have a provision to UPDATE multiple records (say a whole data.frame) at once or allow placeholders making the UPDATE a one row at a time ordeal, so I built a work around hack to do the job in parellel.  The big problem

Read more »

SQL commands in R

January 22, 2013
By

For a class I'm taking this semester on genomics we're dealing with some pretty large data and for this reason we're learning to use mySQL. I decided to be a geek and do the assignments in R as well to demonstrate the ability of R to handle pretty larg...

Read more »

Version 1.0 of sqlutils available on CRAN

January 15, 2013
By

Version 1.0 of sqlutils has been released to CRAN. The sqlutils package is designed to manage a library of SQL files. This package grew out of the needs of an Office of Institutional Research where the vast majority of analysis is conducted on data from our Student Information System (SIS) which is stored in an Oracle database. A lot...

Read more »

Interactive SQL in R

January 12, 2013
By
Interactive SQL in R

I recently taught a very basic introduction to SQL workshop and needed a way to have participants interact with SQL statements. Obviously there are lots of tools to interface with a database, but since we are all R users I thought it would be nice to be able interact without leaving R. Although this interface is fairly basic, the...

Read more »

Querying an SQLite database from R

January 6, 2013
By

You have an SQLite database, perhaps as part of some replication materials, and you want to query it from R. You might want to be able to say: results <- runsql("select * from mytable order by date") and get the results back as an R object. Here's a function to do it. In the following,

Read more »

How to select and merge R data frames with SQL

December 17, 2012
By

The R language provides many features in the language for selecting data from data frames: the "[" operator, logical functions, and utility functions like "subset". But if you know SQL (the query language ubiquitous in database systems), none of this is necessary. With the sqldf package, you can just pretend that your data frame is a database, and use...

Read more »

Video: SQL queries in R using sqldf package

December 17, 2012
By

This video covers how to run SQL queries using the ‘sqldf’ package within R. This sqldf tutorial was part of a Keystone Solutions podcast discussion about data science and what skills beginning analysts should be learning to improve their skill set. The example files from this tutorial can be downloaded from this link: Example Data Video: SQL...

Read more »

Exchange Data between Python and R with SQLite

December 2, 2012
By
Exchange Data between Python and R with SQLite

SQLite is a light-weight database with zero-configuration. Being fast, reliable, and simple, SQLite is a good choice to store / query large data, e.g. terabytes, and is well supported by both Python and R.

Read more »