Linux下使用python脚本执行BCP导入导出操作

create table Test001(
ID int,
age int,
name varchar(50)
)

create table Test002(
Id int,
age int,
name varchar(50),
addr varchar(50)
)

insert into Test002 values(1,25,'Bob','New York')
insert into Test002 values(2,22,'Mary','HongKong')
insert into Test002 values(3,25,'David','London')

#!/usr/bin/env python
#coding=utf-8

import pymssql
import subprocess

class testBcp:

    def getConn(self):
        server = "192.168.1.1"
        user= "sa"
        password = "testBcp@1"
        db = "TESTBCP"
        # 打开数据库连接
        conn = pymssql.connect(server,user,password,db)
        return conn

    def BCPExe(self,bcpString):
        conn = self.getConn()
        # 使用cursor()方法获取操作游标
        cursor = conn.cursor()
        result_code = subprocess.call(bcpString, shell=True)
        print(result_code)

if __name__=="__main__":
    tB=testBcp()
    BCPout="bcp 'select ID,age,name from TESTBCP.dbo.Test002' queryout tempData.csv -c -U sa -P testBcp@1 -S 192.168.1.1"
    BCPin='bcp TESTBCP.dbo.Test001 in tempData.csv -c -U sa -P testBcp@1 -S 192.168.1.1'
    #select ID,age,name from TESTBCP.dbo.Test002 使用sql语句查询出结果集写入tempData.csv文件中
    tB.BCPExe(BCPout)
    #将文件中的数据写入表test001中
    tB.BCPExe(BCPin)