UPDATE values of a column in a table with Python in SQL Server

Question

I want to update a column with SQL server query in python, as you see I am updating the relative column as below: I have a CSV file with some A values of relative table as below:

CSV file: (a.csv)

ART-B-C-ART0015-D-E01
ADC-B-C-ADC00112-V-E01

Python Code: (create Name Value)

ff = pd.read_csv("C:\\a.csv",encoding='cp1252')
ff["Name"]= df["A"].str.extract(r'([a-zA-Z]{3}\d{4,5})') + "-A"

Result of python Code:

ART0015-A
ADC00112-A

Table :

A                              Name                     FamilyName
ART-B-C-ART0015-D-E01          NULL                        ART
ADC-B-C-ADC00112-V-E01         NULL                      ADC00112

Also A is a column in my table (Not all of the A records but some of them) and based on A value I want to update Name column. My database is SQL Server and I don't know how to update in Name Column in SQL Server where the A value in the csv file is equal to A in the relative table. Code in Python:

conn = pyodbc.connect('Driver={SQL Server}; Server=ipaddress; Database=dbname; UID=username; PWD= {password};')
cursor = conn.cursor()
conn.commit()
for row in ff.itertuples():
    cursor.execute('''UPDATE database.dbo.tablename SET Name where ?

)

conn.commit()

Expected result in table

A                              Name                     FamilyName
ART-B-C-ART0015-D-E01          ART0015-A                 ART
ADC-B-C-ADC00112-V-E01         ADC00112-A                ADC00112

I have a CSV file with A value in the column Does this file is available for MySQL server access? Show complete table's CREATE TABLE, some data sample for a table (3-5 rows) and for CSV file (2-3 rows), and desired result (table content after update). Also specify precise MySQL version. — Akina
– Akina, Commented Dec 12, 2020 at 10:59
I updated my content. but please be informed that i am not creating a table. I have a table and i must update a specific column. — Sara Daniel
– Sara Daniel, Commented Dec 12, 2020 at 11:07
please be informed that i am not creating a table. But this table exists, so it has a structure which may be provided. — Akina
– Akina, Commented Dec 12, 2020 at 11:08
PS. The tablename in shown code is database.dbo.tablename which matches SQL Server (MS SQL) dialect, not MySQL. Please look carefully what DB server you deal with. — Akina
– Akina, Commented Dec 12, 2020 at 11:19

Jason Cook · Accepted Answer · 2020-12-12 12:03:28Z

1

I would use an SQL temp table and inner join to update the values. This will work for only updating a subset of records in your SQL table. It can also be efficient at updating many records.

SQL Cursor

# reduce number of calls to server on inserts
cursor.fast_executemany = True

Create Temporary Table

statement = "CREATE TABLE #temp_tablename(A VARCHAR(200), Name VARCHAR(200))"
cursor.execute(statement)

Insert Values into a Temporary Table

# insert only the key and the updated values
subset = ff[['A','Name']]

# form SQL insert statement
columns = ", ".join(subset.columns)
values = '('+', '.join(['?']*len(subset.columns))+')'

# insert
statement = "INSERT INTO #temp_tablename ("+columns+") VALUES "+values
insert = [tuple(x) for x in subset.values]

cursor.executemany(statement, insert)

Update Values in Main Table from Temporary Table

statement = '''
UPDATE
     tablename
SET
     u.Name
FROM
     tablename AS t
INNER JOIN 
     #temp_tablename AS u 
ON
     u.A=t.A;
'''

cursor.execute(statement)

Drop Temporary Table

cursor.execute("DROP TABLE #temp_tablename")

edited Dec 12, 2020 at 12:03

answered Dec 12, 2020 at 11:55

Jason Cook

1,50913 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sara Daniel Over a year ago

Dear Jason. Thanks. isnt any way to just use UPDATE database.dbo.tablename query and not creating a temp table?

Jason Cook Over a year ago

You could but I image it would require creating the syntax in a Python loop due to needing an SQL case statement. I think this would also be the most efficient approach to take.

Collectives™ on Stack Overflow

UPDATE values of a column in a table with Python in SQL Server

1 Answer 1

SQL Cursor

Create Temporary Table

Insert Values into a Temporary Table

Update Values in Main Table from Temporary Table

Drop Temporary Table

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

SQL Cursor

Create Temporary Table

Insert Values into a Temporary Table

Update Values in Main Table from Temporary Table

Drop Temporary Table

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related