Exporting Data from Sqlite 3

Sqlite3 / python - Export from sqlite to csv text file does not exceed 20k

You can try using pandas to load the sql data and then to dump it into a csv. You'd have to install the dependencies (notably NumPy) to use it though. It's really simple then:

import sqlite3
import pandas.io.sql as sql
con = sqlite3.connect('database.db')
table = sql.read_frame('select * from some_table', con)
table.to_csv('output.csv')

Exporting CSV file from python sqlite3

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())

Exporting table from sqlite3

You should be able to dump the table like so:

.output filename
.dump tablename

It'll be dumped to the current folder with the filename you specify.

How to export sqlite to CSV in Python without being formatted as a list?

What you're currently doing is printing out the python string representation of a tuple, i.e. the return value of str(row). That includes the quotes and 'u's and parentheses and so on.

Instead, you want the data formatted properly for a CSV file. Well, try the csv module. It knows how to format things for CSV files, unsurprisingly enough.

with open('output.csv', 'wb') as f:
    writer = csv.writer(f)
    writer.writerow(['Column 1', 'Column 2', ...])
    writer.writerows(data)

---

Related Topics