0

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:

enter image description here

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.

M Z
  • 4,571
  • 2
  • 13
  • 27
Felipe Carlesso
  • 23
  • 3

1 Answers1

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

Dejun Ling
  • 21
  • 2
  • to_sql is very slow.. bulk inserting is many times faster – Felipe Carlesso Apr 27 '23 at 01:22
  • SQL Server db try to enable `fast_executemany=True ` in your `create_engine`, here is the link about bulk insert question 1.(https://stackoverflow.com/questions/63171038/improve-pandas-to-sql-performance-with-sql-server/63178240#63178240) 2.(https://stackoverflow.com/questions/31997859/bulk-insert-a-pandas-dataframe-using-sqlalchemy) – Dejun Ling Apr 27 '23 at 03:00