On Wed, Jan 3, 2018 at 11:21 AM, Jevgenij Kusakovskij <kush...@gmail.com> wrote:
> 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:
I think the first problem is you can't create a column() on the fly
like that and use it in the delete() statement, these Column objects
must be in terms of the Table you are operating upon, so
t = table(tablename, *[column(name) for name in names])
# pseudocode, not quite right, the idea is the column comes from
table.c.<colname>
query = t.delete().where(and_(table.c[name] = bindparam(name) for name
in names))
the next problem is that this is an odd way to DELETE rows, by naming
every single column in the row in the WHERE clause. The data you
have here usually should have some kind of natural key so you can
identify the rows more accurately and efficiently on just those
columns that identify the row.
that should get you a bit further at least.
>
> ....
> 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.
--
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.
