Tried seeing multiple places on how to convert a SQL database file(.db, .sqlite, .sqlite3, .db3) to an excel sheet(.xlsx) directly in python. I was unable to find anything useful. Can anyone tell how to do it, or link a useful resource?
That task is going to by highly dependent on the structure and type of database in question. If we’re just talking simple SQLite databases, start with using the sqlite3 module to connect to the database file. From there you could write query results to a csv file that can be opened and resaved in excel or use another module (like openpyxl, xlsxwriter, or pandas) to write directly to an excel sheet.
Personally, I would probably use pandas for convenience. Create a connection object using sqlite3 and feed your SQL query and the connection to panda’s read_sql function. It will return a DataFrame of the resulting table or query and you can use the DataFrame.to_excel function to save it to an excel file.
import pandas as pd
import sqlite3
connection = sqlite3.connect("path/to/db_file.sqlite")
# Construct your SQL query to get the databse info you need. Alternatively,
# you could grab individual tables as DataFrames and use pandas to merge them.
# If you just need a table, you can specify the table name instead of a SQL query
query = "SELECT * FROM mainTable LEFT JOIN secondaryTable ON mainTable.Id = secondaryTable.mainId"
df = pd.read_sql(query, connection)
df.to_excel("example.xlsx")
1 Like
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.