python生成sql server 数据库表名和sql脚本

# -*- coding:utf-8 -*-
# 作者  ：TY
# 时间  ：2020-08-05 22:03
# 文件名称：sql server数据库导表脚本.PY
# 开发工具:PyCharm

import pymssql
import os

class SQLServer:
    def __init__(self, server, user, password, database):
        # 类的构造函数，初始化DBC连接信息
        self.server = server
        self.user = user
        self.password = password
        self.database = database

    def __GetConnect(self):
        # 得到数据库连接信息，返回conn.cursor()
        if not self.database:
            raise (NameError, "没有设置数据库信息")
        self.conn = pymssql.connect(server=self.server, user=self.user, password=self.password, database=self.database)
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")  # 将DBC信息赋值给cur
        else:
            return cur

    def ExecQuery(self, sql):
        '''
        执行查询语句
        返回一个包含tuple的list，list是元素的记录行，tuple记录每行的字段数值
        '''
        cur = self.__GetConnect()
        cur.execute(sql)  # 执行查询语句
        result = cur.fetchall()  # fetchall()获取查询结果
        # 查询完毕关闭数据库连接
        self.conn.close()
        return result


def main():
    results1=[]  # 打开主键关闭功能
    results2 = []  # 生成insert 脚本
    results3 = []  # 关闭主键关闭功能
    results4 = []  # 生成数据库表名
    results5 = []  # 生成清空数据库表SQL
    msg = SQLServer(server="127.0.0.1", user="sa", password="sa1234.", database="UFDATA_001_2018")
    #查询一个表中的所有数据
    result = msg.ExecQuery(
        "SELECT NAME FROM UFDATA_001_2018.dbo.SYSOBJECTS WHERE XTYPE='U' ORDER BY NAME")

    for Value in result:
       # insert into fa_Cards select * from [UFDATA_555_2018].[dbo].[fa_Cards]
        tableName = "".join(Value)
        result122 = msg.ExecQuery(
           "select count(*) from %s"%(tableName)
        )
        # 生成数据库表名
        results4.append(tableName+",")
        results5.append("delete "+tableName+"\r go \r")


        for value2 in result122:
            count1 = int("".join(str(s) for s in value2))
            if int(count1) > 0 :
                sqlexport1 = " set IDENTITY_INSERT %s  on \r go \r" % (tableName)  # 将元祖转换为字符串
                sqlexport2 = " insert into %s select * from [UFDATA_554_2018].[dbo].%s \r go \r" % (
                tableName, tableName)  # 将元祖转换为字符串
                sqlexport3 = " set IDENTITY_INSERT %s  off \r go \r" % (tableName)  # 将元祖转换为字符串

                results1.append(sqlexport1)
                results2.append(sqlexport2)
                results3.append(sqlexport3)

    # -------将所有的SQL脚本生成到txt文件中
    with open('数据库导表sql脚本1on.txt', 'a') as file0:
        print("".join(results1), file=file0)
    with open('数据库导表sql脚2.txt', 'a') as file0:
        print("".join(results2), file=file0)
    with open('数据库导表sql脚本3off.txt', 'a') as file0:
        print("".join(results3), file=file0)
    with open('数据库导表sql脚本4表名.txt', 'a') as file0:
        print("".join(results4), file=file0)
    with open('数据库导表sql脚本5清空数据表.txt', 'a') as file0:
        print("".join(results5), file=file0)

print("生成完成")




if __name__ == '__main__':
    main()