CODEWITHSUNDEEP
pythonpandasms-accesspyodbc
Chris Macaluso
Chris Macaluso

Reputation: 1482

pyodbc - write a new column of data to existing table in ms access

I have a ms access db I've connected to with (ignore the ... in the drive name, it's working):

driver = 'DRIVER={...'
con = pyodbc.connect(driver)
cursor = con.cursor()

I have a pandas dataframe which is exactly the same as a table in the db except there's an additional column. Basically I pulled the table with pyodbc, merged it with external excel data to add this additional column, and now want to push the data back to the ms access table with the new column. The pandas df containing the new information is merged_df['Item']

Trying things like below does not work, I've had a variety of errors.

cursor.execute("insert into ToolingData(Item) values (?)", merged_df['Item'])
con.commit()

How can I push the new column to the original table? Can I just write over the entire table instead? Would that be easier? Since merged_df is literally the same thing with the addition of one new column.

Upvotes: 0

Views: 652

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

If the target MS Access table does not already contain a field to house the data held within the additional column, you'll first need to execute an alter table statement to add the new field.

For example, the following will add a 255-character text field called item to the table ToolingData:

alter table ToolingData add column item text(255)

Upvotes: 1

Related Questions