How ofter were you asked by a client for a CSV (or excel) file with data from their app? I get asked that question quite often, so I wanted make the process as easy as possible. And guess what? You can create CSV files directly from MySQL with just one query!
Let’s say you want to export the id, name and email fields from your users table to a CSV file. Here is your code:
SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1
Well, if you know MySQL, you’ll know how to customize this query to spit out the the right data. Your csv file can be found in /tmp/result.csv
Make sure your MySQL server has write permissions to the location where you want to store your results file.
General
csv, MySQL, quick, trick
First seen at Kabisa Blog: SQL: Ordering with NULL values
This post tells you how to sort NULL values in a column to the bottom and sort the remaining non-NULL values.
This is really great in combination with LEFT JOIN queries that may yield NULL values.
General
database, null, order, sql
I’m currently writing some RSpec tests that use Time.now.
I want my model to calculate a duration and store the future time in the database. I’ve already specced the calculation of the duration, but I also want to spec that everything gets saved correctly. Here’s my first spec:
it "should do stuff" do
m = Model.create()
m.expires_at.should eql(Time.now + some_value)
end
This fails.
It fails because Time.now is quite accurate and some milliseconds have passed between the two calls.
So how do you test this kind of behaviour? Get out your gloves, because we’re going to start stubbing!
What you need to do is stub out Time#now to return a constant value within this test. This way, both calls will use the same Time.now value and thus yield the same result. This in turn makes your test pass (if the saving goes well, of course).
it "should do stuff" do
@time_now = Time.parse("Feb 24 1981")
Time.stub!(:now).and_return(@time_now)
m = Model.creat()
m.expires_at.should eql(Time.now + some_value)
end
General
Rails, rspec, Ruby, Test, time
Recent Comments