3

I am using python pandas and pyodbc to load data from a SQL Server database and bulk the data to a csv, then, I need to update a table using the values contained in a list. There are a 10,000+ rows so the UPDATE query's take some time.

Is there an efficient way to make an update statement for each row value from a column contained in the df?

I have created a list containing the values I need to update.

This is the statement that I want to execute:

 UPDATE MYTABLE SET ResRet ='1' WHERE ResTip ='FC' AND ResNum = --'#column_values'

Here´s my code:

import pyodbc
import pandas as pd
pd.set_option('display.max_rows',None) 

server = #myserver 
database = #mydb 
username = #myuser 
password = #mypwd 
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

docs_pd = pd.read_csv("data/processed/"+"cufe_vacio.csv", usecols = [4,5,16,17,30])
docs_pd["ResNum"] = docs_pd['ResNum'].astype('int')
docs_pd["ResFec"] = docs_pd['ResFec'].astype('datetime64')
docs_pd["RESFECMOV"] = docs_pd['RESFECMOV'].astype('datetime64')

filter_df = docs_pd.apply(lambda x: x.str.strip() if x.dtype == "object" else x)  # Trim whitespaces

values = filter_df['ResNum'].value_counts()
labels = values.index.unique()
jarlh
  • 42,561
  • 8
  • 45
  • 63
Rafael G
  • 29
  • 7
  • 1
    For modern versions of SQL Server you can pass the Pandas data in bulk as JSON and consume that on the server. See https://stackoverflow.com/questions/57615642/trying-to-insert-pandas-dataframe-to-temporary-table/57616645#57616645 and https://stackoverflow.com/questions/62102584/how-can-i-speed-up-the-code-that-contains-the-sql-query/62103545#62103545 – David Browne - Microsoft Oct 02 '20 at 13:06
  • Thanks for the quickly answer, but I´m working with SQL server 2014.. – Rafael G Oct 02 '20 at 13:09
  • 1
    Older versions support the same idea but using XML. https://learn.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type?view=sql-server-ver15 – David Browne - Microsoft Oct 02 '20 at 13:10

0 Answers0