I feel bad bugging you with questions everyday, but it seems that I get
them answered here... Hope I am not abusing your hospitality.
I would like to delete multiple rows from a table on a MS SQL Server. The
rows to be deleted are given in a `pandas.DataFrame` object and there can
be thousands or even more of them.
The way I tried to tackle the problem is by using the query construction
methods and then passing it all to connection.execute(). My code looks
something like this:
def delete_rows(self, tablename, data):
"""
Args:
tablename - name of db table, e.g. '[schema].[name]'
data - pandas.DataFrame
"""
# Get column names
colnames = data.columns.values
# Create sa.column() objects for producing bindparams
cols = [sa.column(name) for name in data.columns.values]
# Build a DELETE query
query = sa.table(tablename).delete().\
where(sa.and_(*[c == sa.bindparam(cn) for c, cn in
zip(cols,colnames)]))
# Execute query
self.connection.execute(query, data.to_dict(orient='records'))
When running with inputs, that are seen in the echos of the engine, I get
an error:
....
INFO sqlalchemy.engine.base.Engine DELETE FROM some.table WHERE col1 =
? AND col2 = ? AND col3 = ?
INFO sqlalchemy.engine.base.Engine ((123, Timestamp('2012-05-22
00:00:00'), 123, 123), (456, Timestamp('2012-01-27 00:00:00'), 456, 456))
INFO sqlalchemy.engine.base.Engine ROLLBACK
....
pyodbc.ProgrammingError: ('Expected 0 parameters, supplied 4', 'HY000')
Could this be related to the way the dates are stored in the `DataFrame`?
I am using pandas-0.20.3, pyODBC-4.0.21 and SQLAlchemy-1.1.13.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
