I sorted this problem out and, as you predicted, it turned out to be layers
upon layers of problems. If
you do not mind having a quick look at how I resolved this, I would really
appreciate some feedback
and a second opinion...
I actually missed the main problem initially. It had to do with the name of
the table being quoted
incorrectly, which in turn was due to incorrect inputs on my part. In my
actual code the variable of the
table name was *tablename = '[some].[long].[name]'*, which was translated
to a DELETE query with
additional quotes: *'[[some].[long].[name]]'*. I saw then that I had to
provide the schema and the table
names separately - and ASAP read much more about databases and MS SQL
syntax.
I then very lazily tried to add these two to the initiation call of the
table object. Using your pseudocode
and the names from the previous paragraph:
* t = table('name', *[column(name) for name in names],
schema='some.long') query = t.delete().where(and_(table.c[n] =
bindparam(n) for n in names)) *
This resulted in a *TypeError: table() got an unexpected keyword argument
'schema'. *I then made my
peace with the fact that at the moment the depth of my database knowledge
is only sufficient for
building queries with strings and for loops, but here it will not be good
enough. :)
I finally made a call to let the module take care of as much as possible
and everything worked. In the
end I reflected the table from the server and then constructed the query
using that object:
* meta = sa.MetaData(bind=self.engine, schema=schema_name)*
* reflected_table = sa.Table(table_name, meta, autoload=True)*
* # Get column names from the input DataFrame - data*
* colnames = data.columns.values*
* # Build a DELETE query with the SQLAlchemy builder*
* query = reflected_table.delete().\*
* where(sa.and_(reflected_table.c[cname] == sa.bindparam(cname) for
cname in colnames))*
* self.connection.execute(query, data.to_dict(orient='records'))*
With my limited knowledge of everything, it seems that there are several
benefits here. First of
all, I can further optimize my query by looking for the natural key in the
reflected table and then
simply use it in the WHERE conditions. Also, am I right to think that this
way of working provides
a consistency check of the column names in the table and the DataFrame?
This might be a good
indicator for people using this method when they are doing something wrong.
This was a long one, so I will not hold it against you if you did not make
it this far. :) If you are
reading this and still open to give me some feedback, thanks a lot for your
help and for a cool
module that you have built here. I came to Python and database tasks a few
weeks ago and
am still having fun (in some peculiar way) with getting the hang of it.
Keep up the good work!
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
