Update SQL Server using pyodbc

Question

I want to save my dataframe to SQL Server with pyodbc that updates every month (I want the SQL data contains 300 data with updates everymonth).the problem is every time I run the py file, it gets added instead replace all data. Before I'm using sqlachemy and I can do it with if_exist=replace. Now I'm using pyodbc, I don't know what to do. This is what I do

col_names = ["month", "price", "change"]
df = pd.read_csv("sawit.csv",sep=',',quotechar='\'',encoding='utf8', names=col_names,skiprows = 1) # Replace Excel_file_name with your excel sheet name
for index,row in df.iterrows():
    cursor.execute("update dbo.sawit set month = ?, price = ?, change =? ;", (row.month, row.price, row.change))
                    
    cnxn.commit()
cursor.close()
cnxn.close()

But the result that I got is the date all replaced with last record. What should I do? Thank you in advance.

If you want to completely replace the contents of the table each time then you can do TRUNCATE TABLE dbo.sawit followed by INSERT INTO dbo.sawit … — Gord Thompson
– Gord Thompson, Commented Feb 22, 2021 at 14:27

ASH · Accepted Answer · 2021-02-23 04:35:41Z

There's a much simpler way to do this kind of thing.

import pandas as pd
import pyodbc
from fast_to_sql import fast_to_sql as fts

# Test Dataframe for insertion
df = pd.DataFrame(your_dataframe_here)

# Create a pyodbc connection
conn = pyodbc.connect(
    """
    Driver={ODBC Driver 17 for SQL Server};
    Server=localhost;
    Database=my_database;
    UID=my_user;
    PWD=my_pass;
    """
)

# If a table is created, the generated sql is returned
create_statement = fts.fast_to_sql(df, "my_great_table", conn, if_exists="replace")

# Commit upload actions and close connection
conn.commit()
conn.close()

Main Function:

fts.fast_to_sql(df, name, conn, if_exists="append", custom=None, temp=False)

Here is a slightly different way to do essentially the same thing.

import pyodbc

engine = "mssql+pyodbc://server_name/db_name?driver=SQL Server Native Client 11.0?trusted_connection=yes"

# your dataframe is here
df.to_sql(name_of_dataframe, engine, if_exists='append', index=True, chunksize=100000)

NOTE: pyodbc will dynamically create the appropriate strongly-types fields in the table for you.

thomas · Accepted Answer · 2021-02-22 05:14:28Z

0

Your sql sql query does not say what entry to be replaced. There is neither a where clause to select the correct line for each entry, neither there is some primary key. So in every loop, all rows are replaced with the current entry. The last time this is done, is with the last entry, therefore every row is replaced with the last entry.

You can add some a where clause looking for the correct month to replaced.

something equivalent to this:

updatedbo.sawit set month = ?, price = ? where month = ?;", (row.month, row.price, row.month)

answered Feb 22, 2021 at 5:14

thomas

4693 silver badges9 bronze badges

3 Comments

adinda aulia Over a year ago

thank you ! i want update all the column with new data..

thomas Over a year ago

I just missed change because of lazyness. you can simply add it

adinda aulia Over a year ago

the result is still the same tho. all replaced with last record

Collectives™ on Stack Overflow

Update SQL Server using pyodbc

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related