0

I want to use SQLAlchemy to create a view in my PostgreSQL database. I'm using the CreateView compiler from sqlalchemy-views. I'm using the answer to this question as a reference:

How to create an SQL View with SQLAlchemy?

My code for creating the view looks like this:

def create_view(self, myparameter):
    mytable = Table('mytable', metadata, autoload=True)
    myview = Table('myview', metadata)
    engine.execute(CreateView(myview, mytable.select().where(mytable.c.mycolumn==myparameter)))

However, when I attempt to run this query, the following exception is thrown:

KeyError: 'mycolumn_1'

Looking at the compiled query, it seems that a placeholder for my parameter value is not being replaced:

'\nCREATE VIEW myview AS SELECT mytable.mycolumn \nFROM mytable \nWHERE mytable.mycolumn = %(mycolumn_1)s\n\n'

Since the placeholder is not being replaced, the query obviously fails. However, I do not understand why the replacement does not happen, since my code does not differ much from the example.

My first suspicion was that maybe the type of the parameter and the column were incompatible. Currently, the parameter comes in as a unicode string, which should be mapped to a text column in my database. I have also tried mapping the parameter as a long to a bigint column with the same (failed) result.

Does anyone have another suggestion?

thijsfranck
  • 778
  • 1
  • 10
  • 24
  • First, include the full traceback. The KeyError would indicate that for some reason the parameters are not passed along correctly and so it fails when it tries to replace the placeholder. Second, please don't paste your code verbatim, but create a [mcve] that others can then work on as well. – Ilja Everilä Dec 13 '17 at 11:42

1 Answers1

0

From the SQLAlchemy documentation, I can see that when one wants to pass the actual value that will be ultimately used at expression time, the bindparam() is used. A nice example is also provided:

from sqlalchemy import bindparam

stmt = select([users_table]).\
            where(users_table.c.name == bindparam('username'))
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
kingJulian
  • 5,601
  • 5
  • 17
  • 30
  • `mytable.c.mycolumn==myparameter` should actually create a bind param automatically, but is failing for some reason. The example you've referred to is good for deferring providing the actual value. – Ilja Everilä Dec 13 '17 at 11:43