views:

75

answers:

2

How do I call the following in a .sql file?

mysql -u username -h serverip db -e "select a, b, c, d from mytable" > '/home/dump/result.txt';

I will then have to convert the tab separated file to csv for which I want to use sed.

  1. Is there a way to do it all in one line? (append sed command to convert the .txt file to csv)
  2. How to use an os.system call in .sql file? Will it work?
+2  A: 

What about to run this query via mysql console client:

SELECT ... FROM ... INTO OUTFILE  '/path/to/file.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY "\n"
Vadim
I don't have this option cos' the client server and db server are different. Thanks!
A: 

Try this:

mysql --batch --skip-column-names -u username -h serverip db \
  -e "select a, b, c, d from mytable" \
  | sed -e 's/\t/,/g' \
  > '/home/dump/result.txt'

See docs for --batch and --skip-column-names for more info.

Bill Karwin
Thanks Bill. We are migrating from a scenario where db server and client are the same to individual servers for both. I have a bunch of .sql files with select into outfile queries which won't work anymore. Is there a way to execute your solution in the .sql file itself?
The mysql client does not have any option to output directly to CSV. You're going to have to add the `sed` command to your scripts that invoke the mysql client.
Bill Karwin