I am trying to insert few rows using the function executemany of a python cursor using this INSERT query:
INSERT_INTO_EVENTS_QUERY = """MERGE [dbo].[events] AS Target
using (VALUES (
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?
)) AS S([game_id], [event_id], [period_id], [team_id], [player_id],
[related_event_id], [type_id], [bodypart_id], [outcome], [timestamp],
[minute], [second], [start_x], [start_y], [end_x], [end_y], [length],
[angle], [standing], [inside_box], [keypass], [big_chance_created],
[assist], [offensive_duel], [defensive_duel], [opposite_related_event_id],
[related_player_id], [touch], [shot], [goal], [zone],
[current_score_state])
ON Target.game_id = s.game_id
AND Target.event_id = s.event_id
WHEN matched THEN
UPDATE SET Target.game_id = s.game_id,
Target.[event_id] = s.[event_id],
Target.[period_id] = s.[period_id],
Target.[team_id] = s.[team_id],
Target.[player_id] = s.[player_id],
Target.[related_event_id] = s.[related_event_id],
Target.[type_id] = s.[type_id],
Target.[bodypart_id] = s.[bodypart_id],
Target.[outcome] = s.[outcome],
Target.[timestamp] = s.[timestamp],
Target.[minute] = s.[minute],
Target.[second] = s.[second],
Target.[start_x] = s.[start_x],
Target.[start_y] = s.[start_y],
Target.[end_x] = s.[end_x],
Target.[end_y] = s.[end_y],
Target.[length] = s.[length],
Target.[angle] = s.[angle],
Target.[standing] = s.[standing],
Target.[inside_box] = s.[inside_box],
Target.[keypass] = s.[keypass],
Target.[big_chance_created] = s.[big_chance_created],
Target.[assist] = s.[assist],
Target.[offensive_duel] = s.[offensive_duel],
Target.[defensive_duel] = s.[defensive_duel],
Target.[opposite_related_event_id] = s.[opposite_related_event_id],
Target.[related_player_id] = s.[related_player_id],
Target.[touch] = s.[touch],
Target.[shot] = s.[shot],
Target.[goal] = s.[goal],
Target.[zone] = s.[zone],
Target.[current_score_state] = s.[current_score_state]
WHEN NOT matched BY target THEN
INSERT ( [game_id],
[event_id],
[period_id],
[team_id],
[player_id],
[related_event_id],
[type_id],
[bodypart_id],
[outcome],
[timestamp],
[minute],
[second],
[start_x],
[start_y],
[end_x],
[end_y],
[length],
[angle],
[standing],
[inside_box],
[keypass],
[big_chance_created],
[assist],
[offensive_duel],
[defensive_duel],
[opposite_related_event_id],
[related_player_id],
[touch],
[shot],
[goal],
[zone],
[current_score_state] )
VALUES ( s.[game_id],
s.[event_id],
s.[period_id],
s.[team_id],
s.[player_id],
s.[related_event_id],
s.[type_id],
s.[bodypart_id],
s.[outcome],
s.[timestamp],
s.[minute],
s.[second],
s.[start_x],
s.[start_y],
s.[end_x],
s.[end_y],
s.[length],
s.[angle],
s.[standing],
s.[inside_box],
s.[keypass],
s.[big_chance_created],
s.[assist],
s.[offensive_duel],
s.[defensive_duel],
s.[opposite_related_event_id],
s.[related_player_id],
s.[touch],
s.[shot],
s.[goal],
s.[zone],
s.[current_score_state] );"""
However, I keep getting the same error:
cursor.executemany(INSERT_INTO_EVENTS_QUERY, events.replace({np.nan: None}).values.tolist())
returns:
('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to bigint. (8114) (SQLExecute)')
I believe this is happening because player_id has NULL values and that is causing the problem. The odd part is that if I use the events.replace({np.nan: None}).values.tolist():
[[1686101,1686101442,1,44,None,None,32,0,True,'2022-09-06 17:45:00',0,0,0.0,0.0,0.0,0.0,None,None,False,False,False,False,False,False,False,None,None,False,False,0,None,0]]
and run the exact same command:
cursor.executemany(INSERT_INTO_EVENTS_QUERY, [[1686101,1686101442,1,44,None,None,32,0,True,'2022-09-06 17:45:00',0,0,0.0,0.0,0.0,0.0,None,None,False,False,False,False,False,False,False,None,None,False,False,0,None,0]])
It works perfectly and does not throw any error. Also, I tested exactly the same in Azure SQL and it works without any problem.
My table script:
USE [sportsanalytics]
GO
/****** Object: Table [dbo].[events] Script Date: 9/12/2022 11:28:43 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[events](
[game_id] [bigint] NOT NULL,
[event_id] [bigint] NOT NULL,
[period_id] [bigint] NULL,
[team_id] [bigint] NULL,
[player_id] [bigint] NULL,
[related_event_id] [bigint] NULL,
[type_id] [smallint] NULL,
[bodypart_id] [smallint] NULL,
[outcome] [bit] NULL,
[timestamp] [varchar](50) NULL,
[minute] [varchar](50) NULL,
[second] [varchar](50) NULL,
[start_x] [float] NULL,
[start_y] [float] NULL,
[end_x] [float] NULL,
[end_y] [float] NULL,
[length] [float] NULL,
[angle] [float] NULL,
[standing] [bit] NULL,
[inside_box] [bit] NULL,
[keypass] [bit] NULL,
[big_chance_created] [bit] NULL,
[assist] [bit] NULL,
[offensive_duel] [bit] NULL,
[defensive_duel] [bit] NULL,
[opposite_related_event_id] [nvarchar](50) NULL,
[related_player_id] [varchar](50) NULL,
[touch] [bit] NULL,
[shot] [bit] NULL,
[goal] [smallint] NULL,
[zone] [varchar](max) NULL,
[current_score_state] [smallint] NULL,
[xT] [real] NULL,
[VAEP] [real] NULL,
[vaep_offensive_value] [real] NULL,
[vaep_defensive_value] [real] NULL,
CONSTRAINT [PK_events] PRIMARY KEY CLUSTERED
(
[event_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Any idea on what I'm doing wrong?