import xlrd
import pymysql
# import importlib
# importlib.reload(sys) #³öÏÖÄØreload´íÎóʹÓÃ
def open_excel():
  try:
    book = xlrd.open_workbook("XX.xlsx") #ÎļþÃû£¬°ÑÎļþÓëpyÎļþ·ÅÔÚͬһĿ¼ÏÂ
  except:
    print("open excel file failed!")
  try:
    sheet = book.sheet_by_name("sheetÃû³Æ")  #execlÀïÃæµÄworksheet1
    return sheet
  except:
    print("locate worksheet in excel failed!") 
 #Á¬½ÓÊý¾Ý¿â
try:
  db = pymysql.connect(host="127.0.0.1",user="root",
    passwd="XXX",
    db="XXX",
    charset='utf8')
except:
  print("could not connect to mysql server")
 
def search_count():
  cursor = db.cursor()
  select = "select count(id) from XXXX" #»ñÈ¡±íÖÐxxxxx¼Ç¼Êý
  cursor.execute(select) #Ö´ÐÐsqlÓï¾ä
  line_count = cursor.fetchone()
  print(line_count[0])
 
 
def insert_deta():
  sheet = open_excel()
  cursor = db.cursor()
  for i in range(1, sheet.nrows): #µÚÒ»ÐÐÊDZêÌâÃû£¬¶ÔÓ¦±íÖеÄ×Ö¶ÎÃûËùÒÔÓ¦¸Ã´ÓµÚ¶þÐпªÊ¼£¬¼ÆËã»úÒÔ0¿ªÊ¼¼ÆÊý£¬ËùÒÔÖµÊÇ1
 
    name = sheet.cell(i,0).value #È¡µÚiÐеÚ0ÁÐ
    data = sheet.cell(i,1).value#È¡µÚiÐеÚ1ÁУ¬ÏÂÃæÒÀ´ÎÀàÍÆ
    print(name)
    print(data)
    value = (name,data)
    print(value)
    sql = "INSERT INTO XXX(name,data)VALUES(%s,%s)"
    cursor.execute(sql,value) #Ö´ÐÐsqlÓï¾ä
    db.commit()
  cursor.close() #¹Ø±ÕÁ¬½Ó
insert_deta()
db.close()#¹Ø±ÕÊý¾Ý
print ("ok ")

import pymysql
import xlrd 
# Á¬½ÓÊý¾Ý¿â
try:
  db = pymysql.connect(host="127.0.0.1", user="root",
             passwd="XXX",
             db="XXX",
             charset='utf8')
except:
  print("could not connect to mysql server") 
def open_excel():
  try:
    book = xlrd.open_workbook("XXX.xlsx") #ÎļþÃû£¬°ÑÎļþÓëpyÎļþ·ÅÔÚͬһĿ¼ÏÂ
  except:
    print("open excel file failed!")
  try:
    sheet = book.sheet_by_name("XXX")  #execlÀïÃæµÄworksheet1
    return sheet
  except:
    print("locate worksheet in excel failed!") 
def insert_deta():
  sheet = open_excel()
  cursor = db.cursor()
  row_num = sheet.nrows
  for i in range(1, row_num): # µÚÒ»ÐÐÊDZêÌâÃû£¬¶ÔÓ¦±íÖеÄ×Ö¶ÎÃûËùÒÔÓ¦¸Ã´ÓµÚ¶þÐпªÊ¼£¬¼ÆËã»úÒÔ0¿ªÊ¼¼ÆÊý£¬ËùÒÔÖµÊÇ1
    row_data = sheet.row_values(i)
    value = (row_data[0],row_data[1],row_data[2],row_data[3])
    print(i)
    sql = "INSERT INTO demo_yangben(xxx,xxxx,xxxx,xxxx)VALUES(%s,%s,%s,%s)"
    cursor.execute(sql, value) # Ö´ÐÐsqlÓï¾ä
    db.commit()
  cursor.close() # ¹Ø±ÕÁ¬½Ó
open_excel()
insert_deta()

