Solved

Escaping Line Returns in mysql query results

Posted on 2011-09-29
3
216 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
  • 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

Title # Comments Views Activity
Copy Files - Python 7 107
What is Python programming? 3 128
Turning python script into an applet 12 119
instagram media_id Grabber by TAG 1 24
Installing Python 2.7.3 version on Windows operating system For installing Python first we need to download Python's latest version from URL" www.python.org " You can also get information on Python scripting language from the above mentioned we…
A set of related code is known to be a Module, it helps us to organize our code logically which is much easier for us to understand and use it. Module is an object with arbitrarily named attributes which can be used in binding and referencing. …
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 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…

685 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