CODEWITHSUNDEEP
pythonsql-serverpython-3.xpyodbc
Mg Mogaki
Mg Mogaki

Reputation: 113

Python: DataError - String or Binary data would be truncated

I'm trying to insert values into my newly created table. I'm getting the following error:

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)')

I found examples with similar problems and it seems the problem involves inserting string values that have more characters than a column can accommodate.

I however tried to resolve this by increasing the size of my column but the error still appears. 

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()

The data inside the Retailer column looks like this:

 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

Upvotes: 2

Views: 3009

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

Be careful when using an unqualified varchar keyword in CREATE TABLE, DECLARE, etc.. varchar is often (always?) interpreted as varchar(1) in SQL Server.

Upvotes: 1

Related Questions