python入门之excel读数据到mysql

功能：将Excel数据导入到MySQL数据库
练习使用sqlite3 将excel的数据导入到mysql
练习sql语句-查看doc
https://docs.python.org/3/lib...
http://www.runoob.com/sqlite/...

    import xlrd
    import sqlite3

    def save_cd_table():
        # 创建插入SQL语句
        query = '''INSERT INTO orders (job_number, name, section, date, time_in, time_out) VALUES (%s, %s, %s, %s, %s, %s)'''
        cursor.execute('''CREATE TABLE cd_table(job_number, name, section, date, time_in, time_out)''')
        # 创建一个for循环迭代读取xls文件每行数据的, 从第二行开始是要跳过标题
        for r in range(1, sheet.nrows):
          

        # 关闭游标
        cursor.close()
    
        # 提交
        database.commit()
    
        # 关闭数据库连接
        database.close()
    
        # 打印结果
        print ("")
        print ("Done! ")
        print ("")
        #columns = str(sheet.ncols)
        #rows = str(sheet.nrows)
        #print ("我刚导入了  %d columns ",columns )
    
    def read_cd_table(cursor):
        #t=('10759',)
        cursor.execute("SELECT * FROM cd_table WHERE job_number =10759 ")
        result=cursor.fetchall()
        print(result)
        #for row in cursor.execute('SELECT * FROM cd_table ORDER by name'):
        #for row in cursor.execute("SELECT * FROM cd_table WHERE job_number=10759"):
        #    print(row)
    
    def main():
        # Open the workbook and define the worksheet
        book = xlrd.open_workbook("CD.xls")
        #除了sheet_by_index之外还可以sheet_by_name,另外xlwt的写操作词篇暂不赘述
        #对于excel的操作有很多有意思的方法，此后会专门写一篇
        sheet = book.sheet_by_index(0)
        #建立一个MySQL连接
        database = sqlite3.connect ("mysql_cd.db")
        # 获得游标对象, 用于逐行遍历数据库数据
        cursor = database.cursor()
        rows = str(sheet.nrows)
        try:
            save_cd_table()
        except :
            print(".db file is exist")
        finally:
            print('out..')
        read_cd_table(cursor)
        print(rows)
        cursor.close()
        database.close()
    
    if __name__ == '__main__':
    main()