CODEWITHSUNDEEP
pythonsql-serverjoinmergesqlalchemy
Hasan Jawad
Hasan Jawad

Reputation: 317

SQL Merge Join two tables from two separate databases

I'm trying to execute a simple merge join query on Microsoft SQL Server, using python SQL Alchemy core.

First let me explain how it's done using the server management studio:

MERGE [Database1].[Schema1].[Table1] table1
USING [Database2].[Schema2].[Table2] table2
ON table1.[Key] = table2.[Key]
WHEN MATCHED THEN   
UPDATE   
SET table1.[Column] = table2.[Column];

This works fine and the Column is set successfully.

But when I do this using python:

DB_TARGET = {
'drivername': 'mssql+pyodbc',
'servername': 'localhost',
'port': '1443',
'username': 'user1',
'password': 'pass',
'driver': 'ODBC Driver 13 for SQL Server',
'trusted_connection': 'yes',  
'legacy_schema_aliasing': False
}

params = urllib.parse.quote_plus("DRIVER={" + DB_TARGET['driver'] + "}" + 
                              ";SERVER=" + DB_TARGET['servername'] +
                              ";UID=" + DB_TARGET['username']+ 
                              ";PWD=" + DB_TARGET['password'])

engine1 = create_engine( DB_TARGET['drivername'] + ":///?odbc_connect=%s" % params)
metadata = MetaData(bind=engine1)
conn = engine1.connect()


q="MERGE [Database1].[Schema1].[Table1] table1 \
USING [Database2].[Schema2].[Table2] table2 \
ON table1.[Key] = table2.[Key] \
WHEN MATCHED THEN \
UPDATE \
SET table1.[Column] = table2.[Column];"

conn.execute(q)
conn.close()

Nothing happens, no exception thrown, and the Column values are not affected.

I have checked SQL Server profiler and made sure that the query is being submitted.

I tried executing more simple queries, like:

q="SELECT * FROM TABLE1"

Works just fine.

What seems to be the problem here ?

Upvotes: 1

Views: 1260

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

SQLAlchemy does not by default recognize the MERGE statement as a data changing operation, and so does not issue a commit when you're using a connection without using a Transaction. You should read "Understanding Autocommit". So either use an explicit transaction:

with conn.begin() as trans:
    conn.execute(q)

or inform SQLAlchemy that this statement should autocommit:

conn.execute(text(q).execution_options(autocommit=True))

As an aside, Python supports multiline strings with triple quotes without backslashes, unless you specifically want to avoid the newlines:

q = """MERGE [Database1].[Schema1].[Table1] table1
USING [Database2].[Schema2].[Table2] table2
ON table1.[Key] = table2.[Key]
WHEN MATCHED THEN
UPDATE
SET table1.[Column] = table2.[Column];"""

Upvotes: 2

Related Questions