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
--
http://mail.python.org/mailman/listinfo/python-list
