Python操作MySql写入多张表到数据库

Python操作MySql写入多张表到数据库

Knowledge:

• Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table
• To insert multiple rows into a table, use the executemany() method.
• The second parameter of the executemany() method is a list of tuples or lists, containing the data you want to insert.

import xlrd
import pymysql
import sys
import datetime

def mysql_link(database_name):
    try:
        db = pymysql.connect(host = 'localhost',user = 'root',
                             password = '888888',database = database_name)
        return db
    except Exception as e:
        print(e)
        print('数据库连接失败')
        

def read_excel(wb_name):
    try:
        wb = xlrd.open_workbook(wb_name)
        return wb
    except Exception as e:
        print(e)
        print('打开excel文件失败')

def upload_data(db_name,table_name,wb_name):
    db = mysql_link(db_name) # 打开数据库连接
    cursor = db.cursor()
    all_data = []
    wb = read_excel(wb_name) # 读取文件
    shts = wb.sheet_names() # 返回该文件的所有Sheet名
    print(shts)
    for sht in shts:
        sh = wb.sheet_by_name(sht) # 依次遍历每个sheet表格
        row_num = sh.nrows # 获取行数
        num = 0
        for i in range(1,row_num):  # 第2行开始，遍历（row_num - 1）条记录
            row_data = sh.row_values(i)
            value = [row_data[0],row_data[1],row_data[2],
                     row_data[3],row_data[4],
                     row_data[5]]
            all_data.append(value)
#         sql = "insert into " + table_name + 
#               "(salesdate,product_id,salesRegion,salesPerson,SalesQty,salesAmount)"
#               "values(%s,%s,%s,%s,%s,%s)"
        sql = f"""insert into {table_name}(salesdate,product_id,salesRegion,salesPerson,SalesQty,salesAmount)
              values(%s,%s,%s,%s,%s,%s)"""
   
    print(all_data)
    cursor.executemany(sql,all_data)
    db.commit()
    print(cursor.rowcount, "was inserted in total.")
    cursor.close()
    db.close()     

if __name__ == "__main__":
    upload_data('ods_testdb',"t_sales",'销售数据.xls')

#OperationalError: (1292, “Incorrect date value: ‘44400’ for column ‘salesdate’ at row 1”)
#录入数据需要日期数据需要文本？— NO
#excel数据格式要遵循常规格式 ！！！！

'''
去MySql中创建库表：
CREATE TABLE  t_customers(
    `name` VARCHAr(100) not null,
     address VARCHAr(100)
);

'''

import mysql.connector

db = mysql.connector.connect(
      host="localhost",
      user="root",
      password="888888",
      database="ods_testdb"
    )

cursor = mydb.cursor()

sql = "insert into t_customers(name, address)values(%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
] # 列表

cursor.executemany(sql, val)
db.commit()
db.close()

print(cursor.rowcount, "was inserted.")