功能:将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()
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。