xlrd MySQL_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()