Revision history [back]

To add new records to a pandas dataframe that represents a SQL Server table, you can use the append() method combined with a new row of data. For example:

import pandas as pd
import pyodbc

# Connect to SQL Server
conn = pyodbc.connect('Driver={SQL Server};'
                   'Server=server_name;'
                   'Database=database_name;'
                   'Trusted_Connection=yes;')

# Load the data from the SQL Server table
df = pd.read_sql('SELECT * FROM table_name', conn)

# Append a new row of data to the dataframe
new_row = {'column1': 'value1', 'column2': 'value2', 'column3': 'value3'}
df = df.append(new_row, ignore_index=True)

# Modify an existing record in the dataframe
df.loc[df['column1'] == 'old_value', 'column1'] = 'new_value'

# Write the updated dataframe back to the SQL Server table
df.to_sql('table_name', conn, if_exists='replace', index=False)

In this example, we first connect to the SQL Server database using pyodbc. Then, we use pandas to load the data from the SQL Server table into a dataframe. Next, we append a new row of data to the dataframe using a dictionary with key-value pairs that map to the column names and values. We also set the ignore_index parameter to True to make sure that the new row is appended to the end of the dataframe with a new index value.

To modify an existing record in the dataframe, we use the loc[] method to select the row(s) we want to modify based on a condition, and then specify the column(s) we want to modify and the new value(s) we want to set.

Finally, we write the updated dataframe back to the SQL Server table using the to_sql() method, which takes the name of the SQL Server table as an argument, along with the database connection object, and the if_exists parameter set to 'replace' to overwrite the existing table with the updated data.