如何用Python3寫一段將Excel數據導入SQL資料庫？

12-24

提主沒有說導入到哪個sql資料庫，我這裡就以導入sqlite3為例。

主要分為2步：1，讀取xls文件的數據； 2，寫入sql資料庫

以下excel源文件截圖

import xlrd

import sqlite3

file = "H:\xls\全國省市縣列表.xls"

data = xlrd.open_workbook(file)

table = data.sheets()[0] #第一個sheets

datalist=[]

for i in range(1,table.nrows): #總行數

datalist.append(tuple(table.row_values(i)))

conn = sqlite3.connect("d:\database\country.db") #資料庫文件的路徑

cursor = conn.cursor()

cursor.execute("create table country(province varchar(20),num int,town varchar(30),town varchar(30))") ##創建表

sql = "insert into country(province,num,city,town) values(?,?,?,?)"

cursor.executemany(sql,datalist) #插入數據

conn.commit() #提交數據到資料庫

conn.close() #關閉連接

print("導入完成")

導入不同的資料庫有不同的模塊，比如mysql使用pymysql模塊，具體根據你的需求來。

向來主張拿來主義，下面只是mysql的一個實現，自動由excel的表頭構建表，能看懂的，自己拿去。

#######################################

#/usr/bin/env python3

# -*- coding:utf-8 -*-

import os

import sys

import getopt

import openpyxl

import pymysql

import datetime

import re

import itertools

#資料庫連接配置文件

__db_config = {

"host":"127.0.0.1",

"port":3306,

"user":"your.db.username",

"password":"your.db.password",

"db":"xls",

"charset":"utf8"

}

####################

def usage():

print( "xls2db.py usage:" )

print( " xls2db filename(xlsx)" )

print( " version: 1.0.1" )

print( " by sun" )

exit(1)

#主函數執行

if __name__ == "__main__":

if len(sys.argv) != 2:

usage()

fname = sys.argv[1]

if not os.path.exists(fname):

print("file not exist:", fname)

exit(2)

#創建鏈接

connection = pymysql.connect(**__db_config)

print("loadfile:", fname)

wb = openpyxl.load_workbook(fname)

for shname in wb.get_sheet_names():

ws = wb.get_sheet_by_name(shname)

#過濾空表

if ws.max_row <= 1:

break

#構建建表SQL

sql = "DROP TABLE IF EXISTS `xls`.`%s`;
" % (shname)

sql += "CREATE TABLE `xls`.`%s` (
`indx` INT NOT NULL AUTO_INCREMENT,
" % (shname)

#構建表的結構

for col in ws.iter_cols(max_row=2):

if not isinstance(col[0].value, str):

break;

if col[1].is_date:

sql += " `%s` DATETIME NULL DEFAULT NULL,
" % (col[0].value)

else:

sql += " `%s` VARCHAR(64) NULL DEFAULT NULL,
" % (col[0].value)

sql += " PRIMARY KEY (`indx`)
) DEFAULT CHARACTER SET = utf8;
"

with connection.cursor() as cursor:

row_count = cursor.execute( sql );

print("create table:", shname)

#構建自增主鍵

indx = itertools.count(1,1)

#構建插入語句

sql = "INSERT IGNORE INTO `xls`.`%s` " % (shname)

for row in ws.iter_rows(min_row=2):

insql = sql

+ "VALUES (%s, " % next(indx)

+ ", ".join([""%s"" % "".join(filter(lambda x: x not in(""""), str(var.value))) for var in row])

+ ")
"

with connection.cursor() as cursor:

row_count = cursor.execute( insql );

connection.commit()

print("insert table(%s), items(%s)" % (shname, next(indx)))

#關閉數據鏈接

connection.close()

這個問題蠻複雜的，我之前處理過類似的問題，首先要看你說的導入是什麼概念，sqlserver的操作界面有直接導入的功能，其他資料庫不清楚，這種就是把數據直接塞進資料庫，但是Python得server包不一定支持這個功能。我之前的做法是把excel 數據讀取之後放到列表裡面，然後在編輯sql 查詢語言直接插數據到資料庫，需要用到value 關鍵字，資料庫是server08，05版的不支持value。這種方法效率不高一次最多插入1000行數據，需要編輯循環多次插入，這種方法好處就是比較靈活，可以選擇性插入想要的數據。其他資料庫應該可以用類似的方法。包用的是pypysqlserver這個包編輯更簡單。

1、用pandas庫讀取excel ，pandas.read_excel()

2、用pandas.DataFrame.to_sql()存入資料庫

搞定