Using MySQLdb to select into the local file

[John Nagle]

Nikhil wrote:
> I am using the MySQLdb python module. I have a table named 'testing' 
> with few columns, under the 'test' database, what is hosted on a remote 
> mysql server.
> 
> I want to run the following query to get a comma-separated information 
> from the table
> 
> 
> LOCK TABLES foo READ;
> SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
>   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
>   LINES TERMINATED BY '\n'
>   FROM 'testing'
> UNLOCK TABLES;
> 
> ..the query is running fine, but what I am noticing is /tmp/result.txt 
> is getting created locally on a mysqld running machine but not on the 
> client(python program) using the MySQLdb module.

     Unfortunately, while there is LOAD DATA LOCAL INFILE, which
reads a file on the client, there is no SELECT INTO LOCAL OUTFILE.

     Actually, you probably want to turn off the FILE privilege
for your MySQL.  That blocks LOAD DATA INFILE and SELECT INTO
OUTFILE, generally considered a good idea because those commands can
access arbitrary file names.

     Also, if you're still using LOCK TABLES and UNLOCK TABLES,
read up on InnoDB and transactions.

     Typically, you do something like this:

import MySQLdb
import csv

def writedb(db, filename) :
	try :
		outcsv = csv.writer(filename)	# output object for CSV
		cursor = db.cursor()									cursor.execute("SELECT a,b,a+b FROM testing")	
		while True :			# do all rows
			row = cursor.fetchone()	# get a tuple for one row
			if row is None :	# if end of rows
				break		# done
			outcsv.writerow(row)	# write row in CSV format
		db.commit()			# release locks

	except MySQLdb.OperationalError, message:
		print "Database trouble: ", message # handle any db problems
		raise				# reraise exception


hostname="???"					# fill in appropriately
user="???"
password="???"
db = MySQLdb.connect(host=hostname, 		# open database
	user=username, passwd=password, db=databasename)

writedb(db, '/tmp/result.txt')			# do it

===============

   Note that this is ASCII-oriented; if you Unicode, you need
extra params to "connect".  Also, the CSV module doesn't do
Unicode well as yet.  Make sure the "outcsv" object
goes out of scope before you try to read the file, so the
file gets flushed and closed.

					John Nagle