Mike Richards <justanotherb...@gmail.com> wrote:
> When attempting to create a SQL Server view the method located on
> StackOverflow does not work. Am I missing something?
>
> I've tried enabling autocommit in _execution_options to no avail. Beyond that
> I'm unsure what to try next.
>
> Thank you very much for your time and help,
you should look at your SQL output. If the DDL emitted is correct, then it is
just a matter of it being committed.
If you try running like this:
with engine.begin() as connection:
connection.execute(cmd)
you should see CREATE VIEW in your SQL output as well as a COMMIT following it.
> Mike Richards
>
> Details:
> sqlalchemy: 0.9.8
> python: 2.7.6
> pyodbc: 3.0.6
> MS SQL Server Enterprise 2014 64 bit
>
> Code:
>
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.sql.expression import ClauseElement
> from sqlalchemy.sql.expression import Executable
>
> from print_counts.orm import mssql_engine as engine
> from print_counts.orm import get_session
> from print_counts.queries import monthly_print_counts
>
>
> class CreateView(Executable, ClauseElement):
> """
> Copied from stackoverflow site.
>
> Seems to match docs at
> http://docs.sqlalchemy.org/en/rel_0_9/core/compiler.html
> """
> def __init__(self, name, select):
> self.name = name
> self.select = select
>
>
> @compiles(CreateView)
> def visit_create_view(element, compiler, **kwargs):
> """
> Copied from stackoverflow site.
>
> Seems to match docs at
> http://docs.sqlalchemy.org/en/rel_0_9/core/compiler.html
> """
> result = "CREATE VIEW %s AS %s" % (
> element.name,
> compiler.process(element.select, literal_binds=True)
> )
> return result
>
>
> def CreateView_to_SQL(createview, engine):
> "Workaround hack."
> select_sql = createview.select.compile(bind=engine)
> return "CREATE VIEW %s AS %s" % (createview.name, select_sql)
>
>
> if __name__ == '__main__':
> name = 'dbo.monthly_print_counts'
> # returns a query created via session.query; Happy to supply code for this
> # Query created with four declarative ORM models, and includes several
> subqueries.
> # all related tables are in the same database the view would be created in.
> query = monthly_print_counts(get_session())
> create_view_command = CreateView(name, query.selectable)
>
> # this is the statement I'd like help with making work
> # it executes visit_create_view, but does not actually create the table
> # No errors or debug messages are printed
> engine.execute(create_view_command)
>
> # the current workaround. Creates the view, which behaves as expected
> sql = CreateView_to_SQL(create_view_command, engine)
> engine.execute(sql)
>
>
> --
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
