Using MySQLdb to select into the local file

[Nikhil]

John Nagle wrote:
> 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
Thanks John. That was a useful tip.

Regards,
Nikhil