How to insert Pandas dataframe into Access DB-CodePudding

I want to insert a dataframe into a MS Access database. This is what I have so far:

import pyodbc
import pandas as pd

conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=path\file_name.accdb'
)

cnxn = pyodbc.connect(conn_str)
cursor = cnxn.cursor()

query = "SELECT * FROM Table"
d = pd.read_sql(query, cnxn)

df = d[[
    'id',
    'group_Nr'
]].sort_values(by=['id'])


# substitution for the import
df = pd.DataFrame({'group_Nr':[50,50,50,53,53,53,53,56,56,59,59,59]})
df["sequence_Nr"] = (df.groupby("group_Nr").ngroup()   1).astype(str).str.zfill(4)
df['id'] = df.index   1

df = df[['id', 'group Nr', 'sequence_Nr']]

print(df)

sql_create = f"CREATE TABLE Table_NEW  (id int PRIMARY KEY, group_Nr int , sequence_Nr string);"
sql_update = f"INSERT INTO Table_NEW (id, group_Nr, sequence_Nr) VALUES ({df['id']}, {df['group_Nr']}, {df['sequence_Nr']});"

cursor.execute(sql_create)
cursor.execute(sql_update)
cursor.commit()

This is not working, unfortunately. I'm afraid I have to iterate through each row.

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html

I found the to_sql Method, which is mentioned in some examples. However, I couldn't find a similar task so far which is working for me.

Do you know how to do this properly?

CodePudding user response：

I found a solution:

for index, row in result.iterrows():
    with cnxn.cursor() as crsr:
        crsr.execute("INSERT INTO Table_NEW(id, group_Nr, sequence_Nr) VALUES(?,?,?)",
            row['id'], row['group_Nr'], row['sequence_Nr'])

CodePudding user response：

Consider a flatter version of appending data with pyodbc's executemany.

# PREPARED STATEMENT WITH QMARKS
sql = "INSERT INTO Table_NEW (id, group_Nr, sequence_Nr) VALUES (?, ?, ?)"

# CONVERT COLUMN DATA TO LIST OF ROW LISTS
vals = result[["id", "group_Nr", "sequence_Nr"]].to_numpy().tolist()

with cnxn.cursor() as crsr: 
    crsr.executemany(sql, vals)