Alembic migration shows error in mssql in windows server. How do i solve this?

Question

I tried to extract json object from a column in migration. I get this error

__main__ ERROR (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '>'

Here is my code. how do i solve this?

def upgrade():
    op.execute("CREATE VIEW MESSAGE_VIEW AS SELECT "
               "event_messages.message_id,"
               "event_messages.event_id,"
               "event_messages.message,"
               "event_messages.message::json->'message' AS message_input "
               "FROM event_messages "
               "LEFT JOIN chatbot_events ON event_messages.event_id = chatbot_events.event_id;")

Javad Ghasemi · Answer 1 · 2020-08-13T07:13:06.627

1

Your code is incorrect.

you cannot run this command in MSSQL. becuase your command is specific to PostgreSQL.

event_messages.message::json->'message' AS message_input is a JSON Datatype In PostgreSQL that is not supported in MSSQL.

The correct statement for MSSQL is:

def upgrade():
    op.execute("CREATE VIEW MESSAGE_VIEW AS SELECT "
               "event_messages.message_id,"
               "event_messages.event_id,"
               "event_messages.message,"
               "event_messages.message AS message_input "
               "FROM event_messages "
               "LEFT JOIN chatbot_events ON event_messages.event_id = chatbot_events.event_id;")

for more information read this

edited Aug 13 '20 at 07:13

answered Aug 12 '20 at 11:33

Javad Ghasemi

222
1
4

That works on Postgresql. I am actually extracting an object "message" from json data here. – Hasibul Hasan Chowdhury Aug 13 '20 at 06:50
I Edited my answer base on your comment. please re-read it. Thanks – Javad Ghasemi Aug 13 '20 at 07:13
Thanks for your answer. If i want to extract specific object (For example, "incoming_message") form that json data, how do i do that? – Hasibul Hasan Chowdhury Aug 15 '20 at 09:17

Alembic migration shows error in mssql in windows server. How do i solve this?

1 Answers1