Exporter des tables Sybase (Adaptive Server Enterprise) en utilisant bcp.exe sous Python

L’objectif de ce script Python est d’extraire les tables d’une base de données Sybase (Adaptive Server Enterprise) et de produire grâce à l’utilitaire Sybase BCP.exe les données sous forme de fichiers plats « CSV » par exemple.

Prérequis:

• Python 2.5
• La librairie Python config: Librairie Python config
• La suite Sybase Adaptive Server Enterprise, PC Client Components >= 15.0.2

#    Mode: Python tab-width: 4
#    Id: sybase_put_db_data_to_file.py
#    Author: David REGNIER
#    Description: Copy data from Sybase to flat file (use bcp.exe)
#
# ======================================================================
# Copyright 2014 by David REGNIER
#
#                         All Rights Reserved
#
# Permission to use, copy, modify, and distribute this software and
# its documentation for any purpose and without fee is hereby
# granted, provided that the above copyright notice appear in all
# copies and that both that copyright notice and this permission
# notice appear in supporting documentation, and that the name David
# REGNIER not be used in advertising or publicity pertaining to
# distribution of the software without specific, written prior
# permission.
# ======================================================================

# Import lib
import subprocess, logging, datetime, sys, os
from configobj import ConfigObj

# Get config data
oTables = ConfigObj('config/sybase_tables.cfg')
oConfig = ConfigObj('config/config.cfg')
sFolderPath = oConfig['file_section']['folder_output_sybase']
sExtensionFileName = "csv" # Should match files name

"""Begin main class"""
class Sybase(object):
    # Constructor
    def __init__(self, oConfig, sFolderPath, sExtensionFileName):
        self.oConfig = oConfig
        self.sFolderPath = sFolderPath
        self.sExtensionFileName = sExtensionFileName

    # Delete file(s) if empty from root folder
    def deleteemptyfile(self, sFolderPath, sExtensionFileName):
        try:
            if os.path.exists(sFolderPath):
                os.chdir(sFolderPath)
                for files in os.listdir("."):
                    if files.endswith("." + sExtensionFileName) and \
                        os.stat(sFolderPath + "\\" + files).st_size == 0:
                        logging.info(oConfig['label_section']['process_delete_file_name'] + " " + sFolderPath + "\\" + files)
                        os.remove(sFolderPath + "\\" + files)
        except:
            logging.error(oConfig['label_section']['exception_unexpected_error'] + " " + str(sys.exc_info()[0]))
        finally:
            logging.info(oConfig['label_section']['process_delete_empty_file_successfull'])

    def createlogfile(self):
        # Delete log file first
        if os.path.exists(self.oConfig['file_section']['file_output_sybase_log']):
            os.remove(self.oConfig['file_section']['file_output_sybase_log'])
        else:
            # Create log file
            logging.basicConfig(filename = self.oConfig['file_section']['file_output_sybase_log'], level = logging.DEBUG)        

    # Begin GetSybaseData function
    def getsybasedata(self, sDbEnvironment, sDb, oTables):
        try:
            # Get config data
            sDbConnectionString = self.oConfig['sgbd_section']['db_connection_string_bcp_' + sDbEnvironment.lower()]
            for sTable in oTables:
                    # Deal with file name
                    sFileOutputName = self.oConfig['file_section']['file_output_sybase_csv'].replace("%tablename", sTable).replace("%dbname", sDb)
                    # Call subprocess
                    oProcess = subprocess.Popen(
                        "C:\\sybase\\OCS-15_0\\bin\\bcp.exe " + sDb + ".dbo." + sTable + " out " + sFileOutputName + " -Jutf8 -c -t , " + sDbConnectionString,
                        stdout = subprocess.PIPE,
                        stderr = subprocess.PIPE
                    )
                    stdout, stderr = oProcess.communicate()
                    if stdout:
                        logging.info(datetime.datetime.now().strftime('%Y/%m/%d - %H:%M:%S') + " " + stdout)
                    if stderr:
                        logging.error(datetime.datetime.now().strftime('%Y/%m/%d - %H:%M:%S') + " " + stderr)

        except TypeError:
            logging.error(self.config['label_section']['exception_type_error'])
        except WindowsError:
            logging.error(self.config['label_section']['exception_windows_error'])
        except:
            logging.error(self.config['label_section']['exception_unexpected_error'] + " " + str(sys.exc_info()[0]))
        finally:
            # Remove empty file
            self.deleteemptyfile(self.sFolderPath, self.sExtensionFileName)
    # End GetSybaseData function
"""End main class"""

Vous devez adapter le chemin vers votre utilitaire bcp.exe en fonction de votre installation:

 
Voici le contenu du fichier de configuration config/sybase_tables.cfg des tables Sybase, ce fichier doit contenir une liste des noms des tables que vous voulez exporter, exemple:

 
Voici également le contenu du fichier de configuration config/config.cfg, exemple :