I'm trying to save my pandas dataframe as a SQL file
I followed the documentation and tried
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
df.to_sql('filename.sql', engine, chunksize=1000)
However, when I check the directory with os.listdir(), the file is not there
The first argument you pass to to_sql should be the name of the table in your database, not the name of the file. Take a look at the docs.
Also, if you want to create a sqlite file, you should create an engine with a file database, not in-memory:
engine = sqlalchemy.create_engine("sqlite:///mydb.db") # relative path to db
df.to_sql("my_table", engine)
Now there should be a file named mydb.db in the same directory where you run your application/script, with a table my_table containing the data in df.
engine = sqlalchemy.create_engine("sqlite:///mydb.db") , then with engine.connect() as conn, conn.begin():, then data = pd.read_sql_table('data', conn) ?read_sql_table as the con argument. The docs say you can pass a SQLAlchemy connectable. So, this: data = pd.read_sql_table('data', engine).
to_sqlmethod, at least I never used it that way. I always use it to export to a database.