read_sql_query - python read_sql parameters

Pandas actualización sql

Question

¿Hay alguna manera de hacer una actualización de SQL, donde desde un marco de datos sin iterar a través de cada línea? Tengo una base de datos postgresql y para actualizar una tabla en el DB desde un marco de datos, usaría psycopg2 y haré algo como:

con = psycopg2.connect(database=''mydb'', user=''abc'', password=''xyz'')
cur = con.cursor()

for index, row in df.iterrows():
    sql = ''update table set column = %s where column = %s''
    cur.execute(sql, (row[''whatver''], row[''something'']))
con.commit()

Pero, por otro lado, si estoy leyendo una tabla desde sql o escribiendo una dataframe completa en sql (sin actualización-where), entonces simplemente usaría pandas y sqlalchemy. Algo como:

engine = create_engine(''postgresql+psycopg2://user:pswd@mydb'')
df.to_sql(''table'', engine, if_exists=''append'')

Es genial tener un ''one-liner'' usando to_sql. ¿No hay algo similar para hacer una actualización, desde pandas a postgresql? O es la única manera de hacerlo iterando a través de cada fila como lo hice anteriormente. ¿No es iterativo a través de cada fila una forma ineficiente de hacerlo?

Answer 1

Considere una tabla temporal que sería la réplica exacta de su mesa final, limpiada con cada ejecución:

engine = create_engine(''postgresql+psycopg2://user:pswd@mydb'')
df.to_sql(''temp_table'', engine, if_exists=''replace'')

sql = """
    UPDATE final_table AS f
    SET col1 = t.col1
    FROM temp_table AS t
    WHERE f.id = t.id
"""

with engine.begin() as conn:     # TRANSACTION
    conn.execute(sql)

Answer 2

Hasta ahora no he visto ningún caso en el que el conector pandas sql se pueda usar de cualquier forma escalable para actualizar los datos de la base de datos. Puede haber parecido una buena idea construir uno, pero en realidad, para el trabajo operativo simplemente no escala.

Lo que recomendaría es volcar su marco de datos completo como CSV usando

df.to_csv(''filename.csv'', encoding=''utf-8'')

Luego carga el archivo CSV en la base de datos usando COPY para PostgreSQL o LOAD DATA INFILE para MySQL.

Si no realiza otros cambios en la tabla en cuestión mientras los pandas manipulan los datos, puede cargarlos en la tabla.

Si hay problemas de concurrencia, deberá cargar los datos en una tabla de etapas que luego utilizará para actualizar su tabla principal.

En el caso posterior, su tabla principal debe tener una fecha y hora que le indique cuándo fue la última modificación para poder determinar si los cambios de su panda son los más recientes o si los cambios en la base de datos deben permanecer.

Answer 3

Me preguntaba por qué no actualiza el df basado en su ecuación y luego almacena el df en la base de datos, puede usar if_exists = ''replace'', para almacenar en la misma tabla.

Answer 4

Parece que está utilizando algunos datos externos almacenados en df para conocer las condiciones de actualización de la tabla de su base de datos. Si es posible, ¿por qué no simplemente hacer una actualización sql de una línea?

Si está trabajando con una base de datos más bien pequeña (en la que cargar los datos completos al objeto de marco de datos de python no lo matará), entonces definitivamente puede actualizar el marco de datos de manera condicional después de cargarlo usando read_sql . Luego puede usar una palabra clave arg if_exists="replace" para reemplazar la tabla DB con la nueva tabla actualizada.

df = pandas.read_sql("select * from your_table;", engine)

#update information (update your_table set column = "new value" where column = "old value")
#still may need to iterate for many old value/new value pairs
df[df[''column''] == "old value", "column"] = "new value"

#send data back to sql
df.to_sql("your_table", engine, if_exists="replace")

Pandas es una herramienta poderosa, donde el soporte limitado de SQL era solo una pequeña característica al principio. A medida que pasa el tiempo, las personas intentan usar pandas como su único software de interfaz de base de datos. No creo que pandas haya tenido la intención de ser un fin para la interacción con la base de datos, pero hay muchas personas trabajando en nuevas funciones todo el tiempo. Ver: https://github.com/pandas-dev/pandas/issues