将excel里面的数据,或者linux查询sql数据生成sql脚本，并存入本地

左左才

已于 2022-06-20 23:59:48 修改

阅读量1.3k

点赞数

分类专栏：脚本数据库文章标签： sql 数据库 database

于 2022-01-24 23:53:09 首次发布

本文链接：https://blog.csdn.net/weixin_47935594/article/details/122677080

版权

脚本同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

公司领导要求把自己模拟的数据写入excel表格，
然后把表格给开发，让开发往数据库插入数据
身为一名测试，在想，能否自己把excel里面的数据转为sql，存入本地，然后把这个写好的sql给开发

领导提倡测试插入数据，最大的原因是因为测试连接linux，一句句写sql过于麻烦，把文件给开发，让开发批量导入；
多写脚本，练习自己的脚本能力，更熟练的掌握运用python的知识
写好的sql给开发，为他节约点时间。

自己先创建一个xls表：
在这里插入图片描述

# -*- coding: utf-8 -*-
# @Author : chair
# @File : PyCharm /inert_sql.py
# @Data : 2022-01-24  20:39
# @Annotation ： 从excel获取数据，生成sql,存入本地

import xlrd
class InertSql():
    """读取excel文件，创建sal，存入本地"""
    def __init__(self,fil,sheet):
        """fil=excel路径，sheet=表位置，创建一个demo.sql文件，存放生成的sql"""
        self.file_path = xlrd.open_workbook(fil)
        self.sheet = self.file_path.sheet_by_name(sheet)
        self.fil = open("C:\\Users\\chair\\Desktop\\demo.sql",mode='w+',encoding="utf-8")
    def __del__(self):
        """读取文件，存入本地后，关闭两个文件"""
        self.fil.close()
        self.file_path.release_resources()
        del self.file_path
    def read_exle(self,table):
        """table=表名,keys=字段，values==内容，excel格式：第一行字段，第二行~第N行存放内容"""
        keys = self.sheet.row_values(0)
        for i in range(1,self.sheet.nrows):
            values = [seitch_float(x)for x in self.sheet.row_values(i)]
            create_sql = "INSERT INTO {table} ({keys}) VALUES {values};\n".format(table = table,keys = ','.join(keys),values=tuple(values))
            self.fil.write(create_sql)
            print(create_sql)
            # print( end='.')
def seitch_float(i):
    """输入浮点数N.0转换>>N，输入为null/NULL转换>>'' """
    if type(i) == float:
        return int(i) if int(i)==i else i
    else:
        return '' if  i =='null' or i =='NULL' else i
if __name__ == '__main__':
    fil_path = "C:\\Users\\chair\\Desktop\\demo.xls"
    inertsql = InertSql(fil_path,sheet="Sheet1")
    inertsql.read_exle('subject')


>>> INSERT INTO subject (ID,CLASSID,SUBJECTNAME,STATYHOUR,GARDEID) VALUES (1, 8, 'java', 81.9, 56);

>>> INSERT INTO subject (ID,CLASSID,SUBJECTNAME,STATYHOUR,GARDEID) VALUES (2, 11, 'java', 117.24, 49);

>>> INSERT INTO subject (ID,CLASSID,SUBJECTNAME,STATYHOUR,GARDEID) VALUES (3, 11, 'db', 40.6, '');

>>> INSERT INTO subject (ID,CLASSID,SUBJECTNAME,STATYHOUR,GARDEID) VALUES (4, 16, 'python', '', '');

>>> INSERT INTO subject (ID,CLASSID,SUBJECTNAME,STATYHOUR,GARDEID) VALUES (5, 13, 'db', 16.92, 52);

本次学习：

首次使用while循环，因wile循环次数语法太长了，这是为了在公司使用，所以想法是精简，去除while循环，使用for循环，掌握知识点：.nrows 获取行号
读取文件的时候，整数会转为浮点数，这种情况下直接转换，包含0的浮点数统一抓换为整数（后续可能遇到问题，根据问题修改）
不同数据库类型，字段为空的值不同，有的copy出来为‘’，有的copy出来为null，统一把excel里面的null和NULL转换为’’
初次掌握一行内使用for循环
初次掌握一行内使用if…else语句

这种情况下一个excel只能往一个表里面插入数据，在想，是否可以支持多张sheet表格生成不同的sql呢，研究发现可以实现

# -*- coding: utf-8 -*-
# @Author : chair
# @File : PyCharm /create_sql.py
# @Data : 2022-01-24  20:39
# @Annotation ： 将excel数据生成sql脚本，存入本地

