[PYTHON] Excel-> pandas-> sqlite

In order to throw it from an Excel sheet into sql, you have to install an ODBC driver or write vba steadily, but python is easy.

↑ Assuming that such a sheet is open in C: \ temp \ Book1.xlsx It's easy to throw it in via pandas. You can also create db and table in one shot with to_sql without having to do troublesome create.

# -*- coding: utf-8 -*-
from xlwings import Workbook, Range
import pandas as pd
import pandas.io.sql as psql
import sqlite3

#Import an already open Excel workbook
wb = Workbook(r'C:\temp\Book1.xlsx')
#Capture data from A1 to the end
data = Range('A1').table.value
#Convert to pandas dataframe
df = pd.DataFrame(data[1:], columns=data[0])

with sqlite3.connect('fruit.db') as conn:
    psql.to_sql(df, 'fruit', conn)

cur = conn.execute('select * from fruit')
print(cur.fetchall())

This time I used xlwings to read the dynamically edited Excel, but if it is a saved file, you can read it with pd.read_excel.

I think this is probably the easiest (flag)