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?
Since SQLAlchemy is integrated with Pandas, we can use its SQL connection directly with “con = conn”.
Step 1 - Install the Flask-SQLAlchemy extension. Step 2 - You need to import the SQLAlchemy class from this module. Step 3 - Now create a Flask application object and set the URI for the database to use. Step 4 - then use the application object as a parameter to create an object of class SQLAlchemy.
To do this, we need to open our terminal to our working directory and follow this process: Initialize the Python interpreter. Next, import the database variable from the __init__.py file where we initialized the SQLAlchemy instance of the object, then create the database.
Definition of SQLAlchemy create_all. Sqlalchemy create_all method is used to create a new table into the database. This method will first check whether the table exists in the database or not if suppose it has found an existing table it will not create any table.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With