CODEWITHSUNDEEP
pythonoracle-databaseexport-to-csv
user1588433
user1588433

Reputation: 75

use python to print query results to pipe delimited file

I am a beginner with python and I am trying to print the results of a query to a pipe delimited file. How can I modify this script to do so?

import cx_Oracle
import csv

connection = cx_Oracle.connect("blah blah blah connection stuff")
cursor = connection.cursor()
cursor.execute("select column1, column2 from schema.table")
result=cursor.fetchall()
c = csv.writer(open("C:\mystuff\output.csv","wb"))
c.writerow(result)

Right now it prints out like this "(10001965, '0828442C00548')","(10001985, '0696230C35242')","(10001986, 'C41251')"

I want it to print pipe delimited and a new line at the end of the row. Thanks for any help! 10001965|0828442C00548\n 10001985|0696230C35242\n 10001986|C41251\n

Upvotes: 2

Views: 8341

Answers (2)

Hedlok
Hedlok

Reputation: 146

To get the pipes symbol as the delimiter, you can pass in a "delimiter" parameter when creating the writer:

c = csv.writer(open("C:\mystuff\output.csv","wb"), delimiter="|")

you can also add a lineterminator param:

c = csv.writer(open("C:\mystuff\output.csv","wb"), delimiter="|", lineterminator="\n")

though it is supposed to default to "\r\n"

I think you may also have a problem with the call to "writerow". The db result is a list of tuples, to correctly write this to the csv files, you should use "writerows" (NOTE THE plural form of the function). This should correctly handle the list and put the pairs of db values on separate line, correctly delimited with pipes. Currently, your code is writing one "row", that's why there are no newlines separating the data values.

New code:

c = csv.writer(open("C:\mystuff\output.csv","wb"), delimiter="|")

c.writerows(result)

Upvotes: 5

kreativitea
kreativitea

Reputation: 1791

It prints out a list of strings? You can use "|".join(string.split(',')) to join strings that were once comma delimited to be pipe delimited. And, use '\n'.join(list) over the list to get the newline. This will not add a newline to the end, so if you want that, just + '\n' to the result.

Upvotes: 0

Related Questions