How to create multiple tables in a .db file with pandas / sqlalchemy

Question

I have a function that loops through and returns me 3 separate dataframe objects.

How do I put these 3 dataframe objects into 1 .db file with 3 separate tables?

The below code is what I am currently using. It loops through each of the items I have, do the needful and return me a Dataframe object that I can put to sql after that.

for each_item in items:
    engine = create_engine("sqlite:///" + my_variable_database_name + ".db", echo=False)
    connection = engine.connect()
    pandas.DataFrame.to_sql(my_function(each_item), each_item, con=engine, if_exists="replace")
    connection.close()
    print(each_item + " has been completed successfully.")
print("All completed.")

How should I go about doing this?

What's the problem here? Aren't you looping through items returning the my_function's dataframe with to_sql export to database? — Parfait
– Parfait, Commented Jan 22, 2017 at 5:34
Yes, but this creates 3 separate .db files, each with 1 table inside. I want to create 1 .db file, with 3 tables inside. — jake wong
– jake wong, Commented Jan 22, 2017 at 6:26
How do three dbs output here? What are the names of the databases? Obviously can't share the same. How does my_variable_database_name change within loop? Does this posted code match actual? — Parfait
– Parfait, Commented Jan 22, 2017 at 15:36

jake wong · Accepted Answer · 2017-01-22 07:20:03Z

1

I realize what I am doing wrong. Basically I just had to restructure my code. What I had done previously was to put engine in the loop, and set the name to be each_item instead of my specific database.

engine = create_engine("sqlite:///mydatabase.db", echo=False)

for each_item in items:
    connection = engine.connect()
    pandas.DataFrame.to_sql(my_function(each_item), name=each_item, con=engine, if_exists="replace")
    connection.close()
    print(each_item + " has been completed successfully.")
print("All completed.")

answered Jan 22, 2017 at 7:20

jake wong

5,24813 gold badges51 silver badges99 bronze badges

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

2 Comments

Parfait Over a year ago

Also, move connection open and close outside loop as they do not need iteration.

jake wong Over a year ago

Thanks @Parfait. That makes sense

Collectives™ on Stack Overflow

How to create multiple tables in a .db file with pandas / sqlalchemy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related