Development

Running Sybase BCP and ISQL from python

Sybase BCP from python, by shelling out to the bcp process.

import sys
import os
from subprocess import Popen, PIPE
from itertools import chain
from string import Template
from tempfile import NamedTemporaryFile, mkstemp
 
def call(command):
    child = Popen(command, stdout=PIPE)
    stdout, stderr = child.communicate()
    if child.returncode != 0:
        raise OSError, (stderr, stdout)
    return stdout
 
def lines_of(text):
    return (line.strip() for line in text.split("\n"))
 
def strip_nulls(text):
    return text.replace("\0", "")
 
def commands_in(text):
    command = ""
    for line in lines_of(text):
        command += line + "\n"
        if line == "go":
            yield command
            command = ""
 
interface_file_template = Template("""
[SERVER]
query=WNLWNSCK,${host},${port}
master=WNLWNSCK,${host},${port}
""")
 
class Database:
    def __init__(self, host, port, database, user, password):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self._create_interfaces()
 
    def __del__(self):
        os.remove(self.interfaces_name)
 
    def _create_interfaces(self):
        fd, self.interfaces_name = mkstemp()
        self.interfaces = os.fdopen(fd, "w")
        self.interfaces.write(interface_file_template.substitute(self.__dict__))
        self.interfaces.close()
 
    @property
    def _java_connection(self):
        return ( "-S"+self.host + ":" + str(self.port), "-U"+self.user, "-P"+self.password )
 
    def ddlgen(self, *args):
        return commands_in(strip_nulls(
            call(("ddlgen.bat", "-Jiso_1", "-D"+self.database) + self._java_connection + args)))
 
    def run_query(self, sql_file_path):
        command = ("isql",) + self._java_connection + ( "-b", "-i", sql_file_path )
        return list(lines_of(call(command)))[:-3]
 
    def _bcp(self, table, direction, path):
        command = ("bcp", self.database + "." + table , direction, path,
                   "-c", "-SSERVER", "-P", self.password,
                   "-U", self.user, "-I", self.interfaces_name)
        call(command)
 
    def bcpout(self, table, topath):
        self._bcp(table, "out", topath)
 
    def bcpin(self, table, frompath):
        self._bcp(table, "in", frompath)
comments powered by Disqus