0

Upon inserting the data into the MS SQL Server DB, my values are getting truncated and single extra space is added in between. For eg:-

HomeShop18 is saved as H o m e s. It is truncated as well and space is also included.

But when I do select * from table where col= 'Homes' it shows the data. What is the problem? How can I insert the data into the SQL Server DB?

Here's what I'm currently doing? PS : I have read threads on SO pointing out to increase the size and TDS version. I've tried it all but still the inserting entry is truncated.

def post_data(data):
    os.environ['TDSVER'] = '8.0'

    data = (
        data['AWB_Number'], data['Weight'], data['Length'], data['Height'],
        data['Width'], data['Customer_Name'], datetime.datetime.strptime(data['Scan_Time'][:19] , '%Y-%m-%d %H:%M:%S'),data['Series_Flag']
        )

    print data # ('40642847766', '0.011', '1.1', '1.1', '1.1', 'flipkart', datetime.datetime(2014, 8, 14, 11, 14, 53), 'True')
con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
    cnxn = pyodbc.connect(con_string)

    cursor = cnxn.cursor()
    cursor.execute("insert into data_AutoScale_DELHUB(AWB_Number,Weight,Length,Width,Height,Customer_Name,Scan_Time,Series_Flag) VALUES (?, ?, ?, ?, ?, ?, ?, ?)" , data[0],data[1],data[2],data[3],data[4],data[5],data[6],data[7])
    cursor.commit()

    cnxn.close()
Praful Bagai
  • 16,684
  • 50
  • 136
  • 267
  • Why don't you try manually executing the MySQL `insert` using a MySQL development IDE (rather than through your python program)? If you get it to work there, it may show some light on how to make the python program work correctly. You can always use [SQL Fiddle](http://sqlfiddle.com/) as well to test this. – SlimsGhost Aug 14 '14 at 12:49
  • You wrote MySQL on the third paragraph :) – Quentin THEURET Aug 14 '14 at 12:56
  • Can you give us the result of : print data – Quentin THEURET Aug 14 '14 at 12:56
  • Oops. Originally in one place you had "mssql" and later you had "mysql". But the idea is the same, except that you could use SQL Server Management Studio to test your queries, and you can also use SQL Fiddle. – SlimsGhost Aug 14 '14 at 12:57
  • @SlimsGhost- I provided the value for `data`. Kindly check. Thanks – Praful Bagai Aug 14 '14 at 13:13

1 Answers1

0

Be careful of the order of parameters. It seems 'Height' and 'Width' are inverted in the query and in the parameters.

Check if the order of your parameters in execute() is the same as 

(AWB_Number,Weight,Length,Width,Height,Customer_Name,Scan_Time,Series_Flag)
Quentin THEURET
  • 1,222
  • 6
  • 12