1

I'm trying to execute TSQL queries in a remote MSSQL database by using SQLAlchemy and pymssql. I've tested my procedural query directly in the database and it works as intended, it also works if I run it directly through pymssql. If I run a regular one liner queries such as:

select table_name from INFORMATION_SCHEMA.tables

Through SQLAlchemy this also works as it should. But when I try to execute the following TSQL query it does not actually create the table: 

IF NOT EXISTS (SELECT * 
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'SOME_TABLE')
BEGIN
   CREATE TABLE SOME_TABLE (SOME_TEXT VARCHAR(255), SOME_TIME DATETIME)
END

it runs it as it was successful and if I try to read the result it from the execution it gives me "Resource already closed error" as expected since it is a CREATE query. However if I try to add data to table 'SOME_TABLE' it pukes at me and says that the table does not exist. Feels like it is only uploading the query as a function but never executes it. Any ideas? Or even better; TSQL queries that actually works when executing with SQLAlchemy and pymssql.

Thanks,

Improve this question
5
  • Sounds to be like a transaction issue. Did you forget to commit your session at the end? Commented Sep 4, 2013 at 0:04
  • @javex I'm not sure if you need to do that in SQLAlchemy, since oneline queries seems to work properly anyway. If I'm wrong could you point me to sqlalchemy docs, because I tried to find commit there. Commented Sep 4, 2013 at 2:58
  • 1
    The introduction Using the Session is an absolute must-read. In your example, you do a oneline SELECT which does not alter the table and thus does not need a commit. However, if changing something you absolutely need to commit (if not in autocommit-mode). You might want to read up on transaction in general was well. However, it is crucial to understand transaction SQLAlchemy to use the ORM properly. Commented Sep 4, 2013 at 14:47
  • Worked like a charm! Thank you @javex! Add it as an answer if you want me to accept it. Commented Sep 4, 2013 at 23:15
  • I added it as an answer Commented Sep 5, 2013 at 11:56

2 Answers 2

Reset to default
2

You need to commit your pending changes in the Session. For basic understanding of the process read Using the Session. Quick solution:

session.commit()
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Just to clarify for future readers, I was not using sessions at all but instead used the connection from the engine and ran connection.execute() and then there is no commit(). Thus create engine, create session and then use session.execute() and session.commit()
0

TIME and TEXT are reserve words.

I do not know how SQL Alchemy or pymmsql talks to SQL Server. Either native client or ODBC. It eventually all boils down to a tabular data stream (TDS) over a network protocol like TCP/IP.

Check out the reserve word list on TECHNET.

-- Create table ?
IF OBJECT_ID('DBO.SOMETABLE') IS NULL 
  CREATE TABLE DBO.SOMETABLE (MY_TEXT VARCHAR(255), MY_TIME DATETIME);

I use the OBJECT_ID function since it is less typing.

But NOT EXITS works with both SELECT FROM the sys.objects or information_schema.tables with correct WHERE clauses.

Improve this answer

1 Comment

Ok, thanks for the answer, but sorry I just chose those words arbitrarily since the actual names of the columns are not important. I'll update my question with non-keyword names!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.