sqlalchemy creating table and column but not rows

Question

I am struggling with an minor issue that I am trying to create a table into MySQL using Python sqlalchemy, which crates table into the database not did not inserting rows into that. Below is the code. what is the mistake?

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql://root:1234@localhost:3306/paintings")
connection = engine.connect()

df = pd.read_csv("./data/artist.csv")
df.to_sql("artist", con=connection, index=False, if_exists="replace")

can you try connection.commit() after df.to_sql("artist", con=connection, index=False, if_exists="replace")? — python_user
– python_user, Commented Jan 11, 2024 at 2:49
@python_user, brother you are amazing, your suggestion works. But beyond to my curiosity I want to know what does this commit function? — Penguin
– Penguin, Commented Jan 11, 2024 at 17:40
Commit allows you to persist the changes you made (e.g., adding new rows, deleting or modifying existing ones) to the database. This allows you to make changes locally and submit them when you are done. In your code, you create a connection to the db and add new rows to the table. But you have not committed them yet. — Han
– Han, Commented Jan 16, 2024 at 16:10

Han · Accepted Answer · 2024-01-16 16:15:27Z

0

You are trying to push changes to the database, specifically, add new rows to 'artist' table from a csv. In your code, you properly read the csv and create a connection to the database and prepare your database changes, but forgot to persist them. You can do this by calling the commit on the connection. So, it would be

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql://root:1234@localhost:3306/paintings")
df = pd.read_csv("./data/artist.csv")

with engine.connect() as connection:
    df.to_sql("artist", con=connection, index=False, if_exists="replace")
    connection.commit()

The last statement would persist your prepared changes to the database.

answered Jan 16, 2024 at 16:15

Han

3112 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

sqlalchemy creating table and column but not rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related