Saving data from MySQL table to a text file with Python

Question

So I have a mysql table and I am trying to take each element from one of the fields of the table. The field and table are both called "keywords". In the field there are many different random words and I am trying to take all of those and save them to a text file. Any help on how to implement this would be great, here is what I have so far.

#!/usr/bin/env python
import MySQLdb


db = MySQLdb.connect(host="", user="", passwd="", db="")
cursor = db.cursor()
sql = """SELECT DISTINCT keywords FROM keywords"""
cursor.execute(sql)
cursor.fetchall()
db.close()

for s in sql:
   tweets = open("keywords.txt", "w")

What I was thinking is to turn what sql fetches into a list if possible and write that to the file. But I am open to any suggestions, thanks.

why are you iterating over for s in sql, sql is a string... — avasal
– avasal, Commented Dec 12, 2012 at 7:00
Sorry i was just trying multiple different ways for this. I didn't mean to copy and paste this version but basically the last line is what I am looking for to change — Neemaximo
– Neemaximo, Commented Dec 12, 2012 at 7:06

mvp · Accepted Answer · 2016-05-19 15:50:44Z

3

Something like this should work:

import MySQLdb

db = MySQLdb.connect(host="", user="", passwd="", db="")
cursor = db.cursor()
sql = """SELECT DISTINCT keywords FROM keywords"""
tweets = open("keywords.txt", "w")
cursor.execute(sql)
for row in cursor:
   print>>tweets, row[0]
tweets.close()
db.close()

edited May 19, 2016 at 15:50

answered Dec 12, 2012 at 7:12

mvp

118k15 gold badges132 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

fanny Over a year ago

I think that cursor.execute(sql) is a long and you cannot iterate through it. Shouldn't it be something like for row in cursor: ... ?

mvp Over a year ago

@fanny: yes, that would work, but only if you add a line cursor.execute(sql) before loop. Which is not shorter, but may be cleaner.

fanny Over a year ago

you're right, cursor.execute(sql) must be called before :) And it works all right. My question concerns your code - in specific iterating through "for row in cursor.execute(sql)" - how come when I try to mimic your code I get a TypeError (as cursor.execute(sql) is a LONG) while you obviously do not? :)

Diansheng Over a year ago

I experience the save error as fanny. Instead, the following code is working. cursor.execute(sql) for row in cursor:

Collectives™ on Stack Overflow

Saving data from MySQL table to a text file with Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related