Anasayfa / Database / informix / python informix char to nchar varchar to nvarchar replace

python informix char to nchar varchar to nvarchar replace

import re
import sys
import getopt
import os

exclude_rows = ['informix.syserrors',
                'informix.systracemsgs',
                'informix.systraceclasses',
                'grant dba to',
                'grant resource to',
                'create role ',
                'sysbldsqltext',
                'create explicit cast',
                'create implicit cast']

GRANT_EXECUTE_ON_FUNCTION = 'grant  execute on function'
GRANT_EXECUTE_ON_PROCEDURE = 'grant  execute on procedure'


def file_to_list(fname) -> list:
    with open(fname, encoding='ansi') as f:
        content = f.read().splitlines()

    return content


def list_to_file(file_path: str, itemlist: list):
    with open(file_path, "w", encoding='ansi') as outfile:
        outfile.write("\n".join(itemlist))


def subst_char(n: str):
    return f' nchar({n}) '


def subst_varchar(n: str):
    return f' nvarchar({n}) '


def convert_char_to_nchar(inputfile: str, outputfile: str):
    flist = file_to_list(inputfile)
    rx_char = re.compile(r"""[+\s]*    # optional whitespace or +
                        \b             # word boundary
                        n?char         # NCHAR or CHAR
                        [+\s]*         # optional whitespace or +
                        \(             # left paren
                        ([\d\s]+)      # digits or spaces - group 1
                        \)             # right paren
                        [+\s]*         # optional whitespace or +
                    """, re.VERBOSE | re.IGNORECASE)
    new_list = []
    total = len(flist)
    print()
    xval = None
    for i, row in enumerate(flist):
        result = row
        if result not in exclude_rows:
            xval = rx_char.findall(row)

        if xval:
            if len(xval) == 1:
                result = rx_char.sub(subst_char(xval[0]), row)
            if len(xval) > 1:
                for tmpchr in xval:
                    result = rx_char.sub(subst_char(tmpchr), row)
            new_list.append(result)
        else:
            if GRANT_EXECUTE_ON_FUNCTION or GRANT_EXECUTE_ON_PROCEDURE in result:
                regex_char = r"\bchar"
                result = re.sub(regex_char, 'nchar', result, count=0, flags=re.IGNORECASE | re.MULTILINE)

                new_list.append(result)
            else:
                new_list.append(result)
        print(f'\r Islenen char to nchar {i + 1} / {total} ', end='')

    list_to_file(outputfile, new_list)


def convert_varchar_to_nvarchar(inputfile: str, outputfile: str):
    flist = file_to_list(inputfile)
    rx_varchar = re.compile(r"""[+\s]* # optional whitespace or +
                        \b             # word boundary
                        n?varchar      # NVARCHAR or VARCHAR
                        [+\s]*         # optional whitespace or +
                        \(             # left paren
                        ([\d\s]+)      # digits or spaces - group 1
                        \)             # right paren
                        [+\s]*         # optional whitespace or +
                    """, re.VERBOSE | re.IGNORECASE)
    new_list = []
    total = len(flist)
    print()
    xval = None
    for i, row in enumerate(flist):
        result = row
        if result not in exclude_rows:
            xval = rx_varchar.findall(row)

        if xval:
            if len(xval) == 1:
                result = rx_varchar.sub(subst_varchar(xval[0]), row)
            if len(xval) > 1:
                for tmpchr in xval:
                    result = rx_varchar.sub(subst_varchar(tmpchr), row)
            new_list.append(result)
        else:
            if GRANT_EXECUTE_ON_FUNCTION or GRANT_EXECUTE_ON_PROCEDURE in result:
                regex_varchar = r"\bvarchar"
                result = re.sub(regex_varchar, 'nvarchar', result, count=0, flags=re.IGNORECASE | re.MULTILINE)
                new_list.append(result)
            else:
                new_list.append(result)
        print(f'\r Islenen varchar to nvarchar {i + 1} / {total} ', end='')

    list_to_file(outputfile, new_list)


def main(argv):
    inputfile, outputfile = '', ''
    try:
        opts, args = getopt.getopt(argv, "hi:o:", ["ifile=", "ofile="])
    except getopt.GetoptError:
        print('ifx_to_nchar_nvarchar.py -i <inputfile> -o <outputfile>')
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print('ifx_to_nchar_nvarchar.py -i <inputfile> -o <outputfile>')
            sys.exit()
        elif opt in ("-i", "--ifile"):
            inputfile = arg
        elif opt in ("-o", "--ofile"):
            outputfile = arg

    print(f'Input file is {inputfile}'),
    print(f'Output file is {outputfile}'),
    if inputfile and outputfile:
        if os.path.isfile(inputfile):
            convert_char_to_nchar(inputfile, 'tmp_' + inputfile)
            convert_varchar_to_nvarchar('tmp_' + inputfile, outputfile)
            os.remove('tmp_' + inputfile)
        else:
            print(f'Input file: {inputfile} not found')
    else:
        print('Input file or Output file is invalid')


if __name__ == '__main__':
    main(sys.argv[1:])

kullanımı
python ifx_to_nchar_nvarchar.py -i orijinal.sql -o n_orijinal.sql

Hakkında ibrahim

İlgili Makaleler

Informix unload file to postgresql import

TBL_TEST.unl dosyamızın sonunda ki pipe “|” yok etmek için python da küçük bir script ile …

Bir cevap yazın

E-posta hesabınız yayımlanmayacak.