3

How to execute the following raw query using SQLAlchemy?

SELECT *
INTO <table-1>
FROM <table-2>
WHERE condition=<1>

I am working with Microsoft SQL server. And when I run this 

import sqlalchemy as sqla

engine_string = "mssql://%s/%s?driver=%s" % (server_name, database_name, driver)
con = sqla.create_engine(engine_string)
query_str = <above-query>
con.execute(query)

It executes without any errors, but there is no table created. What's happening here?

Isma
  • 14,604
  • 5
  • 37
  • 51
TrigonaMinima
  • 1,828
  • 1
  • 23
  • 35
  • 1
    You want to create a table? That SQL is a selection query. You might want to look into the CREATE TABLE statement. – Isma Nov 27 '17 at 12:42
  • Have you tried designating the schema as shown in the answer to this question: https://stackoverflow.com/questions/18254104/how-do-i-create-a-table-based-on-another-table – brddawg Nov 27 '17 at 13:54
  • @Isma The query I have written works on the MS SQL Server. It pushes the results of select query into the table mentioned. – TrigonaMinima Nov 27 '17 at 14:12
  • @brddawg The query runs and creates a table when I run it directly in MS SQL server management studio. What I want is to create it using sqlalchemy. – TrigonaMinima Nov 27 '17 at 14:13
  • 1
    You are right, that should create the table automatically. Maybe this answer will help: https://stackoverflow.com/questions/30575111/how-to-create-a-new-table-from-select-statement-in-sqlalchemy – Isma Nov 27 '17 at 14:22
  • I came across this answer in my search. It seemed too complex to do such a simple thing. Normal select queries work properly through the way I am executing so I was hoping that this would work too. It shouldn't matter if no rows are being returned. – TrigonaMinima Nov 27 '17 at 15:01
  • Does `table-1` name start with # sign (for a temporary table)? – Wagner DosAnjos Nov 28 '17 at 01:36
  • @wdosanjos no. That is an issue for another question I think. I was not even able to run a select query from a temp table through sqlalchemy. That's why I started making a permanent table. – TrigonaMinima Nov 28 '17 at 12:42
  • 2
    The reason your original textual SQL seems to fail is that it never commits. Read [understanding autocommit](http://docs.sqlalchemy.org/en/latest/core/connections.html#understanding-autocommit). Tl;dr: SQLAlchemy inspects the textual SQL statement, sees that it starts with SELECT and deems it non data modifying. And please rename `con` to `engine`, which it is. – Ilja Everilä Nov 29 '17 at 12:36
  • @IljaEverilä that seems to be in the right direction. I'll try it with ```autocommit=False``` and report back. And yeah didn't even notice the ```con``` part. Will change it in my code. :) – TrigonaMinima Nov 29 '17 at 17:31
  • 3
    If you use `Engine.execute()`, then try wrapping your SQL text with `text(query).execution_options(autocommit=True)`, which informs the autocommit machinery that this statement should be committed. – Ilja Everilä Nov 29 '17 at 18:46
  • @IljaEverilä the above method works. Should I delete this question? – TrigonaMinima Nov 30 '17 at 08:04
  • The solution is [here](https://stackoverflow.com/a/65209793/2445273). – LoMaPh Dec 09 '20 at 02:30

0 Answers0