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")

Answer 1

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.