1

I've been trying to google this for hours now, sorry if the answer is already out there

This is what I have so far:

c.execute("SELECT * FROM table")
table = c.fetchall()
print(table)

#to export as csv file
with open("wub.csv", "wb") as write_file:
     cursor = connection.cursor()
          for row in cursor.execute("SELECT * FROM table"):
          write_file.write(row)

But I keep getting an error:

Traceback (most recent call last):
  File "C:/Users/Rachel/Documents/database.py", line 52, in <module>
    write_file.write(row)
TypeError: a bytes-like object is required, not 'tuple'

so I'm pretty sure I'm doing everything wrong -how would I properly export the table to an external CSV file? Thank you

EDIT: to Janekmuric

Improve this question
2
  • 1
    Does write_file.write(",".join(row)) fix it? Maybe write_file.write(",".join(row) + "\n") in case it doesn't add a newline automatically. Anyway, as @cricket_007's link suggests, there's also a module for handling csv files Commented Mar 23, 2016 at 21:13
  • Warning: if you are using Python3, and still want to write the .cvs file in binary mode, you'll have to encode the strings returned from the sqlite cursor to bytes. Commented Mar 23, 2016 at 21:25

2 Answers 2

Reset to default
13 
import sqlite3
import csv

inpsql3 = sqlite3.connect('input.sql3')
sql3_cursor = inpsql3.cursor()
sql3_cursor.execute('SELECT * FROM clients')
with open('output.csv','w') as out_csv_file:
  csv_out = csv.writer(out_csv_file)
  # write header                        
  csv_out.writerow([d[0] for d in sql3_cursor.description])
  # write data                          
  for result in sql3_cursor:
    csv_out.writerow(result)
inpsql3.close()

Note the use of description member of the sql cursor to get the names of the headers in the clients table.

You can, of course, modify the names of the input and output files. And you can also modify the parameters of the csv.writer.

The original author had the flag 'wb' for writing bytes; however we are not writing bytes.

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

Comments

3

The code below will iterate over a list of tuples that SELECT (execute) returns.

for row in cursor.execute("SELECT * FROM table"):
      write_file.write(row)

Eg.
If you have a database with rows number, word, colour your code will loop over something like this:

(23, "tree", "red")
(17, "code", "green")
(11, "python", "yellow")

And what you're trying to do is write a tuple to a file. You can only write strings (or bytes) to a file.

What you have to do is convert that tuple to a string. For this you can use the join() function.

Your code will now look like this:

c.execute("SELECT * FROM table")
table = c.fetchall()
print(table)

#to export as csv file
with open("wub.csv", "wb") as write_file:
     cursor = connection.cursor()
     for row in cursor.execute("SELECT * FROM table"):
          writeRow = " ".join(row)
          write_file.write(writeRow)

" ".join(row) will join all tuple elements and seperate them with, in this case, a space.

There are a few problems with this.

Join only works with lists and tuples that contain string elements only (so the code above wouldn't work if your database had a REAL or INTEGER type)

You can just write a loop that goes over each element in the list and converts it to a string.

I also added a .encode() because you are opening your CSV file with "rb" so you need to convert it to bytes before writing to the file.

c.execute("SELECT * FROM table")
table = c.fetchall()
print(table)

#to export as csv file
with open("wub.csv", "wb") as write_file:
    cursor = connection.cursor()
    for row in cursor.execute("SELECT * FROM table"):
        writeRow = " ".join([str(i) for i in row])
        write_file.write(writeRow.encode())
Improve this answer

3 Comments

thank you so much for your reply -this worked, but my table has more than one 'type of field' (not really sure how to describe it), and the CSV file has all the data into one field (i'll edit in a screenshot into the question part) -is there a way to get it so that one table field is assigned one excel field, and each entry is on a different row?
@Rachel I'm not sure what you're asking. It would be nice if you could upload the entire code and add more details about what you need. You could also mark this answer as accepted and post a new question.
Why are you using a "wb" flag in open( ) ? it is a text file...

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.