使用pyexcelerator-0.6.3a读取excel并写入mysql数据库

本文链接：https://blog.csdn.net/midi13/article/details/3390320

编码解码（网上搜的）

 
 # encoding.py
'''
module to help encode and/or decode string.
no rights reserved.
'''
import sys
defaultencoding = sys.stdout.encoding
def str2uni(string, enc = defaultencoding):
    '''convert str to unicode
    you can specify encoding. default to local encoding.
    string can be a int.
    '''
    if isinstance(string, str):
        return unicode(string, enc)
    elif isinstance(string, int):
        return unicode(str(string), enc)
    else:
        return string
def uni2str(string, enc = defaultencoding):
    '''convert unicode to str
    you can specify encoding. default to local encoding.
    '''
    if isinstance(string, unicode):
        return string.encode(enc)
    else:
        return string
def utf82uni(string):
    '''convert utf8 str to unicode'''
    return str2uni(string, "utf-8")
def uni2utf8(string):
    '''convert unicode to utf8 str'''
    return uni2str(string, "utf-8")
def local2utf8(string):
    '''convert local str to utf8 str'''
    return uni2utf8(str2uni(string))
def utf82local(string):
    '''convert utf8 str to local str'''
    return uni2str(utf82uni(string))
 

 
 #-*- encoding:utf-8 -*-
#使用pyexcelerator-0.6.3a读取excel并写入mysql数据库
from pyExcelerator import *
import MySQLdb,encoding
#----------------------------------------------------------------------
SERVER = 'localhost'
USERNAME = 'root'
PASSWD = ''
DB = 'db'
conn = MySQLdb.connect(host=SERVER,db=DB,user=USERNAME,passwd=PASSWD,charset='utf8')
curs = conn.cursor()
sheets = parse_xls(u'd:/test.xls')
read_xls = lambda x, y, z: sheets[x][1][(y,z)] 
lenth = len(sheets[0][1])
sqlstr = ''
read_xls(0,182,2)
for r in range(24,181):
    for c in range(0,8,2):
        print c
        print c+1
        f1 = str(encoding.local2utf8(read_xls(0,r,c)))#如果是数字，转换成字符串
            
            
        f2 = encoding.local2utf8(read_xls(0,r,c+1))
        sqlstr="""
           INSERT INTO custom_cde (cde,name)
           VALUES
             (""" + "'" + f1 + "', " + "'" + f2 + "')" + """
         """
        print sqlstr
        curs.execute(sqlstr)
    
    
#提交事务    
conn.commit()
curs.close()
conn.close()
 

 
 #-*- encoding:utf-8 -*-
#查询mysql数据库，显示到wxPython的grid
import wx
import wx.grid
import MySQLdb,encoding
import DBConnection
class SimpleGrid(wx.grid.Grid):
    def __init__(self, parent):
        wx.grid.Grid.__init__(self, parent, -1)
        self.CreateGrid(7, 3)
        
        conn = DBConnection.DBConnection('localhost','codelist','root','')
        cursor = conn.getCursor()
        cursor.execute ("SELECT * FROM cntr_dmg_cde")
        rows = cursor.fetchall()
        i = 0
        self.SetColLabelValue(0, u"中文方位")
        self.SetColLabelValue(1, u"英文方位")
        self.SetColLabelValue(2, u"代码")
        for row in rows:
            tempLen = len(row)
            for col in range(1,tempLen):
                tempStr = encoding.utf82local(row[col])
                
                self.SetCellValue(i, col-1, tempStr)
            i = i + 1
        cursor.close()
        conn.close()
class TestFrame(wx.Frame):
    def __init__(self, parent):
        wx.Frame.__init__(self, parent, -1, u"标题",
                size=(275, 275))
        grid = SimpleGrid(self)
        
if __name__ == '__main__':
    app = wx.PySimpleApp()
    frame = TestFrame(None)
    frame.Show(True)
    app.MainLoop()
 

 
 #-*- encoding:utf-8 -*-
# 操作数据库的封装
# 因为自己擅长java，所以也用oop的思想，把操作数据库封装了一下
# 这个是指初步，还有待完善
import MySQLdb
########################################################################
class DBConnection:
    """"""
    __conn = None
    #----------------------------------------------------------------------
    def __init__(self, host, db, user, password, charset='utf8'):
        """Constructor"""
        
        #if charset == '':
            #charset = 'utf8'
        self.__conn = MySQLdb.connect(host=host,db=db,user=user, passwd=password,charset=charset)
        print ''
    #----------------------------------------------------------------------
    def getConn(self):
        """"""
        return self.__conn
    #----------------------------------------------------------------------
    def getCursor(self):
        """获取游标（奇怪，在java中没听说过这个概念)"""
        return self.getConn().cursor()
    #----------------------------------------------------------------------
    def getResultSet(self):
        """获取结果集"""
        return self.getConn().cursor().fetchAll()
    #----------------------------------------------------------------------
    def getRow(self):
        """get a row"""
        rs = self.getResultSet()
    #----------------------------------------------------------------------
    def close(self):
        """Close Database connecton"""
        self.__conn.close()    
    
#----------------------------------------------------------------------
def test():
    """测试方法"""
    dbOjbect = DBConnection('localhost','codelist','root','')
    cursor = dbOjbect.getCursor()
    cursor.execute ("SELECT * FROM cntr_dmg_cde")
    rs = cursor.fetchall()
    for r in rs:
        for i in range(len(r)):
            print r[i]
        print
       
if __name__ == '__main__':
    test()
 