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

南湖渔歌

已于 2022-07-21 18:20:56 修改

阅读量693

点赞数

分类专栏： Python SQL 文章标签： python 数据库 mysql

于 2022-07-21 17:45:06 首次发布

本文链接：https://blog.csdn.net/weixin_44976611/article/details/125917495

版权

Python 同时被 2 个专栏收录

103 篇文章 8 订阅

订阅专栏

SQL

61 篇文章 7 订阅

订阅专栏

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.")