Pandas Dataframe to SQL Server

Question

I have an API service and in this service I'm writing pandas dataframe results to SQL Server.

But when I want to add new values to the table, I cannot add. I've used append option because in the documentation it says that it adds new values to the dataframe. I didn't use replace option because I don't want to drop my table every time.

My need is to send new values to the database table while I'm keeping the old ones.

I've researched any other methods or ways except pandas to_sql method but I could only see the pandas at everywhere.

Does anybody have an idea about this?

Thanks.

score 2 · Answer 1 · answered Jul 23 '20 at 15:25

You should make sure that your pandas dataframe has the right structure where keys are your mysql column names and data is in lists:

 df = pd.DataFrame({"UserId":["rrrrr"],
         "UserFavourite":["Greek Salad"],
         "MonthlyOrderFrequency":[5],
         "HighestOrderAmount":[30],
         "LastOrderAmount":[21],
         "LastOrderRating":[3],
         "AverageOrderRating":[3],
         "OrderMode":["Web"],
         "InMedicalCare":["No"]})

Establish a proper connection to your db. In my case I am connecting to my local db at 127.0.0.1 and 'use demo;':

 sqlEngine = create_engine('mysql+pymysql://root:@127.0.0.1/demo', pool_recycle=3600)

 dbConnection = sqlEngine.connect()

Lastly, input your table name, mine is "UserVitals", and try executing in a try-except block to handle errors:

 try:

     df.to_sql("UserVitals", con=sqlEngine, if_exists='append');

 except ValueError as vx:

     print(vx)

 except Exception as ex:   

     print(ex)

 else:

     print("Table %s created successfully."%tableName);   

 finally:

     dbConnection.close()

score -1 · Answer 2 · answered Aug 02 '20 at 14:28

Here's an example of how to do that...with a little extra code included.

# Insert from dataframe to table in SQL Server
import time
import pandas as pd
import pyodbc

# create timer
start_time = time.time()
from sqlalchemy import create_engine


df = pd.read_csv("C:\\your_path\\CSV1.csv")

conn_str = (
    r'DRIVER={SQL Server Native Client 11.0};'
    r'SERVER=your_server_name;'
    r'DATABASE=NORTHWND;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str)

cursor = cnxn.cursor()

for index,row in df.iterrows():
    cursor.execute('INSERT INTO dbo.Table_1([Name],[Address],[Age],[Work]) values (?,?,?,?)', 
                    row['Name'], 
                    row['Address'], 
                    row['Age'],
                    row['Work'])
    cnxn.commit()
cursor.close()
cnxn.close()

# see total time to do insert
print("%s seconds ---" % (time.time() - start_time))

Pandas Dataframe to SQL Server

2 Answers2