extel代码练习题目

1、写一个函数，传入一个表名，把这个表里面的数据导出到excel中

def export_excel('students'):

pass

students.xls

思路  

 

 

 

我的代码 

import pymysql,xlwt
def export_excel(ces):
    host = '118.24.3.40'
    user = 'jxz'
    password = '123456'  # 字符串
    db = 'jxz'
    port = 3306  # int类型
    connect = pymysql.connect(host=host, user=user,
                              password=password,
                              port=port, db=db,
                              autocommit=True
                              )
    cur = connect.cursor(pymysql.cursors.DictCursor)  # 建立游标,仓库管理员
    # pymysql.cursors.DictCursor
    cur.execute('select * from %s;'%(ces))
    # print(cur.description)  # 表的描述
    # print(cur.fetchall())  # 拿到所有的结果
    # for data in cur:  也可以循环数据
    #     print(data)
    return cur.fetchall()
    cur.close()
    connect.close()
export_excel('students_2')


def xie():
    book = xlwt.Workbook()#新建一个extel
    sheet = book.add_sheet('students')  #新建一个sheet1
    stus=export_excel('students_2')
    for index, key in enumerate(stus[0]):  # 写表头  这块没有写出来
        sheet.write(0, index, key)
    row = 1
    for stu in stus:  # 控制行
        col = 0  # 列号这是while循环
        for s in stu.values():  # 控制列
            sheet.write(row, col, s)
            print(col)
            col += 1
        row += 1  # 行号在变
    book.save('students.xls')
xie()

标准写法：

import tools


def main():
    table_name = input('请输入你要导出的表名：').strip()
    table_exist_sql = "SELECT table_name FROM information_schema.TABLES WHERE table_name ='%s';" % table_name
    if tools.execute_sql(table_exist_sql):
        query_sql = 'select * from %s;' % table_name
        data = tools.execute_sql(query_sql)
        if data:
            tools.write_excel(table_name,data)
            print('导出完成')
        else:
            print('表中无数据，无法导出')
    else:
        print('表不存在！')



if __name__ == '__main__':
    main()