使用Python和Fastload实用程序将csv上载到Teradata DB

我创建了一个 Gist 使用Python类,该类包含使用pyodbc与Teradata通信所需的所有方法。

1. 使用管道作为分隔符:“|”
2. csv中的所有值必须双引号:“值”

“值1”|“值2”|“值3”

“值1”|“值2”|“值3”

这可以通过以下方式实现:

import pandas as pd
from csv import QUOTE_ALL
data.to_csv('tmp.csv', index=False, sep='|', quotechar='"', quoting=QUOTE_ALL, header=False)

在此之后,您可以使用此功能:

def upload_csv(database, csv_file, table, columns, user, password, verbose=True):
    """
    This function uses Fastlaod utily to upload csv file delimited with "|" instead of ',' and where all values in
    file are quoted. Ex: "value1" | "value2" | . . .
    :param csv_file: csv file without columns names
    :param table: Insertion table
    :param columns: Column names
    :param user: username
    :param password:
    :param verbose: True | False if output is required
    """

    script_text = fastload_template.substitute(DATA_FILE=csv_file,
                    COLUMN_DEFINITIONS=',\n'.join(['"' + column + '" (varchar(2000))' for column in columns]),
                    VALUES=',\n'.join([':' + '"' + column + '"' for column in columns]),
                    DATABASE=database, TABLE=table, USER=user, PASSWORD=password)

    tmp_file = csv_file[:-4]
    script = open(tmp_file, "w")
    script.writelines("%s\n" % script_text)
    script.close()

    try:
        if verbose:
            run(["fastload < " + tmp_file], check=True, shell=True)
        else:
            run(["fastload < " + tmp_file], check=True, shell=True, stdout=open(os.devnull, 'w'))

    except CalledProcessError as e:
        if e.args[0] != 8:  # Fastload gives error 8 but insert is working.. so don't touch :)
            raise e

    os.remove(tmp_file)