pandas mysql How to update some columns of rows using a Dataframe

Question

My data is in the form:

list_data = [{'id': '1', 'city': 'Tokyo', 'country': 'Japan'},
             {'id': '2', 'city': 'Noida', 'country': 'India'},
             {'id': '3', 'city': 'Seoul', 'country': 'South korea'}]

df_data = pd.Dataframe(list_data)

Database Table:

id	colour	code
1	white	125
2	red	48
3	pink	56
4	yellow	456
5	white	213

This is an example. Usually there would be more rows to be updated.
I want to update column 'city' and 'country' for rows with id 1, 2, 3.

What would be the code to update the database table at once?

@Zaki could you elaborate, please? I am new to this so not very familiar with some terms. — nifeco
– nifeco, Commented Dec 21, 2021 at 12:57

Gord Thompson · Accepted Answer · 2021-12-21 15:20:16Z

If you will be working with pandas to write to a database then you will find it advantageous to use SQLAlchemy instead of a raw DBAPI connection. In this case:

from pprint import pprint

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine("mysql+mysqldb://scott:tiger@localhost:3307/mydb")

# create the test environment
#
with engine.begin() as conn:
    conn.exec_driver_sql("DROP TABLE IF EXISTS table1")
    conn.exec_driver_sql(
        """
        CREATE TABLE table1 (
        id int primary key,
        colour varchar(50),
        city varchar(50),
        code varchar(50),
        country varchar(50)
        )
        """
    )
    conn.exec_driver_sql(
        """
        INSERT INTO table1 (id, colour, code) VALUES 
        (1, 'white', '125'),
        (2, 'red', '48'),
        (3, 'pink', '56'),
        (4, 'yellow', '456'),
        (5, 'white', '213')
        """
    )

list_data = [
    {"id": "1", "city": "Tokyo", "country": "Japan"},
    {"id": "2", "city": "Noida", "country": "India"},
    {"id": "3", "city": "Seoul", "country": "South korea"},
]
df_data = pd.DataFrame(list_data)

# run the test
#
with engine.begin() as conn:
    sql = """
    UPDATE table1 SET city = :city, country = :country
    WHERE id = :id
    """
    params = df_data.to_dict("records")
    conn.execute(sa.text(sql), params)

    pprint(conn.exec_driver_sql("SELECT * FROM table1").fetchall())
    """
    [(1, 'white', 'Tokyo', '125', 'Japan'),
     (2, 'red', 'Noida', '48', 'India'),
     (3, 'pink', 'Seoul', '56', 'South korea'),
     (4, 'yellow', None, '456', None),
     (5, 'white', None, '213', None)]
    """

Barbaros Özhan · Accepted Answer · 2021-12-21 14:55:00Z

You can use a prepared statement along with executemany method such as

import pandas as pd
import mysql.connector
from mysql.connector import Error

con = mysql.connector.connect(host='localhost',
                              database='mydbname',
                              user='myschema',
                              password='mypwd')

cur = con.cursor()

list_data = [{'id': '1', 'city': 'Tokyo', 'country': 'Japan'},
{'id': '2', 'city': 'Noida', 'country': 'India'},
{'id': '3', 'city': 'Seoul', 'country': 'South korea'}]

df_data = pd.DataFrame(list_data)
val = df_data[["city","country","id"]].values.tolist()

qry = "UPDATE tab SET city = %s, country = %s WHERE id = %s"
cur.executemany(qry, val)
con.commit()

Collectives™ on Stack Overflow

pandas mysql How to update some columns of rows using a Dataframe

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related