output mysql table into comma separated file with python: syntax error

Question

I am trying to write a MySQL table into a csv table with proper comma separation. It works without formatting and the result is a tab separated file. I need however a comma separated file and I am getting a syntax error. Here is the code:

def export_csv(cxn, filename,filestamp,table, cols='*', where='1'):  
     cur = cxn.cursor()  
     cur.execute("select %s \ 
     into outfile '/tmp/%s-%s.csv' \  
     fields terminated by ',' optionally enclosed by '\"' \  
     escaped by '\\' \  
     lines terminated by '\n'  
     from %s \  
     where %s " \  
     % (cols, table, where, filename, filestamp))  
     cur.close()

It works in pure MySQL. I tried to add more escapes, to no avail.

Janne Karila · Accepted Answer · 2011-01-21 11:40:58Z

2

Use triple quotes ''' around a string that spans multiple lines, in this case around the select statement.

EDIT (there's more...)

Don't add a backslash at end of line inside the triple-quoted string
To pass a backslash to MySQL, escape with backslash \\, or add an r before opening quotes to mark raw string

edited Jan 21, 2011 at 11:40

answered Jan 21, 2011 at 10:13

Janne Karila

25.3k6 gold badges60 silver badges97 bronze badges

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

1 Comment

slooow Over a year ago

def export_csv(cxn, filename, filestamp, table, cols='*', where='1'): cur = cxn.cursor() cur.execute("""select %s into outfile '/tmp/%s-%s.csv' fields terminated by ',' optionally enclosed by '"' escaped by '\' lines terminated by '\n' from %s where %s""" % (cols, filename, filestamp, table, where)) cur.close() Thanks, this saved me from scrubbing the result with the csv module.

Collectives™ on Stack Overflow

output mysql table into comma separated file with python: syntax error

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