Python:DataError - 字符串或二进制数据将被截断

Question

我正在尝试将值插入到新创建的表中。我收到以下错误:

row['Retailer'])

DataError: ('22001', '[22001] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated. (8152) (SQLExecDirectW); [22001] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated. (3621)')

我发现了具有类似问题的示例，问题似乎涉及插入的字符数多于列所能容纳的字符数。

但是，我尝试通过增加列的大小来解决此问题，但错误仍然出现。

from datetime import date
import pandas as pd
import pyodbc
import numpy as np

"""
for driver in pyodbc.drivers():
    print(driver)
"""

#define the server name, database and login credentials
server = '#########'
database ='#########'
username='########'
password='#######'

#define connection string
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; \
                      SERVER=' + server +';\
                      DATABASE='+ database +';\
                      UID='+ username +';\
                      PWD='+ password +';')

cnxn.autocommit = True
cursor = cnxn.cursor()

cursor.execute(" IF OBJECT_ID('dbo.vintage_data_2') IS NOT NULL\
               DROP TABLE dbo.vintage_data_2; \
               CREATE TABLE ABC.[dbo].vintage_data_2 \
               ([ConsumerID] float,[SubscriberID] numeric(11,0), \
               [OpeningBalanceAmt] numeric(19,4),[AccountOpenedDate] varchar,\
               [RetroDate] varchar,[Retailer] varchar(30))")
cnxn.commit()

for index,row in df.iterrows():
    cursor.execute("INSERT INTO ABC.[dbo].vintage_data_2([ConsumerID],\
          [SubscriberID],[OpeningBalanceAmt],[AccountOpenedDate],[RetroDate],\
          ,[Retailer]) \
              values (?,?,?,?,?,?)",
              row['CONSUMERID'],  
              row['SubscriberID'],
              row['OpeningBalanceAmt'], 
              row['AccountOpenedDate'], 
              row['RetroDate'], 
              row['Retailer']) 
cnxn.commit()

零售商列中的数据如下所示:

 print(df['Retailer'])
0      Retailer 4
1      Retailer 6
2      Retailer 3
3      Retailer 6
4      Retailer 2
5      Retailer 6
6      Retailer 2
7      Retailer 5
8      Homechoice

Answer 1

在 CREATE TABLE、DECLARE 等中使用不合格的 varchar 关键字时要小心。varchar 通常(总是？)被解释为 varchar(1) 在 SQL Server 中。