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:

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?

CodePudding user response：

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

CodePudding user response：

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