用途：

利用pandas的read_sql_query()和to_sql()方法，我的python 3.7脚本的目标是通过阅读.sql文件来执行从一个服务器到另一个服务器的多个表的ETL。两个方法中的连接参数都利用sqlalchemy的create_engine模块。

出现错误：

成功提取并加载第一组表+事务后，第四组出错。

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY090', '[HY090] [Microsoft][ODBC Driver Manager] Invalid string or buffer length (0) (SQLExecDirectW)')

请参阅下文了解更多详情。

程序：

• 每个提取的表都作为一个SQL事务（按;拆分）写入一个SQL文件中。

'ExtractTables.sql'

    SET NOCOUNT ON
    SELECT 
        [ID1]       
    ,   [Name]          
    ,   [LastUpdated]   
    ,   [UpdatedBy]     

    INTO #table1
    FROM DB1.dbo.table1 

    SELECT * FROM #table1

    ;

    SET NOCOUNT ON
    SELECT 
        [ID1]
    ,   [ID2]
    ,   [Descr]

    INTO #table2 FROM DB1.dbo.table2

    SELECT * FROM #table2

• ODBC参数是为每台服务器上带有create_engine模块的引擎设置的。这两台服务器都是MS-SQL服务器。根据我的研究，我认为我的错误来自于fast_executemany参数。

'connection.py'

    import pyodbc
    import urllib
    from sqlalchemy import create_engine

    #Use trusted connection to connect to server. fast_executemany is mssql specific. Allows for large data loads.

    params_H = urllib.parse.quote_plus("DRIVER=ODBC Driver 17 for SQL Server;SERVER=SERVER1;DATABASE=DB1;Trusted_Connection=yes")
    engine_H = create_engine(f'mssql+pyodbc:///?odbc_connect={params_H}', fast_executemany=True)

    params_b = urllib.parse.quote_plus("DRIVER=ODBC Driver 17 for SQL Server;SERVER=SERVER2;DATABASE=DB2;Trusted_Connection=yes")
    engine_b= create_engine(f'mssql+pyodbc:///?odbc_connect={params_b}', fast_executemany=True)

• Python脚本，用于迭代每个命令，并对每个要提取的命令使用read_sql_query()，对每个表使用to_sql()。

'LoadTables.py'

    import pandas as pd
    import conn

    def readSQLFile_makeTables(filename):
        # Open and read file
        open_file = open(filename, 'r')
        sql_file = open_file.read()
        open_file.close()

        #all SQL commands (split on ';')
        sql_commands = sql_file.split(';')

        # Execute every command from file
        sql_tables = ['stg_table1', 'stg_table2', 'stg_table3']

        i = 0
        for command in sql_commands:

            table = pd.read_sql_query(command, con=conn.engine_H)
            print(table)
            table.to_sql(sql_tables[i], con=conn.engine_b, chunksize=5000, index=False, if_exists='append')
            i += 1

        print('think this ran')

错误：

为了StackOverflow的目的，我将我的sql代码限制为2个批处理，但它有4个。前3个成功地通过了读取和写入。但第四个在写入时引发了错误。错误表批处理与其他批处理的主要区别是它的大小（700万行x 8列），其次是（150万行x 6列）。

疑难排解：

我在这个主题上研究的所有错误都指向ODBC连接的问题。两个服务器都是64位的，我使用的是pydobc 4.025，并且我测试了只提取整数值的字段。第一个能够成功加载的事务告诉我大多数事务都在工作，但是最后一个有问题阻止了加载。我假设大小，我相信它是由chunksize=5000和fast_executemany = true处理的，错误指向绑定参数。
https://github.com/mkleehammer/pyodbc/issues/548

追溯：

[6721864 rows x 8 columns]
    Traceback (most recent call last):
      File "C:\Users\x\AppData\Local\Continuum\anaconda3\envs\envname\lib\site-packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
        cursor, statement, parameters, context
      File "C:\Users\x\AppData\Local\Continuum\anaconda3\envs\envname\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
        cursor.execute(statement, parameters)
    pyodbc.Error: ('HY090', '[HY090] [Microsoft][ODBC Driver Manager] Invalid string or buffer length (0) (SQLExecDirectW)')

    The above exception was the direct cause of the following exception:
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
      File "PYTHON\Testing\ETL\LoadTables.py", line 21, in readSQLFile_makeTables
        table = pd.read_sql_query(command, con=conn.engine_H)
      File "\lib\site-packages\pandas\io\sql.py", line 314, in read_sql_query
        parse_dates=parse_dates, chunksize=chunksize)
      File "\lib\site-packages\pandas\io\sql.py", line 1063, in read_query
        result = self.execute(*args)
      File "\lib\site-packages\pandas\io\sql.py", line 954, in execute
        return self.connectable.execute(*args,**kwargs)
      File "\lib\site-packages\sqlalchemy\engine\base.py", line 2166, in execute
        return connection.execute(statement, *multiparams,**params)
      File "\lib\site-packages\sqlalchemy\engine\base.py", line 982, in execute
        return self._execute_text(object_, multiparams, params)
      File "\lib\site-packages\sqlalchemy\engine\base.py", line 1155, in _execute_text
        parameters,
      File "\lib\site-packages\sqlalchemy\engine\base.py", line 1248, in _execute_context
        e, statement, parameters, cursor, context
      File "\lib\site-packages\sqlalchemy\engine\base.py", line 1466, in _handle_dbapi_exception
        util.raise_from_cause(sqlalchemy_exception, exc_info)
      File "\lib\site-packages\sqlalchemy\util\compat.py", line 383, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=cause)
      File "\lib\site-packages\sqlalchemy\util\compat.py", line 128, in reraise
        raise value.with_traceback(tb)
      File "\lib\site-packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
        cursor, statement, parameters, context
      File "\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY090', '[HY090] [Microsoft][ODBC Driver Manager] Invalid string or buffer length (0) (SQLExecDirectW)')
    (Background on this error at: http://sqlalche.me/e/dbapi)