473,287 Members | 1,663 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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 4290
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Peter Nikolaidis | last post by:
Greetings, I am attempting to get MySQLdb 0.9.2 installed on Mac OS 10.2 with a Fink distribution of Python 2.2.2. I have seen only a few posts on the subject, some of them relate to...
0
by: Bob Swerdlow | last post by:
I'm trying to install MySQLdb 0.9.2 so I can access my MySQL database from python under Solaris 8. I found the notes on changing the include and lib paths and it seems to find them okay, but when...
21
by: John Fabiani | last post by:
Hi, I'm a newbie and I'm attempting to learn howto create a select statement. When I use >>> string1='18 Tadlock Place' >>> cursor.execute("SELECT * FROM mytest where address = %s",string1) All...
0
by: matt okeson-harlow | last post by:
i am getting errors trying to build MySQLdb on solaris 8, i have tried changing the libs, no joy, anyone have any ideas? Python 2.3.4 gcc -v Reading specs from...
0
by: Wesley Kincaid | last post by:
I'm attempting to run a simple query through MySQLdb's cursor.execute(). However, when the request includes a timestamp field, I'm getting "ValueError: invalid literal for int(): 9-." Could...
2
by: ws Wang | last post by:
MySQLdb is working fine at command line, however when I tried to use it with mod_python, it give me a "server not initialized" error. This is working fine: ----------------------- testmy.py...
4
by: fedor | last post by:
Hi all, I have a problem with mysql connections. After about 28000-29000 connections, I get a "Can't connect to MySQL server on '127.0.0.1'" error. I have made a small program which generates...
0
by: Tommy Grav | last post by:
I am trying to install mysqldb-1.2.2 on my PPC running 10.5.2 and Activepython 2.5.1.1 when I get this error: running build running build_py copying MySQLdb/release.py...
0
by: Edwin.Madari | last post by:
replace the name of table before calling *.execute. s.dbptr.execute(str % (e)) good luck. Edwin -----Original Message----- From: python-list-bounces+edwin.madari=verizonwireless.com@python.org...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.