Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2021 - online Python 3 training - see ((here)).

Our plans were to retire in summer 2020 and see the world, but Coronavirus has lead us into a lot of lockdown programming in Python 3 and PHP 7.
We can now offer tailored online training - small groups, real tutors - works really well for groups of 4 to 14 delegates. Anywhere in the world; course language English.

Please ask about private 'maintenance' training for Python 2, Tcl, Perl, PHP, Lua, etc.
MySQL query to a text file

Posted by John_Moylan (jfp), 12 August 2003
Well this rated high on my "Why didn't I think of that" factor, so I thought I'd share.

I wanted to run a query in MySQL  and save the results in a text file, I could write a script in Perl, PHP, Java etc to do this but saw this and liked it.

Code:
[johnm@linuxbox johnm]$ echo "select * from companies where something = \"No\"" | /usr/local/mysql/bin/mysql -uUSERNAME -pPASSWORD DB_NAME > /tmp/foo.txt;


The query is piped through to MySQL and redirected to a text file.

I liked it, hope it helps someone else too for a quick hack.

Note: I normally save text files from a query using 'dbish' but DBI.pm was not installed on the particular machine.

'dbish' is another goodie to look at.

When I get time I'll post a little discuss on it.

jfp

Posted by admin (Graham Ellis), 12 August 2003
There's also an oprion on SELECT to redirect output to a file, but that will be a file on the machine on which the mysql daemon is running, and not the machine on which the client is running.  Of course, sometimes it's the same machine ...

Posted by John_Moylan (jfp), 12 August 2003
Hello Graham

You can't just tease us with the mere mention of simpler way of doing this.
Any chance of an example query?

jfp

Posted by admin (Graham Ellis), 13 August 2003
Sure, how about

Code:
select * into outfile "/tmp/openshow"  from lisbon;


Which will give you a tab separated file, with \ protection on special charcters.   There are FIELDS SEPARATED BY and other modifiers available too, and if you say "dumpfile" not "outfile", you'll get binary data in a single line.

Sorry to be a biot obtuse before, but I was away and didn't have all the books with me.   Internet access has been from a cafe, with 6 PCs sharing a modem or ISDN line and most computers there in use by the younger generation playing network games - it's been like trying to make a phone call in a war zone  

Posted by 4est (4est), 14 August 2003
i am assuming that using the SELECT to output file method mentioned will create a file if not present and overwrite it if it is present.  am i right?  is there a way to append?  is there a way to do the opposite?  INSERT from a textfile?


thanks so much, 4est.

Posted by admin (Graham Ellis), 14 August 2003
The output file CANNOT already exist.  This is done to prevent users overwriting / modifying system files.  You must also have file privilege on thge server.



This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2021: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho