Solved

Escaping Line Returns in mysql query results

Posted on 2011-09-29
3
223 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Introduction On September 29, 2012, the Python 3.3.0 was released; nothing extremely unexpected,  yet another, better version of Python. But, if you work in Microsoft Windows, you should notice that the Python Launcher for Windows was introduced wi…
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

717 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