使用Python读Excel数据Insert到MySQL

梦里梦外; 2022-07-12 14:50 197阅读 0赞

\#!/usr/bin/env python

\#coding:utf-8

import xlrd

import MySQLdb

import datetime

xlsfile=r'C:\\Users\\XUWU\\Desktop\\data.xlsx'

book = xlrd.open\_workbook(xlsfile)

\#获取sheet的数量

count = len(book.sheets())

\#设置连接数据库

database = MySQLdb.connect(host='192.168.1.30',user='root',passwd='123456',db='crm')

\#设置字符集

database.set\_character\_set('utf8')

cursor = database.cursor()

cursor.execute('SET NAMES utf8;')

cursor.execute('SET CHARACTER SET utf8;')

cursor.execute('SET character\_set\_connection=utf8;')

starttime = datetime.datetime.now()

print '开始时间：%s' % (starttime)

\#循环sheet

for i in range(0,count-1):

sheet = book.sheet\_by\_index(i)

query = """INSERT INTO bill\_test ( member\_id, name, tel, phone, dq\_datetime, address, parking) VALUES ( %s, %s, %s, %s, %s, %s, %s)"""

\#循环每一行

for r in range(1, sheet.nrows):

\#idseq      = sheet.cell(r,0).value

member\_id   = sheet.cell(r,1).value

name        = sheet.cell(r,2).value

tel         = sheet.cell(r,3).value

phone       = sheet.cell(r,4).value

\#dq\_datetime = sheet.cell(r,5).value

\#读日期这里要处理一下，不然全变成数字了

dq\_datetime\_num=xlrd.xldate\_as\_tuple(sheet.cell(r,5).value,0)

dq\_datetime = '%s/%s/%s' % (dq\_datetime\_num\[0\],dq\_datetime\_num\[1\],dq\_datetime\_num\[2\])

address     = sheet.cell(r,6).value

parking     = sheet.cell(r,7).value

values = (member\_id, name, tel, phone, dq\_datetime, address, parking)

\#print query,values

cursor.execute(query, values)

cursor.close()

database.commit()

database.close()

endtime=datetime.datetime.now()

print '结束时间:%s' % (endtime)

print '用时：%s 秒' % (endtime-starttime)

CREATE TABLE \`bill\_test\` (

\`idseq\` mediumint(10) unsigned NOT NULL AUTO\_INCREMENT,

\`member\_id\` int(10) DEFAULT NULL,

\`name\` varchar(20) DEFAULT NULL,

\`tel\` varchar(20) DEFAULT NULL,

\`phone\` varchar(20) DEFAULT NULL,

\`dq\_datetime\` varchar(20) DEFAULT NULL,

\`address\` varchar(200) DEFAULT NULL,

\`parking\` varchar(200) DEFAULT NULL,

PRIMARY KEY (\`idseq\`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

**使用Python从 MySQL写数据到Excel**

\#!/usr/bin/env python

\#coding:utf-8

import xlwt

import MySQLdb

import datetime

database = MySQLdb.connect(host='192.168.1.30',user='root',passwd='123456',db='crm')

\#设置字符集

database.set\_character\_set('utf8')

cursor = database.cursor()

cursor.execute('SET NAMES utf8;')

cursor.execute('SET CHARACTER SET utf8;')

cursor.execute('SET character\_set\_connection=utf8;')

starttime = datetime.datetime.now()

print '开始时间：%s' % (starttime)

\#通过SQL得到该表有多少行，如果想取出指定的数据，只需要在后面加where条件即可。

sql2 = 'select count(\*) from bill\_test;';

cursor.execute(sql2)

count\_rows=cursor.fetchone()\[0\]

wbk = xlwt.Workbook(encoding='utf-8',style\_compression=0)

sheet = wbk.add\_sheet('sheet 1', cell\_overwrite\_ok=True)

\#设置写excel的样式

style = xlwt.XFStyle()

font = xlwt.Font()

font.name = 'Times New Roman'

\#0x0190设置字体为20，默认为0x00C8 字体为10 ，0x00C8为十六进制的数字

font.height = 0x0190

font.bold = True

style.font = font

\#查询得到该表有多少列

query\_colums="select count(\*) from information\_schema.COLUMNS where TABLE\_SCHEMA='crm' and table\_name='bill\_test';"

cursor.execute(query\_colums)

count\_cols = cursor.fetchone()\[0\]

sql = 'select member\_id, name, tel, phone, dq\_datetime, address, parking from bill\_test;'

cursor.execute(sql)

\#定义所有的列名，共7列

columnName = \['账号','名称','电话','手机','到期日期','地址','园区名称'\]

\#将列名插入表格，共7列

for i in range(len(columnName)):

sheet.write(0,i,columnName\[i\],style)

\#通过循环取出每一行数据，写入excel

for i in range(1,count\_rows-1):

data = cursor.fetchone()

for j in range(0,count\_cols-1):

sheet.write(i,j,data\[j\],style)

cursor.close()

database.close()

wbk.save('C:\\Users\\XUWU\\Desktop\\data01.xls')

endtime=datetime.datetime.now()

print '结束时间:%s' % (endtime)

print '用时：%s 秒' % (endtime-starttime)

**python之TXT数据导入数据库**

为了导入数据，可以先对数据做些处理，让其更容易导入数据库

\#!/usr/bin/python

\#coding=utf-8

import \_mysql,sys,time

\#读入数据函数

def add\_data(id,name,created\_time):

try:

conn=\_mysql.connect('127.0.0.1','root','')

conn.query("set names utf8")

conn.query("insert into mysql.test3(%s,%s,%s) values('%s','%s','%s')"%('object\_id','object\_name','created',id,name,created\_time))

result=conn.use\_result()

conn.close()

except \_mysql.Error,e:

print ("error %d:%s" % (e.args\[0\],e.args\[1\]))

sys.exit(1)

if \_\_name\_\_ =="\_\_main\_\_":

f= open("/opt/testdata/aaa.txt","r")

time1=time.time()

print time.ctime()

\#读出第一行数据，作为数据表的段名

line=f.readline()

content=line.strip().split(",")

conn0=\_mysql.connect('127.0.0.1','root','')

print 'connection is builded succesfully'

conn0.query("drop table if exists mysql.test3")

conn0.query("create table mysql.test3(%s varchar(90),%s varchar(90),%s varchar(90))"%(content\[0\]\[1:-1\],content\[1\]\[1:-1\],content\[2\]\[1:-1\]))

conn0.close()

\#运用next函数，让for循环从第二行开始读数据

next(f)

for line in f:

\#做一些处理，让每一段分开，放置在一个列表中

content=line.strip().split(",")

add\_data(id=content\[0\]\[1:-1\],name=content\[1\]\[1:-1\],created\_time=content\[2\]\[1:-1\])

使用Python读Excel数据Insert到MySQL

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 python读取excel数据到mysql

相关使用Python采集web质量数据到Excel表

相关 python3操作MySQL：insert插入数据

相关 Python保存数据到Excel文件

相关 python根据文件名列表读excel数据

相关使用Python读Excel数据Insert到MySQL

相关 EXCEL与Mysql数据相互同步---EXCEL数据上传到Mysql,从Mysql下载数据到EXCEL__1

相关 EXCEL与Mysql数据相互同步---EXCEL数据上传到Mysql,从Mysql下载数据到EXCEL__2

相关使用Python将Excel中的数据导入到MySQL

相关百万数据读写到excel