Execute vs ExecuteMany

[chad-ongstad]

I seem to get 2 different result sets when using executemany vs. execute when inserting data. I have included an example below that shows how the last 3 records insert differently using execute vs. execute many. I am happy to help out if you can point me in the right direction in the code base. Thanks,

Chad

import ctds
dropStmnt='drop table if exists demoSource_sales.W_salesorderdetail'

createStmnt="""Create table  [demoSource_sales].[W_salesorderdetail]
             (  [salesorderid] int
              , [salesorderdetailid] int
              , [carriertrackingnumber] nvarchar(25)
              , [orderqty] smallint
              , [productid] int
              , [specialofferid] int
              , [unitprice] numeric
              , [unitpricediscount] numeric
              , [rowguid] char(36)
              , [modifieddate] datetime2
             ) """

insertStmnt="""Insert into demoSource_sales.W_salesorderdetail             
                       (salesorderid
                        ,salesorderdetailid
                        ,carriertrackingnumber
                        ,orderqty
                        ,productid
                        ,specialofferid
                        ,unitprice
                        ,unitpricediscount
                        ,rowguid
                        ,modifieddate) 
                values (cast( :0 as int)
                        ,cast( :1 as int)
                        ,cast( :2 as nvarchar(25))
                        ,cast( :3 as smallint)
                        ,cast( :4 as int)
                        ,cast( :5 as int)
                        ,cast( :6 as numeric)
                        ,cast( :7 as numeric)
                        ,cast( :8 as char(36))
                        ,cast( :9 as datetime2))"""

dataSet=[
           [u'43659', u'1', u'4911-403C-98', u'1', u'776', u'1', u'2024.994', u'0', u'b207c96d-d9e6-402b-8470-2cc176c42283', u'2011-05-31 00:00:00']
         , [u'43659', u'2', u'4911-403C-98', u'3', u'777', u'1', u'2024.994', u'0', u'7abb600d-1e77-41be-9fe5-b9142cfc08fa', u'2011-05-31 00:00:00']
         , [u'43659', u'3', u'4911-403C-98', u'1', u'778', u'1', u'2024.994', u'0', u'475cf8c6-49f6-486e-b0ad-afc6a50cdd2f', u'2011-05-31 00:00:00']
         , [u'43659', u'4', u'4911-403C-98', u'1', u'771', u'1', u'2039.994', u'0', u'04c4de91-5815-45d6-8670-f462719fbce3', u'2011-05-31 00:00:00']
         , [u'43659', u'5', u'4911-403C-98', u'1', u'772', u'1', u'2039.994', u'0', u'5a74c7d2-e641-438e-a7ac-37bf23280301', u'2011-05-31 00:00:00']
         , [u'43659', u'6', u'4911-403C-98', u'2', u'773', u'1', u'2039.994', u'0', u'ce472532-a4c0-45ba-816e-eefd3fd848b3', u'2011-05-31 00:00:00']
         , [u'43659', u'7', u'4911-403C-98', u'1', u'774', u'1', u'2039.994', u'0', u'80667840-f962-4ee3-96e0-aeca108e0d4f', u'2011-05-31 00:00:00']
         , [u'43659', u'8', u'4911-403C-98', u'3', u'714', u'1', u'28.8404', u'0', u'e9d54907-e7b7-4969-80d9-76ba69f8a836', u'2011-05-31 00:00:00']
         , [u'43659', u'9', u'4911-403C-98', u'1', u'716', u'1', u'28.8404', u'0', u'aa542630-bdcd-4ce5-89a0-c1bf82747725', u'2011-05-31 00:00:00']
         , [u'43659', u'10', u'4911-403C-98', u'6', u'709', u'1', u'5.7', u'0', u'ac769034-3c2f-495c-a5a7-3b71cdb25d4e', u'2011-05-31 00:00:00']
         , [u'43659', u'11', u'4911-403C-98', u'2', u'712', u'1', u'5.1865', u'0', u'06a66921-6b9f-4199-a912-ddafd383472b', u'2011-05-31 00:00:00']
         , [u'43659', u'12', u'4911-403C-98', u'4', u'711', u'1', u'20.1865', u'0', u'0e371ee3-253e-4bb0-b813-83cf4224f972', u'2011-05-31 00:00:00']
        ]

con_args={'server':'xxx.xxx.x.x','port':1433,'user':'xx','password':'xxxxxx','database':'xxxx','timeout':86400}

conn=ctds.connect(**con_args)
cursor=conn.cursor()

cursor.execute(dropStmnt)
cursor.execute(createStmnt)
cursor.executemany(insertStmnt,dataSet)

cursor.execute('select * from demoSource_sales.W_salesorderdetail')

result=cursor.fetchall()

for r in result:
    print tuple(r)

print '#'*20

cursor.execute('truncate table demoSource_sales.W_salesorderdetail')

for row in dataSet:
    cursor.execute(insertStmnt,row)

cursor.execute('select * from demoSource_sales.W_salesorderdetail')

result=cursor.fetchall()

for r in result:
    print tuple(r)

Returns the following

