REPLACE rows in mysql database table with pandas DataFrame

Question

Python version - 2.7.6

Pandas Version - 0.17.1

MySQLdb Version - 1.2.5

In my database ( PRODUCT) I have a table ( XML_FEED). The XML_FEED table is huge (millions of records) I have pandas.DataFrame () ( PROCESSED_DF). A dataframe has thousands of rows.

Now I need to run this

REPLACE INTO TABLE PRODUCT.XML_FEED
(COL1, COL2, COL3, COL4, COL5),
VALUES (PROCESSED_DF.values)

Question: -

Is there any way to run REPLACE INTO TABLEin pandas? I already checked pandas.DataFrame.to_sql(), but this is not what I need. I do not prefer to read the table XML_FEEDin pandas, because it is very huge.

Answer 1

(0.17.1) pandas. . MySQLdb, DataFrame.to_sql(if_exists='append')

,

1) product_id - PRODUCT

2) feed_id - XML_FEED.

import MySQLdb
import sqlalchemy
import pandas

con = MySQLdb.connect('localhost','root','my_password', 'database_name')
con_str = 'mysql+mysqldb://root:my_password@localhost/database_name'
engine = sqlalchemy.create_engine(con_str) #because I am using mysql
df = pandas.read_sql('SELECT * from PRODUCT', con=engine)
df_product_id = df['product_id']
product_id_str = (str(list(df_product_id.values))).strip('[]')
delete_str = 'DELETE FROM XML_FEED WHERE feed_id IN ({0})'.format(product_id_str)
cur = con.cursor()
cur.execute(delete_str)
con.commit()
df.to_sql('XML_FEED', if_exists='append', con=engine)# you can use flavor='mysql' if you do not want to create sqlalchemy engine but it is depreciated

: REPLACE [INTO] INSERT , , UNIQUE KEY ( PRIMARY KEY) INSERT, , .

Answer 2

, - , . , MySQL ( ), / REPLACE INTO df.to_sql().

, delete MySQL pandas, MySQL.

def to_sql_update(df, engine, schema, table):
    df.reset_index(inplace=True)
    sql = ''' SELECT column_name from information_schema.columns
              WHERE table_schema = '{schema}' AND table_name = '{table}' AND
                    COLUMN_KEY = 'PRI';
          '''.format(schema=schema, table=table)
    id_cols = [x[0] for x in engine.execute(sql).fetchall()]
    id_vals = [df[col_name].tolist() for col_name in id_cols]
    sql = ''' DELETE FROM {schema}.{table} WHERE 0 '''.format(schema=schema, table=table)
    for row in zip(*id_vals):
        sql_row = ' AND '.join([''' {}='{}' '''.format(n, v) for n, v in zip(id_cols, row)])
        sql += ' OR ({}) '.format(sql_row)
    engine.execute(sql)

    df.to_sql(name, engine, schema=schema, if_exists='append', index=False)

Answer 3

pandas 0.24.0 , to_sql.

REPLACE INTO, to_sql:

def mysql_replace_into(table, conn, keys, data_iter):
    from sqlalchemy.dialects.mysql import insert
    from sqlalchemy.ext.compiler import compiles
    from sqlalchemy.sql.expression import Insert

    @compiles(Insert)
    def replace_string(insert, compiler, **kw):
        s = compiler.visit_insert(insert, **kw)
        s = s.replace("INSERT INTO", "REPLACE INTO")
        return s

    data = [dict(zip(keys, row)) for row in data_iter]

    conn.execute(table.table.insert(replace_string=""), data)

:

df.to_sql(db, if_exists='append', method=mysql_replace_into)

, INSERT... ON DUPLICATE KEY UPDATE... , :

def mysql_replace_into(table, conn, keys, data_iter):
    from sqlalchemy.dialects.mysql import insert

    data = [dict(zip(keys, row)) for row in data_iter]

    stmt = insert(table.table).values(data)
    update_stmt = stmt.on_duplicate_key_update(**dict(zip(stmt.inserted.keys(), 
                                               stmt.inserted.values())))

    conn.execute(update_stmt)

fooobar.com/questions/215397/... .

Answer 4

to_sql, , , , 'mydb' dataframe 'df' :

df.to_sql(mydb,if_exists='replace')

, , 100%, , .