CODEWITHSUNDEEP
pythonsql-serverpandasscreen-scrapingpyodbc
adinda aulia
adinda aulia

Reputation: 193

Update SQL Server using pyodbc

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.

Upvotes: 0

Views: 3393

Answers (2)

ASH
ASH

Reputation: 20302

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.

Upvotes: 1

thomas
thomas

Reputation: 449

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)

Upvotes: 0

Related Questions