python2将sql_server表数据或sql数据导入csv

淩亂°似流年 2023-06-16 07:27 25阅读 0赞

###  1.将sql\_server的表数据全部导入csv ###

# -*- coding: utf-8 -*-
    
    import pymssql
    import csv
    import sys
    
    reload(sys)
    sys.setdefaultencoding("utf8")
    
    
    def write_csv(cursor_tool, table_name):
        file_object = open("%s%s.csv" % (file_path, table_name), 'wb')
        writer = csv.writer(file_object)
        sql = "SELECT * FROM %s" % table_name
        cursor_tool.execute(sql)
        # 获取列名
        coloumns = [row[0] for row in cursor_tool.description]
        headers = [str(coloumn) for coloumn in coloumns]
        writer.writerow(headers)
    
        results = cursor_tool.fetchall()
        count = 0
        for i in results:
            one_list = []
            for j in i:
                one_list.append(j)
            writer.writerow(one_list)
            count += 1
            print count
        file_object.close()
    
    
    # 要输出的csv文件夹
    file_path = "/mnt/hehe/"
    
    # 数据库信息
    host = "192.168.1.111"
    user = "root"
    password = "123zxcv"
    database = "base_name"
    
    conn = pymssql.connect(host, user, password, database, charset="utf8")
    cursor = conn.cursor()
    
    # 表名列表,同时也是输出的csv文件名
    table_names = ["table_name_1", "table_name_2", "table_name_3"]
    
    for i in table_names:
        print i
        write_csv(cursor, i)
    conn.close()

### 2.将sql\_server的sql数据全部导入csv ###

# -*- coding: utf-8 -*-
    
    import pymssql
    import csv
    import sys
    
    reload(sys)
    sys.setdefaultencoding("utf8")
    
    
    def write_csv(cursor_tool, out_put_file, sql):
        file_object = open("%s%s.csv" % (file_path, out_put_file), 'wb')
        writer = csv.writer(file_object)
        cursor_tool.execute(sql)
        # 获取列名
        coloumns = [row[0] for row in cursor_tool.description]
        headers = [str(coloumn) for coloumn in coloumns]
        writer.writerow(headers)
    
        results = cursor_tool.fetchall()
        count = 0
        for i in results:
            one_list = []
            for j in i:
                one_list.append(j)
            writer.writerow(one_list)
            count += 1
            print count
        file_object.close()
    
    # 要输出的csv文件夹
    file_path = "/mnt/sql/"
    
    # 数据库信息
    host = "192.168.1.111"
    user = "root"
    password = "123zxcv"
    database = "base_name"
    
    conn = pymssql.connect(host, user, password, database, charset="utf8")
    cursor = conn.cursor()
    
    sql_1 = 'select * from table_name_1 limit 100'
    sql_2 = 'select * from table_name_2 where name = "小红"'
    # 要输出的csv文件名列表,与sql一一对应
    out_put_files = ["测试1", "测试2"]
    sqls = [sql_1, sql_2]
    
    for i, j in zip(out_put_files, sqls):
        print i
        write_csv(cursor, i, j)
    conn.close()