import pymysql
import xlrd
import sys 
'''
  Á¬½ÓÊý¾Ý¿â
  args£ºdb_name£¨Êý¾Ý¿âÃû³Æ£©
  returns:db
 
''' 
def mysql_link(de_name):
  try:
    db = pymysql.connect(host="127.0.0.1", user="xxx",
               passwd="xxx",
               db=xxx,
               charset='utf8')
    return db
  except:
    print("could not connect to mysql server")
 
'''
  ¶ÁÈ¡excelº¯Êý
  args£ºexcel_file£¨excelÎļþ£¬Ä¿Â¼ÔÚpyÎļþͬĿ¼£©
  returns£ºbook
'''
 
 
def open_excel(excel_file):
  try:
    book = xlrd.open_workbook(excel_file) # ÎļþÃû£¬°ÑÎļþÓëpyÎļþ·ÅÔÚͬһĿ¼ÏÂ
    print(sys.getsizeof(book))
    return book
  except:
    print("open excel file failed!")
 
 
'''
  Ö´ÐвåÈë²Ù×÷
  args:db_name£¨Êý¾Ý¿âÃû³Æ£©
     table_name(±íÃû³Æ£©
     excel_file£¨excelÎļþÃû£¬°ÑÎļþÓëpyÎļþ·ÅÔÚͬһĿ¼Ï£©
 
'''
 
 
def store_to(db_name, table_name, excel_file):
  db = mysql_link(db_name) # ´ò¿ªÊý¾Ý¿âÁ¬½Ó
  cursor = db.cursor() # ʹÓà cursor() ·½·¨´´½¨Ò»¸öÓαê¶ÔÏó cursor
 
  book = open_excel(excel_file) # ´ò¿ªexcelÎļþ
  sheets = book.sheet_names() # »ñÈ¡ËùÓÐsheet±íÃû
  for sheet in sheets:
    sh = book.sheet_by_name(sheet) # ´ò¿ªÃ¿Ò»Õűí
    row_num = sh.nrows
    print(row_num)
    list = [] # ¶¨ÒåÁбíÓÃÀ´´æ·ÅÊý¾Ý
    num = 0 # ÓÃÀ´¿ØÖÆÿ´Î²åÈëµÄÊýÁ¿
    for i in range(1, row_num): # µÚÒ»ÐÐÊDZêÌâÃû£¬¶ÔÓ¦±íÖеÄ×Ö¶ÎÃûËùÒÔÓ¦¸Ã´ÓµÚ¶þÐпªÊ¼£¬¼ÆËã»úÒÔ0¿ªÊ¼¼ÆÊý£¬ËùÒÔÖµÊÇ1
      row_data = sh.row_values(i) # °´ÐлñÈ¡excelµÄÖµ
      value = (row_data[0], row_data[1], row_data[2], row_data[3], row_data[4], row_data[5], \
           row_data[6], row_data[7], row_data[8], row_data[9], row_data[10], row_data[11], row_data[12],
           row_data[13], row_data[14])
      list.append(value) # ½«Êý¾ÝÔÝ´æÔÚÁбí
      num += 1
      if( num>= 10000 ): # ÿһÍòÌõÊý¾ÝÖ´ÐÐÒ»´Î²åÈë
        print(sys.getsizeof(list))
        sql = "INSERT INTO " + table_name + " (time, xingbie, afdd, xzb, yzb, cfbj, jjlbmc, \
        bjlbmc, bjlxmc, bjlxxlmc, gxqymc,gxdwmc, afql, afxqxx, cjdwmc)\
        VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        cursor.executemany(sql, list) # Ö´ÐÐsqlÓï¾ä
 
        num = 0 # ¼ÆÊý¹éÁã
        list.clear() # Çå¿Õlist
        print("worksheets: " + sheet + " has been inserted 10000 datas!")
 
  print("worksheets: " + sheet + " has been inserted " + str(row_num) + " datas!")
  db.commit() # Ìá½»
  cursor.close() # ¹Ø±ÕÁ¬½Ó
  db.close() 
 if __name__ == '__main__':
  store_to('demo', 'demo_yangben', 'xxx.xlsx')