Solved

Escaping Line Returns in mysql query results

Posted on 2011-09-29
3
220 Views
Last Modified: 2012-05-12
I have a python script that is pulling data from a MySQL database.

Here is a snippet that I use to write the results of the query to file:

cursor = conn.cursor ()
cursor.execute(queryString)
rows = cursor.fetchall ()
for row in rows:
   for col in row:
      output.write("%s\t" % (col))
   output.write("\n")
output.close()

Open in new window


My problem is that multiple fields in the query results are free form text and actually include line returns. Put another way there are multiple fields in the database table being queried that contain free form text including multiple lines and the line returns that come with them.

I need some insight on how I can keep those line returns confined to the one field when I write them to my .csv file and not  actually wrapping around to the next line.
0
Comment
Question by:marcusj2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Expert Comment

by:mish33
ID: 36892806
One (easy) way is to replace %s with %r which will give you all special character escaped.

Another way to replace(r"\n", "\\n").

Third way to enclose %s in quotes, as most csv dialects allows multi -line strings in quotes.
0
 

Accepted Solution

by:
marcusj2 earned 0 total points
ID: 36934706
Apparently line returns was not my problem!!

There were double quotes in the data returned from mysql that were altering grouping of the data. All I need to do was escape those double quotes...

I ended up making this simple change:
for row in rows:
   for col in row:
      output.write("\"%s\"\t" % (col))
   output.write("\n")
output.close()
cursor.close ()
conn.close ()

Works perfectly.
Thank you though.
0
 

Author Closing Comment

by:marcusj2
ID: 36954327
Feels weird answering my own question.... ha
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Here I am using Python IDLE(GUI) to write a simple program and save it, so that we can just execute it in future. Because when we write any program and exit from Python then program that we have written will be lost. So for not losing our program we…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question