how can I write rows from mysql to logfile in python

Question

How can I write the rows of a mysql select to a logfile so every row is on a newline and all columns are split by "|"?

I'm using python3.

EDIT:

I see I have given a bit to little info, sorry.

What I have is this:

query = 'SELECT * FROM `table1` where date < x'

cursor = db.cursor()
cursor.execute(query)
rows = cursor.fetchall()
filename = '/logs/data.log'
fp = open(filename, 'w', newline='\n')
myFile = csv.writer(fp, lineterminator='\n')
myFile.writerows(rows)

However this is comma seperated and it can happen that there are comma's in the datafields and all rows are appended after another instead of on a newline.

And would it be possible to directly tar the file which is created?

Can you post the code you wrote to attempt to solve this problem and describe what specifically is not working the way you expect or want? — takendarkk
– takendarkk, Commented Nov 15, 2016 at 22:11
What have you done so far? Have you queried the database and gotten the results? Have you looped over the results? Do you know about str.join()? — gen_Eric
– gen_Eric, Commented Nov 15, 2016 at 22:12
@takendarkk I edited my question, I do have good results from the query — user3605780
– user3605780, Commented Nov 15, 2016 at 22:24
Besides lineterminator, csv.writer has delimiter and quotechar. Does that do what you want? — James K. Lowden
– James K. Lowden, Commented Nov 15, 2016 at 22:30

gen_Eric · Accepted Answer · 2016-11-16 15:31:30Z

2

Here's a sketch of what I think you need:

import csv
rows = cursor.fetchall()
filename = '/logs/data.log'
fp = open(filename, 'w')  #  newline='\n' not needed

w = csv.writer(fp, lineterminator='\n', delimiter='|', quotechar='')
w.writerows(rows)

fetchall returns a list of tuples. The csv.writer documentation doesn't say, but I expect is takes any sequence of sequences.

edited Nov 16, 2016 at 15:31

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

answered Nov 15, 2016 at 23:10

James K. Lowden

7,8551 gold badge19 silver badges32 bronze badges

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

1 Comment

user3605780 Over a year ago

It gives an error with the empty quotechar but without quotechar it works. I viewed the file in notepad which didn't show the rows on a newline but in nano it does. thanks. Do you happen to know how I can tar it directly?

Collectives™ on Stack Overflow

how can I write rows from mysql to logfile in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related