#!/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])
f.close()
time2=time.time()
print time.ctime()
#计算导入数据的时间
print 'importing time is %f'%(time2-time1)
