pandas get to_sql behind-the-scenes query as string

Question

In pandas, I would like to get the SQL command that is run when something like df.to_sql('table', connection, if_exists='append', method='multi') is run in python.

Meaning the output would look like 'INSERT INTO table (...) VALUES (...)'. Is this possible?

Answer 1

From what I've seen the best way to do this is with the echo kwarg in the engine. So for example:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///:memory:", echo=True)

df = pd.DataFrame(zip(['Alex','Bob'],[30,40]), columns=["name","age"])

with engine.connect() as conn:
    df.to_sql('users', conn)

This will produce the following output:

2023-01-18 11:48:38,369 INFO sqlalchemy.engine.Engine INSERT INTO users ("index", name, age) VALUES (?, ?, ?)
2023-01-18 11:48:38,369 INFO sqlalchemy.engine.Engine [generated in 0.00010s] ((0, 'Alex', 30), (1, 'Bob', 40))

You can also log it using logging and logging_name="myengine" keyword arg in the create_engine call. See SQLAlchemy docs for more information.