Create Engine to Linked MSSQL server

[NN_8935737]

Can anyone help me to understand how to write details for the linked server create engine.

What exact details needs to be mentioned here.

SERVER = ''
DATABASE = ''
DRIVER = 'SQL Server Native Client 11.0'
USERNAME = ''
PASSWORD = ''
DATABASE_URL = f'mssql://{USERNAME}:{PASSWORD}@{SERVER}/{DATABASE}?driver={DRIVER}'
engine = create_engine(DATABASE_URL)
connection = engine.connect()

And i need to use this while doing dataframe.to_sql(). Because when we pass table name along with linked server ip.schema.tablename it throws error.

[Ciprian-Traian]

Hi @NN_8935737 ,

I have did a research on google to see how we can move forward. Here is what I have found :

Modern Pandas versions expect SQLAlchemy engine as a connection, so use SQLAlchemy:
from sqlalchemy import create_engine
con = create_engine('mssql+pyodbc://username:password#myhost:port/databasename?driver=SQL+Server+Native+Client+10.0')

No need to use pyodbc to connect with MSSQL, SQL Alchemy will do that for you.
And also we can insert the data-frame directly into the database without iterating the data-frame using to_sql() method. Here is the code that working fine for me -
# To insert data frame into MS SQL database without iterate the data-frame
import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, select
from six.moves import urllib
params = urllib.parse.quote_plus("DRIVER={SQL
Server};SERVER=serverName;DATABASE=dbName;UID=UserName;PWD=password")
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
engine.connect()
# suppose df is the data-frame that we want to insert in database
df.to_sql(name='table_name',con=engine, index=False, if_exists='append')

Reference doc https://python.developreference.com/article/12788985/Python%3A+Write+Pandas+Dataframe+to+MSSQL+--%3E+Database+Error

https://docs.sqlalchemy.org/en/13/core/engines.html

Hope it helps,

[NN_8935737]

Hi,

Thank you for the detailed information. But my problem is, I'm able to write dataframe to DB table but in this scenario it is creating table in the server given. But i need the data to be inserted to the linked server.

Here is example :

SERVER = 'ABC'
DATABASE = 'ZZZ'
DRIVER = 'SQL Server Native Client 11.0'
USERNAME = ''
PASSWORD = ''
DATABASE_URL = f'mssql://{USERNAME}:{PASSWORD}@{SERVER}/{DATABASE}?driver={DRIVER}'
engine = create_engine(DATABASE_URL)
connection = engine.connect()

and now Table 'Test' is in a linked server('123') to 'ABC'.:

SERVER = '123'
DATABASE = '456'

Table = 'Test'

So when i do df.to_sql(tablename = 'Test') , it loads data in 'ZZZ' database of server 'ABC' where in I need the data in database '456' of server '123'- which is a linked server to 'ABC'