Panda dataframe update existing table with to_sql

Hi

I am new to Python and is trying to make my first python application. But I have some problem with panda.

How do I update an existing table with panda dataframe with out getting duplicate errors saying key already exists. Is it possible to skip record that already exists or what is best practice?

The only solution I can come up with is to write the data to a buffer table and then make an insert from that table for new records. But I think that this solution is very inefficient.

It would be much easier to understand the problem if you could show a simple example of what you are trying, and a copied and pasted error. (In particular, your title mentions .to_sql, but I can’t see how it relates to the task you’re describing here - perhaps because “update” is too vague of a word to explain what you actually want to do.) Please read the pinned thread to understand the forum’s formatting tools, and format both code and error messages in separate code blocks.

def get_sma(df,MA):
    df['value'] = df['value'].rolling(int(MA)).mean()
    return df

db = SessionLocal()
query = 'select stock.id, indicator.code from stock,indicator where stock.blocked=false order by stock.id,indicator.code'
stocks = engine.execute(query)
for stock in stocks:
    query = 'select stock_id, \'' + stock.code + '\' as indicator_code, date, close as value from stock_exchange_rate where stock_id=' + format(stock.id)
    df = pd.read_sql(query, engine)
    match stock.code[:3]:
        case 'SMA':
            get_sma(df,200)
            df.to_sql(name='stock_indicator_value', con=engine, if_exists='append', index=False)
        case 'EMA':
            print('EMA ' + stock.code)
        case 'MAC':
            print('MAC ' + stock.code)
        case '_':
            print('indicator ' + stock.code + ' error')

it gives me this error.

EMA EMA10
EMA EMA150
EMA EMA5
MAC MACD
MAC MACDSignal
SMA SMA200
Traceback (most recent call last):
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 77, in _compiler_dispatch
    meth = getter(visitor)
AttributeError: 'PGCompiler_psycopg2' object has no attribute 'visit_on_duplicate_key_update'. Did you mean: 'visit_on_conflict_do_update'?

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 1322, in insert_records
    return table.insert(chunksize=chunksize, method=method)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 950, in insert
    num_inserted = exec_insert(conn, keys, chunk_iter)
  File "/Users/jma/Documents/Python/FastAPI/stockscreen/technical_analysis.py", line 26, in insert_on_duplicate
    conn.execute(on_duplicate_key_stmt)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1306, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1490, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 536, in _compile_w_cache
    compiled_sql = self._compiler(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 558, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 776, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 451, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 486, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 3937, in visit_insert
    post_values_clause = self.process(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 486, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 79, in _compiler_dispatch
    return visitor.visit_unsupported_compilation(self, err, **kw)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 470, in visit_unsupported_compilation
    util.raise_(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x140a65ab0> can't render element of type <class 'sqlalchemy.dialects.mysql.dml.OnDuplicateClause'> (Background on this error at: https://sqlalche.me/e/14/l7de)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/jma/Documents/Python/FastAPI/stockscreen/technical_analysis.py", line 44, in <module>
    df.to_sql(name='stock_indicator_value', con=engine, if_exists='append', index=False, chunksize=4096, method=insert_on_duplicate)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/core/generic.py", line 2951, in to_sql
    return sql.to_sql(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 1739, in to_sql
    total_inserted = sql_engine.insert_records(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 1328, in insert_records
    err_text = str(err.orig)
AttributeError: 'UnsupportedCompilationError' object has no attribute 'orig'
jma@MBPtilhrendeJan stockscreen % python technical_analysis.py
EMA EMA10
EMA EMA150
EMA EMA5
MAC MACD
MAC MACDSignal
SMA SMA200
Traceback (most recent call last):
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_executemany(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 953, in do_executemany
    context._psycopg2_fetched_rows = xtras.execute_values(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/psycopg2/extras.py", line 1270, in execute_values
    cur.execute(b''.join(parts))
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "stock_indicator_value_pkey"
DETAIL:  Key (stock_id, indicator_code, date)=(1, SMA200, 2012-06-18) already exists.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/jma/Documents/Python/FastAPI/stockscreen/technical_analysis.py", line 43, in <module>
    df.to_sql(name='stock_indicator_value', con=engine, if_exists='append', index=False)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/core/generic.py", line 2951, in to_sql
    return sql.to_sql(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 1739, in to_sql
    total_inserted = sql_engine.insert_records(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 1332, in insert_records
    raise err
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 1322, in insert_records
    return table.insert(chunksize=chunksize, method=method)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 950, in insert
    num_inserted = exec_insert(conn, keys, chunk_iter)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/pandas/io/sql.py", line 857, in _execute_insert
    result = conn.execute(self.table.insert(), data)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1306, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_executemany(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 953, in do_executemany
    context._psycopg2_fetched_rows = xtras.execute_values(
  File "/Users/jma/.pyenv/versions/3.10.4/lib/python3.10/site-packages/psycopg2/extras.py", line 1270, in execute_values
    cur.execute(b''.join(parts))
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "stock_indicator_value_pkey"
DETAIL:  Key (stock_id, indicator_code, date)=(1, SMA200, 2012-06-18) already exists.

[SQL: INSERT INTO stock_indicator_value (stock_id, indicator_code, date, value) VALUES (%(stock_id)s, %(indicator_code)s, %(date)s, %(value)s)]
[parameters: ({'stock_id': 1, 'indicator_code': 'SMA200', 'date': datetime.date(2012, 6, 18), 'value': None}, {'stock_id': 1, 'indicator_code': 'SMA200', 'date': datetime.date(2012, 6, 19), 'value': None}, {'stock_id': 1, 'indicator_code': 'SMA200', 'date': datetime.date(2012, 6, 20), 'value': None}, {'stock_id': 1, 'indicator_code': 'SMA200', 'date': datetime.date(2012, 6, 21), 'value': None}, {'stock_id': 1, 'indicator_code': 'SMA200', 'date': datetime.date(2012, 6, 22), 'value': None}, {'stock_id': 1, 'indicator_code': 'SMA200', 'date': datetime.date(2012, 6, 25), 'value': None}, {'stock_id': 1, 'indicator_code': 'SMA200', 'date': datetime.date(2012, 6, 26), 'value': None}, {'stock_id': 1, 'indicator_code': 'SMA200', 'date': datetime.date(2012, 6, 27), 'value': None}  ... displaying 10 of 3004 total bound parameter sets ...  {'stock_id': 1, 'indicator_code': 'SMA200', 'date': datetime.date(2024, 5, 23), 'value': 2.8596}, {'stock_id': 1, 'indicator_code': 'SMA200', 'date': datetime.date(2024, 5, 24), 'value': 2.85875})]

The error is very descriptive

Your table has a uniqueness constraint, meaning you can’t use if_exists='append'. If you are ok with overwriting the original table, use if_exists='replace'. If you aren’t ok with that, you will need to rethink your solution.

But also, to_sql writes the entire table, which isn’t exactly what you want I think.

Or with other words: You can’t use to_sql, you would need to manually build your update sql statements.

Thanks, no I do not want to write existing records, only new.

I have tried that also, but do not know how to loop through the data frame and insert data.

Is there a good guide somewhere? I havn’t found any.