468,507 Members | 1,577 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,507 developers. It's quick & easy.

Using MySQLdb to select into the local file

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-seperated 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.
I am wondering if anyone has gone through this before and made some
arrangements to iterate over the data but get the /tmp/result.txt
generated locally on the client machine?

On the local or the server file, from
http://dev.mysql.com/doc/refman/5.0/en/select.html

---
The SELECT ... INTO OUTFILE statement is intended primarily to let you
very quickly dump a table to a text file on the server machine. If you
want to create the resulting file on some client host other than the
server host, you cannot use SELECT ... INTO OUTFILE. In that case, you
should instead use a command such as mysql -e "SELECT ..." file_name
to generate the file on the client host.
---

So, what is the equivalent of using '-e' mysql commandline option in the
MySQLdb python module?

I am sorry if this is supposed to go to only MySQL, but not really sure
so copying the relevant assumed.

Thanks,
Nikhil
Jun 27 '08 #1
2 3967
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
Jun 27 '08 #2
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
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Peter Nikolaidis | last post: by
reply views Thread by Bob Swerdlow | last post: by
21 posts views Thread by John Fabiani | last post: by
reply views Thread by matt okeson-harlow | last post: by
reply views Thread by Wesley Kincaid | last post: by
2 posts views Thread by ws Wang | last post: by
4 posts views Thread by fedor | last post: by
reply views Thread by Tommy Grav | last post: by
reply views Thread by Edwin.Madari | last post: by
reply views Thread by NPC403 | last post: by
3 posts views Thread by gieforce | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.