将数据从excel导出到sqlite数据库

问题描述

我有一个包含所有数据的Excel工作表.我想要相同的数据在sqlite数据库表中.目前，我必须手动输入1 by 1字段.有什么方法可以直接将数据导出到sqlite数据库表吗?

I have a excel sheet with full of data. I want same data in sqlite database table. Currently I have to manually enter 1 by 1 field. Is there any way to direct exporting data to sqlite database table?

推荐答案

尝试以下新鲜代码exceltosql:

Try this fresh code exceltosql:

'''
This code uses the openpyxl package for playing around with excel using Python code
to convert complete excel workbook (all sheets) to an SQLite database
The code assumes that the first row of every sheet is the column name
Every sheet is stored in a separate table
The sheet name is assigned as the table name for every sheet
'''

import sqlite3
import openpyxl
from openpyxl import load_workbook
import re

def slugify(text, lower=1):
    if lower == 1:
        text = text.strip().lower()
    text = re.sub(r'[^\w _-]+', '', text)
    text = re.sub(r'[- ]+', '_', text)
    return text

#Replace with a database name
con = sqlite3.connect('test.db')
#replace with the complete path to youe excel workbook
wb = load_workbook(filename=r'abc.xlsx')

sheets = wb.get_sheet_names()

for sheet in sheets:
    ws = wb[sheet]

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

    con.execute(query)

    tup = []
    for i, rows in enumerate(ws):
        tuprow = []
        if i == 0:
            continue
        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(slugify(sheet)) + '('
    insQuery2 = ''
    for col in columns:
        insQuery1 += col + ', '
        insQuery2 += '?, '
    insQuery1 = insQuery1[:-2] + ') VALUES('
    insQuery2 = insQuery2[:-2] + ')'
    insQuery = insQuery1 + insQuery2

    con.executemany(insQuery, tup)
    con.commit()

con.close()

这篇关于将数据从excel导出到sqlite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！