How to generate SQL using pandas without a database connection?

Question

The pandas package have a method called .to_sql that help to insert the current data frame on to the database.

.to_sql doc:

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

The second parameter is con

sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection

Is it possible to generate the SQL query without passing a database connection?

Does this answer your question? Output SQL as string from pandas.DataFrame.to_sql — Laurent
– Laurent, Commented Nov 19, 2022 at 7:20

Rafa Acioly · Accepted Answer · 2022-11-19 10:53:20Z

1

We actually cannot print the query without a database connection, but we can use sqlalchemy create_mock_engine method and pass "memory" as the database URI to trick pandas, e.g:

from sqlalchemy import create_mock_engine, Metadata

def dump(sql, *multiparams, **params):
    print(sql.compile(dialect=engine.dialect))

engine = create_mock_engine("sqlite://:memory:", echo=True)
Metadata.create_all(engine, checkfirst=False)

frame.to_sql(engine)

answered Nov 19, 2022 at 10:53

Rafa Acioly

6561 gold badge17 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

How to generate SQL using pandas without a database connection?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related