xls填充sql

xlrd >>> d=xlrd.open_workbook(‘D:/data.xls‘) #获取xls文件 >>> sheet=d.sheets()[0] #3种方式获取sheet >>> sheet=data.sheet_by_index(0) >>> sheet=data.sheet_by_name(‘sheetA‘) >>> sheet.row_values(0) #某一行数据 [1.0, 2.0, 3.0] >>> sheet.col_values(0) #某一列数据 [1.0, u‘a‘] >>> sheet.nrows #行数 2 >>> sheet.ncols #列数 3 >>> sheet.cell(0,0).value #获取值 1.0 >>> table.row(0)[0].value #获取值 1.0 >>> table.col(0)[0].value #获取值 1.0

2.

 

import MySQLdb,xlrd,sys
db_config={‘user‘:‘root‘,‘passwd‘:‘passwd‘,‘host‘:‘localhost‘,‘db‘:‘test‘,‘port‘:3306}

def get_connction(db_config):
    ‘‘‘
    返回数据库的链接,游标信息
    ‘‘‘
    try:
        conn=MySQLdb.connect(**db_config)
        cur=conn.cursor()
    except Exception as e:
        print ‘Can\‘t Connect the database: ‘,e
        sys.exit(1)
    return conn,cur


def main():
    conn,cur=get_connction(db_config)
    sql=‘‘‘create table if not exists basic_info
        (id int primary key not null,
        name char(20),
        gender char(2)
        )‘‘‘
    cur.execute(sql) 
    excel=xlrd.open_workbook(r‘D:/data.xls‘)
    sheet=excel.sheets()[0]
    nrow=sheet.nrows
    for row in range(1,nrow):  #获取xls每行数据,insert入表，table中id是int型号,填充时也用%s
        cur.execute(‘insert into basic_info values (%s,%s,%s)‘,tuple(sheet.row_values(row)))
    conn.commit()
    cur.close()
    conn.commit()

if __name__==‘__main__‘:
    main()

 

xls填充sql

标签：