3

I'm trying to delete some rows before inserting new data in my table (the past 30 days have to be updated daily to get accurate numbers).

I'm executing this: 

from datetime import date, datetime, timedelta  
import pandas as pd from sqlalchemy import create_engine

conn = create_engine("insert here string for connection")
conn.execution_options(autocommit = True) 
start_date = datetime.strftime(date.today() - timedelta(days = 30),'%Y-%m-%d')
end_date = datetime.strftime(date.today(),'%Y-%m-%d')

delete_query = "delete from table where date between %s and %s" %(start_date,end_date)

pd.io.sql.execute(delete_query, conn)

Although I get no error, I can see in the database that the table is just always being duplicated. Any ideas?

mpopa
  • 31
  • 1
  • 2

2 Answers2

1

We just need to add instance.autocommit=True, that will look like: cnxn.autocommit=True

Ramana Ram R
  • 11
  • 1
0

Try setting autoflush and expire_on_commit to False:

conn.execution_options(autocommit=True, autoflush=False, expire_on_commit=False)

In the absence of a demarcated transaction, the Session cannot make appropriate decisions as to when autoflush should occur nor when auto-expiration should occur, so these features should be disabled with autoflush=False, expire_on_commit=False.

For additional details: http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html?highlight=autocommit#autocommit-mode

Alexander
  • 105,104
  • 32
  • 201
  • 196
  • I just finished trying it, same result, unfortunately. I'm also open to workarounds. I tried actually using this in a transaction, but I got back that the connection does not have a "commit" method. Basically I tried something like this: `db = my_connection db.begin() conn = db.connect() conn.execute(delete_query) db.commit() conn.close()` – mpopa Sep 03 '15 at 19:23
  • in this particular case, I'm using only sqlalchemy to create the engine and then working on that. I am using the same connection, but formatted for psycopg2, for other methods throughout the code. might that affect it in some way? although I'm pretty sure in this case it's clear enough that it's using only the sqlalchemy create_engine method – mpopa Sep 03 '15 at 19:30
  • Have a look at this link: http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#autocommit-mode – Alexander Sep 04 '15 at 00:08
  • I found the bug :) had to sleep on it, I guess. Apparently there were missing quotes around %s in the delete query. so this was the output if I was printing it. `delete from table where date between 2015-08-03 and 2015-09-03` and it should be like so: `delete from table where date between '2015-08-03' and '2015-09-03'` such a silly mistake. thanks for the help anyway! – mpopa Sep 04 '15 at 09:32