éè¿pythonå°å¤§æ¹excelæ°æ®å¯¼å ¥mysqlï¼
å©ç¨Pythonä»EXCELä¸è¯»å两åæ°æ®åå¨å¨LISTä¸ç¶åè¿æ¥æ°æ®åºå©ç¨insertè¯å¥åLIST å 容ç»åï¼å¯¼å ¥æ°æ®åºï¼ç¶ååä»æ°æ®åºè¯»å表çå段åå 容ï¼åæ¾å°EXCELæ°è¡¨ä¸å³å¯ã
以ä¸ç导å
¥çæ ¸å¿ä»£ç ï¼
#æç
§è¯´æå®è£
å³å¯
import win32com.client as win32
import MySQLdb
app='Excel'
#å¯å¨EXCELç¨åº
xl=win32.gencache.EnsureDispatch('%s.Application' % app)
#æå¼ç¸åºçEXCELæ件
xlbook=win32.Dispatch('Excel.Application').Workbooks.Open('D:\\pyd\\opsdata.xls')
#读åEXCELå·¥å
·è¡¨ï¼æå¼è¦å¯¼å
¥æ°æ®ç表
sh=xlbook.Worksheets('sheet2')
#建ç«ä¸ä¸ªç©ºLISTå两个循ç¯åæ°
dfun=[]
jcount=2
ncount=1
#ä»ç¬¬ä¸åå¼å§åä¸å¾ªç¯ï¼æ¾å°ä¸ºç©ºçé£ä¸è¡è¿åè¡æ°ï¼å³ä¸ºç¬¬ncountè¡ä¸ºç©º
for ncount in range(1,65566):
if sh.Cells(ncount,1).Value==None:
break
else:
continue
#print ncountï¼æµè¯ä»£ç ï¼æµè¯ncountå¼
#EXCELçæ°æ®å®æ为第ä¸è¡æ¯å段ï¼ç¬¬äºè¡å¼å§æ¯æ°æ®ï¼æ
ä»ç¬¬2è¡å¼å§å¾ªç¯ï¼å°ä¸¤åæ°æ®å并å°ä¸ä¸ªLISTä¸
#LISTç»æ为[('a','b'),('c','d')]
for jcount in range(2,ncount):
dfun.append((sh.Cells(jcount,1).Value,sh.Cells(jcount,2).Value))
#å
³éEXCELç¨åº
#xl.Application.Quit()
#print dfunï¼æµè¯ä»£ç æµè¯dfunçæ°æ®
#建ç«ä¸ä¸ªç©ºLIST foç¨æ¥åæ¾å段ï¼è¯»åEXCELä¸ç¬¬ä¸è¡çå段åå¨å°foä¸
fo=[]
icount=1
for icount in range(1,2):
fo.append((sh.Cells(1,icount).Value,sh.Cells(1,icount+1).Value))
#æµè¯ä»£ç ï¼print fo
#æå¼MYSQLé¾æ¥
conn=MySQLdb.connect(host='localhost',user='root',passwd='0.789asd',db='python')
#è·å游æ æä½
cursor=conn.cursor()
#建ç«ä¸ä¸ªç©ºè¡¨
cursor.execute("create table test("+fo[0][0]+" varchar(100),"+fo[0][1]+" varchar(100));")
#å©ç¨executemanyå½ä»¤ï¼å°LISTï¼dfunä¸çæ°æ®éè¿insertè¯å¥åå
¥æ°æ®åº
cursor.executemany("""insert into test values(%s,%s);""" ,dfun)
#确认æ°æ®æä½ï¼æ³¨æå¦æ没æconn.commit()è¿ä¸ªå½ä»¤ï¼æ°æ®æ æ³è¢«æå
¥å°æ°æ®åºä¸
conn.commit()
#æ§è¡æ¥è¯¢æ£æ¥ç»æ
count = cursor.execute('select * from test')
print 'has %s record' % count
#é置游æ ä½ç½®
cursor.scroll(0,mode='absolute')
#æåææç»æ
results = cursor.fetchall()
#æµè¯ä»£ç ï¼print results
#è·åMYSQLéçæ°æ®å段
fields = cursor.description
#å°å段åå
¥å°EXCELæ°è¡¨ç第ä¸è¡
sh2=xlbook.Worksheets('sheet3')
#æ¸
空sheet3
sh2.Cells.Clear
for ifs in range(1,len(fields)+1):
sh2.Cells(1,ifs).Value=fields[ifs-1][0]
#å°è¯»åçæ°æ®å¡«å
¥å°ç¸åºçè¡åä¸
ics=2
jcs=1
for ics in range(2,len(results)+2):
for jcs in range(1,len(fields)+1):
sh2.Cells(ics,jcs).Value=results[ics-2][jcs-1]
#å
³éEXCELç¨åº
xl.Application.Quit()
#å
³é游æ åæ¥è¯¢é¾æ¥
cursor.close()
conn.close()