how to import data from SQLITE to excel using xlsxwriter in python

Question

import sqlite3
from xlsxwriter.workbook import Workbook
workbook = Workbook('output2.xlsx')
worksheet = workbook.add_worksheet()

conn=sqlite3.connect('test.sqlite')
c=conn.cursor()
c.execute("select * from abc")
mysel=c.execute("select * from abc ")
for row in mysel:
 print row
 worksheet.write(0, 0, row[0])
workbook.close()

here is layout of SQLITE database table

with worksheet.write(0, 0, row[0]) i get output as 4 in First cell of excel file.

with worksheet.write(0, 0, row[1]) i get output as 400 in First cell of excel file

am not able to figure out the FOR LOOP issue.

please help

all_the_rage · Accepted Answer · 2018-11-02 13:12:41Z

4

Your loop is fine except that you are writing each row of data into the same cell. I don't know xlswriter but I guess the first and second parameters are the row and column. If you want to write all the data as a table you will need to change these on each write operation.

Something like this (untested)?

import sqlite3
from xlsxwriter.workbook import Workbook
workbook = Workbook('output2.xlsx')
worksheet = workbook.add_worksheet()

conn=sqlite3.connect('test.sqlite')
c=conn.cursor()
c.execute("select * from abc")
mysel=c.execute("select * from abc ")
for i, row in enumerate(mysel):
    print row
    worksheet.write(i, 0, row[0])
    worksheet.write(i, 1, row[1])
workbook.close()

To handle multiple columns you could do this

import sqlite3
from xlsxwriter.workbook import Workbook
workbook = Workbook('output2.xlsx')
worksheet = workbook.add_worksheet()

conn=sqlite3.connect('test.sqlite')
c=conn.cursor()
c.execute("select * from abc")
mysel=c.execute("select * from abc ")
for i, row in enumerate(mysel):
    for j, value in enumerate(row):
        worksheet.write(i, j, value)
workbook.close()

edited Nov 2, 2018 at 13:12

all_the_rage

251 gold badge1 silver badge4 bronze badges

answered Sep 7, 2013 at 22:22

Graeme Stuart

6,0632 gold badges29 silver badges46 bronze badges

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

6 Comments

ashir nasir Over a year ago

thanks it works. please advise how i can tweak this for loop to handle n number of rows and columns. e.g instead of 4 rows i have thousand of rows and 15 columns to dump in the sheet from sqlite

jmcnamara Over a year ago

Good answer. Just to note that as an alternative the inner for loop could be written as write_row(i, 0, row)

Graeme Stuart Over a year ago

presumably you mean worksheet.write_row(i, 0, row). Upvote if you like the answer ;-)

ashir nasir Over a year ago

hahah, yes you right sir, worksheet.write_row(i, 0, row) is the correct thing

jmcnamara Over a year ago

Correct worksheet.write_row(). I did upvote :-). And @ashir_nasir should accept since this is a good answer.

|

Taylan · Accepted Answer · 2016-03-25 21:56:19Z

4

import sqlite3
from xlsxwriter.workbook import Workbook
workbook = Workbook('output2.xlsx')
worksheet = workbook.add_worksheet()

conn=sqlite3.connect('test.sqlite')
c=conn.cursor()
c.execute("select * from abc")
mysel=c.execute("select * from abc ")
for i, row in enumerate(mysel):
    for j, value in enumerate(row):
        worksheet.write(i, j, row[j])
workbook.close()

answered Mar 25, 2016 at 21:56

Taylan

7461 gold badge5 silver badges14 bronze badges

Comments

Dharman · Accepted Answer · 2021-11-09 04:26:45Z

Instead of creating many CSVs sheets in the same file, you can create a XLSX file with many sheets. Depending on your needs, it can be a great solution. Here's what I've got.

import sqlite3
import xlsxwriter
from tkinter import filedialog # Used to get a path where to save the new excel file
from datetime import datetime # Used to name the file with today's date

DB_Path = filedialog.askdirectory() # OPTIONAL: Asks to select folder path
workbook = xlsxwriter.Workbook(DB_Path+"/"+"MyDatabaseInExcel  "+datetime.now().strftime("%Y %m %d")+'.xlsx') # Create file
conn = sqlite3.connect('MyDatabaseInSQLite3.db') # Connect to your database
cursor = conn.cursor() # Create the cursor
tables = list(cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")) # Get all table names
tables = list(map(lambda x: x[0], tables)) # convert the list of tuple to list of str
for table in tables:
    try: worksheet = workbook.add_worksheet(name=table[0:31]) # Sheet names in excel can have up to 31 chars
    except:pass
    for row_number, row in enumerate(cursor.execute('SELECT * FROM '+table)): # row is a tuple here
        for column_number, item in enumerate(row):
            try: worksheet.write(row_number, column_number, item) # Write the cell in the current sheet
            except:pass
workbook.close() # Saves the new document

I used some try just to prevent cracking the program for any reason and not to write the other sheets. For me it was interesting at a time in my project. If you want, you can not use it.

Collectives™ on Stack Overflow

how to import data from SQLITE to excel using xlsxwriter in python

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related