import xlrd,re
class create_Sql():
    """读取excel文件，创建sql，存入本地"""
    def __init__(self,fil):
        """fil=excel路径，sheet=表位置，创建一个demo.sql文件，存放生成的sql"""
        self.file_path = xlrd.open_workbook(fil)
        self.fil = open("C:\\Users\\chair\\Desktop\\InertSql{0}.sql".format( fil.split("\\")[-1].split('.')[0]),mode='w+',encoding="utf-8")
    def __del__(self):
        """读取文件，存入本地后，关闭两个文件"""
        self.fil.close()
        self.file_path.release_resources()
        del self.file_path
    def read_exle(self):
        """table=表名,keys=字段，values==内容，excel格式：第一行字段，第二行~第N行存放内容"""
        sheets = self.file_path.sheet_names()   # 查看所有的sheet表名
        for sheet_name in sheets:
            sheet_name = fileter_sheet(sheet_name)  # 把sheet名字为非字母或者未重命名的表打上标签0，进行过滤
            if bool(sheet_name) == 1:
                try:
                    sheet = self.file_path.sheet_by_name(sheet_name)
                    keys = sheet.row_values(0)
                    if sheet.nrows > 2:
                        for i in range(2,sheet.nrows):
                            values = [seitch_float(x)for x in sheet.row_values(i)]
                            create_sql = "INSERT INTO {table} ({keys}) VALUES {values};\n".format(table = sheet_name,keys = ','.join(keys),values=tuple(values))
                            self.fil.write(create_sql)
                            print( end='.')
                    else:
                        print("\n表{}生成sql失败\n请检查excel格式，默认设置：第一行显示字段名，第二行显示字段备注，第三行填写插入的数据,"
                              "例如：\nid\tname\n用户id\t用户名字\n9001\t测试1".format(sheet_name))
                except:
                    print("\n表{}生成sql失败".format(sheet_name))
def seitch_float(i):
    """输入浮点数N.0转换>>N，输入为null/NULL转换>>'' """
    if type(i) == float:
        return int(i) if int(i)==i else i
    else:
        return '' if  i =='null' or i =='NULL' else i
def fileter_sheet(sheet_name):
    if bool(re.findall("^[a-zA-Z].*",sheet_name)) == 1 and bool(re.findall("^Sheet(25[0-5]|2[0-4][0-9]|[0-1]?[0-9]?[0-9])$",sheet_name)) == 0:
            # 过滤非字母数字下划线开头的表格名称 计划过滤Sheet1-Sheet255，后考虑没必要过滤
        return sheet_name
    else:
        content = "\n表名字《{}》非数据库标准名称格式，被过滤,不生成sql,建议:Sheet表名填写为标准数据库表名，以英文字母开头".format(sheet_name)
        print(content)
        return 0
if __name__ == '__main__':
    fil_path = "C:\\Users\\chair\\Desktop\\数据准备.xls"
    inertsql = create_Sql(fil_path)
    inertsql.read_exle()

若数据库的名字是动态名字，生成动态sql

# -*- coding: utf-8 -*-
# @Author : chair
# @File : PyCharm /create_trends_sql.py
# @Data : 2022-02-08  22:23
# @Annotation ： 生成动态sql

import xlrd
class Create_Trends_Sql():
    def __init__(self,fil):
        self.file_path = xlrd.open_workbook(fil)
        self.fil = open("C:\\Users\\chair\\Desktop\\InertSql{0}.sql".format( fil.split("\\")[-1].split('.')[0]),mode='w+',encoding="utf-8")
    def __del__(self):
        self.fil.close()
        self.file_path.release_resources()
        del self.file_path
    def create_Trends_Sql(self,sheet,colx,sql_table_names,expression):
        sheet = self.file_path.sheet_by_name(sheet) # 输入表名字
        keys = sheet.row_values(0) #获取第一行字段内容
        if sheet.nrows > 2:
            for i in range(2, sheet.nrows): #获取所有字段值
                values = [seitch_float(x) for x in sheet.row_values(i)] #输入浮点数N.0转换>>N，输入为null/NULL转换>>''
                values_id = sheet.cell(i,colx).value   # 获取动态内容

                sql_table_name = '{0}{1}'.format(sql_table_names,eval("{0}{1}".format(int(values_id),expression))) #根据动态内容合成数据库表名
                create_sql = "INSERT INTO {table} ({keys}) VALUES {values};\n".format(table=sql_table_name,keys=','.join(keys),values=tuple(values))
                self.fil.write(create_sql)
                print(end='.')
        else:
            print(keys)
def seitch_float(i):
    """输入浮点数N.0转换>>N，输入为null/NULL转换>>'' """
    if type(i) == float:
        return int(i) if int(i)==i else i
    else:
        return '' if  i =='null' or i =='NULL' else i
