MySQL -> R

Here is how you pull SQL data into R.

	require( sqlutils )
	require( RMySQL )
	library( Cairo )
	library( yaml )
	my.cnf = yaml.load_file( '~/.my.yaml' )
	default = my.cnf$clients$default
	p_cols <- c( "blue" , "red" , "orange" , "green" )
	con <- dbConnect(
	MySQL(),
	user=default$user ,
	password=default$password,
	dbname=default$database,
	host=default$host
	)
	rs <- dbSendQuery(
	con ,
	'
	SELECT TIME_FORMAT( time , "%l %p" ) time
	, AVG( temp_f ) temperature
	FROM weather
	WHERE HOUR( TIMEDIFF( SYSDATE() , TIME ) ) < 24
	GROUP BY HOUR( time )
	ORDER BY HOUR( TIMEDIFF( SYSDATE() , TIME ) )
	DESC;
	'
	)
	fields = dbColumnInfo(rs)
	data_frame = fetch(rs, n = 24 )
	time = data_frame[[ "time" ]]
	temp = data_frame[[ "temperature" ]]
	CairoPNG(
	filename="/path/to/where/i/write/test.png" ,
	width = 500 ,
	height = 500 ,
	pointsize = 9
	)
	plot ( temp
	, main="Temperatures for the last 24 Hours"
	, sub="West Lafayette, IN - Temperatures in Fahrenheit"
	, col=p_cols[2]
	, type="l"
	, xlab="Time"
	, ylab="Temperature(F)"
	, xaxt='n'
	)
	axis( 1 , las=2 , at=1:24 , lab=time )
	box()

view raw gistfile1.r hosted with ❤ by GitHub

Cool, isn't it?

Except there's a password in plain text. Which means I have to modify it when I want to show it off.

I'll have to work on that.