2

I have this code to import Excel data into SQLlite, but wondering how to do it into SQLAlchemy instead, without naming the fields manually.

Am not sure how to create the fields and schema automatically from Excel column fields in SQLAlchemy.

con = sqlite3.connect('test237.db')
wb = load_workbook(filename=r'test.xlsx')
sheets = wb.get_sheet_names()

for sheet in sheets:
    ws = wb[sheet] 
    columns= []
    query = 'CREATE TABLE ' + str(sheet) + '(ID INTEGER PRIMARY KEY AUTOINCREMENT'
    for row in ws.rows[0]:
        query += ', ' + row.value + ' TEXT'
        columns.append(row.value)
    query += ');'

    con.execute(query)

    tup = []
    for i, rows in enumerate(ws):
        tuprow = []
        for row in rows:
            tuprow.append(unicode(row.value).strip()) if unicode(row.value).strip() != 'None' else tuprow.append('')
        tup.append(tuple(tuprow))


    insQuery1 = 'INSERT INTO ' + str(sheet) + '('
    insQuery2 = ''
    for col in columns:
        insQuery1 += col + ', '
        insQuery2 += '?, '
    insQuery1 = insQuery1[:-2] + ') VALUES('
    insQuery2 = insQuery2[:-2] + ')'
    insQuery = insQuery1 + insQuery2

    con.execute(insQuery, tup)
con.close()
tensor
  • 3,088
  • 8
  • 37
  • 71
  • From this limited context I do not see how doing it in `sqlalchemy` would be better compared to the direct approach. Although it might be an overkill, if i needed to take excel data into sql format, i would use `pandas` with `sqlalchemy` as those are the tools I have anyways: [1) Read Excel](http://pandas.pydata.org/pandas-docs/stable/io.html#io-excel-reader), then [2) Write to sql](http://pandas.pydata.org/pandas-docs/stable/io.html#writing-dataframes) – van Dec 04 '16 at 08:54
  • here is an example: http://pyexcel.readthedocs.io/en/latest/showcases/db_injection.html – chfw Oct 08 '17 at 06:53

0 Answers0