J'ajoute une seule colonne à une table Postgres avec plus de 100 colonnes via Django (une nouvelle migration). Comment mettre à jour une colonne dans une table PostgreSQL avec les données d'un data_frame pandas ? Le pseudo-code pour Postgres SQL UPDATE serait :

UPDATE wide_table wt
SET wt.z = df.z
WHERE date = 'todays_date'

La raison pour laquelle je procède de cette façon est que je calcule une colonne dans le data_frame à l'aide d'un CSV qui se trouve dans S3 (il s'agit de df.z). Les documents pour la mise à jour Postgres sont simples à utiliser, mais je ne sais pas comment le faire via Django, sqlalchemy, pyodbc, ou similaire.

Je m'excuse si c'est un peu alambiqué. Un petit exemple incomplet serait :

Table large (colonne de pré-mise à jour z)

identifier    |      x       |      y      |      z       |      date       
foo           |      2       |      1      |     0.0      |      ...           
bar           |      2       |      8      |     0.0      |      ...      
baz           |      3       |      7      |     0.0      |      ...      
foo           |      2       |      8      |     0.0      |      ...      
foo           |      1       |      5      |     0.0      |      ...      
baz           |      2       |      8      |     0.0      |      ...      
bar           |      9       |      3      |     0.0      |      ...      
baz           |      2       |      3      |     0.0      |      ...      

Exemple d'extrait Python

def apply_function(identifier):
    # Maps baz-> 15.0, bar-> 19.6, foo -> 10.0 for single date
    df = pd.read_csv("s3_file_path/date_file_name.csv")
    # Compute 'z' based on identifier and S3 csv
    return z

postgres_query = "Select identifier from wide_table"
df = pd.read_sql(sql=postgres_query, con=engine)
df['z'] = df.identifier.apply(apply_function)

# Python / SQL Update Logic here to update Postgres Column
???

Table large (colonne post-mise à jour z)

identifier    |      x       |      y      |      z        |      date 
foo           |      2       |      1      |     10.0      |      ...     
bar           |      2       |      8      |     19.6      |      ... 
baz           |      3       |      7      |     15.0      |      ... 
foo           |      2       |      8      |     10.0      |      ... 
foo           |      1       |      5      |     10.0      |      ... 
baz           |      2       |      8      |     15.0      |      ... 
bar           |      9       |      3      |     19.6      |      ... 
baz           |      2       |      3      |     15.0      |      ... 

REMARQUE : les valeurs de z changeront quotidiennement, donc créer simplement une autre table pour contenir ces valeurs z n'est pas une bonne solution. De plus, je préférerais vraiment éviter de supprimer toutes les données et de les rajouter.

5
Scott Skiles 7 mars 2019 à 23:35

2 réponses

Meilleure réponse

J'ai rencontré un problème similaire et la solution actuellement acceptée était trop lente pour moi. Ma table avait plus de 500 000 lignes et je devais mettre à jour plus de 100 000 lignes. Après de longues recherches et essais et erreurs, je suis arrivé à une solution efficace et correcte.

L'idée est d'utiliser psycopg comme rédacteur et d'utiliser une table temporaire. df est votre cadre de données pandas qui contient les valeurs que vous souhaitez définir.

import psycopg2

conn = psycopg2.connect("dbname='db' user='user' host='localhost' password='test'")
cur = conn.cursor()

rows = zip(df.id, df.z)
cur.execute("""CREATE TEMP TABLE codelist(id INTEGER, z INTEGER) ON COMMIT DROP""")
cur.executemany("""INSERT INTO codelist (id, z) VALUES(%s, %s)""", rows)

cur.execute("""
    UPDATE table_name
    SET z = codelist.z
    FROM codelist
    WHERE codelist.id = vehicle.id;
    """)

cur.rowcount
conn.commit()
cur.close()
conn.close()
5
olive_tree 3 juil. 2019 à 06:28

J'ai réussi à concocter moi-même une solution dans laquelle je compresse les valeurs id et z, puis j'exécute une instruction SQL générique UPDATE et utilise SQL UPDATE FROM VALUES.

Préparation des données

sql_query= "SELECT id, a FROM wide_table"
df = pd.read_sql(sql=sql_query, con=engine)
df['z'] = df.a.apply(apply_function)

zipped_vals = zip(df.id, df.z)
tuple_to_str= str(tuple(zipped_vals))
entries_to_update = tuple_to_str[1:len(tuple_to_str)-1] # remove first and last paren in tuple

Solution de requête SQL :

# Update column z by matching ID from SQL Table & Pandas DataFrame
update_sql_query = f"""UPDATE wide_table t SET z = v.z
                        FROM (VALUES {entries_to_update}) AS v (id, z)
                        WHERE t.id = v.id;"""

with engine.begin() as conn:
    conn.execute(update_sql_query)

conn.exec(sql_query)

Réponse sur la mise à jour de la colonne de la table PostgreSQL à partir des valeurs

Documents de mise à jour PostgreSQL

1
Scott Skiles 27 mars 2019 à 15:15