python 3 excelテーブルをmongodb/mysqlデータベースにインポート
あまり話さないで、直接コードをつけます.
1.mongodb:
2.mysql:
1.mongodb:
import xlrd
import json
from pymongo import MongoClient
def dump_excel(file):
#
client = MongoClient('localhost',27017)
db = client.scrapy
account = db.user
data = xlrd.open_workbook(file)
table = data.sheets()[0]
# excel mongodb
rowstag = table.row_values(0)
nrows = table.nrows
returnData = {}
for i in range(1,nrows):
# excel , json
returnData[i] = json.dumps(dict(zip(rowstag,table.row_values(i))))
#
returnData[i] = json.loads(returnData[i])
account.insert(returnData[i])
return returnData
if __name__ == '__main__':
dump_excel('test.xls')
2.mysql:
import xlrd
import pymysql
def dump_excel(cur,file):
query = """INSERT INTO user(u_id,name,address,sex) values(%s,%s,%s,%s)"""
data = xlrd.open_workbook(file)
sheets = data.sheet_names()
datasheet = data.sheet_by_name(sheets[0])
nrows = datasheet.nrows
ncols = datasheet.ncols
for i in range(1,nrows):
# row = datasheet.row(i)
# print(row)
sqlstr = []
for j in range(0,ncols):
sqlstr.append(datasheet.cell_value(i,j))
valuestr = [int(sqlstr[0]), str(sqlstr[1]), str(sqlstr[2]), str(sqlstr[3])]
cur.execute(query,valuestr)
def data_contnect():
con = pymysql.connect(
host='localhost', port=3306, user='root', password='root', charset='utf8mb4'
)
cur = con.cursor()
cur.execute("drop database if exists scrapy")
cur.execute("create database scrapy")
cur.execute("use scrapy")
sql = """create table if not exists user(u_id INT (11),name VARCHAR (20),address VARCHAR (45),sex VARCHAR (2))"""
cur.execute(sql)
dump_excel(cur,'test.xls')
cur.close()
con.commit()
con.close()
if __name__ == '__main__':
data_contnect()