我的数据是这样的:
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)
数据库表:
这是一个例子。通常会有更多行要更新。
我想为 ID 为 1、2、3 的行更新列“城市”和“国家/地区”。
一次更新数据库表的代码是什么?
最佳答案
如果您将使用 pandas 写入数据库,那么您会发现使用 SQLAlchemy 而不是原始 DBAPI 连接是有利的。在这种情况下:
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)]
"""
https://stackoverflow.com/questions/70435792/