(43659L, 1L, u'4911-403C-98', 1L, 776L, 1L, Decimal('2025'), Decimal('0'), u'b207c96d-d9e6-402b-8470-2cc176c42283', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 2L, u'4911-403C-98', 3L, 777L, 1L, Decimal('2025'), Decimal('0'), u'7abb600d-1e77-41be-9fe5-b9142cfc08fa', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 3L, u'4911-403C-98', 1L, 778L, 1L, Decimal('2025'), Decimal('0'), u'475cf8c6-49f6-486e-b0ad-afc6a50cdd2f', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 4L, u'4911-403C-98', 1L, 771L, 1L, Decimal('2040'), Decimal('0'), u'04c4de91-5815-45d6-8670-f462719fbce3', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 5L, u'4911-403C-98', 1L, 772L, 1L, Decimal('2040'), Decimal('0'), u'5a74c7d2-e641-438e-a7ac-37bf23280301', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 6L, u'4911-403C-98', 2L, 773L, 1L, Decimal('2040'), Decimal('0'), u'ce472532-a4c0-45ba-816e-eefd3fd848b3', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 7L, u'4911-403C-98', 1L, 774L, 1L, Decimal('2040'), Decimal('0'), u'80667840-f962-4ee3-96e0-aeca108e0d4f', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 8L, u'4911-403C-98', 3L, 714L, 1L, Decimal('29'), Decimal('0'), u'e9d54907-e7b7-4969-80d9-76ba69f8a836', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 9L, u'4911-403C-98', 1L, 716L, 1L, Decimal('29'), Decimal('0'), u'aa542630-bdcd-4ce5-89a0-c1bf82747725', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 1L, u'4911-403C-98', 6L, 709L, 1L, Decimal('6'), Decimal('0'), u'ac769034-3c2f-495c-a5a7-3b71cdb25d4e', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 1L, u'4911-403C-98', 2L, 712L, 1L, Decimal('5'), Decimal('0'), u'06a66921-6b9f-4199-a912-ddafd383472b', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 1L, u'4911-403C-98', 4L, 711L, 1L, Decimal('20'), Decimal('0'), u'0e371ee3-253e-4bb0-b813-83cf4224f972', datetime.datetime(2011, 5, 31, 0, 0))
####################
(43659L, 1L, u'4911-403C-98', 1L, 776L, 1L, Decimal('2025'), Decimal('0'), u'b207c96d-d9e6-402b-8470-2cc176c42283', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 2L, u'4911-403C-98', 3L, 777L, 1L, Decimal('2025'), Decimal('0'), u'7abb600d-1e77-41be-9fe5-b9142cfc08fa', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 3L, u'4911-403C-98', 1L, 778L, 1L, Decimal('2025'), Decimal('0'), u'475cf8c6-49f6-486e-b0ad-afc6a50cdd2f', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 4L, u'4911-403C-98', 1L, 771L, 1L, Decimal('2040'), Decimal('0'), u'04c4de91-5815-45d6-8670-f462719fbce3', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 5L, u'4911-403C-98', 1L, 772L, 1L, Decimal('2040'), Decimal('0'), u'5a74c7d2-e641-438e-a7ac-37bf23280301', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 6L, u'4911-403C-98', 2L, 773L, 1L, Decimal('2040'), Decimal('0'), u'ce472532-a4c0-45ba-816e-eefd3fd848b3', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 7L, u'4911-403C-98', 1L, 774L, 1L, Decimal('2040'), Decimal('0'), u'80667840-f962-4ee3-96e0-aeca108e0d4f', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 8L, u'4911-403C-98', 3L, 714L, 1L, Decimal('29'), Decimal('0'), u'e9d54907-e7b7-4969-80d9-76ba69f8a836', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 9L, u'4911-403C-98', 1L, 716L, 1L, Decimal('29'), Decimal('0'), u'aa542630-bdcd-4ce5-89a0-c1bf82747725', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 10L, u'4911-403C-98', 6L, 709L, 1L, Decimal('6'), Decimal('0'), u'ac769034-3c2f-495c-a5a7-3b71cdb25d4e', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 11L, u'4911-403C-98', 2L, 712L, 1L, Decimal('5'), Decimal('0'), u'06a66921-6b9f-4199-a912-ddafd383472b', datetime.datetime(2011, 5, 31, 0, 0))
(43659L, 12L, u'4911-403C-98', 4L, 711L, 1L, Decimal('20'), Decimal('0'), u'0e371ee3-253e-4bb0-b813-83cf4224f972', datetime.datetime(2011, 5, 31, 0, 0))

[joshuahlang]

This is due to the implementation of executemany, which internally uses sp_executesql for better performance (and to avoid the possibility of SQL injection). The sequence in the parameter set is used to determine the SQL type to declare for each of the parameters passed to sp_executesql. in this case, you have a string of length 1, so the type is inferred as CHAR(1). Of course later items in the parameter set may have values longer than 1 character and are truncated.

It should be fairly easy to default to SQL type inference to default to the largest widths of the variable sized SQL types for this case. In the meantime, you could explicitly specify the SQL type for the string columns using a type wrapper such as: https://zillow.github.io/ctds/types.html#ctds.SqlNVarChar

Alternatively passing the INT columns as Python int (or long) objects would also avoid the truncation issue.

[joshuahlang]

Fixed in v1.8.0.

[chad-ongstad]

Thank you!! This is definitely the fastest python sql server library out there.. I wish I had the c programming background so I could help.

Chad

Sent from my iPhone

On Sep 7, 2018, at 5:53 PM, joshuahlang notifications@github.com<mailto:notifications@github.com> wrote:

Fix in v1.8.0.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/zillow/ctds/issues/25#issuecomment-419586952, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AebiKhjOuhMy_DpH7DcMbd5_Q_qteqSJks5uYvjSgaJpZM4V9gx1.