Pandas df to database using flask-sqlalchemy

Question

I'm trying to insert a pandas dataframe into a mysql database. I am using flask-sqlalchemy.

I have created this table:

class Client_Details(db.Model):
    __tablename__ = "client_history"

    client_id = db.Column(db.Integer, primary_key=True)
    client_name = db.Column(db.VARCHAR(50))
    shack= db.Column(db.VARCHAR(50))

and I would like to insert the data from this df into it:

index   name     shack
0        jay       H9
1        ray       I8
2        t-bop     I6
3        jay-k     F89
4        phil      D89

This doesn't seem to work:

for index, row in df.iterrows():
    client_add = client_history(client_name = row[1], shack =row[2])
    db.session.add(client_add)
    db.session.commit()

Is there a better way to do this, using to_sql, perhaps?

df.to_sql() will do the trick, although I find it to be very slow for large tables. — VinceP
– VinceP, Commented Apr 23, 2017 at 16:43

ZaxR · Accepted Answer · 2017-06-06 20:28:07Z

15

Kyle's answer was close - the flask-sqlalchemy engine IS created behind the scenes with some magic, but the correct way to access it is with db.engine. Here's what you're looking for:

df.to_sql(name='client_history', con=db.engine, index=False)

I also agree with VinceP's assessment that to_sql can be slow for larger tables, so keep that in mind.

For what it's worth, you can also access the session with Flask-SQLAlchemy as db.session.

edited Jun 6, 2017 at 20:28

answered Jun 6, 2017 at 20:22

ZaxR

5,1954 gold badges29 silver badges46 bronze badges

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

Comments

Kyle Stuart · Accepted Answer · 2017-04-23 21:49:40Z

2

df.to_sql(engine, 'client_history')

Is what you are looking for. There is no lighting fast way to do this (that I am aware of) but typically using builtin pandas functions is going to be faster than any solution that you can create on your own.

answered Apr 23, 2017 at 21:49

Kyle Stuart

1115 bronze badges

2 Comments

warrenfitzhenry Over a year ago

thanks. sqlalchemy doesn't use the engine, how does one do this with flask?

app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI app.config["SQLALCHEMY_POOL_RECYCLE"] = 299 app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER  db = SQLAlchemy(app)

Kyle Stuart Over a year ago

SQLAlchemy does use the engine. I am not 100% sure about how to get the engine with sqlalchemy + flask but I would guess that you can access it through your db object if that is not an instance of the engine already.

Collectives™ on Stack Overflow

Pandas df to database using flask-sqlalchemy

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related