I am using the below function to insert Sybase table with high volume data. I am using fast_executemany = True because without this the performance is very slow and not acceptable for my requirement. The below code is giving error for the mentioned table. This is generic function which takes as dataframe and insert to target table passed as input to the function. I can't make table specific customized as table structure varies form one table to other.
Function
def _sybase_insert(source_df, targetTable, loadType, target, database):
cursor = target.cnxn.cursor()
sql = 'select top 1 * from ' + targetTable
cursor.execute(sql)
row = cursor.fetchall()
db_types = [d[1] for d in cursor.description]
print(db_types)
rows = source_df.values.tolist()
parms = ("?," * len(rows[0]))[:-1]
if loadType == 'replace':
sql = "delete from " + targetTable
cursor.execute(sql)
sql = "INSERT INTO " + targetTable +" VALUES (%s)" % (parms)
cursor.fast_executemany = True
cursor.setinputsizes(db_types)
cursor.executemany(sql, rows)
cursor.close()
target.cnxn.commit()
My connection string is
self.cnxn = pyodbc.connect('DRIVER={Adaptive Server Enterprise};uid=' + self.user +';EncryptPassword=1;pwd=' + self.password + ';Port=' + self.dbport + ';Server=' + self.server +';Database=' + dbname)
Below is the table structure it is failing for with the error **pyodbc.ProgrammingError: ('String data, right truncation: length 22 buffer 20', 'HY000')**
I tried with below as well but did not resolve this issue
cursor.setinputsizes(
[
(pyodbc.SQL_VARCHAR,500,1000),
(pyodbc.SQL_INTEGER),
(pyodbc.SQL_FLOAT),
]
)
Target Table
COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX IS_NULLABLE
---------------- --------- --------- ----------- ------------- -------------- -------------- -----------
Column1 4 int 10 10 0 10 NO
Column2 4 int 10 10 0 10 NO
Column3 4 int 10 10 0 10 NO
Column4 4 int 10 10 0 10 NO
Column5 4 int 10 10 0 10 NO
column6 4 int 10 10 0 10 NO
column7 4 int 10 10 0 10 NO
column8 1 char 12 12 (null) (null) NO
column9 1 char 12 12 (null) (null) NO
column10 4 int 10 10 0 10 NO
column11 4 int 10 10 0 10 NO
column12 1 char 12 12 (null) (null) NO
column13 4 int 10 10 0 10 NO
column14 4 int 10 10 0 10 NO
column15 4 int 10 10 0 10 NO
column16 4 int 10 10 0 10 NO
column17 1 char 30 30 (null) (null) NO
column18 4 int 10 10 0 10 NO
column19 4 int 10 10 0 10 NO
column20 4 int 10 10 0 10 NO
column21 93 datetime 23 23 3 10 NO
column22 1 char 10 10 (null) (null) NO
column23 4 int 10 10 0 10 NO
Since this is a generic function , kindly let me know what is the issue with the code and why it is failing for the table I mentioned above.
Actual output with Error
Below is the output with actual error.
[<class 'int'>, <class 'int'>, <class 'int'>, <class 'int'>, <class 'int'>, <class 'int'>, <class 'int'>, <class 'str'>, <class 'str'>, <class 'int'>, <class 'int'>, <class 'str'>, <class 'int'>, <class 'int'>, <class 'int'>, <class 'int'>, <class 'str'>, <class 'int'>, <class 'int'>, <class 'int'>, <class 'datetime.datetime'>, <class 'str'>, <class 'int'>]
Traceback (most recent call last):
File "ODBCTableCopy.py", line 121, in <module>
out.append(_table_copy_(targetTable=target_table, sourceObject=sql, source=source, target=target, database=Database , loadType=load_type))
File "ODBCTableCopy.py", line 67, in _table_copy_
_sybase_insert(source_df,targetTable, loadType, target, database)
File "ODBCTableCopy.py", line 47, in _sybase_insert
cursor.executemany(sql, rows)
pyodbc.ProgrammingError: ('String data, right truncation: length 22 buffer 20', 'HY000')