Bulk inserting in python using SQLAlchemy and Pandas [duplicate]

Question

I am migrating from using pyodbc directly in favor of sqlalchemy as this is recommended for Pandas. I could do a simple executemany(con, df.values.tolist()) to bulk insert all rows from my pandas dataframe into a SQL Server table. After migrating, this is what I currently have:

def insert_into_table(self, con: sqlalchemy.Connection, table: str, df: pd.DataFrame):

    # Create insert statement
    query = f"INSERT INTO {table} (" + ",".join(df.columns) + ") VALUES (" + ",".join(["?"] * len(df.columns)) + ")"

    # Replace nan to None to prevent insert errors
    df = df.replace({np.nan: None})

    # Insert all rows
    con.execute(statement = sqlalchemy.text(query), parameters = df.to_dict(orient = "records"))

    # Commit the table
    con.commit()

    return

The error returned by the con.execute statement is: sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('Expected 31 parameters, supplied 0', 'HY000')

The query is correct and below is some example of the df.to_dict(orient = "records") output:

Not sure what else is missing... any help is appreciated.

Thanks

I am expecting that con.execute() insert all dataframe rows into a SQL Server table.

Dejun Ling · Accepted Answer · 2023-04-27 00:58:07Z

0

you can use dataframe.to_sql()

import pandas as pd
import pymysql
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://USER:PASSWORD@HOST/DATABASE")

df = pd.DataFrame()
df.to_sql(name='dbtable_name', con=engine, if_exists = 'append', index=False)

I use mysql in my codes,you can change db what you want

answered Apr 27, 2023 at 0:58

Dejun Ling

312 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Felipe Carlesso Over a year ago

to_sql is very slow.. bulk inserting is many times faster

Dejun Ling Over a year ago

SQL Server db try to enable fast_executemany=True in your create_engine, here is the link about bulk insert question 1.(stackoverflow.com/questions/63171038/…) 2.(stackoverflow.com/questions/31997859/…)

Collectives™ on Stack Overflow

Bulk inserting in python using SQLAlchemy and Pandas [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related