Python SQL script inserts only the last row of the dataframe to SQL Server [pyodbc]

Question

I am trying to import a pandas dataframe to Microsoft SQL Server. Even though, the command is correct and executed successfully, when I select the first 1000 rows of the table in SQL server only the last row of the imported dataframe is shown.

I have the latest version of SQL Server (downloaded yesterday)

Do you know what's causing this?

SQL Code (Stored procedure)

USE [Movies_Dataset]
GO
/****** Object:  StoredProcedure [dbo].[store_genres]    Script Date: xxx ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[store_genres]
                                    @Movie_Title NVARCHAR(MAX), 
                                    @Genres NVARCHAR(MAX)
  AS
  BEGIN
        DROP TABLE IF EXISTS [dbo].[table_genres];
        CREATE TABLE [dbo].[table_genres]([@Movie_Title] NVARCHAR(MAX),
                                          [@Genres] NVARCHAR(MAX)
                                         )
        INSERT INTO [dbo].[table_genres]
        VALUES
             ( @Movie_Title,
               @Genres
             )
  END;

Python script to import the data

connStr = pyodbc.connect("DRIVER={SQL Server Native Client 11.0};"
                         "SERVER=LAPTOP-IFTEP7AL;"
                         "DATABASE=Movies_Dataset;"
                         "Trusted_Connection=yes")

cursor = connStr.cursor()

delete_table =  """           
                        IF dbo.TableExists('table_genres') = 1
                             DELETE FROM table_genres     
                """

insert_values = """ 
                        EXEC [dbo].[store_genres] @Movie_Title = ?, @Genres = ?;
                """   

cursor.execute(delete_table)

for index, row in genres.iterrows():

    params = (row['title'], row['genres'])

    cursor.execute(insert_values, params)

connStr.commit()

cursor.close()

connStr.close()

Dataframe I want to import

SQL Result

David Browne - Microsoft · Accepted Answer · 2020-01-26 16:08:57Z

I select the first 1000 rows of the table in SQL server only the last row of the imported dataframe is shown.

Because your stored procedure drops the table every time before inserting a row.

So don't do that. Also don't use @ in your column names. That's just for parameters, arguments, and local variables.

EG:

USE [Movies_Dataset]
GO

SET QUOTED_IDENTIFIER ON
GO
        DROP TABLE IF EXISTS [dbo].[table_genres];
        CREATE TABLE [dbo].[table_genres]([Movie_Title] NVARCHAR(MAX),
                                          [Genres] NVARCHAR(MAX)
                                         )

GO
/****** Object:  StoredProcedure [dbo].[store_genres]    Script Date: xxx ******/
SET ANSI_NULLS ON
GO

CREATE OR ALTER PROCEDURE [dbo].[store_genres] 
                                    @Movie_Title NVARCHAR(MAX), 
                                    @Genres NVARCHAR(MAX)
  AS
  BEGIN
        INSERT INTO [dbo].[table_genres](Movie_Title,Genres)
        VALUES
             ( @Movie_Title,
               @Genres
             )
  END;

Collectives™ on Stack Overflow

Python SQL script inserts only the last row of the dataframe to SQL Server [pyodbc]

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related