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

 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()