if __name__ == '__main__':
    create_trends_Sql = Create_Trends_Sql('C:\\Users\\chair\\Desktop\\sql.xls')
    sheet = "abc"   # excel表名
    sql_table_name = "table_"   # 数据库表名
    colx = 0    # 变量所在的excel表格位置，第N列
    expression = "%9"  # 表达式
    content = create_trends_Sql.create_Trends_Sql(sheet,colx,sql_table_name,expression)

数据结构为`|`拼接,将其生成sql

例如:

CustomerId|FirstName|LastName    |Company                                         |Address                                 |City               |State |Country       |PostalCode|Phone              |Fax               |Email                        |SupportRepId|
----------+---------+------------+------------------------------------------------+----------------------------------------+-------------------+------+--------------+----------+-------------------+------------------+-----------------------------+------------+
         1|Luís     |Gonçalves   |Embraer - Empresa Brasileira de Aeronáutica S.A.|Av. Brigadeiro Faria Lima, 2170         |São José dos Campos|SP    |Brazil        |12227-000 |+55 (12) 3923-5555 |+55 (12) 3923-5566|luisg@embraer.com.br         |           3|
         2|Leonie   |Köhler      |                                                |Theodor-Heuss-Straße 34                 |Stuttgart          |      |Germany       |70174     |+49 0711 2842222   |                  |leonekohler@surfeu.de        |           5|
         3|François |Tremblay    |                                                |1498 rue Bélanger                       |Montréal           |QC    |Canada        |H2G 1A7   |+1 (514) 721-4711  |                  |ftremblay@gmail.com          |           3|
         4|Bjørn    |Hansen      |                                                |Ullevålsveien 14                        |Oslo               |      |Norway        |0171      |+47 22 44 22 22    |                  |bjorn.hansen@yahoo.no        |           4|
         5|František|Wichterlová |JetBrains s.r.o.                                |Klanova 9/506                           |Prague             |      |Czech Republic|14700     |+420 2 4172 5555   |+420 2 4172 5555  |frantisekw@jetbrains.com     |           4|

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author : chairDu
# @Email : chair7@163.com
# @File : demo.py
# @DataTime : 2022-06-20 22:31:00
# @Description ： 获把linux查询的sql结果生成插入sql


table = "Customer"  # 插入数据的表名
w = open("./txt", "r")  # 读取文件
cont = w.readlines()
w.close()
sqlKey = ",".join([i.strip() for i in cont[0].split("|")][:-1]) # 获取第一行数据,表头
num = 1
b = open("./a.txt", "w+")  # 写入文件
while num <= len(cont) - 2:  # 从第三行开始循环,循环到最后一行
    num += 1
    sqlValues = "','".join([i.replace("'", "\"").strip() for i in cont[num].split("|")][:-1])    # 循环获取表数据
    sql = "\ninsert into {table} ({sqlKey}) values ('{sqlValues}') ;".format(table=table, sqlKey=sqlKey, sqlValues=sqlValues)
    b.write(sql)
b.close()

生成sql:

insert into Customer (CustomerId,FirstName,LastName,Company,Address,City,State,Country,PostalCode,Phone,Fax,Email,SupportRepId) values ('1','Luís','Gonçalves','Embraer - Empresa Brasileira de Aeronáutica S.A.','Av. Brigadeiro Faria Lima, 2170','São José dos Campos','SP','Brazil','12227-000','+55 (12) 3923-5555','+55 (12) 3923-5566','luisg@embraer.com.br','3') ;
insert into Customer (CustomerId,FirstName,LastName,Company,Address,City,State,Country,PostalCode,Phone,Fax,Email,SupportRepId) values ('2','Leonie','Köhler','','Theodor-Heuss-Straße 34','Stuttgart','','Germany','70174','+49 0711 2842222','','leonekohler@surfeu.de','5') ;
insert into Customer (CustomerId,FirstName,LastName,Company,Address,City,State,Country,PostalCode,Phone,Fax,Email,SupportRepId) values ('3','François','Tremblay','','1498 rue Bélanger','Montréal','QC','Canada','H2G 1A7','+1 (514) 721-4711','','ftremblay@gmail.com','3') ;
insert into Customer (CustomerId,FirstName,LastName,Company,Address,City,State,Country,PostalCode,Phone,Fax,Email,SupportRepId) values ('4','Bjørn','Hansen','','Ullevålsveien 14','Oslo','','Norway','0171','+47 22 44 22 22','','bjorn.hansen@yahoo.no','4') ;
insert into Customer (CustomerId,FirstName,LastName,Company,Address,City,State,Country,PostalCode,Phone,Fax,Email,SupportRepId) values ('5','František','Wichterlová','JetBrains s.r.o.','Klanova 9/506','Prague','','Czech Republic','14700','+420 2 4172 5555','+420 2 4172 5555','frantisekw@jetbrains.com','4') ;